MySQL Bugs: #82879: Changing column case causes auto increment to break

Bug #82879	Changing column case causes auto increment to break
Submitted:	6 Sep 2016 23:48	Modified:	7 Sep 2016 7:37
Reporter:	Brandon Rohde	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.6.31	OS:	Any (AWS RDS instance)
Assigned to:		CPU Architecture:	Any

Description:
lower_case_table_names is set to 0
Engine:  InnoDB

When changing my auto increment field from `ID` to `id` (changing case), it broke the auto increment value after rebooting the server. The auto inc value was set to 0. Performing an ALTER command to set the auto inc value to a number higher than the MAX value did not work.  The only fix I could find was changing the table to MyISAM and then back to InnoDB.

How to repeat:
mysql> CREATE TABLE `test` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `FirstName` varchar(10) NOT NULL,
  `LastName` varchar(10) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

############################################

mysql> INSERT INTO  `lrs`.`test` (
`ID` ,
`FirstName` ,
`LastName`
)
VALUES (
NULL ,  'John',  'Doe'
), (
NULL ,  'Jane Doe',  ''
);

############################################

mysql> ALTER TABLE  `test` CHANGE  `ID`  `id` INT( 11 ) NOT NULL AUTO_INCREMENT ,
CHANGE  `FirstName`  `first_name` VARCHAR( 10 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL ,
CHANGE  `LastName`  `last_name` VARCHAR( 10 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL

############################################

mysql> INSERT INTO  `lrs`.`test` (
`id` ,
`first_name` ,
`last_name`
)
VALUES (
NULL ,  'Jim',  'Doe'
);

############################################

## REBOOT THE SERVER

############################################

mysql> select Auto_increment from information_Schema.tables where table_Schema='db' and table_name='table'\G;

*************************** 1. row ***************************
Auto_increment: 0
1 row in set (0.01 sec)

ERROR: 
No query specified

############################################

##ATTEMPTED FIX:
mysql> SELECT MAX(id) FROM db.table FOR UPDATE;
+---------+
| MAX(id) |
+---------+
|     3  |
+---------+
1 row in set (0.00 sec)

mysql> ALTER TABLE db.table AUTO_INCREMENT =4;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

############################################

# At this point, I was unable to reset the auto increment value

Suggested fix:
No suggested fix outside of changing the table from InnoDB to MyISAM and then back to InnoDB.

In the "how to replicate" part, I started using lrs.test, and later my comments said db.table.  That was a typo on my part. It should have been db.table all along.

Hello Brandon,

Thank you for the report and test case.
I'm not seeing this with 5.6.31/5.6.32.

-
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-advanced-5.6.32: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.32-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2016, 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 database if exists db;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create database db;
Query OK, 1 row affected (0.00 sec)

mysql> use db;
Database changed
mysql>
mysql>  CREATE TABLE `test` (
    ->   `ID` int(11) NOT NULL AUTO_INCREMENT,
    ->   `FirstName` varchar(10) NOT NULL,
    ->   `LastName` varchar(10) NOT NULL,
    ->   PRIMARY KEY (`ID`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO  `test` (
    -> `ID` ,
    -> `FirstName` ,
    -> `LastName`
    -> )
    -> VALUES (
    -> NULL ,  'John',  'Doe'
    -> ), (
    -> NULL ,  'Jane Doe',  ''
    -> );
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql> ALTER TABLE  `test` CHANGE  `ID`  `id` INT( 11 ) NOT NULL AUTO_INCREMENT ,
    -> CHANGE  `FirstName`  `first_name` VARCHAR( 10 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL ,
    -> CHANGE  `LastName`  `last_name` VARCHAR( 10 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> INSERT INTO  `test` (
    -> `id` ,
    -> `first_name` ,
    -> `last_name`
    -> )
    -> VALUES (
    -> NULL ,  'Jim',  'Doe'
    -> );
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> select Auto_increment from information_Schema.tables where table_Schema='db' and table_name='test'\G
*************************** 1. row ***************************
Auto_increment: 6
1 row in set (0.00 sec)

mysql> \q
Bye
-- stop/start or kill/start - end result is same

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-advanced-5.6.32: bin/mysqladmin -uroot -S /tmp/mysql_ushastry.sock shutdown
[1]+  Done                    bin/mysqld --basedir=/export/umesh/server/binaries/GABuilds/mysql-advanced-5.6.32 --datadir=/export/umesh/server/binaries/GABuilds/mysql-advanced-5.6.32/82879 --core-file --socket=/tmp/mysql_ushastry.sock --port=3306 --log-error=/export/umesh/server/binaries/GABuilds/mysql-advanced-5.6.32/82879/log.err 2>&1
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-advanced-5.6.32:
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-advanced-5.6.32: bin/mysqld --basedir=/export/umesh/server/binaries/GABuilds/mysql-advanced-5.6.32 --datadir=/export/umesh/server/binaries/GABuilds/mysql-advanced-5.6.32/82879 --core-file --socket=/tmp/mysql_ushastry.sock   --port=3306 --log-error=/export/umesh/server/binaries/GABuilds/mysql-advanced-5.6.32/82879/log.err  2>&1 &
[1] 11346
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-advanced-5.6.32: 2016-09-07 08:56:05 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-09-07 08:56:05 0 [Note] bin/mysqld (mysqld 5.6.32-enterprise-commercial-advanced) starting as process 11346 ...

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-advanced-5.6.32: bin/mysql -uroot -S /tmp/mysql_ushastry.sock                                                                        Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.32-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2016, 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> select Auto_increment from information_Schema.tables where table_Schema='db' and table_name='test'\G
*************************** 1. row ***************************
Auto_increment: 6
1 row in set (0.01 sec)

mysql> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 0     |
+------------------------+-------+
1 row in set (0.00 sec)

If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Thanks,
Umesh