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 1:55	Modified:	14 Dec 2007 9:19
Reporter:	Kolbe Kegel
Status:	Verified
Category:	Server: InnoDB	Severity:	S4 (Feature request)
Version:	5.1.22	OS:	Any
Assigned to:	Heikki Tuuri	Target Version:
Tags:	innodb, foreign keys, error
Triage:	Triaged: D5 (Feature request)

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.