Transactions not working for mysql using php

[realin]

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 !

[realin]

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

[realin]

please guys i need to know this ..
please

[ronverdonk]

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

[code green]

Are the tables InnoDB?

[realin]

hiya ronald,

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

much of thanks :)

[ronverdonk]

hiya ronald,

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

much of thanks :)

You are welcome. See you.

Ronald

[realin]

hiya again Ronald,

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

here my System specs.

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

[realin]

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 ?

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)

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.

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 !

[realin]

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

[realin]

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

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.

SHOW TABLE STATUS FROM test2 LIKE 'pop'

5) If you do not get the expected results, then goto mysql console and write

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

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

[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

[realin]

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

[ronverdonk]

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

[autocommit]

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.