List:General Discussion« Previous MessageNext Message »
From:Proemial Date:August 7 2009 1:55pm
Subject:Problems with auto_increment updating when (i think) it shouldn't
View as plain text  
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.
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