Bug #47720 REPLACE INTO Autoincrement column with negative values

• Submitted: 29 Sep 2009 16:51
• Modified: 20 Jun 2010 17:15
• Reporter: Nathan Gray
• Status: Closed
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 5.1.37-2, 5.1.40-bzr
• OS: Any (Debian)
• Assigned to: Calvin Sun
• CPU Architecture: Any

[29 Sep 2009 16:51] Nathan Gray

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

[29 Sep 2009 17:12] Valeriy Kravchuk

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)

[1 Oct 2009 16:58] James Walker

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.

[30 Nov 2009 9:47] Bugs System

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.

[1 Dec 2009 10:06] Satya B

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

[2 Dec 2009 8:05] Bugs System

Pushed into 5.1.42 (revid:[e-mail]-20091202080033-mndu4sxwx19lz2zs) (version source revid:[e-mail]-20091130094645-pxgshh3oywgtn9hr) (merge vers: 5.1.42) (pib:13)

[15 Dec 2009 2:56] Paul DuBois

Noted in 5.1.42 changelog.

Using REPLACE to update a previously inserted negative value in an
AUTO_INCREMENT coumn in an InnoDB table caused the table 
auto-increment value to be updated to 2147483647. 

Setting to verified and assigning to Calvin per earlier comment.

[16 Dec 2009 8:41] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20091216083311-xorsasf5kopjxshf) (version source revid:[e-mail]-20091214191830-wznm8245ku8xo702) (merge vers: 6.0.14-alpha) (pib:14)

[16 Dec 2009 8:48] Bugs System

Pushed into 5.5.0-beta (revid:[e-mail]-20091216082430-s0gtzibcgkv4pqul) (version source revid:[e-mail]-20091202114649-zt975apdali0jy3c) (merge vers: 5.5.0-beta) (pib:14)

[16 Dec 2009 8:55] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20091216083231-rp8ecpnvkkbhtb27) (version source revid:[e-mail]-20091212203859-fx4rx5uab47wwuzd) (merge vers: 5.6.0-beta) (pib:14)

[22 Feb 2010 20:29] Mark Callaghan

We don't think this was fixed in the InnoDB plugin. As the plugin is the only InnoDB in 5.1.44, this should be reopened.

[22 Feb 2010 21:18] Calvin Sun

Mark - do not understand what you mean "The plugin is the only InnoDB in
5.1.44". Just checked the 5.1.44 source (mysql-5.1.44.tar.gz), both innobase and innodb_plugin are there. The fix is in internal repo right now, and will be released under 1.0.7.

Thanks,
Calvin

[12 Mar 2010 14:19] Bugs System

Pushed into 5.1.44-ndb-7.0.14 (revid:[e-mail]-20100312135944-t0z8s1da2orvl66x) (version source revid:[e-mail]-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)

[12 Mar 2010 14:34] Bugs System

Pushed into 5.1.44-ndb-6.2.19 (revid:[e-mail]-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:[e-mail]-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)

[12 Mar 2010 14:50] Bugs System

Pushed into 5.1.44-ndb-6.3.33 (revid:[e-mail]-20100312135724-xcw8vw2lu3mijrhn) (version source revid:[e-mail]-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)

[6 Apr 2010 8:01] Bugs System

Pushed into 5.1.46 (revid:[e-mail]-20100405111026-7kz1p8qlzglqgfmu) (version source revid:[e-mail]-20100401151005-c6re90vdvutln15d) (merge vers: 5.1.46) (pib:16)

[5 May 2010 15:24] Bugs System

Pushed into 5.1.47 (revid:[e-mail]-20100505145753-ivlt4hclbrjy8eye) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[6 May 2010 17:15] Paul DuBois

Noted in 5.5.4 changelog.

[28 May 2010 6:14] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100524190136-egaq7e8zgkwb9aqi) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (pib:16)

[28 May 2010 6:42] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20100524190941-nuudpx60if25wsvx) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[28 May 2010 7:10] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100524185725-c8k5q7v60i5nix3t) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[29 May 2010 23:22] Paul DuBois

Noted in 5.5.5, 6.0.14 changelogs.

[15 Jun 2010 8:21] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100615080459-smuswd9ooeywcxuc) (version source revid:[e-mail]-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)

[15 Jun 2010 8:38] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100615080558-cw01bzdqr1bdmmec) (version source revid:[e-mail]-20100415070122-1nxji8ym4mao13ao) (pib:16)

[17 Jun 2010 12:20] Bugs System

Pushed into 5.1.47-ndb-7.0.16 (revid:[e-mail]-20100617114014-bva0dy24yyd67697) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 13:08] Bugs System

Pushed into 5.1.47-ndb-6.2.19 (revid:[e-mail]-20100617115448-idrbic6gbki37h1c) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 13:48] Bugs System

Pushed into 5.1.47-ndb-6.3.35 (revid:[e-mail]-20100617114611-61aqbb52j752y116) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)