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
