Transactions not working for mysql using php

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 !

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
[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

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.[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

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 :)

re: Transactions not working for mysql using php

You are welcome. See you.

Ronald

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.

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 !!

re: Transactions not working for mysql using php

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 ?

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)

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.



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 !

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 .. :)

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
4) Now to check if you table was created under InnoDB engine or no, write the following query in your console.
5) If you do not get the expected results, then goto mysql console and write

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 ::

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 !

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

re: Transactions not working for mysql using php

heheheh.. sure mayne :)
cheers !!

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