Description:
This is effectively a cosmetic error about the error message that I received back being a bit deceptive as to the actual problem.
I created 2 tables one which had a foreign key referencing the other and the foreign key had the on delete set null however I had messed up and put a not null constraint on the column. The DB caught this error on my part and yielded the following error message:
ERROR 1005 (HY000): Can't create table './analyzer/#sql-47c1_16.frm' (errno: 150)
I believe this error message could be improved to tell what the problem was in this case. Perhaps the error could be something more like:
ERROR 1005 (HY000): Can't set null on not null column
How to repeat:
mysql> CREATE TABLE `retention_rule` (
-> `rule_id` bigint(20) unsigned NOT NULL,
-> `name` varchar(64) default NULL,
-> PRIMARY KEY (`rule_id`),
-> UNIQUE KEY `AK1an_retention_rule` (`name`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> CREATE TABLE `retention_rule_items` (
-> `rule_item_id` bigint(20) unsigned NOT NULL,
-> `rule_id` bigint(20) unsigned NOT NULL,
-> PRIMARY KEY (`rule_item_id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> ALTER TABLE retention_rule_items
-> ADD CONSTRAINT fkey
-> FOREIGN KEY (rule_id)
-> REFERENCES retention_rule (rule_id)
-> ON DELETE SET NULL;
ERROR 1005 (HY000): Can't create table './analyzer/#sql-47c1_16.frm' (errno: 150)
mysql>
mysql>
mysql> alter table retention_rule_items modify column rule_id bigint(20) unsigned NULL;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> ALTER TABLE retention_rule_items
-> ADD CONSTRAINT fkey
-> FOREIGN KEY (rule_id)
-> REFERENCES retention_rule (rule_id)
-> ON DELETE SET NULL;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
Suggested fix:
Change error message in this case to be something like "Can't set null on not null column"
Description: This is effectively a cosmetic error about the error message that I received back being a bit deceptive as to the actual problem. I created 2 tables one which had a foreign key referencing the other and the foreign key had the on delete set null however I had messed up and put a not null constraint on the column. The DB caught this error on my part and yielded the following error message: ERROR 1005 (HY000): Can't create table './analyzer/#sql-47c1_16.frm' (errno: 150) I believe this error message could be improved to tell what the problem was in this case. Perhaps the error could be something more like: ERROR 1005 (HY000): Can't set null on not null column How to repeat: mysql> CREATE TABLE `retention_rule` ( -> `rule_id` bigint(20) unsigned NOT NULL, -> `name` varchar(64) default NULL, -> PRIMARY KEY (`rule_id`), -> UNIQUE KEY `AK1an_retention_rule` (`name`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.01 sec) mysql> mysql> CREATE TABLE `retention_rule_items` ( -> `rule_item_id` bigint(20) unsigned NOT NULL, -> `rule_id` bigint(20) unsigned NOT NULL, -> PRIMARY KEY (`rule_item_id`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.01 sec) mysql> mysql> ALTER TABLE retention_rule_items -> ADD CONSTRAINT fkey -> FOREIGN KEY (rule_id) -> REFERENCES retention_rule (rule_id) -> ON DELETE SET NULL; ERROR 1005 (HY000): Can't create table './analyzer/#sql-47c1_16.frm' (errno: 150) mysql> mysql> mysql> alter table retention_rule_items modify column rule_id bigint(20) unsigned NULL; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> mysql> ALTER TABLE retention_rule_items -> ADD CONSTRAINT fkey -> FOREIGN KEY (rule_id) -> REFERENCES retention_rule (rule_id) -> ON DELETE SET NULL; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 Suggested fix: Change error message in this case to be something like "Can't set null on not null column"