We are trying to figure out why this one table keeps losing auto-increment value, thus giving an error message: Failed to read auto-increment value from storage engine.

The auto ID column has type BIGINT(20), and table has about 140K records. There are other tables that also use auto increment IDs and have more records (1M to 5M records).

It started a few month ago, that every time the service restarts, the same table loses the auto-increment, and we had to manually reset.

The MySQL server is of version 5.7, and is running on windows2012.

Where should we look into to fix it? Or should we rebuild the table?


Here is the create table;

CREATE TABLE `notification` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT, 
  `subject` varchar(400) NOT NULL,
  `category` varchar(200) DEFAULT NULL, 
  `created_by` bigint(20) DEFAULT NULL,
  `created_date` datetime NOT NULL,
  `priority` int(11) NOT NULL DEFAULT '2',
  `mime_type` varchar(45) DEFAULT NULL, 
  `attachment` varchar(500) DEFAULT NULL, 
  PRIMARY KEY (`id`) 
  FULLTEXT KEY `ix_notification_subject` (`subject`)
  • Please provide SHOW CREATE TABLE. It sounds like you don't have an index on ID.
    – Rick James
    Apr 21, 2017 at 16:46

1 Answer 1


Try inserting dummy ID

select max(ID) fromtable1into @IDmax; set @IDmax = @IDmax + 1; insert intotable1setID= @IDmax; delete fromtable1whereID= @IDmax;

This bug usually when trying to insert a duplicated value on a key.

  • If you have multiple connections, this code could cause trouble.
    – Rick James
    Apr 21, 2017 at 16:48

Not the answer you're looking for? Browse other questions tagged or ask your own question.