AUTO_INCREMENT inconsitent with MyISAM on mySQL 4.0.13-log

------=_NextPart_000_11D6_01C3983A.DE4B0750
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

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.htm l) 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

-----------SQL Code: ----------
/*these lines were mostly written ad hoc & manually, but I believe it works
and shows the issue at hand */

CREATE TABLE `testje` (
`id` INT( 4 ) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL AUTO_INCREMENT ,
`val` VARCHAR( 1 ) NOT NULL ,
PRIMARY KEY ( `id` )
); /* Default is myISAM */

INSERT INTO `testje` (val) VALUES ('a');
REMOVE FROM `testje`;

INSERT INTO `testje` (val) VALUES ('b');
SELECT * FROM `testje` /*the result shows: id = 2, val = 'b' */

ALTER TABLE `facturen` TYPE = INNODB
INSERT INTO `testje` (val) VALUES ('a');
REMOVE FROM `testje`;

INSERT INTO `testje` (val) VALUES ('b');
SELECT * FROM `testje` /*the result shows: id = 1, val = 'b' */


ALTER TABLE `facturen` TYPE = ISAM
INSERT INTO `testje` (val) VALUES ('a');
REMOVE FROM `testje`;

INSERT INTO `testje` (val) VALUES ('b');
SELECT * FROM `testje` /*the result shows: id = 1, val = 'b' */


------=_NextPart_000_11D6_01C3983A.DE4B0750
Content-Type: text/plain; charset=us-ascii

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org
------=_NextPart_000_11D6_01C3983A.DE4B0750--

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.htm l) 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
/ /|_/ / // /\ \/ /_/ / /__ 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/bugs?unsub=gcdmb-bugs@m.gmane.org

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.htm l) 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
/ /|_/ / // /\ \/ /_/ / /__ 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/bugs?unsub=gcdmb-bugs@m.gmane.org