424,303 Members | 1,339 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,303 IT Pros & Developers. It's quick & easy.

Transactions not working for mysql using php

realin
100+
P: 254
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 :
[PHP]
<?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();
}
?>[/PHP]

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 !
Apr 27 '08 #1
Share this Question
Share on Google+
14 Replies


realin
100+
P: 254
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
[PHP]
<?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";

?>[/PHP]

please help me thanks
Apr 27 '08 #2

realin
100+
P: 254
please guys i need to know this ..
please
May 7 '08 #3

ronverdonk
Expert 2.5K+
P: 4,258
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.[php]<?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>";
}
?>[/php]Ronald
May 7 '08 #4

code green
Expert 100+
P: 1,726
Are the tables InnoDB?
May 9 '08 #5

realin
100+
P: 254
hiya ronald,

thanks for the reply, will go home and check it out..

much of thanks :)
May 9 '08 #6

ronverdonk
Expert 2.5K+
P: 4,258
hiya ronald,

thanks for the reply, will go home and check it out..

much of thanks :)
You are welcome. See you.

Ronald
May 10 '08 #7

realin
100+
P: 254
hiya again Ronald,

Sorry to bother you, but this piece of code aint working for me..

here my System specs.

Expand|Select|Wrap|Line Numbers
  1. OS :: Windows Vista 
  2. Using XAMPP latest version so, 
  3.  
  4. PHP Version 5.2.5
  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 10 '08 #8

realin
100+
P: 254
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 ?

Expand|Select|Wrap|Line Numbers
  1. 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)

Expand|Select|Wrap|Line Numbers
  1. 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.

Expand|Select|Wrap|Line Numbers
  1. 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 10 '08 #9

realin
100+
P: 254
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 10 '08 #10

realin
100+
P: 254
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
Expand|Select|Wrap|Line Numbers
  1. 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.
Expand|Select|Wrap|Line Numbers
  1. SHOW TABLE STATUS FROM test2 LIKE 'pop'
5) If you do not get the expected results, then goto mysql console and write

Expand|Select|Wrap|Line Numbers
  1. SHOW ENGINES
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 ::
Expand|Select|Wrap|Line Numbers
  1. # Comment the following if you are using InnoDB tables
  2. #skip-innodb
  3. innodb_data_home_dir = "D:/xamp/mysql/"
  4. innodb_data_file_path = ibdata1:10M:autoextend
  5. innodb_log_group_home_dir = "D:/xamp/mysql/"
  6. innodb_log_arch_dir = "D:/xamp/mysql/"
  7. ## You can set .._buffer_pool_size up to 50 - 80 %
  8. ## of RAM but beware of setting memory usage too high
  9. innodb_buffer_pool_size = 16M
  10. innodb_additional_mem_pool_size = 2M
  11. ## Set .._log_file_size to 25 % of buffer pool size
  12. innodb_log_file_size = 5M
  13. innodb_log_buffer_size = 8M
  14. innodb_flush_log_at_trx_commit = 1
  15. innodb_lock_wait_timeout = 50
  16.  

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 11 '08 #11

code green
Expert 100+
P: 1,726
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 12 '08 #12

realin
100+
P: 254
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 12 '08 #13

ronverdonk
Expert 2.5K+
P: 4,258
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
May 14 '08 #14

P: 1
Hi Ronald,
Why do I need to use "SET AUTOCOMMIT=0" if "BEGIN" can do it default. I wrote 2 pages where transactions work on one page without "SET AUTOCOMMIT=0" and on another page I need to use "SET AUTOCOMMIT=0".

Do you have any idea, why this is happening?

Actually issue is for rollback. Rollback not removing entries after begin statement from table.
Jan 15 '19 #15

Post your reply

Sign in to post your reply or Sign up for a free account.