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?