MySQL Forums
Forum List  »  InnoDB

Advanced Search
New Topic

Re: Problem with "false" increment in InnoDB using a table with UNIQUE constraint
Posted by: Rick James
Date: May 01, 2011 11:15AM

This NOT a bug. It is an artifact of the details of AUTO_INCREMENT.

AUTO_INCREMENT guarantees uniqueness; it does not guarantee consecutiveness. It is not wise to assume anything other than uniqueness. Ids will be monotonic unless you are using multi-master. The value after a server restart may be surprising, but it will be greater than all existing values (single-master).

Roughly, what happened is that the second insert (the duplicate) grabbed id=2, thereby disallowing anyone else to get id=2. But the it aborted (dup key, you our case; it could have been for a variety of other reasons).

To further confuse people, MyISAM and InnoDB act differently in various edge cases.

Navigate: Previous Message• Next Message
Options: Reply• Quote


Subject
Views
Written By
Posted
Problem with "false" increment in InnoDB using a table with UNIQUE constraint
2327
Jefferson Felix
April 30, 2011 12:15AM
Re: Problem with "false" increment in InnoDB using a table with UNIQUE constraint
705
Rick James
May 01, 2011 11:15AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.