Table Definition-Related Issues B.3.6 Table Definition-Related Issues B.3.6.1 Problems with ALTER TABLE B.3.6.2 TEMPORARY Table Problems 5314 •There are known issues in using temporary tables with replication. See Section 17.5.1.31, “Replication and Temporary Tables” , for more information. •If a  TEMPORARY  is created with the same name as an existing non- TEMPORARY  table, the non- TEMPORARY  table is hidden until the  TEMPORARY  table is dropped, even if the tables use different storage engines. •The  Can’t reopen table  error also occurs if you refer to a temporary table multiple times in a stored function under different aliases, even if the references occur in different statements within the function. It may occur for temporary tables created outside stored functions and referred to across multiple calling and callee functions. •You cannot refer to a  TEMPORARY  table more than once in the same query. For example, the following does not work: •To rename  TEMPORARY  tables,  RENAME TABLE  does not work. Use  ALTER TABLE  instead: •The  SHOW TABLES  statement does not list  TEMPORARY  tables. •Temporary tables are not supported for NDB Cluster. • TEMPORARY  tables are supported only by the  InnoDB ,  MEMORY ,  MyISAM , and  MERGE  storage engines. If you get a duplicate-key error when using  ALTER TABLE  to change the character set or collation of a character column, the cause is either that the new column collation maps two keys to the same value or that the table is corrupted. In the latter case, you should run  REPAIR TABLE  on the table.  REPAIR TABLE works for  MyISAM ,  ARCHIVE , and  CSV  tables. If you use  ALTER TABLE  on a transactional table or if you are using Windows,  ALTER TABLE  unlocks the table if you had done a  LOCK TABLE  on it. This is done because  InnoDB  and these operating systems cannot drop a table that is in use. Temporary tables created with  CREATE TEMPORARY TABLE  have the following limitations: The statement produces this error: You can work around this issue if your query permits use of a common table expression (CTE) rather than a  TEMPORARY  table. For example, this fails with the  Can’t reopen table  error: To avoid the error, use a  WITH  clause that defines a CTE, rather than the  TEMPORARY  table: WITH cte AS (SELECT 1 AS col_a, 2 AS col_b) SELECT * FROM cte AS t1 JOIN cte AS t2; CREATE TEMPORARY TABLE t SELECT 1 AS col_a, 2 AS col_b; SELECT * FROM t AS t1 JOIN t AS t2; ERROR 1137: Can’t reopen table: ’temp_table’ SELECT * FROM temp_table JOIN temp_table AS t2; ALTER TABLE old_name RENAME new_name;