List:Internals« Previous MessageNext Message »
From:Alaric Snell-Pym Date:June 18 2009 11:35am
Subject:Implementing auto_increment
View as plain text  
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])

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.

As far as I can see, the auto_increment isn't incrementing.

So I dug a bit in other storage engines: many of them support a
HA_STATUS_AUTO flag to the info method, and set
stats.auto_increment_value from somewhere in their internals. And in
the source code for update_auto_increment in, is the
following interesting comment:

     Record this autogenerated value. If the caller then
     succeeds to insert this value, it will call
     which will set first_successful_insert_id_in_cur_stmt if it's not
     already set.

This seems to suggest the handler has to confirm it's used an
auto_increment value, so perhaps the reason I'm getting 1 every time
is because I'm not doing that.

But the only reference to first_successful_insert_id_in_cur_stmt I
find in the storage subtree is in ha_federated, which does this:


Looking through the different storage engines, many of them seem to
"do things" relating to auto_increment when creating or opening
tables, but they all do different things.

So what's required to implement auto_increment?

I just want to get some auto_increment numbers, that obey
auto_increment_increment and auto_increment_offset as we are a multi-
master replication system - I might be interested in making up my own
numbers in future, but for now, I just want numbers! 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...



Alaric Snell-Pym ACGI MIAP MBCS
Chief software engineer, GenieDB

Implementing auto_incrementAlaric Snell-Pym18 Jun
  • Re: Implementing auto_incrementSergei Golubchik23 Jun