CODEWITHSUNDEEP
mysqlinnodb
Timmy
Timmy

Reputation: 12828

Auto_increment values in InnoDB?

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?

Upvotes: 9

Views: 12336

Answers (5)

user189142
user189142

Reputation: 111

I checked. alter table TableA auto_increment=1; does NOT work. And the reason I found in two documents

http://docs.oracle.com/cd/E17952_01/refman-5.1-en/innodb-auto-increment-handling.html InnoDB uses the following algorithm to initialize the auto-increment counter for a table t that contains an 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 the value retrieved by the statement and assigns it to the column and to the auto-increment counter for the table. By default, the value is incremented by one. This default can be overridden by the auto_increment_increment configuration setting.

and http://docs.oracle.com/cd/E17952_01/refman-5.1-en/alter-table.html

You cannot reset the counter to a value less than or equal to any that have already been used.

This is the reason why alter table will not work. I think that only option is to wipe out data and rewrite it in a new table with new id. In my case table was logfile , so I just did:

RENAME TABLE SystemEvents To SystemEvents_old; CREATE TABLE SystemEvents LIKE SystemEvents_old;

Upvotes: 1

f3r3nc
f3r3nc

Reputation: 620

Create another table with a column that remembers the last created Id. This way you don't have to take care of the max values in new tables that have this as foreign key.

Upvotes: 1

Ken Fox
Ken Fox

Reputation: 1679

If you have a foreign key constraint, how can you delete a row from table A when table B references that row? That seems like an error to me.

Regardless, you can avoid the reuse of auto-increment values by resetting the offset when your application starts back up. Query for the maximum in all the tables that reference table A, then alter the table above that maximum, e.g. if the max is 989, use this:

alter table TableA auto_increment=999;

Also beware that different MySQL engines have different auto-increment behavior. This trick works for InnoDB.

Upvotes: 5

Mark Byers
Mark Byers

Reputation: 838376

Use a foreign key constraint with 'SET NULL' for updates and deletes.

Upvotes: 1

Adrian J. Moreno
Adrian J. Moreno

Reputation: 14859

So you have two tables:

TableA

A_ID [PK]

and

TableB

B_ID [PK]

A_ID [FK, TableA.A_ID]

And in TableB, the value of A_ID is not unique? Or is there a value in TableB.A_ID that is not in TableA.A_ID?

If you need the value of TableB.A_ID to be unique, then you need to add a UNIQUE constraint to that column.

Or am I still missing something?

Upvotes: 1

Related Questions