Bug #60278 Make "CREATE TEMPORARY TABLE LIKE <partitionned table>" ignore partitioning info
Submitted: 28 Feb 2011 9:38 Modified: 28 Feb 2011 10:23
Reporter: Romain Muller Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S4 (Feature request)
Version:5.5.9 OS:Any
Assigned to: CPU Architecture:Any
Tags: partitioning temporary table regression

[28 Feb 2011 9:38] Romain Muller
Description:
Issuing a CREATE TEMPORARY TABLE LIKE <partitionned table> fails in MySQL 5.5.9 (possibly the whole 5.5 series), whereas it used to succeed in MySQL 5.1.x. This is breaking my replication setup and some apps, though CREATE TEMPORARY TABLE LIKE should simply drop the partitioning data when executed and go on.

How to repeat:
CREATE TABLE T1 (A INT) PARTITION BY HASH(A);
CREATE TEMPORARY TABLE T2 LIKE T1;

Suggested fix:
Have CREATE TEMPORARY TABLE ignore partitioning information when building the temporary table.
[28 Feb 2011 10:07] Valeriy Kravchuk
In what exact 5.1.x version this worked? I get error message in 5.1.54:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 73
Server version: 5.1.54-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE T1 (A INT) PARTITION BY HASH(A);
Query OK, 0 rows affected (0.19 sec)

mysql> CREATE TEMPORARY TABLE T2 LIKE T1;
ERROR 1562 (HY000): Cannot create temporary table with partitions
[28 Feb 2011 10:09] Romain Muller
5.1.47 has it working. As far as I remember it has been working at least since 5.1.41 and until 5.1.47 at least.
[28 Feb 2011 10:13] Valeriy Kravchuk
Got it. This is a documented change in behavior since 5.1.48. Check http://dev.mysql.com/doc/refman/5.1/en/news-5-1-48.html:

"Partitioning: It was possible to execute a CREATE TEMPORARY TABLE tmp LIKE pt statement, where pt is a partitioned table, even though partitioned temporary tables are not permitted, which caused the server to crash. Now a check is performed to prevent such statements from being executed. (Bug #49477)"

So, formally this is not a bug. But I agree that just ignoring partitioning in this case would be a reasonable feature request to consider.
[28 Feb 2011 10:23] Romain Muller
Changed severity to Feature Request. Updated title: