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 valueALTER TABLE tbl AUTO_INCREMENT = 70809190;
-- find out whats the next increment valueSELECT 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