Prevent reset of auto_increment id in Innodb database after server restart

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?

(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).

StackExchange Database Administrators Q#16602, answer score: 6