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
>
> http://forge.mysql.com/w/index.php?title=MySQL_Internals_Custom_Engine&printable=yes#Adding_Support_for_INSERT_to_a_Storage_Engine
> 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
--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / 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
