Problems with auto_increment updating when (i think) it shouldn"t

Problems with auto_increment updating when (i think) it shouldn"t

am 07.08.2009 15:55:19 von Proemial

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=gcdmg-mysql-2@m.gmane.org

Re: Problems with auto_increment updating when (i think) it shouldn"t

am 07.08.2009 18:32:11 von Johnny Withers

--0016e6d6487d3a422604708fc74e
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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 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=johnny@pixelated.net
>
>


--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--0016e6d6487d3a422604708fc74e--

Re: Problems with auto_increment updating when (i think) it shouldn"t

am 07.08.2009 18:41:46 von Proemial

Hmm, that makes sense. I should have thought of that. Thanks!

On Fri, Aug 7, 2009 at 12:32 PM, Johnny Withers 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(
>   =A0 -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
>   =A0 -> c1 VARCHAR(255),
>   =A0 -> PRIMARY KEY(id)
>   =A0 -> ) ENGINE=3DInnoDB;
> 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` (
> =A0 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
> =A0 `c1` varchar(255) DEFAULT NULL,
> =A0 PRIMARY KEY (`id`)
> ) ENGINE=3DInnoDB AUTO_INCREMENT=3D3 DEFAULT CHARSET=3Dlatin1
> 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 th=
e
> 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 wrote:
>>
>> Hey folks. =A0I'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. =A0Slave 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. =A0Can
>> someone point me in the right direction?
>>
>> Thank you!
>> Martin
>>
>> Using Mysql 5.1.34
>> TEST CASE:
>>
>> CREATE TABLE =A0`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=3DInnoDB AUTO_INCREMENT=3D4 DEFAULT CHARSET=3Dlatin1;
>>
>> insert some values
>>
>> ============
>> 1, 'test'
>> 2, 'test2'
>> 3, 'test3'
>> ============
>>
>> SHOW TABLE STATUS
>> Name =A0 =A0test_table
>> Engine =A0InnoDB
>> Version 10
>> Row_format =A0 =A0 =A0Compact
>> Rows =A0 =A03
>> Avg_row_length =A05461
>> Data_length =A0 =A0 16384
>> Max_data_length 0
>> Index_length =A0 =A016384
>> Data_free =A0 =A0 =A0 0
>> Auto_increment =A04
>> Create_time =A0 =A0 2009-08-07 09:33:04
>> Update_time
>> Check_time
>> Collation =A0 =A0 =A0 latin1_swedish_ci
>> Checksum
>> Create_options
>> Comment
>>
>> -----------
>> INSERT INTO test.test_table (name) VALUES ('test')
>>
>> SHOW TABLE STATUS
>> Name =A0 =A0test_table
>> ...
>> Auto_increment =A05
>>
>> -----------
>> INSERT IGNORE test.test_table (name) VALUES ('test')
>>
>> SHOW TABLE STATUS
>> Name =A0 =A0test_table
>> ...
>> Auto_increment =A06
>>
>>
>>
>> --
>> ---
>> This is a signature.
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Djohnny@pixel=
ated.net
>>
>
>
>
> --
> -----------------------------
> Johnny Withers
> 601.209.4985
> johnny@pixelated.net
>



--=20
---
This is a signature.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg