note 105579 added to mysqli.multi-query

Please note that MySQL does not support Transactions on DDL statements. <?php $link = mysqli_connect('localhost', 'user', 'pw', 'db'); $script = "INSERT INTO T1('pk', 'val') VALUES('1', 'A');"; $script .= "INSERT INTO T1('pk', 'val') VALUES('2', 'B');" $script .= "INSERT INTO T1('pk', 'val') VALUES('3', 'C');" $script .= "ALTER TABLE T1 ADD COLUMN foo INT(1) NOT NULL;"; $script .= "UPDATE T1 SET val = 'D' where pkkkkkkkkkkkk = '1'"; // Note that this statement will force a SQL Error since column pkkkkkkkkkkkk doesn't exist mysqli_autocommit($link, false); // set autocommit to false mysqli_multi_query($link, $script); // execute statements mysqli_rollback($link); // rollback all statements ?> I would expect that the entire set of statements would not occur since the last update would generate an error. However after the code execution you'll see the values on table T1. I've spent a couple of days to find that this behavior is normal in MySQL since the ALTER TABLE statement automatically commits the transaction. See more in http://dev.mysql.com/doc/refman/5.0/en/implicit-commit.html ---- Server IP: 194.8.30.16 Probable Submitter: 93.102.160.31 ---- Manual Page -- http://www.php.net/manual/en/mysqli.multi-query.php Edit -- https://master.php.net/note/edit/105579 Del: integrated -- https://master.php.net/note/delete/105579/integrated Del: useless -- https://master.php.net/note/delete/105579/useless Del: bad code -- https://master.php.net/note/delete/105579/bad+code Del: spam -- https://master.php.net/note/delete/105579/spam Del: non-english -- https://master.php.net/note/delete/105579/non-english Del: in docs -- https://master.php.net/note/delete/105579/in+docs Del: other reasons-- https://master.php.net/note/delete/105579 Reject -- https://master.php.net/note/reject/105579 Search -- https://master.php.net/manage/user-notes.php