Prevent reset of auto_increment id in Innodb database after server restart

Question

I recently read that because of how InnoDB recalculates the AUTO_INCREMENT value when the server restarts, any records on the high end of the ID list may have their IDs reused.

Normally, this isn't a problem, because when a user is deleted everything associated with the ID is deleted from other tables too.

But I'm deliberately leaving their forum posts orphaned, labelled as "Posted by =User #123=", so that past conversations are retained. Clearly, should an ID be reused, this will be a problem.

I've never had this issue before because there were always enough new users to make it unlikely for an ID to be reused in this way. However on my new project signups are rare and inactive user deletions frequent (especially since the "Open Alpha" accounts only last for three days as a preview), and such ID reuse has happened three for three now.

I have "fixed" the issue by saving the correct value for AUTO_INCREMENT elsewhere and using that instead of relying on the internal value. Is there an actual way to have InnoDB remember the actual last value?

Answer 1

(avoiding the issue by never deleting)

Since you want to keep the "Posted by =User #123=" information after you delete the user with id=123, you could also consider using 2 tables for storing users data. One for Active users and one for all (including deleted ones from the active users). And never delete those ids from the AllUser table:

CREATE TABLE AllUser
( user_id INT AUTO_INCREMENT
, ...
, PRIMARY KEY (user_id)
) ;

------
--- Forum posts FK should reference the `AllUser` table

CREATE TABLE ActiveUser
( user_id INT 
, ...
, PRIMARY KEY (user_id)
, FOREIGN KEY (user_id)
    REFERENCES AllUser (user_id)
) ;

------
--- All other FKs should reference the `ActiveUser` table

This will of course complicate the insert new user operation. Any new user will mean 2 inserts, one in each table. Deleting a user though will be by deleting from the ActiveUser table only. All FKs will be deleted with cascading, except the forum posts, which will be referencing the Alluser table (where no deleting will ever happen).

Answer 2

There is no natural way to do this except to use information_schema.tables to record all columns with auto_increment option.

You could collect those columns as follows:

CREATE TABLE mysql.my_autoinc ENGINE=MyISAM
SELECT table_schema,table_name,auto_increment
FROM information_schema.tables WHERE 1=2;
ALTER TABLE mysql.my_autoinc ADD PRIMARY KEY (table_schema,table_name);
INSERT INTO mysql.my_autoinc
SELECT table_schema,table_name,auto_increment
FROM information_schema.tables WHERE auto_increment IS NOT NULL;

Create a script that will reset the auto_increment values

AUTOINC_SCRIPT=/var/lib/mysql/ResetAutoInc.sql
mysql -u... -p... -AN -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' AUTO_INCREMENT=',auto_increment,';') FROM mysql.my_autoinc" > ${AUTOINC_SCRIPT}

You could then do one of two things:

OPTION #1 : Run script manually after startup

mysql> source /var/lib/mysql/ResetAutoInc.sql

OPTION #2 : Have mysqld execute script before allowing connections

You would have to add this option

[mysqld]
init-file=/var/lib/mysql/ResetAutoInc.sql

That way, every time you restart mysql, this script is executed in the beginning. You will have to remember to regenerate /var/lib/mysql/ResetAutoInc.sql before doing a planned mysql restart.

Answer 3

The 5.5 docs suggests storing the auto-increment value elsewhere as you already have.

An alternative solution would be to emulate a SEQUENCE so you don't use auto-increment in the actual table itself. This has been discussed on SO before and again. The MySQL Performance blog mentions it.

Yet another MySQL data screwing that other RDBMS don't have...

Answer 4

Just don't delete the user. Relational integrity is more important. If you have to because of privacy reasons or whatever, simply change the username to 'deleted' and clear out any other fields.

Answer 5

This is an old question and still relevant.

1) This behavior is being fixed in Mysql 8.0.

2) One solution is to use a dummy row to your data, to keep the AUTO_INCREMENT above a certain value. Not super convenient depending on what you are storing, but is a simple solution in some cases.

Answer 6

We had a need for this an extrapolated a solution for our own system based on instructions on this post. If this can help anyone reach their goal in an even easier way.

Our system uses a tombstone table pattern to store deleted items because we do 2 way sync on disconnected systems, so we use this code to match the tombstone tables with their live tables and extract the highest value possible :)

DROP PROCEDURE IF EXISTS `reset_auto_increments`;
DELIMITER $
CREATE PROCEDURE reset_auto_increments()
BEGIN

    DECLARE done INT DEFAULT 0;
    DECLARE schemaName VARCHAR(255) DEFAULT '';
    DECLARE liveTableName VARCHAR(255) DEFAULT '';
    DECLARE tombstoneTableName VARCHAR(255) DEFAULT '';
    DECLARE liveAutoIncrement INT DEFAULT 0;
    DECLARE tombstoneAutoIncrement INT DEFAULT 0;
    DECLARE newAutoIncrement INT DEFAULT 0;

    DECLARE autoIncrementPairs CURSOR FOR 
        SELECT
            liveTables.TABLE_SCHEMA AS schemaName,
            liveTables.TABLE_NAME AS liveTable, 
            tombstoneTables.TABLE_NAME AS tombstoneTable,
            liveTables.AUTO_INCREMENT AS live_auto_increment,
            tombstoneTables.AUTO_INCREMENT AS tombstone_auto_increment,
            GREATEST(liveTables.AUTO_INCREMENT, tombstoneTables.AUTO_INCREMENT) AS new_auto_increment
        FROM 
            information_schema.tables AS liveTables
            JOIN information_schema.tables AS tombstoneTables
                ON liveTables.TABLE_SCHEMA = tombstoneTables.TABLE_SCHEMA
                    AND CONCAT('deleted', UCASE(LEFT(liveTables.TABLE_NAME, 1)), SUBSTRING(liveTables.TABLE_NAME, 2))
                        = tombstoneTables.TABLE_NAME
        WHERE
            GREATEST(liveTables.AUTO_INCREMENT, tombstoneTables.AUTO_INCREMENT) IS NOT NULL;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    SET done = 0;

    SET schemaName = '';
    SET liveTableName = '';
    SET tombstoneTableName = '';
    SET liveAutoIncrement = 0;
    SET tombstoneAutoIncrement = 0;
    SET newAutoIncrement = 0;

    OPEN autoIncrementPairs;
    REPEAT

        FETCH autoIncrementPairs INTO 
            schemaName, 
            liveTableName, 
            tombstoneTableName, 
            liveAutoIncrement, 
            tombstoneAutoIncrement, 
            newAutoIncrement;

        SET @statement = CONCAT('ALTER TABLE ', schemaName, '.', liveTableName, ' AUTO_INCREMENT=', newAutoIncrement);
        PREPARE updateAutoIncrementStatement FROM @statement;
        EXECUTE updateAutoIncrementStatement;
        DEALLOCATE PREPARE updateAutoIncrementStatement;

    UNTIL done END REPEAT;

    CLOSE autoIncrementPairs;

END$

DELIMITER ;