Re: AUTO_INCREMENT inconsitent with MyISAM on mySQL 4.0.13-log

Hi!

On Oct 22, Erik de Bruijn - LowVoice wrote:
> Hi List,
> 
> FIRSTLY: Please give me a CC, because I won't be on this list forever to
> look for responses!
> 
> My AUTO_INCREMENT behaves different on different table types. According to
> the manual it should have lowered/refreshed the LAST_INSERT_ID() on removal
> of records so it is equal to MAX(auto_incrementing_field)+1 (see:
> http://www.mysql.com/doc/en/example-AUTO_INCREMENT.html).

On this page:

"Note that in this case (when the AUTO_INCREMENT column is part of a
multiple-column index), AUTO_INCREMENT values will be reused if you
delete the row with the biggest AUTO_INCREMENT value in any group. This
happens even for MyISAM tables, for which AUTO_INCREMENT values normally
are not reused.)"

That is "for MyISAM tables ...  AUTO_INCREMENT values normally
are not reused.".

And later, http://www.mysql.com/doc/en/MyISAM.html

"7.1 MyISAM Tables
...
The following is new in MyISAM:
...
* Internal handling of one AUTO_INCREMENT column.  ...  This will make
  AUTO_INCREMENT columns faster (at least 10%) and old numbers will not
  be reused as with the old ISAM.  Note that when an AUTO_INCREMENT is
  defined on the end of a multi-part-key the old behaviour is still
  present."

> On the InnoDB specific page about AUTO_INCREMENTING (see:
> http://www.mysql.com/doc/en/InnoDB_auto-increment_column.html) it says
> it should behave the same (I leave transaction rollbacks out of the
> emphasis).

Nope. It says:

"After a database startup, when a user first does an insert to a table T
where an auto-increment column has been defined, and the user does not
provide an explicit value for the column, then InnoDB executes SELECT
MAX(auto-inc-column) FROM T, and assigns that value incremented by one
to the column and the auto-increment counter of the table. We say that
the auto-increment counter for table T has been initialised."

That is only on FIRST insert it does SELECT MAX(auto-inc-column) FROM T.
It assigns the result to internal "auto-increment counter of the table".

Later on this page:

"After the auto-increment counter has been initialised, if a user
...  does not explicitly specify a value, then InnoDB increments the
counter by one and assigns its new value to the column."

That is, this counter is NOT decremented on DELETE, so old numbers are
not reused until server restart.

For MyISAM tables they are never reused (unless you reset auto-increment
counter with ALTER TABLE or myisamchk).

Regards,
Sergei

-- 
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <[email protected]>
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer
/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
       <___/  www.mysql.com

-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]