MySQL Bugs: #6270: UPDATE report a duplicate key error whereas it shouldn't

Bug #6270	UPDATE report a duplicate key error whereas it shouldn't
Submitted:	26 Oct 2004 20:28	Modified:	1 Dec 2004 19:44
Reporter:	jocelyn fournier (Silver Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	4.1.7	OS:	Linux (Linux)
Assigned to:	Sergei Golubchik	CPU Architecture:	Any

Description:
Hi,

When trying to execute the following query : 

UPDATE indexpagedesgroupiestest SET page=page-1 WHERE cat='1' AND numeropost='1' AND numreponse>12;

I get an error :

ERROR 1062 (23000): Duplicate entry '1-1-44805' for key 1

If I'm inserting the data in exactly the same table, and I try the same query, it works :

mysql> INSERT INTO indexpagedesgroupies1 SELECT * FROM indexpagedesgroupies;
Query OK, 70307 rows affected (2.67 sec)
Records: 70307  Duplicates: 0  Warnings: 0

mysql> UPDATE indexpagedesgroupies1 SET page=page-1 WHERE cat='1' AND numeropost='1' AND numreponse>12;
Query OK, 44722 rows affected (3.51 sec)
Rows matched: 44722  Changed: 44722  Warnings: 0

REPAIR TABLE on indexpagedesgroupies doesn't solve the problem.

(I'm uploading a testcase with the table)

Thanks !
  Jocelyn

How to repeat:
Try 

UPDATE indexpagedesgroupies SET page=page-1 WHERE cat='1' AND numeropost='1' AND numreponse>12;

I have uploaded the testcase in ftp://support.mysql.com/pub/mysql/secret/update_bug.tar.gz

(table name is indexpagedesgroupiestest, not indexpagedesgroupies)

Verified on BK source server.

Hi Sanja,

Any update on this bug ? (it affects quite badly my application :( )

Thanks !
  Jocelyn

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

This is not a bug.
As you know MyISAM (and InnoDB, and HEAP) do not support delayed constraints, when you do UPDATE page=page-1 uniqueness of (`cat`,`numeropost`,`page`) conbination (your primary key) is verified after each row update.

Basically in your UPDATE you rely on the fact that rows will be updated in increasing page order. It is not the fact in your table, try

select page from i ignore key (cat,cat_3,`PRIMARY`) WHERE cat='1' AND numeropost='1' AND numreponse>12;

to see it. Of course when you copy data in the new table row order is not necessarily preserved (you may see in explain 'Using index' - that your   UNIQUE KEY `cat` (`cat`,`numeropost`,`page`,`numreponse`) index - so rows come up ordered by the index) so UPDATE in the new table succeeds.

To make your update working, specify explicit ORDER BY clause:

UPDATE i SET page=page-1 WHERE cat='1' AND numeropost='1' AND numreponse>12 order by page;

Hi !

Thanks for clarification Sergei !

  Jocelyn