failure of auto increment in inndb (mysql)

[Malik Rumi]

I read this on the django project site: 

Since MySQL 5.5.5, the default storage engine is InnoDB. This engine is fully transactional and supports foreign key references. It’s probably the best choice at this point. However, note that the the InnoDB autoincrement counter is lost on a MySQL restart because it does not remember the AUTO_INCREMENT value, instead recreating it as “max(id)+1”. This may result in an inadvertent reuse of AutoField values.

Now to my newby senses, this is a huge problem. How can you have a primary key, or a foreign key, without the assurance that they are unique? But I did some searching around the net, and while there are some suggested fixes here and there, no one seems to be in a panic about this. So, my question is, why? Are unique primary keys not as important as I thought they were? How are you dealing with this, if you use mysql/innodb? Is this a reason to jump to nosql? Help me wrap my brain around this. Thanks.  

[Erik Cederstrand]

Den 27/01/2014 kl. 17.22 skrev Malik Rumi <malik....@gmail.com>:

> I read this on the django project site:
> Since MySQL 5.5.5, the default storage engine is InnoDB. This engine is fully transactional and supports foreign key references. It’s probably the best choice at this point. However, note that the the InnoDB autoincrement counter is lost on a MySQL restart because it does not remember the AUTO_INCREMENT value, instead recreating it as “max(id)+1”. This may result in an inadvertent reuse of AutoField values.
> Now to my newby senses, this is a huge problem. How can you have a primary key, or a foreign key, without the assurance that they are unique?

“reuse” does not imply “non-unique” in this situation - MySQL will still make sure that the ID field is unique. It just means that an ID you have previously deleted from the table may be reused. Consider this:

1) The last user you added was assigned ID 122
2) You add user “John Doe", MySQL assigns the value 123 to the ID field
3) You delete record with ID 123 again
4) MySQL reboots, resetting AUTO_INCREMENT to 122+1=123
5) You add user “Jane Smith", MySQL assigns the value 123 to the ID field

Now Jane Smith has the same ID as John Doe had previously. This may not be a problem in your situation, or it might. Anyway it’s good practice to never re-purpose an ID.

Erik

[Tom Lockhart]

> ...

> Now Jane Smith has the same ID as John Doe had previously. This may not be a problem in your situation, or it might. Anyway it's good practice to never re-purpose an ID.

MySQL started as a non-ACID query server (not a full relational database in the accepted sense) and these kinds of issues likely stem from that history. Folks getting started with databases and django may want to consider using Postgres for their foundation.

hth

- Tom

[Javier Guerra Giraldez]

On Mon, Jan 27, 2014 at 2:13 PM, Tom Lockhart <tlockh...@gmail.com> wrote:
> MySQL started as a non-ACID query server (not a full relational database in the accepted sense) and these kinds of issues likely stem from that history. Folks getting started with databases and django may want to consider using Postgres for their foundation.

it's true that MySQL started as a very limited project and grew from
there, meaning that there are still several shortcomings when compared
with more complete and robust implementations, like Postgresql.

but this specific issue, isn't because of that history. in fact, the
old MyISAM storage engine handles it differently: autoincrement fields
are monotonically incrementing. (unless the storage gets corrupted).

it's the InnoDB implementation the one that didn't bother to handle
full monotonicity, considering the uniqueness and incrementing
conditions enough.

AFAIR, the SQL standard doesn't require full monotonicity... does it
even define autoincrement fields? if there's no hard standard, i
don't think it could be considered a bug.

--
Javier

[Erik Cederstrand]

Actually, auto_increment has nothing to do with ACID. MySQL just chooses to not store the nextval across reboots. You can set it manually with “ALTER TABLE Foo AUTO_INCREMENT=123”.

PostgreSQL creates a sequence as a separate single-row table to store the nextval so it is persistent across reboots, but you have to explicitly set the nextval if you e.g. bulk loading data into tables, which bypasses the sequence. If you don’t, inserts may fail because the sequence is creating duplicate keys.

MySQL goes the usual not-always-helpful route of guessing what you want (February 31st, anyone?). PostgreSQL has more control at the expense of foot-shooting.

Erik