| Bug #86727 | auto_increment field not incrementing when using select 0, <other vals> into .. | ||
|---|---|---|---|
| Submitted: | 16 Jun 2017 8:33 | Modified: | 16 Jun 2017 15:32 |
| Reporter: | Eric Tiffany | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.6.36-82 | OS: | CentOS |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | auto_increment, autoinc | ||
[16 Jun 2017 11:24]
MySQL Verification Team
Hello Eric Tiffany,
Thank you for the report and test case.
Could you please confirm if you have you set sql_mode to NO_AUTO_VALUE_ON_ZERO in 5.6 environment?
## 5.6.36 with default sql_mode, and when sql_mode is set to NO_AUTO_VALUE_ON_ZERO
root@localhost [test]> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.6.36 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.36 |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | linux-glibc2.5 |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)
root@localhost [test]> show variables like 'sql_mode';
+---------------+------------------------+
| Variable_name | Value |
+---------------+------------------------+
| sql_mode | NO_ENGINE_SUBSTITUTION |
+---------------+------------------------+
1 row in set (0.00 sec)
root@localhost [test]>
root@localhost [test]> use test;
Database changed
root@localhost [test]> drop table if exists testdata;
Query OK, 0 rows affected (0.00 sec)
root@localhost [test]> drop table if exists testauto;
Query OK, 0 rows affected (0.00 sec)
root@localhost [test]> create table testauto (
-> autoinc int(11) not null auto_increment,
-> other varchar(24),
-> key autoinc (autoinc)
-> );
Query OK, 0 rows affected (0.00 sec)
root@localhost [test]>
root@localhost [test]> create table testdata (
-> other varchar(24)
-> );
Query OK, 0 rows affected (0.00 sec)
root@localhost [test]>
root@localhost [test]> insert into testdata (other) values ('foo'),('bar'),('baz');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
root@localhost [test]> insert into testauto (autoinc, other) select 0, other from testdata;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
root@localhost [test]> select * from testauto;
+---------+-------+
| autoinc | other |
+---------+-------+
| 1 | foo |
| 2 | bar |
| 3 | baz |
+---------+-------+
3 rows in set (0.00 sec)
root@localhost [test]>
root@localhost [test]> set sql_mode='NO_AUTO_VALUE_ON_ZERO';
Query OK, 0 rows affected (0.00 sec)
root@localhost [test]>
root@localhost [test]> show variables like 'sql_mode';
+---------------+-----------------------+
| Variable_name | Value |
+---------------+-----------------------+
| sql_mode | NO_AUTO_VALUE_ON_ZERO |
+---------------+-----------------------+
1 row in set (0.00 sec)
root@localhost [test]>
root@localhost [test]> use test;
Database changed
root@localhost [test]> drop table if exists testdata;
Query OK, 0 rows affected (0.00 sec)
root@localhost [test]> drop table if exists testauto;
Query OK, 0 rows affected (0.00 sec)
root@localhost [test]> create table testauto (
-> autoinc int(11) not null auto_increment,
-> other varchar(24),
-> key autoinc (autoinc)
-> );
Query OK, 0 rows affected (0.00 sec)
root@localhost [test]>
root@localhost [test]> create table testdata (
-> other varchar(24)
-> );
Query OK, 0 rows affected (0.00 sec)
root@localhost [test]>
root@localhost [test]> insert into testdata (other) values ('foo'),('bar'),('baz');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
root@localhost [test]> insert into testauto (autoinc, other) select 0, other from testdata;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
root@localhost [test]> select * from testauto;
+---------+-------+
| autoinc | other |
+---------+-------+
| 0 | foo |
| 0 | bar |
| 0 | baz |
+---------+-------+
3 rows in set (0.00 sec)
root@localhost [test]>
Thanks,
Umesh
[16 Jun 2017 15:32]
Eric Tiffany
Ah, it would appear that the NO_AUTO_VALUE_ON_ZERO is included in my sql_mode. Thanks for that. What an evil parameter. mysql> show variables like "%sql_mode%"; +---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ | sql_mode | ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ However, I'm not sure where those setting came from -- they are in my.cnf but I'm not sure why (I didn't install this DB). It *is* a percona installation. Maybe that has something to do with it. mysql> show variables like "%version%"; +-------------------------+------------------------------------------------------+ | Variable_name | Value | +-------------------------+------------------------------------------------------+ | innodb_version | 5.6.36-82.0 | | protocol_version | 10 | | slave_type_conversions | | | tls_version | TLSv1.1,TLSv1.2 | | version | 5.6.36-82.0-log | | version_comment | Percona Server (GPL), Release 82.0, Revision 58e846a | | version_compile_machine | x86_64 | | version_compile_os | Linux | +-------------------------+------------------------------------------------------+
Description: It seems that an auto_increment column is not incrementing in the situation where it is being populated by a "select 0, ... into" statement. This works as expected on MySQL 5.7 (on Mac OS). How to repeat: Create a database and execute the following statements: create table testauto ( autoinc int(11) not null auto_increment, other varchar(24), key autoinc (autoinc) ); create table testdata ( other varchar(24) ); insert into testdata (other) values ('foo'),('bar'),('baz'); insert into testauto (autoinc, other) select 0, other from testdata; You might expect (which is what I get in MySQL 5.7) autoinc,other 1,foo 2,bar 3,baz But what I get instead on 5.6 is: autoinc,other 0,foo 0,bar 0,baz The '0' value in the insert/select statement is taken literally and is not used as an indicator that autoincrement should be performed.