Tuesday, September 16, 2008

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

-- 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


No comments:

Post a Comment