在mysql中插入单行会导致行重复

Question

In a PHP script I perform the following:

SELECT t1.*, t2.flag_contenuto
FROM lin_98_4_sgs_formazione_elearning_allegati as t1
JOIN agews_elearning_allegati as t2 USING(id_allegato, id_elearning)
WHERE id_dipendente='432'
AND id_sgs_formazione='7'
AND id_azienda='3'
AND id_sede='12'
AND revisione_documento='0'
AND id_allegato='7'

to retrieve some specific informations I need, then I determine the current timestamp ad date('Y-m-d H:i:s') and do this:

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:39' -- timestamp derived as described above
)
ON DUPLICATE KEY UPDATE data_letto=VALUES(data_letto)

Lastly, I perform this select and then var_dump() its output:

SELECT MAX(id_statistica)
FROM lin_98_4_sgs_formazione_elearning_statistiche
WHERE `id_allegato` = 7
AND `id_elearning` = 6
AND `id_dipendente` = 432
AND `id_sgs_formazione` = 7
AND `id_azienda` = 3
AND `id_sede` = 12
AND `revisione_documento` = 0
AND `data_letto` = '2012-09-13 11:50:39'

Which should get me the ID of the last inserted row.

I'm doing this specific query instead of using some more specific function because I noticed that sometimes the script would insert two rows insted of one, as I would expect.

In fact, the var_dump() of the result of the last select might be something like: 1, 2, 4, 5, 6, 7, 9 and so on, randomly performing a double insert sometimes.

What could be causing this issue? Here is the full table definition:

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`),
  CONSTRAINT `fk_id_allegato_lin_98_4_sgs_formazione_elearning_statistiche` FOREIGN KEY (`id_allegato`, `id_elearning`, `id_dipendente`, `id_sgs_formazione`, `id_azienda`, `id_sede`, `revisione_documento`) REFERENCES `lin_98_4_sgs_formazione_elearning_allegati` (`id_allegato`, `id_elearning`, `id_dipendente`, `id_sgs_formazione`, `id_azienda`, `id_sede`, `revisione_documento`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

I'm using MySQL 5.1.41 on Windows 7 32-bit with 3 GB RAM, if this can be of help.

Answer 1

In the end this turned out being a MySQL bug in dealing with AUTO_INCREMENT generation when the ON DUPLICATE KEY UPDATE clause was used: even if the INSERT hits a unique key, and gets "converted" to an UPDATE, the AUTO_INCREMENT would still be incremented.

This has already been reported: http://bugs.mysql.com/bug.php?id=66807 but the answer it that this is not a bug.

In fact, reading this http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html points out that it all depends on your InnoDB configuration, that is, default configuration would always increment the counter even if no actual insert was performed, whereas the traditional configuration would keep the counter unchanged if there were no real inserts.