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	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.5/5.6/5.7	OS:	MacOS (10.10.3)
Assigned to:		CPU Architecture:	Any
Tags:	cascade, set null, SQL

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.

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)

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 >

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'.

Page generated in 0.045 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.