Details
Trivial
Description
create table t1 (a int not null auto_increment primary key, val int);
insert into t1 (val) values (1);
update t1 set a=2 where a=1;
insert into t1 (val) values (1);
leads to:
drizzletest: At line 307: query 'insert into t1 (val) values (1)' failed: 1062: Duplicate entry '2' for key 'PRIMARY'
For InnDB this fails, for MyISAM, it passes as MyISAM updates the auto increment value.
Gliffy Diagrams
Attachments
Issue Links
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Patch that mimicks the MyISAM behaviour in doUpdateRecord() for innobase plugin.
drizzle patch (should be easy to apply to others)
LPexportBug314570_innodb_autoinc_update.patch
Re: update is not changing internal auto increment value
Patch that mimicks the MyISAM behaviour in doUpdateRecord() for innobase plugin.
Re: update is not changing internal auto increment value
All development of XtraDB has moved under the Percona Server project - https://launchpad.net/percona-server - If this bug can be reproduced against current Percona Server, please file this bug against percona-server (you can simply do so by using the "Also affects project" link above).
Thanks,
Stewart Smith
Director of Server Development
Percona.
Launchpad bug id: 314570
It's now documented as a feature in MySQL manual (e.g. http://dev.mysql.com/doc/refman/5.5/en/example-auto-increment.html):
For InnoDB tables, be careful if you modify the column containing the auto-increment value in the middle of a sequence of INSERT statements. For example, if you use an UPDATE statement to put a new, larger value in the auto-increment column, a subsequent INSERT could encounter a “Duplicate entry” error. The test whether an auto-increment value is already present occurs if you do a DELETE followed by more INSERT statements, or when you COMMIT the transaction, but not after an UPDATE statement.
update is not changing internal auto increment value
broken.auto_increment [ fail ]
drizzletest: At line 307: query 'insert into t1 (val) values (1)' failed: 1062: Duplicate entry '2' for key 'PRIMARY'
The result from queries just before the failure was:
< snip >
a b
2 2
3 1
4 3
drop table t1;
create table t1 (rowid int not null auto_increment, val int not null,primary
key (rowid), unique(val));
replace into t1 (val) values ('1'),('2');
replace into t1 (val) values ('1'),('2');
insert into t1 (val) values ('1'),('2');
ERROR 23000: Duplicate entry '1' for key 'val'
select * from t1;
rowid val
3 1
4 2
drop table t1;
create table t1 (a int not null auto_increment primary key, val int);
insert into t1 (val) values (1);
update t1 set a=2 where a=1;
insert into t1 (val) values (1);