CODEWITHSUNDEEP
phpmysqlduplicates
Matteo Tassinari
Matteo Tassinari

Reputation: 18584

Insert of a single row in mysql cause row duplication

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.

Upvotes: 1

Views: 613

Answers (2)

Matteo Tassinari
Matteo Tassinari

Reputation: 18584

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.

Upvotes: 1

Zathrus Writer
Zathrus Writer

Reputation: 4331

Interesting, this would hint that MySQL updates the AUTO_INCREMENT value even when nothing is actually being inserted.

Or perhaps it has something to do with your foreign keys? try removing them in a testing environment and running the same code - if the state persists, we have the winner.

Upvotes: 0

Related Questions