Thursday, September 25, 2008

MYSQL duplicate entry error with a non-unique index/key

This was a strange one. A insert was erroring out due to duplicate entry error on a key that is non -unique. The error was: ERROR 1062 (23000): Duplicate entry '445982' for key 2. Did a check table to check if everything was alright and there it was: an error with the table. Repair quick did not work so had to do a full repair. See below log for query statements:

Here's the log:



mysql> show create table tableA;

......
.....
.....
PRIMARY KEY ("category_id"),
KEY "ix_campaign_category_unpacked_root" ("root_campaign_id"),
KEY "ix_campaign_category_client" ("client_id","site_id"),
KEY "ix_campaign_category_site" ("site_id","client_id")
) ENGINE=MyISAM DEFAULT CHARSET=utf8

-- Error from insert

mysql> ERROR 1062 (23000): Duplicate entry '445982' for key 2

-- check table result

mysql> check table tableA;
+----------------------------------+-------+----------+--------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+----------------------------------+-------+----------+--------------------------------------------------------------+
| tableA | check | warning | Table is marked as crashed |
| tableA | check | warning | 2 clients are using or haven't closed the table properly |
| tableA | check | warning | Size of datafile is: 34234576 Should be: 34234400 |
| tableA | check | warning | Found 0 deleted space in delete link chain. Should be 361132 |
| tableA | check | error | Found 0 deleted rows in delete link chain. Should be 2385 |
| tableA | check | error | record delete-link-chain corrupted |
| tableA | check | error | Corrupt |
+----------------------------------+-------+----------+--------------------------------------------------------------+
7 rows in set (0.00 sec)

-- Repair table attempts

mysql> repair local table tableA quick;
+----------------------------------+--------+----------+-------------------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+----------------------------------+--------+----------+-------------------------------------------------------------------------------+
| tableA | repair | error | Couldn't fix table with quick recovery: Found wrong number of deleted records |
| tableA | repair | error | Run recovery again without -q |
| tableA | repair | status | OK |
+----------------------------------+--------+----------+-------------------------------------------------------------------------------+
3 rows in set (9.64 sec)

mysql> repair local table tableA;
+----------------------------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------------------------+--------+----------+----------+
| tableA | repair | status | OK |
+----------------------------------+--------+----------+----------+
1 row in set (2.43 sec)

No comments:

Post a Comment