Bug #80052 SET NULL does not work, but CASCADE is OK

• Submitted: 19 Jan 2016 16:09
• Modified: 10 Mar 2016 6:10
• Reporter: David Lobron
• Status: Not a Bug
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 5.5/5.6/5.7
• OS: MacOS (10.10.3)
• CPU Architecture: Any
• Tags: cascade, set null, SQL

[19 Jan 2016 16:09] David Lobron

Description:
I have a series of SQL statements.  One of them contains a SET NULL, and it causes an error.  If I change the SET NULL to CASCADE, the error goes away.  I verified that the error does not occur with PostGRESQL.  I've included the full SQL below- it reproduces every time on my system.  The error I get looks like this:

ERROR 1005 (HY000): Can't create table 'chugbot_db.block_instances' (errno: 150)

How to repeat:
Run the following:

CREATE DATABASE IF NOT EXISTS chugbot_db COLLATE utf8_unicode_ci;

USE chugbot_db;

CREATE TABLE sessions(
session_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
name varchar(50) NOT NULL,
UNIQUE KEY uk_sessions(name))
COLLATE utf8_unicode_ci;

CREATE TABLE blocks(
block_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
name varchar(50) NOT NULL,
UNIQUE KEY uk_blocks(name))
COLLATE utf8_unicode_ci;

CREATE TABLE edot(
edah_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
name varchar(50) NOT NULL,
UNIQUE KEY uk_edot(name))
COLLATE utf8_unicode_ci;

CREATE TABLE campers(
camper_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
edah_id int,
FOREIGN KEY fk_edah_id(edah_id) REFERENCES edot(edah_id)
ON DELETE SET NULL
ON UPDATE CASCADE,
session_id int,
FOREIGN KEY fk_session_id(session_id) REFERENCES sessions(session_id)
ON DELETE SET NULL
ON UPDATE CASCADE,
first varchar(50) NOT NULL,
last varchar(50) NOT NULL,
email varchar(50) NOT NULL,
needs_first_choice bool DEFAULT 0,
active bool NOT NULL DEFAULT 1)
COLLATE utf8_unicode_ci;

CREATE TABLE block_instances(
block_id int NOT NULL,
FOREIGN KEY fk_block_id(block_id) REFERENCES blocks(block_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
session_id int,
FOREIGN KEY fk_session_id(session_id) REFERENCES sessions(session_id)
ON DELETE SET NULL
ON UPDATE CASCADE,
PRIMARY KEY pk_block_instances(block_id, session_id))
COLLATE utf8_unicode_ci;

Suggested fix:
I'm able to fix this by changing SET NULL to CASCADE in the block_instances table.

[27 Jan 2016 15:19] MySQL Verification Team

Not repeatable with 5.0 and 5.1 version. Looks like the issue exists from 5.5 to 5.7 version.

C:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.97-Win X64 Source distribution

Copyright (c) 2000, 2011, 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 5.0 > CREATE DATABASE IF NOT EXISTS chugbot_db COLLATE utf8_unicode_ci;
Query OK, 1 row affected (0.00 sec)

mysql 5.0 >
mysql 5.0 > USE chugbot_db;
Database changed
mysql 5.0 >
mysql 5.0 > CREATE TABLE sessions(
    -> session_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> name varchar(50) NOT NULL,
    -> UNIQUE KEY uk_sessions(name))
    -> COLLATE utf8_unicode_ci;
Query OK, 0 rows affected (0.01 sec)

mysql 5.0 >
mysql 5.0 > CREATE TABLE blocks(
    -> block_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> name varchar(50) NOT NULL,
    -> UNIQUE KEY uk_blocks(name))
    -> COLLATE utf8_unicode_ci;
Query OK, 0 rows affected (0.01 sec)

mysql 5.0 >
mysql 5.0 > CREATE TABLE edot(
    -> edah_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> name varchar(50) NOT NULL,
    -> UNIQUE KEY uk_edot(name))
    -> COLLATE utf8_unicode_ci;
Query OK, 0 rows affected (0.01 sec)

mysql 5.0 >
mysql 5.0 > CREATE TABLE campers(
    -> camper_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> edah_id int,
    -> FOREIGN KEY fk_edah_id(edah_id) REFERENCES edot(edah_id)
    -> ON DELETE SET NULL
    -> ON UPDATE CASCADE,
    -> session_id int,
    -> FOREIGN KEY fk_session_id(session_id) REFERENCES sessions(session_id)
    -> ON DELETE SET NULL
    -> ON UPDATE CASCADE,
    -> first varchar(50) NOT NULL,
    -> last varchar(50) NOT NULL,
    -> email varchar(50) NOT NULL,
    -> needs_first_choice bool DEFAULT 0,
    -> active bool NOT NULL DEFAULT 1)
    -> COLLATE utf8_unicode_ci;
Query OK, 0 rows affected (0.01 sec)

mysql 5.0 >
mysql 5.0 > CREATE TABLE block_instances(
    -> block_id int NOT NULL,
    -> FOREIGN KEY fk_block_id(block_id) REFERENCES blocks(block_id)
    -> ON DELETE CASCADE
    -> ON UPDATE CASCADE,
    -> session_id int,
    -> FOREIGN KEY fk_session_id(session_id) REFERENCES sessions(session_id)
    -> ON DELETE SET NULL
    -> ON UPDATE CASCADE,
    -> PRIMARY KEY pk_block_instances(block_id, session_id))
    -> COLLATE utf8_unicode_ci;
Query OK, 0 rows affected (0.01 sec)

mysql 5.0 >

C:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --debug-info --prompt="mysql 5.1 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.74-Win X64 Source distribution

Copyright (c) 2000, 2013, 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 5.1 > CREATE DATABASE IF NOT EXISTS chugbot_db COLLATE utf8_unicode_ci;
Query OK, 1 row affected (0.00 sec)

mysql 5.1 >
mysql 5.1 > USE chugbot_db;
Database changed
mysql 5.1 >
mysql 5.1 > CREATE TABLE sessions(
    -> session_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> name varchar(50) NOT NULL,
    -> UNIQUE KEY uk_sessions(name))
    -> COLLATE utf8_unicode_ci;
Query OK, 0 rows affected (0.01 sec)

mysql 5.1 >
mysql 5.1 > CREATE TABLE blocks(
    -> block_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> name varchar(50) NOT NULL,
    -> UNIQUE KEY uk_blocks(name))
    -> COLLATE utf8_unicode_ci;
Query OK, 0 rows affected (0.01 sec)

mysql 5.1 >
mysql 5.1 > CREATE TABLE edot(
    -> edah_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> name varchar(50) NOT NULL,
    -> UNIQUE KEY uk_edot(name))
    -> COLLATE utf8_unicode_ci;
Query OK, 0 rows affected (0.01 sec)

mysql 5.1 >
mysql 5.1 > CREATE TABLE campers(
    -> camper_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> edah_id int,
    -> FOREIGN KEY fk_edah_id(edah_id) REFERENCES edot(edah_id)
    -> ON DELETE SET NULL
    -> ON UPDATE CASCADE,
    -> session_id int,
    -> FOREIGN KEY fk_session_id(session_id) REFERENCES sessions(session_id)
    -> ON DELETE SET NULL
    -> ON UPDATE CASCADE,
    -> first varchar(50) NOT NULL,
    -> last varchar(50) NOT NULL,
    -> email varchar(50) NOT NULL,
    -> needs_first_choice bool DEFAULT 0,
    -> active bool NOT NULL DEFAULT 1)
    -> COLLATE utf8_unicode_ci;
Query OK, 0 rows affected (0.01 sec)

mysql 5.1 >
mysql 5.1 > CREATE TABLE block_instances(
    -> block_id int NOT NULL,
    -> FOREIGN KEY fk_block_id(block_id) REFERENCES blocks(block_id)
    -> ON DELETE CASCADE
    -> ON UPDATE CASCADE,
    -> session_id int,
    -> FOREIGN KEY fk_session_id(session_id) REFERENCES sessions(session_id)
    -> ON DELETE SET NULL
    -> ON UPDATE CASCADE,
    -> PRIMARY KEY pk_block_instances(block_id, session_id))
    -> COLLATE utf8_unicode_ci;
Query OK, 0 rows affected (0.01 sec)

[27 Jan 2016 15:25] MySQL Verification Team

Thank you for the bug report. Affected version: 5.5/5.6/5.7:

C:\dbs>c:\dbs\5.5\bin\mysql -uroot --port=3550 --prompt="mysql 5.5 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.49 Source distribution PULL: 2016-JAN-14

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 5.5 > CREATE DATABASE IF NOT EXISTS chugbot_db COLLATE utf8_unicode_ci;
Query OK, 1 row affected (0.00 sec)

mysql 5.5 >
mysql 5.5 > USE chugbot_db;
Database changed
mysql 5.5 >
mysql 5.5 > CREATE TABLE sessions(
    -> session_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> name varchar(50) NOT NULL,
    -> UNIQUE KEY uk_sessions(name))
    -> COLLATE utf8_unicode_ci;
Query OK, 0 rows affected (0.01 sec)

mysql 5.5 >
mysql 5.5 > CREATE TABLE blocks(
    -> block_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> name varchar(50) NOT NULL,
    -> UNIQUE KEY uk_blocks(name))
    -> COLLATE utf8_unicode_ci;
Query OK, 0 rows affected (0.01 sec)

mysql 5.5 >
mysql 5.5 > CREATE TABLE edot(
    -> edah_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> name varchar(50) NOT NULL,
    -> UNIQUE KEY uk_edot(name))
    -> COLLATE utf8_unicode_ci;
Query OK, 0 rows affected (0.01 sec)

mysql 5.5 >
mysql 5.5 > CREATE TABLE campers(
    -> camper_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> edah_id int,
    -> FOREIGN KEY fk_edah_id(edah_id) REFERENCES edot(edah_id)
    -> ON DELETE SET NULL
    -> ON UPDATE CASCADE,
    -> session_id int,
    -> FOREIGN KEY fk_session_id(session_id) REFERENCES sessions(session_id)
    -> ON DELETE SET NULL
    -> ON UPDATE CASCADE,
    -> first varchar(50) NOT NULL,
    -> last varchar(50) NOT NULL,
    -> email varchar(50) NOT NULL,
    -> needs_first_choice bool DEFAULT 0,
    -> active bool NOT NULL DEFAULT 1)
    -> COLLATE utf8_unicode_ci;
Query OK, 0 rows affected (0.01 sec)

mysql 5.5 >
mysql 5.5 > CREATE TABLE block_instances(
    -> block_id int NOT NULL,
    -> FOREIGN KEY fk_block_id(block_id) REFERENCES blocks(block_id)
    -> ON DELETE CASCADE
    -> ON UPDATE CASCADE,
    -> session_id int,
    -> FOREIGN KEY fk_session_id(session_id) REFERENCES sessions(session_id)
    -> ON DELETE SET NULL
    -> ON UPDATE CASCADE,
    -> PRIMARY KEY pk_block_instances(block_id, session_id))
    -> COLLATE utf8_unicode_ci;
ERROR 1005 (HY000): Can't create table 'chugbot_db.block_instances' (errno: 150)

C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 -p --prompt="mysql 5.6 > "
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.30 Source distribution PULL: 2016-JAN-14

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 5.6 > CREATE DATABASE IF NOT EXISTS chugbot_db COLLATE utf8_unicode_ci;
Query OK, 1 row affected (0.00 sec)

mysql 5.6 >
mysql 5.6 > USE chugbot_db;
Database changed
mysql 5.6 >
mysql 5.6 > CREATE TABLE sessions(
    -> session_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> name varchar(50) NOT NULL,
    -> UNIQUE KEY uk_sessions(name))
    -> COLLATE utf8_unicode_ci;
Query OK, 0 rows affected (0.03 sec)

mysql 5.6 >
mysql 5.6 > CREATE TABLE blocks(
    -> block_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> name varchar(50) NOT NULL,
    -> UNIQUE KEY uk_blocks(name))
    -> COLLATE utf8_unicode_ci;
Query OK, 0 rows affected (0.03 sec)

mysql 5.6 >
mysql 5.6 > CREATE TABLE edot(
    -> edah_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> name varchar(50) NOT NULL,
    -> UNIQUE KEY uk_edot(name))
    -> COLLATE utf8_unicode_ci;
Query OK, 0 rows affected (0.03 sec)

mysql 5.6 >
mysql 5.6 > CREATE TABLE campers(
    -> camper_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> edah_id int,
    -> FOREIGN KEY fk_edah_id(edah_id) REFERENCES edot(edah_id)
    -> ON DELETE SET NULL
    -> ON UPDATE CASCADE,
    -> session_id int,
    -> FOREIGN KEY fk_session_id(session_id) REFERENCES sessions(session_id)
    -> ON DELETE SET NULL
    -> ON UPDATE CASCADE,
    -> first varchar(50) NOT NULL,
    -> last varchar(50) NOT NULL,
    -> email varchar(50) NOT NULL,
    -> needs_first_choice bool DEFAULT 0,
    -> active bool NOT NULL DEFAULT 1)
    -> COLLATE utf8_unicode_ci;
Query OK, 0 rows affected (0.03 sec)

mysql 5.6 >
mysql 5.6 > CREATE TABLE block_instances(
    -> block_id int NOT NULL,
    -> FOREIGN KEY fk_block_id(block_id) REFERENCES blocks(block_id)
    -> ON DELETE CASCADE
    -> ON UPDATE CASCADE,
    -> session_id int,
    -> FOREIGN KEY fk_session_id(session_id) REFERENCES sessions(session_id)
    -> ON DELETE SET NULL
    -> ON UPDATE CASCADE,
    -> PRIMARY KEY pk_block_instances(block_id, session_id))
    -> COLLATE utf8_unicode_ci;
ERROR 1215 (HY000): Cannot add foreign key constraint

C:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.12 Source distribution PULL: 2016-JAN-14

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 5.7 > CREATE DATABASE IF NOT EXISTS chugbot_db COLLATE utf8_unicode_ci;
Query OK, 1 row affected (0.00 sec)

mysql 5.7 >
mysql 5.7 > USE chugbot_db;
Database changed
mysql 5.7 >
mysql 5.7 > CREATE TABLE sessions(
    -> session_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> name varchar(50) NOT NULL,
    -> UNIQUE KEY uk_sessions(name))
    -> COLLATE utf8_unicode_ci;
Query OK, 0 rows affected (0.03 sec)

mysql 5.7 >
mysql 5.7 > CREATE TABLE blocks(
    -> block_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> name varchar(50) NOT NULL,
    -> UNIQUE KEY uk_blocks(name))
    -> COLLATE utf8_unicode_ci;
Query OK, 0 rows affected (0.03 sec)

mysql 5.7 >
mysql 5.7 > CREATE TABLE edot(
    -> edah_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> name varchar(50) NOT NULL,
    -> UNIQUE KEY uk_edot(name))
    -> COLLATE utf8_unicode_ci;
Query OK, 0 rows affected (0.03 sec)

mysql 5.7 >
mysql 5.7 > CREATE TABLE campers(
    -> camper_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> edah_id int,
    -> FOREIGN KEY fk_edah_id(edah_id) REFERENCES edot(edah_id)
    -> ON DELETE SET NULL
    -> ON UPDATE CASCADE,
    -> session_id int,
    -> FOREIGN KEY fk_session_id(session_id) REFERENCES sessions(session_id)
    -> ON DELETE SET NULL
    -> ON UPDATE CASCADE,
    -> first varchar(50) NOT NULL,
    -> last varchar(50) NOT NULL,
    -> email varchar(50) NOT NULL,
    -> needs_first_choice bool DEFAULT 0,
    -> active bool NOT NULL DEFAULT 1)
    -> COLLATE utf8_unicode_ci;
Query OK, 0 rows affected (0.05 sec)

mysql 5.7 >
mysql 5.7 > CREATE TABLE block_instances(
    -> block_id int NOT NULL,
    -> FOREIGN KEY fk_block_id(block_id) REFERENCES blocks(block_id)
    -> ON DELETE CASCADE
    -> ON UPDATE CASCADE,
    -> session_id int,
    -> FOREIGN KEY fk_session_id(session_id) REFERENCES sessions(session_id)
    -> ON DELETE SET NULL
    -> ON UPDATE CASCADE,
    -> PRIMARY KEY pk_block_instances(block_id, session_id))
    -> COLLATE utf8_unicode_ci;
ERROR 1215 (HY000): Cannot add foreign key constraint
mysql 5.7 >

[10 Mar 2016 6:10] Karthik Kamath Koteshwar

Here an error is being reported when a SET NULL constraint is applied for a Foreign key when the Foreign key column is used as a Primary key. This is an expected behaviour beacause a Primary Key cannot have a NULL value. Hence closing it as 'not a bug'.