StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL 5.5.24 - Duplicate entry on UPDATE, when there's no real duplicate
text
Body
copied!<p>I have to update a table with the following structure:</p> <pre><code>CREATE TABLE `eav_entity_attribute` ( `entity_attribute_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity Attribute Id', `entity_type_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity Type Id', `attribute_set_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Attribute Set Id', `attribute_group_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Attribute Group Id', `attribute_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Attribute Id', `sort_order` smallint(6) NOT NULL DEFAULT '0' COMMENT 'Sort Order', PRIMARY KEY (`entity_attribute_id`), UNIQUE KEY `UNQ_EAV_ENTITY_ATTRIBUTE_ATTRIBUTE_SET_ID_ATTRIBUTE_ID` (`attribute_set_id`,`attribute_id`), UNIQUE KEY `UNQ_EAV_ENTITY_ATTRIBUTE_ATTRIBUTE_GROUP_ID_ATTRIBUTE_ID` (`attribute_group_id`,`attribute_id`), KEY `IDX_EAV_ENTITY_ATTRIBUTE_ATTRIBUTE_SET_ID_SORT_ORDER` (`attribute_set_id`,`sort_order`), KEY `IDX_EAV_ENTITY_ATTRIBUTE_ATTRIBUTE_ID` (`attribute_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Eav Entity Attributes' </code></pre> <p>Above table contains a single row:</p> <pre><code>INSERT INTO `eav_entity_attribute` (`entity_attribute_id`, `entity_type_id`, `attribute_set_id`, `attribute_group_id`, `attribute_id`, `sort_order`) VALUES (32758, 4, 224, 3423, 5171, 12) </code></pre> <p>I'm running an automatic import procedure, which will read an external source of data and write into this table. </p> <p>This import runs multiple times and, therefore, sometimes the same data is imported several times. In such case, the procedure simply overwrites the old data with the new one, even when the new one is identical to the old. The condition where the same data exists is handled with an ON DUPLICATE KEY UPDATE clause. This works almost perfectly, except on this specific table. </p> <p>On this table, when the procedure attempts an UPDATE, I receive a "Duplicate key" message, which I can't explain. I debugged the code, and this is the query that fails (extracted from the INSERT..ON DUPLICATE KEY):</p> <pre><code>UPDATE eav_entity_attribute SET `attribute_group_id` = 3423 ,`attribute_id` = 5171 ,`attribute_set_id` = 223 ,`entity_type_id` = 4 ,`sort_order` = 320 WHERE (`attribute_group_id` = 3423) AND (`attribute_id` = 5171) </code></pre> <p>The error is the following:</p> <pre><code>Error Code: 1062. Duplicate entry '3423-5171' for key 'UNQ_EAV_ENTITY_ATTRIBUTE_ATTRIBUTE_GROUP_ID_ATTRIBUTE_ID' </code></pre> <p>I know that the pair 3423-5171 already exists, but the UPDATE would replace these values with themselves, not create a new entry. I'm quite confused about the cause of this issue, any suggestion would be very welcome. Thanks.</p> <p><strong>Update - New finding</strong></p> <p>I got some sort of "inspiration" and I made an experiment. I removed the Unique constraint involving on <em>(<code>attribute_set_id</code>,<code>attribute_id</code>)</em> (note, this is not the one in the error) and I ran the INSERT..ON DUPLICATE query. It worked perfectly.</p> <p>Mine is a conjecture, but this is what I think: the data I'm trying to write to the table clashes with two constraints: </p> <ul> <li>UNIQUE(<code>attribute_set_id</code>,<code>attribute_id</code>)</li> <li>UNIQUE(<code>attribute_group_id</code>,<code>attribute_id</code>)</li> </ul> <p>The INSERT fails, presumably because of the duplication error raised by the <strong>first</strong> constraint. This triggers the UPDATE, which uses the first constraint as the implicit WHERE clause. My speculation is that, in such case, the first constraint is somehow ignored, but the UPDATE trips over the second, which didn't get involved earlier.</p> <p>This still doesn't seem, to me, a valid reason for an UPDATE which replaces something with itself to raise a duplicate entry error, but it may shed some light on the logic behind it.</p> <p><strong>Second Update</strong> </p> <p>I found out that the table I was testing against actually contains a lot of rows (I forgot to disable the filtered view) resulting from the successful import of other data. However, the "duplicate candidate" is still unique in the set.</p> <p>I confirm what posted in the comments, when the table contains only that rows, the INSERT..ON DUPLICATE works, as well as the UPDATE alone. Now I'm wondering why does the table get messed up when there is more data in it, since we are still talking about a single unique row being updated with the same data.</p> <p><strong>Third Update - Found the root cause</strong></p> <p>I finally found out the reason why the UPDATE fails, now I have to find out how do I get in such condition. </p> <p>The clue was my conjecture in the first update. Simply, I have two very similar rows (please note I'm using different values as I started from a clean database).</p> <pre><code>row,entity_attribute_id,entity_type_id,attribute_set_id,attribute_group_id,attribute_id,sort_order 1,16919, 4, 120, 1746, 80, 1 2,16649, 4, 119, 1744, 80, 210 </code></pre> <p>Here's what happens:</p> <ul> <li>The INSERT attempts to insert a row with the following values: <code>120, 4, 1744, 80, 54</code>.</li> <li>This triggers the "duplicate key", since the values <code>120, 80</code> are a duplicate for the fields <code>attribute_set_id, attribute_id</code> (row 1).</li> <li><p>MySQL then tries the UPDATE, which becomes as follows:</p> <p>UPDATE table <code>entity_type_id</code> = 4 ,<code>attribute_group_id</code> = 1744 ,<code>sort_order</code> = 54 WHERE (<code>attribute_set_id</code> = 120) AND (<code>attribute_id</code> = 80)</p></li> <li><p>This time, the UPDATE fails because the values <code>1744,80</code> are violate the constraint on the pair <code>attribute_group_id, attribute_id</code>, found in row 2.</p></li> </ul> <p><strong>In summary</strong> </p> <ul> <li>The INSERT fails because row 1 has the same values for the key <code>attribute_set_id, attribute_id</code>.</li> <li>The UPDATE fails because row 2 has the same values for the key <code>attribute_group_id, attribute_id</code>.</li> </ul> <p><strong>Solution</strong></p> <p>I will have to review the whole import procedure, as, in theory, none of such duplicates should arise. MySQL is doing its job fine, it's the database that is complicated.</p> <p>Thanks for all the suggestions.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload