Transactions not working for mysql using php
Question posted by: realin
(Familiar Sight)
on
April 27th, 2008 05:18 PM
Hiya all,
after my 1-2 weeks research i finally found a more consistent way to fire mysql queries in php i.e. transactions. I have two options to insert data in multiple tables ::
1) through procedures
2) through transactions
Of course i am going to prefer is the 2 way i.e. transactions. I went thru all the manuals of which Ronald gave me the links. But reading many forums and tutorials, i land up here again just to know why my code wont output the way i want. Just have a look at my code :
Code: ( text )
<?php try { $dbh = new PDO('mysql:host=localhost;port=3306;dbname=test', 'root', '', array( PDO::ATTR_PERSISTENT => true)); $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $dbh->beginTransaction(); $dbh->exec("update pop set age=1 where id=1"); $dbh->rollBack(); } catch (PDOException $e) { print "Error!: " . $e->getMessage() . "<br/>"; die(); } ?>
It wont rollback, as written in code, it should not affect the age column of the table pop, because it meets the $dbh->rollback() function, but it will automaticaly commit.
please help me ..
I want the autoCommit thing to turn off using php.
cheers !!
Realin !
Would you like to answer this question?
Sign up for a free account, or Login (if you're already a member).
|
|
April 27th, 2008 06:52 PM
# 2
|
Re: Transactions not working for mysql using php
i tried the conventional way, but even that wont help..
the change simply reflects in the database, it wont rollback :(
here is one more code, which i tried
Code: ( text )
<?php $con=mysql_connect("localhost","root",""); mysql_select_db("test"); $res=mysql_query("START TRANSACTION",$con); if($res) echo "transaction started \n"; $res=mysql_query("update pop set age=90 where id=2"); if($res) echo "Query Executed \n"; $res=mysql_query("ROLLBACK"); if($res) echo "Rolled back"; ?>
please help me thanks
Re: Transactions not working for mysql using php
please guys i need to know this ..
please
Re: Transactions not working for mysql using php
I think the AUTOCOMMIT statement has to do with this. The following sample works for me, so give it a try. I used a $debug variable to be able to switch between rollback and commit modes.
Code: ( text )
<?php $debug=1; $con=mysql_connect("localhost","xxx","yyy") or die("Connect error: ".mysql_error()); mysql_select_db("zzz") or die("Select db error: ".mysql_error()); $res=mysql_query("SET AUTOCOMMIT=0",$con) or die("Set autocommit error: ".mysql_error()); $res=mysql_query("START TRANSACTION",$con) or die("Start xact error: ".mysql_error()); echo "transaction started <br>"; $res=mysql_query("update a set yyyy='xest' where id > 5") or die("Update error: ".mysql_query()); echo "Query Executed <br>"; if ($debug) { $res=mysql_query("ROLLBACK") or die("Rollback error: ".mysql_query()); echo "Rolled back<br>"; } else { $res=mysql_query("COMMIT") or die("Commit error: ".mysql_query()); echo "Committed<br>"; } ?>
Ronald
__________________
RTFM is an almost extinct art form.
Re: Transactions not working for mysql using php
Are the tables InnoDB?
Re: Transactions not working for mysql using php
hiya ronald,
thanks for the reply, will go home and check it out..
much of thanks :)
|
|
May 10th, 2008 01:23 PM
# 7
|
Re: Transactions not working for mysql using php
Quote:
Originally Posted by realin
hiya ronald,
thanks for the reply, will go home and check it out..
much of thanks :)
|
You are welcome. See you.
Ronald
|
|
|
May 10th, 2008 06:39 PM
# 8
|
Re: Transactions not working for mysql using php
hiya again Ronald,
Sorry to bother you, but this piece of code aint working for me..
here my System specs.
Code: ( text )
OS :: Windows Vista Using XAMPP latest version so, PHP Version 5.2.5 MYSQL Client API version 5.0.51a
I pasted your code, changed DB Details, and table details.. It showed Rolled back in browser but actually affected the databse :(
I am not actually able to perform transactions on mysql straight a way without having to use PHP even ..
What could be the problem ?
thanks for ur replies :)
cheers !!
|
|
|
May 10th, 2008 07:00 PM
# 9
|
Re: Transactions not working for mysql using php
Quote:
Originally Posted by code green
Are the tables InnoDB?
|
hiya code green,
how do i check if my tables are innoDB or no..
cause i tried creating a new database and then executing the following command, and it should (as i believe) create a table under InnoDB engine,
please see if that is perfect ?
Code: ( text )
CREATE TABLE pop (id INT, age vachar (20), INDEX (id)) ENGINE=InnoDB;
thanks
Cheers!!
Realin !
############# EDIT ###############
hey hey,
i guess i got the issue here,
well i am using two tools to create tables and to manipulate mysql database, they are
Mysql query tools
phpmyadmin
Now when i fired the query (read from mysql website)
Code: ( text )
SHOW TABLE STATUS FROM test2 LIKE 'pop'
i could figure out that, the line of code written below does not create table under InnoDB engine, rather it creates table under MyISAM engine, which does not support TRANSACTIONS.
Code: ( text )
CREATE TABLE pop (id INT, age vachar (20), INDEX (id)) ENGINE=InnoDB;
pheeeeeeeeewwwww (deep breathe)
Well, i guess now i gotta search a way to make tables in InnoDB instead of MyISAM engine..
When i try altering the table, it just do not let me do that and instead gives an error saying
2014 Commands out of sync; you can't run this command now
well, if some gets to know, what this all is happening, i will be thankful :)
Ronald, i really thank you mann for every single LOC :0
thanks & cheers to every1 :)
Realin !
|
|
|
May 10th, 2008 07:36 PM
# 10
|
Re: Transactions not working for mysql using php
All right,
I m trying it from command line, even then i am unable to create a table under InnoDB engine, well why so ?
trust me, i never bothered about the engine in the last 2 years :p
Now i come to know, why these things are brought into existence .. hehehe .. :)
|
|
|
May 11th, 2008 05:15 AM
# 11
|
Re: Transactions not working for mysql using php
FINAL WORKAROUND
hiya guys,
everything is cool and is working like a charm, thanks to everyone making an effort for this thread :)
Would like to jot up steps for other who are facing same issues ..
1) Learn what transactions are, and why do you need them ?
3) Create a new table under InnoDB engine and try to implement transaction code, given above my Ronald. (that is the simplest and best way to test).
To create simple InnoDB table
Code: ( text )
CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) ENGINE=INNODB;
4) Now to check if you table was created under InnoDB engine or no, write the following query in your console.
Code: ( text )
SHOW TABLE STATUS FROM test2 LIKE 'pop'
5) If you do not get the expected results, then goto mysql console and write
Code: ( text )
this will tell what all engines are available and what are their status, in my case InnoDB was disables. Hence i gotta enable it to make the transaction working.
6) Well to enable the engines, you gotta open my.cnf which is a configuration file of MYSQL database, it can be found under mysql_install_folder/bin directory.
7) open my.cnf in your favorite text editor and search for the buzz work InnoDB
You show be able to see some commented lines (prefixed with #).
The configuration should look something like this ::
Code: ( text )
# Comment the following if you are using InnoDB tables #skip-innodb innodb_data_home_dir = "D:/xamp/mysql/" innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = "D:/xamp/mysql/" innodb_log_arch_dir = "D:/xamp/mysql/" ## You can set .._buffer_pool_size up to 50 - 80 % ## of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 16M innodb_additional_mem_pool_size = 2M ## Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 5M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50
Now all is set to work, just create table in InnoDB and you can use the transactions like a Bank does :p
cheers !!
Any help required let me know :)
Realin !
|
|
May 12th, 2008 08:46 AM
# 12
|
Re: Transactions not working for mysql using php
Glad you worked it out Realin.
I have only used transactions in mssql.
MySql tables are ISAM by default and do not support transactions.
I will archive your post.
I am sure I will make use of it the day I brave transactions in MySql
|
|
|
May 12th, 2008 01:16 PM
# 13
|
Re: Transactions not working for mysql using php
Quote:
Originally Posted by code green
Glad you worked it out Realin.
I have only used transactions in mssql.
MySql tables are ISAM by default and do not support transactions.
I will archive your post.
I am sure I will make use of it the day I brave transactions in MySql
|
heheheh.. sure mayne :)
cheers !!
|
|
|
May 14th, 2008 11:12 PM
# 14
|
Re: Transactions not working for mysql using php
I am glad it worked out for you in the end. I am sorry, but I just forgot to mention that all my tables (usually) are created engine=InnoDB. Until next time.
Ronald
__________________
RTFM is an almost extinct art form.
Not the answer you were looking for? Post your question . . .
170,099 Experts ready to help you find a solution.
Sign up for a free account, or Login (if you're already a member).
|
|
|
Top PHP Forum Contributors
|
