3 ms·

Wait, what? MySQL reuses them? Can you elaborate on that?

• davidw 10y ago
  Here's what happens. Perhaps it has been fixed now: * New record is created, with new ID. * Record is deleted. * Database is shut down (cleanly). * Database is started again. * New record is created. It gets the old ID, because it's just looking at the current max(ID) or something like that.
  • billmalarky 10y ago
    How old was the version of mysql? Even years ago I remember mysql stored the auto increment value separately.
• yardie 10y ago
  You can also do the same in certain versions SQL Server. Change the ID field from auto-increment enabled to disabled. Add and delete a few fields. Then re-enable auto increment. Now, normally the server should start new IDs at the last position. Normally being the operative word.
• pietroalbini 10y ago
  That'a bug reported in 2003 and not yet fixed. https://bugs.mysql.com/bug.php?id=199 https://bugs.mysql.com/bug.php?id=199
  • mercer 10y ago
    Oh wow, that's definitely good to know. I do quite a number of migrations and so far this hasn't bitten me, but it could.
    • davidw 10y ago
      Migrating from Mysql to Postgres might be a good migration :-)
      • mercer 10y ago
        Sadly that's not always an option for me...