Bug #77755 auto_INC resets in 5.6.24-72.2-log while renaming table
Submitted: 17 Jul 2015 0:08 Modified: 17 Jul 2015 8:59
Reporter: Mannoj Kumar Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.6.24-72.2-log OS:Ubuntu
Assigned to: CPU Architecture:Any

[17 Jul 2015 0:08] Mannoj Kumar
Description:
Renaming an empty table with a value in autoinc id to another table name resets the autoinc to 0? (edited)

It happens in Mysql 5.6.24-72.2-log and not in 5.6.23-72.1-log

Fyi. Its Percona-Mysql server. Not sure if this is in Mysql.

How to repeat:
mysql> show create table audit_CHECKER \G
*************************** 1. row ***************************
       Table: audit_CHECKER
Create Table: CREATE TABLE `audit_CHECKER` (
  `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_audits_on_created_at` (`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=111111111 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select * from audit_CHECKER ;
Empty set (0.00 sec)

mysql> rename table audit_CHECKER to audit_CHECKER_DAMN;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table audit_CHECKER_DAMN;
+--------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table              | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+--------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| audit_CHECKER_DAMN | CREATE TABLE `audit_CHECKER_DAMN` (
  `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
   `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_audits_on_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------------+
| version()       |
+-----------------+
| 5.6.24-72.2-log |
+-----------------+
1 row in set (0.00 sec)

mysql>

Suggested fix:
The renamed table should not reset the auto-inc id, it should carry forward as however it is in source table.
[17 Jul 2015 1:21] MySQL Verification Team
Thank you for the bug report. I couldn't repeat with current release 5.6.25:

miguel@vbub14:~$ mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.25 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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> CREATE DATABASE td;
Query OK, 1 row affected (0,02 sec)

mysql> USE td
Database changed
mysql> CREATE TABLE `audit_CHECKER` (
    ->   `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
    ->   `created_at` datetime DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `index_audits_on_created_at` (`created_at`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=111111111 DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0,02 sec)

mysql> rename table audit_CHECKER to audit_CHECKER_DAMN;
Query OK, 0 rows affected (0,00 sec)

mysql> show create table audit_CHECKER_DAMN\G
*************************** 1. row ***************************
       Table: audit_CHECKER_DAMN
Create Table: CREATE TABLE `audit_CHECKER_DAMN` (
  `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_audits_on_created_at` (`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=111111111 DEFAULT CHARSET=latin1
1 row in set (0,00 sec)
[17 Jul 2015 8:59] Mannoj Kumar
Hope it is fixed in 5.6.25 but do you think in 5.6.24 it was a feature or miss in backward compatibility during the build phase?