MySQL Lists are EOL. Please join:

List:Internals« Previous MessageNext Message »
From:Sergei Golubchik Date:June 23 2009 12:27pm
Subject:Re: Implementing auto_increment
View as plain text  
Hi, Alaric!

On Jun 18, Alaric Snell-Pym wrote:
> Hi there. I'm finally getting round to adding AUTO_INCREMENT support
> to our storage engine.
> As suggested at 
>  I added the following code to my write_row method:
>   if (table->next_number_field && buf == table->record[0])
>     update_auto_increment();
> But, when I then try to insert two rows without specifying the
> auto_incrementing PK, the first row gets a PK of 1... and then I get a
> PK collision error on the second one.

It's should work pretty much automatically, without you doing
anything (but see below).

Do you implement ::get_auto_increment() method or you keep the default
one ? If you keep the default - look at it in a debugger, all it does is
an index_last() lookup to find the largest value in your PK. From there you
will be able to figure out what's wrong. May be you didn't implement
index_last() ?

> If I have to implement my own counter and manage its state between
> restarts, I can, but I get the impression MySQL is willing to handle
> that for me...

MySQL can do that, but auto_inc numbers will be reused. Like

  INSERT t1 VALUES (NULL);              -- 1 gets inserted
  DELETE FROM t1 WHERE auto_inc=1;
  INSERT t1 VALUES (NULL);              -- again 1 gets inserted

because the new auto_inc value is just a MAX(all existing values)+1.

If you'll manage the state internally, like. for example, MyISAM does,
the numbers will be not reused (2 will be inserted above), which is
faster than an index lookup on every write_row(), and more standard
compatible. From what I understand users generally don't want auto_inc
values to be reused.

Note that if you will manage the state internally, you'll need to
implement ::get_auto_increment() method.

Regards / Mit vielen Grüßen,

   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <serg@stripped>
 / /|_/ / // /\ \/ /_/ / /__  Principal Software Engineer/Server Architect
/_/  /_/\_, /___/\___\_\___/  Sun Microsystems GmbH, HRB München 161028
       <___/                  Sonnenallee 1, 85551 Kirchheim-Heimstetten
Geschäftsführer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel
Vorsitzender des Aufsichtsrates: Martin Häring
Implementing auto_incrementAlaric Snell-Pym18 Jun
  • Re: Implementing auto_incrementSergei Golubchik23 Jun