Bug #12946 Duplicate key for record
Submitted: 2 Sep 2005 8:55 Modified: 5 Oct 2005 11:27
Reporter: Jan Arve Sunndt Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.0.18 OS:Linux (Red Hat Linux release 7.3 (Valha)
Assigned to: CPU Architecture:Any

[2 Sep 2005 8:55] Jan Arve Sunndt 
Description:
When we do large deletes on tables with analyse table, we get
this error for this tables -->  'duplicate key for record'

-------------------------------------------------------+
| Table | Op | Msg_type | Msg_text
|
+-------------------------------------+--------+----------+------------
-------------------------------------------------------+
| db1.table_1 | repair | warning | Duplicate 
| key for record at 252512316 against record at 113290002 |
 db1.table_1 | repair | warning | Duplicate key for
record at 252513072 against record at 133629090 |
| db1.table_1 | repair | warning | Number of rows
changed from 16004043 to 16004014 |
| db1.table_1 | repair | status | OK

mysql> show create table db1.table_1;
+--------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                    | Create Table                                                                                                                                                                                                                                                                                                 |
+--------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| table_1 | CREATE TABLE `table_1` (
  `Number` int(10) unsigned NOT NULL default '0',
  `hint` char(32) NOT NULL default '',
  `id` int(10) unsigned NOT NULL default '0',
  `day` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`Number`,`day`)
) TYPE=MyISAM |
+--------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

How to repeat:
Do large deletes with analyse table for a MyISAM table, than you get this error.

Suggested fix:
When we have this error, we have to run repair two times to get it up and running again.

mysql> repair table db1.table_1;
-------------------------------------------------------+
| Table | Op | Msg_type | Msg_text
|
+-------------------------------------+--------+----------+------------
-------------------------------------------------------+
| db1.table_1 | repair | warning | Duplicate 
| key for record at 252512316 against record at 113290002 |
 db1.table_1 | repair | warning | Duplicate key for
record at 252513072 against record at 133629090 |
| db1.table_1 | repair | warning | Number of rows
changed from 16004043 to 16004014 |
| db1.table_1 | repair | status | OK

mysql> repair table db1.table_1;
+-------------------------------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------------------------------+--------+----------+----------+
| db1.table_1 | repair | status | OK |
+-------------------------------------+--------+----------+----------+
1 row in set (1 min 58.10 sec)

mysql>
[5 Sep 2005 11:27] Valeriy Kravchuk 
Thank you for your bug report.

How many records you have before delete? How many of them where deleted? The real delete statement will be useful too.

I tried to repeat with a relatively small table_1 (192 rows) but with no lock.

Have you tried to repeat this behaviour with the newer versions from 4.0 or 4.1 branch? 

For me it looks similar to http://bugs.mysql.com/12633 and, epecially, http://bugs.mysql.com/10405, but we really need more information to build a repetable test case.
[5 Oct 2005 23:00] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".