1

This question already has an answer here:

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;

marked as duplicate by Evan Carroll, jcolebrand Oct 11 '17 at 15:32

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • Please provide SHOW CREATE TABLE. It sounds like you don't have an index on ID. – Rick James Apr 21 '17 at 16:46
0

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 '17 at 16:48

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