Re: AUTO_INCREMENT inconsitent with MyISAM on mySQL 4.0.13-log

Erik de Bruijn - LowVoice writes:
> 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 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).
> 
> Still, both on MySQL 4.0.13-log and 3.23.49-log on two different servers and
> different it behaves as I've written down in the SQL code below.
> 
> My question is: Am I misinterpreting the documentation or have I spotted
> inconsistent behavoir? The first could well be the case, but then the docs
> might not be too clear on the matter, while talking a great deal about
> SIGNED values and their problems with auto_increment and other problems...
> 
> Before trying a different table type I've flushed the mysqld, myisamchk'ed
> it, restored backups and checked if newly generated tables behaved the same,
> and finally came to the conclusion that ANY insert will increase the
> AUTO_INCREMENT, while that does NOT mean it's MAX(field)+1 because of
> modifications and deletions.
> 
> The SQL is below.
> 
> Kind regards,
> 
> Erik de Bruijn
> www.lowvoice.nl
> 

Hi!

Yes, MyISAM and InnoDB do behave differently when it comes to
AUTO_INCREMENT. 

InnoDB will fix their behaviour in 4.1 tree.

Meanwhile all we have to do is document it more properly.

-- 

Sincerely,

-- 
For technical support contracts, go to https://order.mysql.com/?ref=msmi
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /    Mr. Sinisa Milivojevic <[email protected]>
 / /|_/ / // /\ \/ /_/ / /__   MySQL AB
/_/  /_/\_, /___/\___\_\___/   Fulltime Developer and Support Coordinator
       <___/   www.mysql.com   Larnaca, Cyprus


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