List:General Discussion« Previous MessageNext Message »
From:Proemial Date:August 7 2009 4:41pm
Subject:Re: Problems with auto_increment updating when (i think) it shouldn't
View as plain text  
Hmm, that makes sense.  I should have thought of that.  Thanks!

On Fri, Aug 7, 2009 at 12:32 PM, Johnny Withers<johnny@stripped> 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 <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
>



-- 
---
This is a signature.
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