List:InnoDB Storage Engine« Previous MessageNext Message »
From:Marcus Bointon Date:June 28 2009 1:27am
Subject:Error 1005 with errno -1 on CREATE TEMPORARY TABLE
View as plain text  
Hi,

I'm having a weird problem with creating temporary tables in a 100%  
InnoDB database.

This works (so I have permissions etc):
mysql> create temporary table a (b int);

With identical schemas and permissions, this works on 5.0.67, and  
5.0.77, but fails on 5.0.51a:
mysql> create temporary table a like b;
ERROR 1005 (HY000): Can't create table '/tmp/ 
#sql7a02_727779_4' (errno: -1)

However, this works on both:
mysql> create table a like b;

 From the docs I see this:

> You cannot create a table with a column name that matches the name  
> of an internal InnoDB column (including DB_ROW_ID, DB_TRX_ID,  
> DB_ROLL_PTR and DB_MIX_ID). In versions of MySQL before 5.1.10 this  
> would cause a crash, since 5.1.10 the server will report error 1005  
> and refers to errno -1 in the error message.

There's no indication that this limitation is specific to temporary  
tables, though the fact that non-temp table creation works OK suggests  
there is no such clash. I can't find an obvious reference to what  
these reserved column names might be - though in the table in question  
they are all simple things like 'firstname', 'lastname',  
'last_modified'. I've tried it with some other tables and all exhibit  
the same behaviour.

In other docs it says temp table creation can fail if foreign keys  
prevent it, however, in this table there are (deliberately) no FKs  
defined, and the table is empty (it's just used as a template for temp  
tables).

Is this a bug in 5.0.51a that I need to upgrade to work around?

Marcus
-- 
Marcus Bointon
Synchromedia Limited: Creators of http://www.smartmessages.net/
UK resellers of info@hand CRM solutions
marcus@stripped | http://www.synchromedia.co.uk/


Thread
Error 1005 with errno -1 on CREATE TEMPORARY TABLEMarcus Bointon28 Jun
  • Re: Error 1005 with errno -1 on CREATE TEMPORARY TABLEMarcus Bointon29 Jun