0

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;

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 < 600000

marked as duplicate by Evan Carroll, mustaccio, RDFozz, LowlyDBA, Erik Darling Oct 12 '17 at 10:23

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.

2

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):

  1. DELETE the highest sequence number(s). (Or TRUNCATE or ROLLBACK, ...)
  2. Restart mysqld (or power failure, ...)
  3. 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.)

  • Update: MySQL 8.0 avoids reuse of id across server crash. Still, I would not want my business to depend on such. – Rick James Oct 12 '17 at 17:17
2

Only a restart can explain this (as far as I know). So, "if too much time passes before an insert occurs," as you say, and a restart happens at this time, there are no rows in the table and the engine starts again the auto-increment from 1 (the auto-increment value is stored only in memory, not in any persistent storage unfortunately) - ypercubeᵀᴹ


From the manual:

InnoDB uses the in-memory auto-increment counter as long as the server runs. When the server is stopped and restarted, InnoDB reinitializes the counter for each table for the first INSERT to the table, as described earlier.

That's how InnoDB behaves, maybe your table was MyISAM previously? - jkavalik


I guess a workaround, that I can do, (in order to persist the correct auto increment id) is to always leave the highest (id) row in the table, then ignore that row next time a I process the queue. - Lonnie Best


Or record it before stopping the server, then set it up on start with ALTER TABLE. See Change auto increment starting number? on Stack Overflow. - jynus


Community Wiki answer created from comments on the question

1

You can have the application to do soft delete instead of delete from table. You can add a column called deleted and update that as 1/0. By this way the inserts would exist and auto_inc is maintained and app can mark deleted flag as 1 if it wants to remove it. Run a job once a day to archive the old data that is

delete from table where id < MAX_AUTO_INC_ID -1

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