MariaDB is Forgetting Previous Auto Increment IDs 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;


The original 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 &lt; 600000
No, that is not the guarantee of AUTO_INCREMENT.  It only assures uniqueness.  It does not assure deleted number won't recur.

Old numbers will be resurrected in this scenario (and perhaps others):


DELETE the highest sequence number(s).  (Or TRUNCATE or ROLLBACK, ...)
Restart mysqld (or power failure, ...)
InnoDB, as it opens the table, computes the next id to use as MAX(id)


You need another mechanism, such as

CREATE TABLE seq (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL,
    code CHAR(1) NOT NULL,
    PRIMARY KEY(id),
    UNIQUE(code)
) ENGINE=InnoDB;

INSERT INTO seq (code) VALUE ('a');   -- initialize only

To get next number:
INSERT INTO seq (code) VALUE ('a')
    ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id+1);
then use LAST_INSERT_ID() to retrieve the new number: SELECT LAST_INSERT_ID();


Notes:


This allows multiple sequences, based on code.
The nums will be monotonically increasing (never dup, never decrease).
It will provide consecutive numbers (no gaps).
It will not back-fill.
It will survive crashes; deletes from your table; etc.
If you get a new number inside a transaction, replication could generate the ids not in chronological order.
If you get a new number outside a transaction (autocommit), you could 'burn' the number (create a gap) if certain failures occur.


(There may be something simpler.)