note 96658 added to mysqli.rollback

Just a note about auto incremental ids and rollback. When using transactions and inserting into a table containing a column with auto incremental ids, the id will be incremented even though the transaction is rolled back. This might occupy a lot of ids if a lot of rollbacks are performed. Example: <?php $mysqli = new mysqli("localhost", "gugbageri", "gugbageri", "gugbageri"); /* check connection */ if (mysqli_connect_errno()) { printf("Connect failed: %s\n", mysqli_connect_error()); exit(); } /* disable autocommit */ $mysqli->autocommit(FALSE); /* We just create a test table with one auto incremental primary column and a content column*/ $mysqli->query("CREATE TABLE TestTable ( id_column INT NOT NULL AUTO_INCREMENT , content INT NOT NULL , PRIMARY KEY ( id_column )) ENGINE = InnoDB;"); /* commit newly created table */ $mysqli->commit(); /* we insert a row */ $mysqli->query("INSERT INTO TestTable (content) VALUES (99)"); /* we commit the inserted row */ $mysqli->commit(); /* we insert another three rows */ $mysqli->query("INSERT INTO TestTable (content) VALUES (99)"); $mysqli->query("INSERT INTO TestTable (content) VALUES (99)"); $mysqli->query("INSERT INTO TestTable (content) VALUES (99)"); /* we the rollback */ $mysqli->rollback(); /* we insert a row */ $mysqli->query("INSERT INTO TestTable (content) VALUES (99)"); /* we commit the inserted row */ $mysqli->commit(); if ($result = $mysqli->query("SELECT id_column FROM TestTable")) { while($row = $result->fetch_row()) { printf("Id: %d.\n", $row[0]); } /* Free result */ $result->close(); } /* Drop table TestTable */ $mysqli->query("DROP TABLE TestTable"); $mysqli->close(); ?> This will output: Id: 1. Id: 5. ---- Server IP: 69.147.83.197 Probable Submitter: 130.225.243.71 ---- Manual Page -- http://www.php.net/manual/en/mysqli.rollback.php Edit -- https://master.php.net/note/edit/96658 Del: integrated -- https://master.php.net/note/delete/96658/integrated Del: useless -- https://master.php.net/note/delete/96658/useless Del: bad code -- https://master.php.net/note/delete/96658/bad+code Del: spam -- https://master.php.net/note/delete/96658/spam Del: non-english -- https://master.php.net/note/delete/96658/non-english Del: in docs -- https://master.php.net/note/delete/96658/in+docs Del: other reasons-- https://master.php.net/note/delete/96658 Reject -- https://master.php.net/note/reject/96658 Search -- https://master.php.net/manage/user-notes.php