4

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?

Niet the Dark Absol
  • 320,036
  • 81
  • 464
  • 592
  • 5
    Rather than deleting the account record, why not just clear it out, change the name to "user #11", and set a "deleted" flag on it? Then you maintain referential integrity (and can use FKs) and not worry about IDs getting re-used. – mu is too short Apr 17 '12 at 03:09

1 Answers1

1

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

Community
  • 1
  • 1
Naveen Kumar
  • 4,543
  • 1
  • 18
  • 36