Hi!

Manuel> Hello, Manuel> Please acknowledge this bug report and let me know when you have Manuel> analized/fixed it. BTW, is there a bug tracking application for MySQL Manuel> development like PHP development has?

We are just about to start using support wizard for our support customers. As soon as we are sure we have got all parameters optimized for our usage we will extend this to also handle all mails to bu...@lists.mysql.com

Manuel> If you insert a negative integer number (say -1) in signed
AUTO_INCREMENT Manuel> field and then insert a NULL to make it return the next value (0) as the Manuel> last inserted id, it will return a very large unsigned integerinstead Manuel> (4294967296).

Manuel> This bug was detected with Metabase driver test suite. Metabase is a Manuel> database abstraction package for PHP. See this page for more
information:

Manuel> http://phpclasses.UpperDesign.com/browse.html/package/20

Manuel> Metabase uses auto-incremented fields to emulates sequences that MySQL
does Manuel> not support. To create a sequence with a given starting number, a table
is Manuel> created with only one integer AUTO_INCREMENT field. To make the initial Manuel> sequence number be one, Metabase MySQL driver inserts the value -1 in
the Manuel> table.

Manuel> The bug shows in the tested version (3.23.6 alpha on Linux) but it
didn't show Manuel> on version "mysql Ver 9.16 Distrib 3.21.31, for sun-solaris2.6
(sparc)".

Manuel> CREATE TABLE test (sequence INT DEFAULT 0 NOT NULL AUTO_INCREMENT,
PRIMARY KEY (sequence)); Manuel> INSERT INTO test (sequence) VALUES (-1); Manuel> INSERT INTO test (sequence) VALUES (NULL); Manuel> SELECT LAST_INSERT_ID(); Manuel> SELECT * FROM test;

The problem is that MySQL only supports unsigned values for auto_increment-fields; This is true even if you define the auto_increment field as a signed value. (This is basicly sound as it will ensure that MySQL can use the full potential range for a number as autoincrement values).

Inserting -1 is basicly the same as inserting the max value for the field. As MySQL can't give you a higher value for the next value it will insert the max possible value for the column in the table.

In other words, we don't think this is really a bug in MySQL, but rather that Metabase uses the auto_increment option in a way it was not designed for. It's also not a good idea to change MySQL to allow wrap-around for the last_insert id (from max-value or -1 to 0), as if we would allow this we would get other much more interesting problems in normal usage!

In MySQL 3.23, with MyISAM tables, you can ensure that MySQL will start the auto_increment from some specific value by doing:

mysql> CREATE TABLE test (sequence INT DEFAULT 0 NOT NULL AUTO_INCREMENT,
PRIMARY KEY (sequence)) AUTO_INCREMENT=10; Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values (NULL); Query OK, 1 row affected (0.00 sec)

mysql> select * from test; +----------+ | sequence | +----------+ | 10 | +----------+ 1 row in set (0.00 sec)

By the way, I wonder why MetaBase inserts -1 if they want MySQL starts from 1. Just not inserting anything would handle this case much better.

Regards, Monty

PS: Sorry for the change of behaveour, but we think that the current model is better and gives us more possibilities to ensure that things doesn't get 'out of hands' in normal usage'