One MySQL Table keeps losing auto-increment after restart [duplicate]

Question

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?

Thanks

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`)
) ENGINE=InnoDB AUTO_INCREMENT=141570 DEFAULT CHARSET=utf8;

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.