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