This question already has an answer here:
I'm using MariaDB 5.5.39, and it seems like it is forgetting the max id of the auto-incremented primary key if I delete all rows from the table and then too much time passes before another row gets inserted. Here's the schema according to HeidiSQL:
CREATE TABLE `submissionqueue` ( `SubmissionID` BIGINT(20) NOT NULL AUTO_INCREMENT, `JSON` TEXT NULL, PRIMARY KEY (`SubmissionID`), ) COLLATE='latin1_swedish_ci' ENGINE=InnoDB AUTO_INCREMENT=354;
AUTO_INCREMENT was probably 1, HeidiSQL is probably just indicating what the code would be to create the table right now.
I'm using this table as a queue to hold submissions until approval, where the
id column is auto incremented.
Later, the queue is processed leaving this table empty. When a new submission occurs, if the table is functioning correctly, a new row is inserted into this table and it receives a new auto increment ID that is higher than the IDs of all previously inserted rows (even though most "previous rows" were deleted long ago).
Over time, more than 600,000 rows have been processed from this queue. So you'd expect the auto increment id to be in the 600,000s.
However, for some time now, the auto increment IDs keep becoming reset. And when I go to process this queue, the auto increment IDs have started over at 1.
It seems like, when I empty that table, and too much time passes before another row is added to it, MariaDB is forgetting the max auto increment ID that was previously in that table and instead the IDs start over at 1.
This is a problem because during processing, any rows deleted are first moved to another table with the exact same schema. Because the auto increment IDs have started over, the program errors out because the ids are no longer unique in respect to all previously processed rows.
For years this program worked flawlessly, but at some point the database started forgetting the correct auto id, starting over at 1 instead.
This table, I'm experiencing this with, has never been dropped. The only thing I do, is remove all the rows that are in the table each time the queue is processed. After this, if I immediately go insert a row, it will received the proper Auto Increment ID (respecting all the previous ids that use to be in the table). Again, however, if too much time passes before an insert occurs, the increment starts over instead of respecting previously existing row's auto IDs.
I'm just using plain old delete statements like:
delete from tableName where id < 600000