Hmm, that makes sense. I should have thought of that. Thanks! On Fri, Aug 7, 2009 at 12:32 PM, Johnny Withers<joh...@pixelated.net> wrote: > 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 >
-- --- This is a signature. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org