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 | Email Updates: | |
Status: | No Feedback | Impact on me: | |
Category: | MySQL Server: DDL | Severity: | S1 (Critical) |
Version: | 5.5.40 | OS: | Linux (2.6.39-gentoo-r3 i686) |
Assigned to: | CPU Architecture: | Any |
[20 Oct 2014 12:26]
Igor Zinkovsky
[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".