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)