How to make autoincrement after checking a unique index

Asked by NT Man on 2012-11-06

How to make autoincrement after checking a unique index. In the construction of ON DUPLICATE KEY UPDATE if occurs update, counter is incremented by One unit resulting in inefficient use of the field.

I am also know about "innodb_autoinc_lock_mode = 0" parameter, but I am here this is bad for database.

Question information

English Edit question
MariaDB Edit question
No assignee Edit question
Last query:
Last reply:
Sergei Golubchik (sergii) said : #1

Did you actually *try* what you're saying and observed the "inefficient use of the field"? Or it's just a hearsay?
I've seen these blog posts too, but they were about MySQL, not MariaDB.

We have done certain improvements in this area (auto-increment in the presence of unique key conflicts), so it is qiute possible that this MySQL issue does not apply to MariaDB at all.

NT Man (mikhail-v-gavrilov) said : #2

I am use MariaDB. How to stop auto-increment on upgrade record?

Michael Widenius (monty) said : #3

Can you please provide a full example (that we can trivially repeat) of the problem you have.

Note that in most cases the easiest solution is to just make the auto-increment column a bigint, in which case there is no risk of overflow.

Sergei Golubchik (sergii) said : #4

create table t1 (a int primary key auto_increment, b int unique, c varchar(20)) engine=innodb;
insert t1 (b) values (10),(20),(30);
insert t1 (b) values (20) on duplicate key update c='!';
insert t1 (b) values (40);
select * from t1;

Sergei Golubchik (sergii) said : #6

This is the innodb bug. It will not be fixed, MySQL considers this an "expected behavior". More information in the MySQL bug tracker:

NT Man (mikhail-v-gavrilov) said : #7

So, I am use MariaDB 5.5.28 and this issue is present there.

Sergei Golubchik (sergii) said : #8

Yes, that's what I said. We do not develop InnoDB (Oracle does) or XtraDB (Percona does).
Oracle developers decided that this behavior of InnoDB is not a bug, they will not fix it.

You may try to convince them or submit a bug on launchpad for Percona Server project and try to convince Percona developers.
Although I think it is unlikely, if the original InnoDB developers intentionally implemented this behavior, they must've had a good reason for that.

Can you help with this problem?

Provide an answer of your own, or ask NT Man for more information if necessary.

To post a message you must log in.