MySQL Bugs: #10105: Obscure error when creating temporary table with foreign key

Bug #10105	Obscure error when creating temporary table with foreign key
Submitted:	22 Apr 2005 21:00	Modified:	16 Jun 2005 16:49
Reporter:	Boris Burtin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	4.1.11	OS:	Windows (Windows XP)
Assigned to:	Heikki Tuuri	CPU Architecture:	Any

Description:
It took me a while to figure out why my temporary table wasn't being created properly.  Turned out that foreign keys aren't allowed on temporary tables.  The error that MySQL generated was pretty obscure.

How to repeat:
mysql> create table a (id integer not null primary key);
Query OK, 0 rows affected (0.19 sec)

mysql> create temporary table b (a_id integer not null, foreign key (a_id) refer
ences a(id));
ERROR 1005 (HY000): Can't create table 'c:\temp\#sqla98_14_0.frm' (errno: 150)

If I take out the word "temporary", table b is created successfully.

Suggested fix:
Handle this case more gracefully and generate a meaningful error message.

Hi!

Please send the output of

SHOW INNODB STATUS\G

It contains a detailed explanation of the latest FOREIGN KEY error.

Regards,

Heikki

Looks like even SHOW INNODB STATUS doesn't explain what's going wrong.  It would be great if the error I got from MySQL when creating table b clearly told me that foreign keys on temporary tables are not allowed.

------------------------
LATEST FOREIGN KEY ERROR
------------------------
050425  9:59:40 Error in foreign key constraint of table temp/#sqleb4_5_0:
foreign key (a_id) references a(id)):
Cannot resolve table name close to:
(id))

Boris,

now I see: since the temp table is really in the pseudo-database 'tmp', there is no table 'a' there! That is why it cannot resolve.

Temporary tables have an internal name #sql... . The foreign key parser is not aware what logical table name corresponds to what internal table name. That is why foreign keys cannot work on temporary tables.

I have to document this in the manual. Also a better error message in SHOW INNODB STATUS is warranted.

Thank you,

Heikki

<listitem><para>
     Both tables must be <literal>InnoDB</literal> type and they must
     not be temporary tables.
    </para></listitem>

Shouldn't this be made to work rather than just marking it as an error? Are not foreign keys still valid within temporary tables?

(Newbie if I have missed something!)