MySQL Bugs: #47720: REPLACE INTO Autoincrement column with negative values

Bug #47720	REPLACE INTO Autoincrement column with negative values
Submitted:	29 Sep 18:51	Modified:	2 Dec 9:05
Reporter:	Nathan Gray
Status:	Documenting
Category:	Server: InnoDB	Severity:	S3 (Non-critical)
Version:	5.1.37-2, 5.1.40-bzr	OS:	Any (Debian)
Assigned to:	Satya B	Target Version:
Triage:	Triaged: D2 (Serious)

Description:
When inserting a negative key into an auto increment field, the insert works and the auto
increment value is not changed.  Subsequent inserts are successful, with the auto
increment field updated for positive values, and untouched for negative values.

However, updating a previously inserted negative ID with REPLACE INTO causes the auto
increment field to be updated to 2147483647.

Related:
http://bugs.mysql.com/bug.php?id=36411
http://bugs.mysql.com/bug.php?id=35602

Supposedly changes should be in, (Pushed into 5.1.28), but the issue persists in 5.1.37-2

How to repeat:
drop table if exists `qa05`;
create table `qa05` (`id` int auto_increment primary key)engine=innodb;
insert into `qa05` set `id` = 1;
show table status like 'qa05';
insert into `qa05` set `id` = 2;
insert into `qa05` set `id` = -1;
insert into `qa05` set `id` = -1;
show table status like 'qa05';
replace into `qa05` values (-1);
show table status like 'qa05';

Thank you for the bug report. Verified just as described with recent 5.1.40 from bzr on
Mac OS X:

valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uroot testReading table information
for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.40-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> rop table if exists `qa05`;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'rop table if
exists `qa05`' at line 1
mysql> create table `qa05` (`id` int auto_increment primary key)engine=innodb;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into `qa05` set `id` = 1;
Query OK, 1 row affected (0.01 sec)

mysql> show table status like 'qa05';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length |
Max_data_length | Index_length | Data_free | Auto_increment | Create_time         |
Update_time | Check_time | Collation         | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| qa05 | InnoDB |      10 | Compact    |    1 |          16384 |       16384 |           
   0 |            0 |   7340032 |              2 | 2009-09-29 20:06:06 | NULL        |
NULL       | latin1_swedish_ci |     NULL |                |         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)

mysql> insert into `qa05` set `id` = 2;
Query OK, 1 row affected (0.00 sec)

mysql> insert into `qa05` set `id` = -1;
Query OK, 1 row affected (0.00 sec)

mysql> insert into `qa05` set `id` = -1;
ERROR 1062 (23000): Duplicate entry '-1' for key 'PRIMARY'
mysql> show table status like 'qa05';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length |
Max_data_length | Index_length | Data_free | Auto_increment | Create_time         |
Update_time | Check_time | Collation         | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| qa05 | InnoDB |      10 | Compact    |    3 |           5461 |       16384 |           
   0 |            0 |   7340032 |              3 | 2009-09-29 20:06:06 | NULL        |
NULL       | latin1_swedish_ci |     NULL |                |         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)

mysql> replace into `qa05` values (-1);
Query OK, 1 row affected (0.00 sec)

mysql> show table status like 'qa05';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length |
Max_data_length | Index_length | Data_free | Auto_increment | Create_time         |
Update_time | Check_time | Collation         | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| qa05 | InnoDB |      10 | Compact    |    3 |           5461 |       16384 |           
   0 |            0 |   7340032 |     2147483647 | 2009-09-29 20:06:06 | NULL        |
NULL       | latin1_swedish_ci |     NULL |                |         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)

mysql> select * from qa05;
+----+
| id |
+----+
| -1 |
|  1 |
|  2 |
+----+
3 rows in set (0.00 sec)

mysql> show create table qa05\G
*************************** 1. row ***************************
       Table: qa05
Create Table: CREATE TABLE `qa05` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2147483647 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

We ran into this issue with 5.1.33 and found that if you first DELETE the entry then
INSERT it instead of using REPLACE, auto_increment doesn't increment (Which it shouldn't
when inserting negative values).

So:
replace into `qa05` values (-1);

will break it, but 

DELETE FROM 'qa05' WHERE id = -1;
INSERT INTO `qa05` SET `id` = -1;

will not.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/92037

3219 Satya B	2009-11-30
      Applying InnoDB snapshot 5.1-ss6242, part 8. Fixes BUG#47720
      
      1. BUG#47720 - REPLACE INTO Autoincrement column with negative values.
      
      Detailed revision comments:
      
      r6235 | sunny | 2009-11-26 01:14:42 +0200 (Thu, 26 Nov 2009) | 9 lines
      branches/5.1: Fix Bug#47720 - REPLACE INTO Autoincrement column with negative
values.
      
      This bug is similiar to the negative autoinc filter patch from earlier,
      with the additional handling of filtering out the negative column values
      set explicitly by the user.
      
      rb://184
      Approved by Heikki.

patch queued to 5.1-bugteam storage/innobase only and not for plugin.

Notes to Docs Team:
Please change back to verified and assign to calvin after documenting

Pushed into 5.1.42 (revid:joro@sun.com-20091202080033-mndu4sxwx19lz2zs) (version source
revid:satya.bn@sun.com-20091130094645-pxgshh3oywgtn9hr) (merge vers: 5.1.42) (pib:13)