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/[email protected]
