List:General Discussion« Previous MessageNext Message »
From:Johnny Withers Date:August 7 2009 4:32pm
Subject:Re: Problems with auto_increment updating when (i think) it shouldn't
View as plain text  
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 <proemial@stripped> 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=1
>
>


-- 
-----------------------------
Johnny Withers
601.209.4985
johnny@stripped

Thread
Problems with auto_increment updating when (i think) it shouldn'tProemial7 Aug
  • Re: Problems with auto_increment updating when (i think) it shouldn'tJohnny Withers7 Aug
    • Re: Problems with auto_increment updating when (i think) it shouldn'tProemial7 Aug