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
