How to make autoincrement after checking a unique index

Asked by Mikhail Gavrilov

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

Language:
English Edit question
Status:
Answered
For:
MariaDB Edit question
Assignee:
No assignee Edit question
Last query:
Last reply:
Revision history for this message
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.

Revision history for this message
Mikhail Gavrilov (mikegav) said :
#2

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

Revision history for this message
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.

Revision history for this message
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;

Revision history for this message
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: http://bugs.mysql.com/bug.php?id=28781

Revision history for this message
Mikhail Gavrilov (mikegav) said :
#7

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

Revision history for this message
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 Mikhail Gavrilov for more information if necessary.

To post a message you must log in.