CODEWITHSUNDEEP
phpmysqlasp.netsql
Ahmed
Ahmed

Reputation: 43

mysql transaction not working with innodb tables

i write this but transaction not working and i also convert both tables in innodb type can any one guide me whats wrong in my coding or another alternative of transaction.

mysql_query("begin;");
  $query1 = mysql_query("ALTER TABLE products ADD COLUMN {$_POST[fields]} VARCHAR(60)");

  $query2 = mysql_query("INSERT INTO fields (cid5,fields,field_title,field_type)
                      VALUE ('$_POST[cid]','$_POST[fields]','$_POST[field_title]','$_POST[field_type]')");                

if (($query1)&&($query2)) {mysql_query("commit;");}
else {mysql_query("rollback;");}

}

i am using mysql 5.1.69-cll

Upvotes: 0

Views: 518

Answers (1)

Mark Baker
Mark Baker

Reputation: 212412

ALTER TABLE is a DDL (Data Definition Language) statement; which is not transactional in MySQL innodb engine. INSERT is a DML statement (Data Manipulation Language), which is transactional. Because one statement isn't transactional and one is, the two shouldn't be combined in a transaction.

Quoting from the MySQL manual:

Some statements cannot be rolled back. In general, these include data definition language (DDL) statements, such as those that create or drop databases, those that create, drop, or alter tables or stored routines.

You should design your transactions not to include such statements. If you issue a statement early in a transaction that cannot be rolled back, and then another statement later fails, the full effect of the transaction cannot be rolled back in such cases by issuing a ROLLBACK statement.

http://dev.mysql.com/doc/refman/5.6/en/cannot-roll-back.html

Upvotes: 1

Related Questions