It will also update the auto_increment column when you ROLLBACK a failed insert:
mysql> USE test; Database changed mysql> SELECT * FROM t1\G Empty set (0.00 sec) mysql> DROP TABLE t1; Query OK, 0 rows affected (0.06 sec) mysql> mysql> CREATE TABLE t1( -> id INT UNSIGNED NOT NULL AUTO_INCREMENT, -> c1 VARCHAR(255), -> PRIMARY KEY(id) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.13 sec) mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t1(c1) VALUES('TEST1'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO t1(c1) VALUES('TEST2'); Query OK, 1 row affected (0.00 sec) mysql> ROLLBACK; Query OK, 0 rows affected (0.02 sec) mysql> SHOW CREATE TABLE t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `c1` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB *AUTO_INCREMENT=3* DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> SELECT * FROM t1\G Empty set (0.00 sec) mysql> INSERT INTO t1(c1) VALUES('TEST3'); Query OK, 1 row affected (0.03 sec) mysql> SELECT * FROM t1\G *************************** 1. row *************************** id: 3 c1: TEST3 1 row in set (0.00 sec) mysql> I believe this is how it has to work. In the event that I start a transaction, then another transaction starts, mine fails, the other completes and commit's, it has to get ID #3 and not ID #1. At the time the transaction was taking place, ID #1 and #2 were in use. Essentially, your SQL statement is a single transaction with AUTO_COMMIT set to '1'. On Fri, Aug 7, 2009 at 8:55 AM, Proemial <proem...@gmail.com> wrote: > Hey folks. I'm getting some weird behaviour out of Auto_increment. > If I enter a attempt to INSERT a row into a table with a UNIQUE index, > where the insert would violate uniqueness of existing data, I'm seeing > the auto_increment increase even though the insert fails. > > The server in question is 5.1.34 running as master. Slave is also 5.1.34. > > First noticed through a script operating over ODBC, but replicated by > hand through the query browser. > > I couldn't see anything in the ref manual stating this as standard > behaviour -- but I easily could have missed something there. Can > someone point me in the right direction? > > Thank you! > Martin > > Using Mysql 5.1.34 > TEST CASE: > > CREATE TABLE `test`.`test_table` ( > `id` int(10) unsigned NOT NULL AUTO_INCREMENT, > `name` varchar(45) NOT NULL, PRIMARY KEY (`id`), > UNIQUE KEY `index_2` (`name`) > ) > ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1; > > insert some values > > ============ > 1, 'test' > 2, 'test2' > 3, 'test3' > ============ > > SHOW TABLE STATUS > Name test_table > Engine InnoDB > Version 10 > Row_format Compact > Rows 3 > Avg_row_length 5461 > Data_length 16384 > Max_data_length 0 > Index_length 16384 > Data_free 0 > Auto_increment 4 > Create_time 2009-08-07 09:33:04 > Update_time > Check_time > Collation latin1_swedish_ci > Checksum > Create_options > Comment > > ----------- > INSERT INTO test.test_table (name) VALUES ('test') > > SHOW TABLE STATUS > Name test_table > ... > Auto_increment 5 > > ----------- > INSERT IGNORE test.test_table (name) VALUES ('test') > > SHOW TABLE STATUS > Name test_table > ... > Auto_increment 6 > > > > -- > --- > This is a signature. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=joh...@pixelated.net > > -- ----------------------------- Johnny Withers 601.209.4985 joh...@pixelated.net