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)

Mysql duplicate entry error with auto-increment column

I have been looking at this issue with one of our tables where we were getting a duplicate entry error on key 1 which is basically the primary key on the table. The primary key field is set to auto-increment and was puzzling me as to why it was causing the duplicate entry. Ran the checks to ensure the values were right (see checks below):

Someone suggested to alter the auto-increment value to a higher value and that would reset the next value. So I did a alter statement which took a while as the table was huge. The query to check the next ID value came out correctly (see below for query)

However this still did not resolve the issue. Finally looked in the direction of index and turns out that index might have been corrupted. Ran a repair table to check index and that fixed the value. (see below for query)

*********** checks ********************

--   select max(id) from tableA;
returned: 70809185

--  insert into tableA values (xxxxxx);
Error:  Duplicate entry '70809186' for key 1

--  select * from tableA where id=70809186;
Returned 0 rows

************** alter index **************

-- query to change the next increment value
 ALTER TABLE tbl AUTO_INCREMENT = 70809190;

-- find out whats the next increment value
 SELECT Auto_increment FROM information_schema.tables WHERE table_name='tableA';

************* repair index *****************

--  repair table tableA quick;
quick option repairs the indexes

/

mysql duplicate key error due to sequence issue

I recently had to deal with this error so I am blogging it here so the rest can use.

Error: On one of our tables we kept getting a duplicate key exception on insets. The puzzling thing was that the value actually did not exist in the table. Here's a low down:

--- Table definition
CREATE TABLE "job_message" (
  "message_id" bigint(20) NOT NULL auto_increment,
  "run_id" int(11) NOT NULL,
  "object_id" bigint(20) default NULL,
  "date" datetime NOT NULL,
  "priority" tinyint(2) NOT NULL,
  "type" varchar(32) default NULL,
  "msg" varchar(255) NOT NULL,
  "stack_trace" varchar(255) default NULL,
  PRIMARY KEY  ("message_id"),
  KEY "ix_job_message" ("run_id","object_id","date"),
  KEY "ix_job_message_object_id" ("object_id"),
  KEY "ix_job_message_dt" ("date")
) 


-- proof that row did not exist
mysql> select max(message_id) from job_message_200836;
+-----------------+
| max(message_id) |
+-----------------+
|       753222920 |
+-----------------+
1 row in set (0.00 sec)


-- error with insert statement
mysql> insert into job_message (run_id, object_id, date, priority, msg) values (1279408,
 22231896, '2008-09-02 15:53:40', 4, 'test');

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

Thats what was puzzling.

Solution:

Did a lot of googling around but got nothing. I did get some hint that the issue might be with the sequences and that the sequence is not updated but got no clue on how to resolve the issue. Out of desperation I tried to see if the table was healthy and voila there it was. The table was corrupted. The solution was to either repair the table or get a copy from the slave. I tried the repair and it worked fine. I used local in the repair command as I did not want the repair to propagate to slave as the slave copy was fine. See commands below to check and repair a table.

-- check table
mysql> check table job_message_200836;
+-------------------------+-------+----------+-----------------------------------------------------------+
| Table                   | Op    | Msg_type | Msg_text                                                  |
+-------------------------+-------+----------+-----------------------------------------------------------+
| main.job_message_200836 | check | warning  | Size of datafile is: 352975060       Should be: 352974784 |
| main.job_message_200836 | check | error    | Found 3189125 keys of 3189124                             |
| main.job_message_200836 | check | error    | Corrupt                                                   |
+-------------------------+-------+----------+-----------------------------------------------------------+
3 rows in set (0.69 sec)


-- repair table command
mysql> repair local table job_message_200836;
+-------------------------+--------+----------+------------------------------------------------+
| Table                   | Op     | Msg_type | Msg_text                                       |
+-------------------------+--------+----------+------------------------------------------------+
| main.job_message_200836 | repair | warning  | Number of rows changed from 3189124 to 3189125 |
| main.job_message_200836 | repair | status   | OK                                             |
+-------------------------+--------+----------+------------------------------------------------+
2 rows in set (29.93 sec)