Creating a temporary copy of a table with partitions

I've got a little issue and wonder if anyone has come across it themselves or seen a solution.

In one of my reporting scripts, it creates a temporary copy of a table before inserting some rows in order to a bulk update on the source table. To create a copy I used the SQL

CREATE TEMPORARY TABLE myTable LIKE source.myTable;

Which works fine, apart from one table which is partitioned. MySQL can't create temp tables with partitions.

I can't do the alternative which would be to do...

CREATE TEMPORARY TABLE myTable SELECT * from source.myTable;

If you do that it a) only creates the column definitions to fit the data in the select (i.e. a VARCHAR column will only be defined according to the max length of the column in the select statement) and b) I don't want to copy all 4 million rows simply to truncate them in the next statement!

Is the only other option to create it manually from the information schema? That really feels like using a sledgehammer to crack a walnut, surely there must be a simpler way to do it? Not too mention a lot of work.

You can create a non temporary copy of the original table first.

CREATE TABLE intermediate_table LIKE my_table;

It won't have any records so altering it should be quick:

ALTER TABLE intermediate_table REMOVE PARTITIONING;

Then create your temporary table:

CREATE TEMPORARY TABLE tmp_table LIKE intermediate_table;

Then just drop the intermediate:

DROP TABLE intermediate_table;

Now you can play around with your temporary table without worrying of leaving any permanent table behind if your script halts, which I assume is the reason why we use temporary tables instead of permanent ones.

StackExchange Database Administrators Q#210869, answer score: 4