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 23:00	Modified:	16 Jun 2005 18:49
Reporter:	Boris Burtin
Status:	Closed
Category:	Server: InnoDB	Severity:	S3 (Non-critical)
Version:	4.1.11	OS:	Microsoft Windows (Windows XP)
Assigned to:	Heikki Tuuri	Target Version:

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>