MySQL Duplicate Key Entry Error when there is not already an entry for this key

MySQLprimary-key

Duplicate entry '35839' for key 'PRIMARY'

I am receiving the above error message suddenly on a database I keep logs in. This is an auto-increment field which the last key for the last entry is 35838. If I perform a search for the key 35839, no results are returned. (Also, I'm looking at the database through phpMyAdmin and can see that the last record is 35838.)

The table structure is:

CREATE TABLE IF NOT EXISTS `raw_data` (
  `id` int(12) unsigned NOT NULL AUTO_INCREMENT,
  `oid` int(12) unsigned NOT NULL,
  `data` longtext NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `oid` (`oid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=35839 ;

You can see that the next ID it is expecting to make is 35839. Yet it thinks that it is already in the table…

Also, the oid that is trying to be inserted is also unique. It is an order ID being generated by another system and is never duplicated. Plus, I've checked to see if it is already in the database and it is also not in the database already.

Is there anything I could be overlooking as to why this would suddenly start telling me that it is trying to enter a duplicate? It has been working for two years now. haha~

Thanks!

Select Statement

Select * From raw_data Where id = 35839;

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0012 sec)

Best Answer

From @ypercubeᵀᴹ's suggestion:

The table is MyISAM so this is probably a corruption issue. Try running CHECK and then REPAIR the table (taking the necessary backup before!)

Details and various options for both commands can be found in CHECK and the links from there.

I then ran Check Table raw_data Extended which resulted in:

livereps_webdb.raw_data     check   warning     1 client is using or hasn't closed the table prope...
livereps_webdb.raw_data     check   warning     Size of datafile is: 12921069568       Should be: ...
livereps_webdb.raw_data     check   error   Found 15839 keys of 15838
livereps_webdb.raw_data     check   error   Corrupt

I then ran Repair Table raw_data Extended which resulted in:

livereps_webdb.raw_data     repair  info    Found block that points outside data file at 12921...
livereps_webdb.raw_data     repair  info    Found block with too small length at 12921068496; ...
livereps_webdb.raw_data     repair  info    Found block that points outside data file at 12921...
livereps_webdb.raw_data     repair  info    Found block that points outside data file at 12921...
livereps_webdb.raw_data     repair  status  OK

This fixed the problem.

Related Solutions

Mysql – Finding rows for a specified date range

Another way to get the result is this. It finds first all groups that the teacher has surely taught (or is going to) by checking that she has started within the month and then in another subquery it finds - for every group - the last teacher that started at the first day of the month or earlier.

With the unique index you have on the table, the second subquery should be quite efficient. The first subquery would benefit from an index on (teacherid, startdate, groupid):

SELECT groupid
FROM pupilgroupteacher
WHERE teacherid = @teacher 
  AND startdate >= @month + INTERVAL 1 DAY
  AND startdate < @month + INTERVAL 1 MONTH

UNION DISTINCT

SELECT gg.groupid
FROM 
    ( SELECT DISTINCT groupid
      FROM pupilgroupteacher
    ) AS gd
  JOIN pupilgroupteacher AS gg
    ON  gg.groupid = gd.groupid
    AND gg.startdate =
        ( SELECT MAX(gi.startdate)
          FROM pupilgroupteacher AS gi 
          WHERE gi.groupid = gd.groupid
            AND gi.startdate < @month + INTERVAL 1 DAY
        )
WHERE gg.teacherid = @teacher ;

Mysql – Adding index to large thesql tables

The activity of altering big tables are done in phases:

Create a new table with required fields and indexes say in test DB (just structure)
Dump the data from the existing table and load the same to the newly created table in test DB
Now announce your downtime :)
Swap the tables by renaming - RENAME table ur_db.table_name to test.temp, test.table_name to ur_db.table_name, test.temp to test.table_name; This is an atomic operation taking fraction of a second.
Load the extra records to the newly created table ( the records that came after dumping and then loading). This step can be done before Step: 3 also once to reduce your downtime.
And your system is back

Few notes:

You need not hit information schema directly like this, try using SHOW TABLE STATUS from db like 'table_name'
The speed of alter table is more or less linked with the I/O speed. Last time when we ran a direct alter table(without the above steps), we had 40GB+ table size it took around 4hours. If your 20GB data is taking years, you are working on some outdated machine.
Also drop of the unwanted indexes like index_bslt_ondate, index_base_schedule_line_items_loan_base_schedule as some other indexes has the left most column as the indexed column

Please let me know if you need any clarification on any of these steps.

Edit : A simple python script to automate the process https://github.com/georgecj11/hotswap_mysql_table.git

Best Answer

Related Solutions

Mysql – Finding rows for a specified date range

Mysql – Adding index to large thesql tables

Related Question