MySQL Bugs: #33211: CREATE TEMPORARY TABLE ignores TABLESPACE argument under Falcon

Bug #33211	CREATE TEMPORARY TABLE ignores TABLESPACE argument under Falcon
Submitted:	13 Dec 2007 13:02	Modified:	30 Jul 2008 14:24
Reporter:	Philip Stoev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S3 (Non-critical)
Version:	6.0.4-BK	OS:	Any
Assigned to:	Sergey Vojtovich	CPU Architecture:	Any

Description:
CREATE TABLE has a TABLESPACE argument which allows one to place a specific table in a specific tablespace.

Falcon *silently* ignores this argument when creating temporary tables and creates them into the falcon_temporary tablespace. This may come as a surpise for people who want to direct a certain huge table to a certan tablespace, even if the table was temporary, or shops where every table is individually positioned and the default --datadir is supposed to be (almost) empty.

In addition, manual section "B.1.7.3. TEMPORARY TABLE Problems" says that "A TEMPORARY table can only be of type MEMORY, MyISAM, MERGE, or InnoDB", which would imply that Falcon does not support temporary tables, which I think is not the case.

How to repeat:
mysql> create temporary table b1 (f1 integer) engine = Falcon tablespace nosuchspace;
Query OK, 0 rows affected (0.01 sec)

Contrast with:

mysql> create table b1 (f1 integer) engine = Falcon tablespace nosuchspace;
ERROR 1005 (HY000): Can't create table 'test.b1' (errno: 156)

Suggested fix:
A suitable error message would be nice.

By design, temporary tables must be created in the temporary tablespace.
Space used and released will be reused while the server is running and
released to the operating system on server shutdown.

If indeed a specific tablespace is mandatory and the TABLESPACE clause is ignored, this will make Falcon's behavoir different from say MyISAM's DATADIR option.

Therefore I humbly request that we at least return an error message if TEMPORARY is combined with TABLESPACE and clarify this restriction in the documentation.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/45047

ChangeSet@1.2631, 2008-04-08 13:45:29+05:00, svoj@mysql.com +3 -0
  BUG#33211 - CREATE TEMPORARY TABLE ignores TABLESPACE argument under
              Falcon
  
  Falcon silently ignores TABLESPACE option when creating a temporary
  table.
  
  With this fix a warning is issued.

Pushed into 6.0.5-alpha

A note has been added to the 6.0.5 changelog: 

When creating temporary tables within Falcon, the tables are automatically created in the FALCON_TEMPORARY tablespace. If you specify an alternate tablespace to the CREATE TABLE statement a warning will now be issued to that effect.