Bug #1366 signed auto_increment does not work
Submitted: 21 Sep 2003 14:41 Modified: 11 Dec 2003 8:21
Reporter: Peter Zaitsev (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0.15 OS:Any (all)
Assigned to: Alexey Botchkov CPU Architecture:Any

[21 Sep 2003 14:41] Peter Zaitsev
Description:
If you add an negative value to a which is defined as int, the next 
auto_increment value is always max. int

How to repeat:
mysql> create table a (a int not null auto_increment primary key);
Query OK, 0 rows affected (0.00 sec)

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

mysql> insert into a values(-1);
Query OK, 1 row affected (0.31 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

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

mysql> select * from a;
+------------+
| a          |
+------------+
|         -1 |
|          1 |
| 2147483647 |
+------------+
3 rows in set (0.00 sec)
[4 Nov 2003 3:35] Alexey Botchkov
changeset 1.1596 (27 Oct 2003)
[11 Dec 2003 8:21] Michael Widenius
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Fix will be in 4.0.17
(Now negative auto_increment values are ignored)