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.