1

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.

  • Please elaborate on what you will be doing with the temp table; there may be other tricks to use. – Rick James Jun 29 '18 at 18:34
1

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.

  • That's a really good idea! I can't remember what I was doing where I had this issue (this ticket is a bit old), but that would address the partitioning issue! – Steve Childs Sep 13 at 12:43
0
CREATE TEMPORARY TABLE myTable
    SELECT * from source.myTable
        LIMIT 0;           -- Add this

Still creates the temp table, but puts no rows in it.

0

Why don't you do this in two steps instead?

CREATE TEMPORARY TABLE myTable LIKE db1.new_table;

Insert into db1.myTable (col1, col2, col3) select col11, col22, col33 from db1.new_table where id > 10 and id <20 ;

Then you don't have to transfer 4 million rows, only the rows you need

  • The CREATE fails. – Rick James Jun 29 '18 at 18:29
  • @RickJames I edited the code a bit, tested it on the server and it seems to be working – Anirban Jun 29 '18 at 21:29
  • Hmmm... I get ERROR 1562 (HY000): Cannot create temporary table with partitions. My table has PARTITION BY RANGE(YEAR(dt)); how is yours partitioned? – Rick James Jun 30 '18 at 2:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.