note 96658 deleted from mysqli.rollback by danbrown

Note Submitter: Ove Andersen ---- 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.