Bug #74454 Mysql server crashes on CREATE TABLE statement with SET NULL for NOT NULL column

• Submitted: 20 Oct 2014 12:26
• Modified: 21 Nov 2014 10:11
• Reporter: Igor Zinkovsky
• Status: No Feedback
• Category: MySQL Server: DDL
• Severity: S1 (Critical)
• Version: 5.5.40
• OS: Linux (2.6.39-gentoo-r3 i686)
• CPU Architecture: Any

[20 Oct 2014 12:26] Igor Zinkovsky

Description:
Attempt to create a foreign key with 'ON DELETE SET NULL' causes server crash when corresponding column is defined as NOT NULL.

I got this error in deep night time during a huge DB update script. Server goes down caused long downtime. So I guess it's S1.

How to repeat:
1. Create table to point foreign key to.

CREATE TABLE `parent` (
`id` INT UNSIGNED NOT NULL PRIMARY KEY
) ENGINE=InnoDB;

2. Try to create a child table with following DDL:

CREATE TABLE `child` (
`id` INT UNSIGNED NOT NULL PRIMARY KEY
`parent_id` INT UNSIGNED NOT NULL,
FOREIGN KEY (`parent_id`) REFERENCES `parent`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

ER: Some kind of error message shown table is not created.
AR: Attempt to create table is performed, .ibd file is created under the corresponding database directory, server crashed.

In error log:

stack_bottom = ffffffffae5bb2fc thread_stack 0x30000
/usr/sbin/mysqld(my_print_stacktrace+0x33)[0x83a82c3]
/usr/sbin/mysqld(handle_fatal_signal+0x432)[0x82909f2]
linux-gate.so.1(__kernel_sigreturn+0x0)[0xb77b9400]
/usr/sbin/mysqld[0x84ab23e]
/usr/sbin/mysqld[0x843c504]
/usr/sbin/mysqld[0x843c62d]
/usr/sbin/mysqld[0x83cf232]
/usr/sbin/mysqld[0x83d0ae8]
/usr/sbin/mysqld[0x83b560f]
/usr/sbin/mysqld(_Z15ha_create_tableP3THDPKcS2_S2_P24st_ha_create_informationb+0x139)[0x8296b19]
/usr/sbin/mysqld(_Z16rea_create_tableP3THDPKcS2_S2_P24st_ha_create_informationR4ListI12Create_fieldEjP6st_keyP7handler+0x1c9)[0x821ff59]
/usr/sbin/mysqld(_Z26mysql_create_table_no_lockP3THDPKcS2_P24st_ha_create_informationP10Alter_infobjPb+0x8a5)[0x81f88e5]
/usr/sbin/mysqld(_Z18mysql_create_tableP3THDP10TABLE_LISTP24st_ha_create_informationP10Alter_info+0xa7)[0x81f9447]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x4e05)[0x81a3645]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x188)[0x81a3d68]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x1a08)[0x81a5f88]
/usr/sbin/mysqld(_Z10do_commandP3THD+0xbe)[0x81a68ce]
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0xd3)[0x8236633]
/usr/sbin/mysqld(handle_one_connection+0x3c)[0x823674c]
/lib/libpthread.so.0(+0x6d47)[0xb777bd47]
/lib/libc.so.6(clone+0x5e)[0xb73a3d4e]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (ffffffffabb866f8): is an invalid pointer
Connection ID (thread ID): 23
Status: NOT_KILLED

[20 Oct 2014 12:29] Igor Zinkovsky

Mistype in title fixed.

[20 Oct 2014 13:19] MySQL Verification Team

Thank you for the bug report. I couldn't repeat with current source server, it's repeatable for you always? any clue to make repeatable?

[miguel@cuzcatlan dbs]$ 5.5/bin/mysql -uroot --socket=/tmp/mysql.sock 
Welcome to the MySQL monitor.  Commands end with ; or \g.             
Your MySQL connection id is 2                                         
Server version: 5.5.41-debug Source distribution                      

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> use test
Database changed
mysql> CREATE TABLE `parent` (
    -> `id` INT UNSIGNED NOT NULL PRIMARY KEY
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.10 sec)

mysql> CREATE TABLE `child` (
    -> `id` INT UNSIGNED NOT NULL PRIMARY KEY
    -> `parent_id` INT UNSIGNED NOT NULL,
    -> FOREIGN KEY (`parent_id`) REFERENCES `parent`(`id`) ON DELETE SET NULL
    -> ) ENGINE=InnoDB;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`parent_id` INT UNSIGNED NOT NULL,
FOREIGN KEY (`parent_id`) REFERENCES `parent`' at line 3
mysql> show variables like "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| innodb_version          | 5.5.41              |
| protocol_version        | 10                  |
| slave_type_conversions  |                     |
| version                 | 5.5.41-debug        |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | Linux               |
+-------------------------+---------------------+
7 rows in set (0.01 sec)

mysql>

[20 Oct 2014 14:33] MySQL Verification Team

After fixing the syntax error on the testcase, I get expected error but no crash:

mysql> CREATE TABLE `child` (
    -> `id` INT UNSIGNED NOT NULL PRIMARY KEY,
    -> `parent_id` INT UNSIGNED NOT NULL,
    -> FOREIGN KEY (parent_id) REFERENCES `parent`(`id`) ON DELETE SET NULL
    -> ) ENGINE=InnoDB;
ERROR 1005 (HY000): Can't create table 'test.child' (errno: 150)

------------------------
LATEST FOREIGN KEY ERROR
------------------------
141020 16:33:54 Error in foreign key constraint of table test/child:
FOREIGN KEY (parent_id) REFERENCES `parent`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB:
You have defined a SET NULL condition though some of the
columns are defined as NOT NULL.

[20 Oct 2014 21:22] Igor Zinkovsky

Unfortunately I can not reproduce a crash with newly created DB only on populated with data one.

Here is what i see on new DB without any data in it:

mysql> use crashdb;
Database changed
mysql> CREATE TABLE `user1` (
    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `login` varchar(25) NOT NULL DEFAULT '',
    ->   `password` varchar(32) DEFAULT NULL,
    ->   `default_jid` int(10) unsigned DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `login` (`login`),
    ->   KEY `default_jid` (`default_jid`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.09 sec)

mysql> CREATE TABLE `jid` (
    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.06 sec)

mysql> ALTER TABLE `user1` ADD FOREIGN KEY (default_jid) REFERENCES jid(id);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE `penalty` (
    ->   `id` INT UNSIGNED NOT NULL PRIMARY KEY auto_increment,
    ->   `uid` INT UNSIGNED NOT NULL,
    ->   `creator_uid` INT UNSIGNED NOT NULL,
    ->   `amnesty_uid` INT UNSIGNED NOT NULL,
    ->   FOREIGN KEY (uid) REFERENCES user(id) ON DELETE CASCADE,
    ->   FOREIGN KEY (creator_uid) REFERENCES user1(id) ON DELETE SET NULL,
    ->   FOREIGN KEY (amnesty_uid) REFERENCES user1(id) ON DELETE SET NULL
    -> ) ENGINE=InnoDB;
ERROR 1005 (HY000): Can't create table 'crashdb.penalty' (errno: 150)

And here is one of production databases with data in it (mysqlcheck reports all tables in it as OK):

mysql> use rescue;
Database changed
mysql> CREATE TABLE `user1` (
    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `login` varchar(25) NOT NULL DEFAULT '',
    ->   `password` varchar(32) DEFAULT NULL,
    ->   `default_jid` int(10) unsigned DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `login` (`login`),
    ->   KEY `default_jid` (`default_jid`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ERROR 1050 (42S01): Table 'user1' already exists
mysql> CREATE TABLE `jid` (
    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ERROR 1050 (42S01): Table 'jid' already exists
mysql> ALTER TABLE `user1` ADD FOREIGN KEY (default_jid) REFERENCES jid(id);
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE `penalty` (
    ->   `id` INT UNSIGNED NOT NULL PRIMARY KEY auto_increment,
    ->   `uid` INT UNSIGNED NOT NULL,
    ->   `creator_uid` INT UNSIGNED NOT NULL,
    ->   `amnesty_uid` INT UNSIGNED NOT NULL,
    ->   FOREIGN KEY (uid) REFERENCES user(id) ON DELETE CASCADE,
    ->   FOREIGN KEY (creator_uid) REFERENCES user1(id) ON DELETE SET NULL,
    ->   FOREIGN KEY (amnesty_uid) REFERENCES user1(id) ON DELETE SET NULL
    -> ) ENGINE=InnoDB;
ERROR 2013 (HY000): Lost connection to MySQL server during query

That's all server crashed, thats what I see in db directory:

rescue # ls -al | grep penalty
-rw-rw----  1 mysql mysql       8672 Oct 21 01:20 penalty.frm
-rw-rw----  1 mysql mysql     147456 Oct 21 01:20 penalty.ibd

[20 Oct 2014 21:28] Igor Zinkovsky

Fixed OS details. It's actually 32bit system.

And some version data from mysql:

mysql> show variables like '%version%';
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| innodb_version          | 5.5.40              |
| protocol_version        | 10                  |
| slave_type_conversions  |                     |
| version                 | 5.5.40-log          |
| version_comment         | Source distribution |
| version_compile_machine | i686                |
| version_compile_os      | Linux               |
+-------------------------+---------------------+

[21 Oct 2014 10:11] MySQL Verification Team

Not repeatable even with 5.5.40(32bit) with binary tarball and source distribution....

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 | i686                         |
| version_compile_os      | linux2.6                     |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)

mysql> CREATE TABLE `parent` (
    -> `id` INT UNSIGNED NOT NULL PRIMARY KEY
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE `child` (
    -> `id` INT UNSIGNED NOT NULL PRIMARY KEY,
    -> `parent_id` INT UNSIGNED NOT NULL,
    -> FOREIGN KEY (`parent_id`) REFERENCES `parent`(`id`) ON DELETE SET NULL
    -> ) ENGINE=InnoDB;
ERROR 1005 (HY000): Can't create table 'test.child' (errno: 150)

// Downloaded - http://dev.mysql.com/downloads/file.php?id=453784

[root@ushastry mysql-5.5.40]# cmake -DBUILD_CONFIG=mysql_release -DCMAKE_INSTALL_PREFIX=/home/ushastry/Downloads/mysql-5.5.40
[root@ushastry mysql-5.5.40]# make
[root@ushastry mysql-5.5.40]# make install

root@ushastry mysql-5.5.40]# scripts/mysql_install_db --basedir=/home/ushastry/Downloads/mysql-5.5.40 --datadir=/tmp/master --user=mysql
[root@ushastry mysql-5.5.40]# bin/mysqld  --basedir=/home/ushastry/Downloads/mysql-5.5.40 --datadir=/tmp/master --user=mysql --socket=/tmp/master/mysql.sock &

//
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         | Source distribution |
| version_compile_machine | i686                |
| version_compile_os      | Linux               |
+-------------------------+---------------------+
7 rows in set (0.00 sec)

mysql> CREATE TABLE `parent` (
    -> `id` INT UNSIGNED NOT NULL PRIMARY KEY
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.07 sec)

mysql> 
mysql> CREATE TABLE `child` (
    -> `id` INT UNSIGNED NOT NULL PRIMARY KEY,
    -> `parent_id` INT UNSIGNED NOT NULL,
    -> FOREIGN KEY (`parent_id`) REFERENCES `parent`(`id`) ON DELETE SET NULL
    -> ) ENGINE=InnoDB;
ERROR 1005 (HY000): Can't create table 'test.child' (errno: 150)

//

[root@ushastry mysql-5.5.40]# uname -an
Linux ushastry 2.6.32-431.el6.i686 #1 SMP Fri Nov 22 00:26:36 UTC 2013 i686 i686 i386 GNU/Linux
[root@ushastry mysql-5.5.40]# cat /etc/*release
CentOS release 6.5 (Final)
LSB_VERSION=base-4.0-ia32:base-4.0-noarch:core-4.0-ia32:core-4.0-noarch:graphics-4.0-ia32:graphics-4.0-noarch:printing-4.0-ia32:printing-4.0-noarch
CentOS release 6.5 (Final)
CentOS release 6.5 (Final)

How did you build this instance? Complete cmake etc details required further to investigate this issue..

[21 Oct 2014 10:13] MySQL Verification Team

Sorry, included wrong build details - I removed -DBUILD_CONFIG=mysql_release while compiling

[root@ushastry mysql-5.5.40]# cmake -DCMAKE_INSTALL_PREFIX=/home/ushastry/Downloads/mysql-5.5.40
[root@ushastry mysql-5.5.40]# make
[root@ushastry mysql-5.5.40]# make install

[22 Nov 2014 1:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".