| Bug #64025 | ON DUPLICATE KEY UPDATE sets auto_increment column value to 0 | ||
|---|---|---|---|
| Submitted: | 13 Jan 2012 14:21 | Modified: | 13 Jan 2012 15:06 |
| Reporter: | Eugen Stoianovici | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.1.61, 5.5.15 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | auto_increment, insert, ON DUPLICATE KEY UPDATE | ||
[13 Jan 2012 15:06]
Valeriy Kravchuk
Thank you for the problem report. Verified with 5.1.61 on Mac OS X:
macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading 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 11
Server version: 5.1.61-debug Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> drop table t;
ERROR 1051 (42S02): Unknown table 't'
mysql> CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY, c CHAR(1) UNIQUE);
Query OK, 0 rows affected (0.13 sec)
mysql>
mysql> INSERT INTO t(c) VALUES('a');
Query OK, 1 row affected (0.14 sec)
mysql> INSERT INTO t(c) VALUES('b');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> SELECT * FROM t;
+----+------+
| id | c |
+----+------+
| 1 | a |
| 2 | b |
+----+------+
2 rows in set (0.00 sec)
mysql> INSERT INTO t(c) VALUES ('a') ON DUPLICATE KEY UPDATE id = NULL;
Query OK, 2 rows affected (0.03 sec)
mysql> SELECT * FROM t;
+----+------+
| id | c |
+----+------+
| 0 | a |
| 2 | b |
+----+------+
2 rows in set (0.00 sec)
mysql> select @@sql_mode;
+-------------------+
| @@sql_mode |
+-------------------+
| STRICT_ALL_TABLES |
+-------------------+
1 row in set (0.00 sec)
mysql> show create table t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c` char(1) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `c` (`c`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1 row in set (0.03 sec)
Description: INSERT ... ON DUPLICATE KEY UPDATE updates auto_increment primary key value to 0 when specified as NULL in update clause How to repeat: CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY, c CHAR(1) UNIQUE); INSERT INTO t(c) VALUES('a'); INSERT INTO t(c) VALUES('b'); SELECT * FROM t; +----+------+ | id | c | +----+------+ | 1 | a | | 2 | b | +----+------+ 2 rows in set (0.00 sec) running the following query should fail (because id is by default NOT NULL because it is a primary key) mysql> INSERT INTO t(c) VALUES ('a') ON DUPLICATE KEY UPDATE id = NULL; Query OK, 2 rows affected (0.02 sec) mysql> SELECT * FROM t; +----+------+ | id | c | +----+------+ | 0 | a | | 2 | b | +----+------+ 2 rows in set (0.00 sec) SELECT VERSION(); +-----------+ | version() | +-----------+ | 5.5.15 | +-----------+ 1 row in set (0.00 sec)