Bug #66807 Wrong AUTO_INCREMENT value when using ON DUPLICATE KEY UPDATE clause
Submitted: 13 Sep 2012 12:34 Modified: 13 Sep 2012 17:09
Reporter: Matteo Tassinari Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.1.41 OS:Windows (Windows 7 32bit)
Assigned to: CPU Architecture:Any

[13 Sep 2012 12:34] Matteo Tassinari
Description:
When inserting multiple rows on a table which has a UNIQUE constraint defined, a column which is an AUTO_INCREMENT value, and using the ON DUPLICATE KEY UPDATE clause, if an insert is converted in update due to the violation of a unique constraint, the autoincrement value is incremented all the same.

How to repeat:
Prepare the table, here is the one on which I verified the problem:

CREATE TABLE `lin_98_4_sgs_formazione_elearning_statistiche` (
  `id_statistica` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_allegato` int(10) unsigned DEFAULT '0',
  `id_elearning` int(10) unsigned DEFAULT '0',
  `id_dipendente` int(10) unsigned DEFAULT '0',
  `id_sgs_formazione` int(10) unsigned DEFAULT '0',
  `id_azienda` int(10) unsigned NOT NULL DEFAULT '1',
  `id_sede` int(10) unsigned NOT NULL DEFAULT '1',
  `revisione_documento` int(10) unsigned NOT NULL DEFAULT '0',
  `data_letto` datetime DEFAULT NULL,
  `tempo_letto` smallint(5) unsigned DEFAULT '0',
  PRIMARY KEY (`id_statistica`,`id_azienda`,`id_sede`,`revisione_documento`),
  UNIQUE KEY `data_letto` (`data_letto`,`id_allegato`,`id_elearning`,`id_dipendente`,`id_sgs_formazione`,`id_azienda`,`id_sede`,`revisione_documento`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

Do some inserts which will cause the ON DUPLICATE KEY UPDATE clause to come in effect:

INSERT INTO lin_98_4_sgs_formazione_elearning_statistiche (
  `id_allegato`,
  `id_elearning`,
  `id_dipendente`,
  `id_sgs_formazione`,
  `id_azienda`,
  `id_sede`,
  `revisione_documento`,
  `data_letto`
) VALUES (
  7,
  6,
  432,
  7,
  3,
  12,
  0,
  '2012-09-13 11:50:40'
), (
  7,
  6,
  432,
  7,
  3,
  12,
  0,
  '2012-09-13 11:50:40'
), (
  7,
  6,
  432,
  7,
  3,
  12,
  0,
  '2012-09-13 11:50:40'
), (
  7,
  6,
  432,
  7,
  3,
  12,
  0,
  '2012-09-13 11:50:40'
)
ON DUPLICATE KEY UPDATE data_letto=VALUES(data_letto);

At this point there is one row inserted with id_statistica = 1, the other inserts where converted in updates.

Now do another insert:

INSERT INTO lin_98_4_sgs_formazione_elearning_statistiche (
  `id_allegato`,
  `id_elearning`,
  `id_dipendente`,
  `id_sgs_formazione`,
  `id_azienda`,
  `id_sede`,
  `revisione_documento`,
  `data_letto`
) VALUES (
  7,
  6,
  432,
  7,
  3,
  12,
  0,
  '2012-09-13 11:50:45'
)
ON DUPLICATE KEY UPDATE data_letto=VALUES(data_letto);

Now the table contains two rows, the first with id_statistica = 1 and the second with id_statistica = 5

Suggested fix:
The second inserted row, in this specific example, should have id_statistica = 2
[13 Sep 2012 12:52] Peter Laursen
I think you should read this:
http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_autoinc_lock_m...
and
http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html

(and besides there is no rule in SQL databases that there cannot be 'gaps' in values assigned to an auto_increment column).

Unless I overlook some detail this is the same as was reported here at least 10 times before - and in all cases rejected as 'not a bug'.  But you can achieve the behavior you want by setting innodb_autoinc_lock_mode to "0" in configuration.  

As far as I know "1" is now default as it improves some replication scenarios.  But if you don't use replication, it does not matter of course.

Peter
(not a MySQL/Oracle person)
[13 Sep 2012 17:09] Sveta Smirnova
Thank you for the report.

Peter is absolutely correct: this is not a bug. So I am closing it as such.