MySQL Bugs: #33027: misleading error message for FK referencing non InnoDB table

Bug #33027	misleading error message for FK referencing non InnoDB table
Submitted:	6 Dec 2007 0:55	Modified:	4 Sep 2019 17:55
Reporter:	Kolbe Kegel	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	5.1.22	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	error, foreign keys, innodb

Description:
When attempting to create a Foreign Key that references a partitioned InnoDB table, a misleading error message is issued.

How to repeat:
create table t1 (id int not null primary key) engine=innodb partition by hash (id) partitions 2;
create table t2 (id int, foreign key (id) references t1 (id)) engine=innodb;

mysql 5.1.22-rc (root) [test]> create table t1 (id int not null primary key) engine=innodb partition by hash (id) partitions 2;
Query OK, 0 rows affected (0.01 sec)

mysql 5.1.22-rc (root) [test]> create table t2 (id int, foreign key (id) references t1 (id)) engine=innodb;
ERROR 1005 (HY000): Can't create table 'test.t2' (errno: 150)

"errno: 150" is a generic InnoDB error that can mean almost anything has gone wrong. So, one looks at SHOW ENGINE INNODB STATUS to get the full explanation for the most recent Foreign Key problem:

mysql 5.1.22-rc (root) [test]> show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
071205 16:47:39 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 56 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 12, signal count 12
Mutex spin waits 0, rounds 140, OS waits 0
RW-shared spins 22, OS waits 11; RW-excl spins 2, OS waits 1
------------------------
LATEST FOREIGN KEY ERROR
------------------------
071205 16:46:55 Error in foreign key constraint of table test/t2:
foreign key (id) references t1 (id)) engine=innodb:
Cannot resolve table name close to:
 (id)) engine=innodb

The error here, "Cannot resolve table name close to:", does not even begin to describe that the problem is in some way related to the fact that the parent table is partitioned.

Removing partitioning from the parent table allows t2 to be created without any problem:

mysql 5.1.22-rc (root) [test]> alter table t1 remove partitioning;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 5.1.22-rc (root) [test]> create table t2 (id int, foreign key (id) references t1 (id)) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

Suggested fix:
The error message should more accurately describe the nature of the problem and possibly point the user towards a resolution.

A similar error is issued if trying to add a foreign key to an existing partitioned InnoDB table.

create table t1 (id int not null primary key) engine=innodb partition by hash(id) partitions 2;
create table t2 (id int not null primary key) engine=innodb partition by hash(id) partitions 2;
alter table t2 add foreign key (id) references t1 (id);

mysql 5.1.22-rc (root) [test]> create table t1 (id int not null primary key) engine=innodb partition by hash(id) partitions 2;
Query OK, 0 rows affected (0.02 sec)

mysql 5.1.22-rc (root) [test]> create table t2 (id int not null primary key) engine=innodb partition by hash(id) partitions 2;
Query OK, 0 rows affected (0.00 sec)

mysql 5.1.22-rc (root) [test]> alter table t2 add foreign key (id) references t1 (id);
ERROR 1005 (HY000): Can't create table 'test.#sql-5d4a_2' (errno: 1)

mysql 5.1.22-rc (root) [test]> show engine innodb status\G
[...]
------------------------
LATEST FOREIGN KEY ERROR
------------------------
071205 17:16:59 Error in foreign key constraint of table test/#sql-5d4a_2#P#p0:
foreign key (id) references t1 (id):
Cannot resolve table name close to:
 (id)

looks like a duplicate of 32948.

This is not a duplicate of 32948, really. This bug is about a misleading error message; the other bug is about behavior that is currently allowed but *should* produce an error message (better than those discussed in this bug, ideally).

Not a partitioning bug, since the result is the same if you try to create a foreign key from a InnoDB table to a non InnoDB table. (A partitioned innodb does not use innodb storage engine at the table level and it is documented as not supported, just like MyISAM).

5.1.23:
CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=MyISAM;
CREATE TABLE t2 (a INT, FOREIGN KEY (a) REFERENCES t1 (a)) ENGINE=InnoDB;
ERROR HY000: Can't create table 'test.t2' (errno: 150)

... I wouldn't make any attempt to dispute what you said, since it makes perfect sense. You've simply explained why I originally opened the bug in the "Server: InnoDB" category. Why it was later changed to "Server: Partitioning" is a mystery to me, but I don't see how that by itself makes this "Not a bug".

This is not related to Bug#32948. The bug is:
When referencing a FK to a non InnoDb table (or partitioned InnoDB table) it gives a misleading error message:
RROR HY000: Can't create table 'test.t2' (errno: 150)

It would be better if it could return something that showed that it failed on FK referencing table.

Changing back to Server: InnoDB. (I have updated the Synopsis, and changed it back to P4)

Hmm... InnoDB could check if a partition table t1#p1 or something exists.

When Konstantin's foreign key implementation for MySQL is completed, it may give a more descriptive error message.

Posted by developer:
 
Hello!

The fix for bug#25722927 "NEWDD FK: ALTER TABLE CHANGE
COLUMN TYPE SHOULD CHECK FK CONSTRAINT", which was
published in MySQL version 8.0.14 has improved error
message which is emitted when someone tries to
create a foreign key with partitioned table as a
parent. Error emitted in this case now is:
ERROR HY000: Foreign keys are not yet supported in conjunction with partitioning

Error which is emitted in case when one tries to reference
table in storage engine different than one of child table
is less clear, but still better than generic error before:
ERROR 1824 (HY000): Failed to open the referenced table 'myisam_table'

Taking into account the above (and the fact that second situation should
be more rare nowadays) I am closing this feature request as implemented.

Moving it to Documenting state to let Documentation Team to decide if
anything needs to be adjusted in our manual.

Posted by developer:
 
Fixed as of the upcoming 8.0.14 release, and here's the changelog entry:

Misleading error messages were reported for unsupported foreign key
operations, including creating a foreign key that referenced a partitioned
table, and referencing a table that uses a storage engine that does not
support foreign keys. The error messages are now more informative.