MySQL Bugs: #40794: Unable to create a temporary table when the "LIKE" source is a innoDB

Bug #40794	Unable to create a temporary table when the "LIKE" source is a innoDB
Submitted:	17 Nov 2008 17:32	Modified:	17 Dec 2008 19:50
Reporter:	Flavien Volken	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	6.0.7	OS:	MacOS (10.5.5)
Assigned to:		CPU Architecture:	Any
Tags:	innodb temporary table mac

Description:
I've a table A of type InnoDB into the database B, I want to create a temporary table T with the same characteristics of A using the "LIKE" function.
My query is the following one: CREATE TEMPORARY TABLE `B`.`T` LIKE A;

While I've all the rights to create temp tables, and even if I'm able to do so by specifying directly the fields names, instead of creating a T table the query above always returned the same error: Unknown table 'T'.
The problem was even worst on mysql 5.0.67 mac where I even got an error message like you cannot even understand the meaning… like a -Tmp#thing or so… I don't remember and did not took note of it.
Anyway, finally I decided to change the type from InnoDB to MyISAM and this solved the problem.

How to repeat:
Run mysql 6.0.7 on mac os x 10.5.5 and create A DB named "B" containing an InnoDB table "A"

Add a single field to the table "A" (is does not matter which type)

Run the following query from your client: CREATE TEMPORARY TABLE `B`.`T` LIKE A;

You should get your error message...

Suggested fix:
I changed the source table type from InnoDB to MyISAM

I can not repeat this with 6.0.7. Look:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3311 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 6.0.7-alpha-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show create table t1\G
ERROR 1146 (42S02): Table 'test.t1' doesn't exist
mysql> create table t1 (c1 int) engine=InnoDB;
Query OK, 0 rows affected (0.20 sec)

mysql> create temporary table test.t_temp2 like t1;
Query OK, 0 rows affected (0.09 sec)

mysql> create temporary table test.t_temp3 like test.t1;
Query OK, 0 rows affected (0.06 sec)

Please, send your my.cnf.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".