MySQL 4.0.20 auto_increment bug

--=-dwYJjHBl5wdjxdYq9sD9
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

For InnoDB tables, the auto_increment is not handled properly if the
index is updated thus:

create table foobar (id bigint not null auto_increment, primary key(id),
name text) type=3DInnoDB;

insert into foobar (name) values ("red"), ("green"), ("blue");
-- red is 1, green is 2, blue is 3

update foobar set id =3D id+10000;
-- red is 10001, blue is 10002, green is 10003, the next auto_increment
entry SHOULD BE 10004

insert into foobar (name) values ("white");
-- white's id is 4 instead of 10004

Version:
mysql Ver 12.22 Distrib 4.0.20, for pc-linux (i686)

Don't know if this bug exists elsewhere, but I suspect it does.

--=20
Fred Mitchell, HydraNuke.com
E-Commerce and Hosting Solutions=20
Whatever you want, we'll make it happen
(603)557-5986


--=-dwYJjHBl5wdjxdYq9sD9
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQBBEDdGVZ97vCsahg4RAgM/AKCGDq2cRptHSZJMTcg7VRzZ50NZjwCg t45o
DvJ/EHhzubGF0mCePKy08PA=
=nCgs
-----END PGP SIGNATURE-----

--=-dwYJjHBl5wdjxdYq9sD9--

Re: MySQL 4.0.20 auto_increment bug

I may be wrong, bit it is my understanding of the SQL standard the
AUTO_INCREMENT is only required to generate unique numbers that are
monotonic for values that the server generates.... hence this may not be
a bug. However, if it generates multiple Id's at 10001, etc then that
would definitely be a bug.

Also, if my understanding is correct then there would appear to still be
a documentation error as in section "6.5.3 CREATE TABLE Syntax" in the
MySQL 4.0 manual it suggests that AUTO_INCREMENT columns should behave
as Fred expected and not as I expected.

From the documentation:

An integer column may have the additional attribute AUTO_INCREMENT.
When you insert a value of NULL (recommended) or 0 into an
AUTO_INCREMENT column, the column value is set to value+1 where value is
the largest value for the column currently in the table.

Cheers,

Paul

Fred wrote:

>For InnoDB tables, the auto_increment is not handled properly if the
>index is updated thus:
>
>create table foobar (id bigint not null auto_increment, primary key(id),
>name text) type=InnoDB;
>
>insert into foobar (name) values ("red"), ("green"), ("blue");
>-- red is 1, green is 2, blue is 3
>
>update foobar set id = id+10000;
>-- red is 10001, blue is 10002, green is 10003, the next auto_increment
>entry SHOULD BE 10004
>
>insert into foobar (name) values ("white");
>-- white's id is 4 instead of 10004
>
>Version:
>mysql Ver 12.22 Distrib 4.0.20, for pc-linux (i686)
>
>Don't know if this bug exists elsewhere, but I suspect it does.
>
>
>



--
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: MySQL 4.0.20 auto_increment bug

Fred wrote:
> For InnoDB tables, the auto_increment is not handled properly if the
> index is updated thus:
>
> create table foobar (id bigint not null auto_increment, primary key(id),
> name text) type=InnoDB;
>
> insert into foobar (name) values ("red"), ("green"), ("blue");
> -- red is 1, green is 2, blue is 3
>
> update foobar set id = id+10000;
> -- red is 10001, blue is 10002, green is 10003, the next auto_increment
> entry SHOULD BE 10004
>
> insert into foobar (name) values ("white");
> -- white's id is 4 instead of 10004
>
> Version:
> mysql Ver 12.22 Distrib 4.0.20, for pc-linux (i686)
>
> Don't know if this bug exists elsewhere, but I suspect it does.
>


expected behaviour for InnoDB, see
http://dev.mysql.com/doc/mysql/en/InnoDB_auto-increment_colu mn.html

--
Hartmut Holzgraefe

--
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: MySQL 4.0.20 auto_increment bug

--=-NvKGUbFlTSRsZEv/UP77
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

On Fri, 2004-08-06 at 13:02, Hartmut Holzgraefe wrote:
....
>=20
> expected behaviour for InnoDB, see
> http://dev.mysql.com/doc/mysql/en/InnoDB_auto-increment_colu mn.html

Most curious. I've read that before, and now I've read it again. What
caught my eye is this:

===3D
InnoDB uses the following algorithm to initialize the auto-increment
counter for a table T that contains an AUTO_INCREMENT column named
ai_col: After a server startup, when a user first does an insert to a
table T, InnoDB executes the equivalent of this statement:

SELECT MAX(ai_col) FROM T FOR UPDATE;
===3D

No where in this documentation does it mentions what happens if someone
re sequence the numbers. Restarting MySQL works around the problem
because it forces the above SELECT statement (or the equivalent) to be
called again. Another workaround would be to execute that SELECT
statement after any re-sequencing of an auto_increment column.=20

I am a big proponent of *expected* behavior, especially since a subtlety
such as this could easily be missed and wind up corrupting a database.
So I personally would consider this a bug.

--=20
Fred Mitchell, HydraNuke.com
E-Commerce and Hosting Solutions=20
Whatever you want, we'll make it happen
(603)557-5986


--=-NvKGUbFlTSRsZEv/UP77
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQBBEpQIVZ97vCsahg4RAjEBAKDOdGFjKI+IkaXkj8LB3cqSH2OpJQCf awsO
taA4SfIXtafg+MyfaZXVofA=
=dU9C
-----END PGP SIGNATURE-----

--=-NvKGUbFlTSRsZEv/UP77--

Re: MySQL 4.0.20 auto_increment bug

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Fred,

> No where in this documentation does it mentions what happens if someone
> re sequence the numbers. Restarting MySQL works around the problem
> because it forces the above SELECT statement (or the equivalent) to be
> called again. Another workaround would be to execute that SELECT
> statement after any re-sequencing of an auto_increment column.
>
> I am a big proponent of *expected* behavior, especially since a subtlety
> such as this could easily be missed and wind up corrupting a database.
> So I personally would consider this a bug.

We do have an existing bug report regarding this, though it also is
noted as being expected behavior for the InnoDB storage engine:

http://bugs.mysql.com/bug.php?id=4683

Currently it is considered a feature request.

Best regards,
- --
Dean Ellis, Support Engineer & Software Developer
MySQL AB, www.mysql.com

Are you MySQL certified? http://www.mysql.com/certification
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFBEplKAebV/bGJ5NERApPoAKCIJ8GolVbmNeeFE/+MrivDf503xACe IHSF
ZnzUdwA6ZVquwFshBXdaWus=
=gTF+
-----END PGP SIGNATURE-----

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