MySQL Bugs: #74642: Mysql process restarted when querying INNODB table with AUTO INCREMENT column

Bug #74642	Mysql process restarted when querying INNODB table with AUTO INCREMENT column
Submitted:	31 Oct 2014 7:24	Modified:	4 Nov 2014 7:32
Reporter:	Rajesh Kota	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: InnoDB Plugin storage engine	Severity:	S1 (Critical)
Version:	Server version: 5.5.21-log MySQL Communi	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
Mysql process restarted when querying INNODB table with AUTO INCREMENT column. Please suggest a fix or workaround in the same mysql version to avoid mysql restart. This bug is not there in the mysql 5.6 version. 

How to repeat:
1. Create a table with the below structure

    CREATE TABLE `T1` (
      `C0` bigint(19) NOT NULL,
      `C1` bigint(20) DEFAULT NULL,
      `C2` bigint(20) DEFAULT NULL,
      PRIMARY KEY (`C0`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8

2. Insert a record

    insert into T1(C0,C1) values(1,2);

3. Add auto increment column by hitting the below query

    ALTER TABLE T1 MODIFY COLUMN C2 BIGINT AUTO_INCREMENT UNIQUE NULL;

4. The above table will work till you stop the mysql process. Once you started the mysql again and accessing the above table will make the mysql process to restart.

Thank you for the bug report. Your server version 5.5.21 is quite older please upgrade:

miguel@miguelpc ~/mysql-5.5.40-linux2.6-x86_64 $ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.40 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, 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 TABLE `T1` (
    ->       `C0` bigint(19) NOT NULL,
    ->       `C1` bigint(20) DEFAULT NULL,
    ->       `C2` bigint(20) DEFAULT NULL,
    ->       PRIMARY KEY (`C0`)
    ->     ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    -> ;
Query OK, 0 rows affected (0.08 sec)

mysql> insert into T1(C0,C1) values(1,2);
Query OK, 1 row affected (0.05 sec)

mysql>  ALTER TABLE T1 MODIFY COLUMN C2 BIGINT AUTO_INCREMENT UNIQUE NULL;
Query OK, 1 row affected (0.27 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SHOW VARIABLES LIKE "%VERSION%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.5.40                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.5.40                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | linux2.6                     |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)

Thank you for your reply.

Yes bug was fixed in the higher mysql version(Tested with Mysql5.6 and it is working fine). 

With the Mysql 5.5.40, have you restarted your mysql process and try access the same table created in the above steps? Because the problem is coming only after restarting the mysql process only.