List:General Discussion« Previous MessageNext Message »
From:Dante Lorenso Date:December 11 2009 2:59pm
Subject:temporary merge table as table name alias
View as plain text  
All,

Is is possible to create temporary MERGE tables?

This code gives an error:

-------------------- 8< ---------------------------------------- 8<
--------------------

-- clean slate
DROP TABLE IF EXISTS test_abcdefgh;
DROP TABLE IF EXISTS dante;

-- create demo table
CREATE TABLE test_abcdefgh (
   `myvalue` INT(11) UNSIGNED NOT NULL,
   `modified` DATETIME DEFAULT NULL
) ENGINE=MYISAM DEFAULT CHARSET=utf8;

-- sample data
INSERT INTO test_abcdefgh (myvalue) VALUES (1), (3), (5);

-- clone the table structure
CREATE TEMPORARY TABLE dante LIKE test_abcdefgh;

-- change the temporary table to a merge table
ALTER TABLE dante ENGINE=MERGE UNION(test_abcdefgh);

-- check tables
SELECT * FROM test_abcdefgh;
SELECT * FROM dante;

-------------------- 8< ---------------------------------------- 8<
--------------------

Error Code : 1168
Unable to open underlying table which is differently defined or of
non-MyISAM type or doesn't exist

If you change the line to:

> CREATE TABLE dante LIKE test_abcdefgh;

instead of:

> CREATE TEMPORARY TABLE dante LIKE test_abcdefgh;

Then, the error goes away and the code works.  Having a TEMPORARY table is
very important for me, though, because I want the table name "dante" to not
be visible to other connections.  The whole point of this code is to enable
me to create "alias" names for tables inside a stored procedure.

Can someone tell me why the temporary merge table is failing?

Dante

Thread
temporary merge table as table name aliasDante Lorenso11 Dec