From: Alaric Snell-Pym
Date: June 18 2009 11:35am
Subject: Implementing auto_increment
List-Archive: http://lists.mysql.com/internals/36929
Message-Id: <954A901A-0BB6-4D68-849F-85FABC19E1D2@geniedb.com>
MIME-Version: 1.0 (Apple Message framework v935.3)
Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes
Content-Transfer-Encoding: 7bit


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.

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 handler.cc, is the
following interesting comment:

   /*
     Record this autogenerated value. If the caller then
     succeeds to insert this value, it will call
     record_first_successful_insert_id_in_cur_stmt()
     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:

   ha_federated::info(HA_STATUS_AUTO);
   thd->first_successful_insert_id_in_cur_stmt=
     stats.auto_increment_value;

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...

Thanks,

ABS

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