stupid error 1062 (23000): Duplicate entry

17 Aug 2010

so i have something like this

mysql> insert into logs (target, nick, text) values ('derp', 'derp', 'derp');
ERROR 1062 (23000): Duplicate entry '23004' for key 'PRIMARY'
mysql> show create table logs\G
*************************** 1. row ***************************
       Table: logs
Create Table: CREATE TABLE `logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `target` varchar(100) DEFAULT NULL,
  `nick` varchar(100) DEFAULT NULL,
  `text` varchar(1024) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=23004 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into logs (target, nick, text) values ('derp', 'derp', 'derp');
ERROR 1062 (23000): Duplicate entry '23004' for key 'PRIMARY'
mysql> select max(id) from logs;
+---------+
| max(id) |
+---------+
|   23003 |
+---------+
1 row in set (0.00 sec)

i forgot how i solved it last time. i remember it was hackish. well, that ended up being retarded and easier than i expected:

mysql> check table logs;
+----------------+-------+----------+----------------------------------------------------------+
| Table          | Op    | Msg_type | Msg_text                                                 |
+----------------+-------+----------+----------------------------------------------------------+
| woodstove.logs | check | warning  | 6 clients are using or haven't closed the table properly |
| woodstove.logs | check | warning  | Size of datafile is: 1704688       Should be: 1704072    |
| woodstove.logs | check | error    | Found 23012 keys of 23003                                |
| woodstove.logs | check | error    | Corrupt                                                  |
+----------------+-------+----------+----------------------------------------------------------+
4 rows in set (0.05 sec)

mysql> repair table logs;
+----------------+--------+----------+--------------------------------------------+
| Table          | Op     | Msg_type | Msg_text                                   |
+----------------+--------+----------+--------------------------------------------+
| woodstove.logs | repair | warning  | Number of rows changed from 23003 to 23012 |
| woodstove.logs | repair | status   | OK                                         |
+----------------+--------+----------+--------------------------------------------+
2 rows in set (0.10 sec)