Auto_increment values in InnoDB?

Posted by Timmy on Stack Overflow See other posts from Stack Overflow or by Timmy
Published on 2009-12-28T23:40:31Z Indexed on 2010/04/20 0:13 UTC
Read the original article Hit count: 727

Filed under:
|

I've been using InnoDB for a project, and relying on auto_increment. This is not a problem for most of the tables, but for tables with deletion, this might be an issue:

AUTO_INCREMENT Handling in InnoDB

particularly this part:

AUTO_INCREMENT column named ai_col: After a server startup, for the first insert into a table t, InnoDB executes the equivalent of this statement: SELECT MAX(ai_col) FROM t FOR UPDATE; InnoDB increments by one the value retrieved by the statement and assigns it to the column and to the auto-increment counter for the table.

This is a problem because while it ensures that within the table, the key is unique, there are foreign keys to this table where those keys are no longer unique.

The mysql server does/should not restart often, but this is breaking. Are there any easy ways around this?

© Stack Overflow or respective owner

Related posts about mysql

Related posts about innodb