Search code examples
pythonmysqlinnodb

create table without partitions

I am trying to create a copy of a table through a python script that has all the qualities of the original except for the partitions. I want to do this multiple times in my script (through a for loop) because I want to mysqldump daily files of old data from that table, so I'm trying to use something like:

CREATE TABLE temp_utilization LIKE utilization WITHOUT PARTITIONING;

Here is the original table: 

CREATE TABLE `utilization` (
  `wrep_time` timestamp NULL DEFAULT NULL,
  `end_time` timestamp NULL DEFAULT NULL,
  `location` varchar(64) NOT NULL,
  `sub_location` varchar(64) NOT NULL,
  `model_id` varchar(255) DEFAULT NULL,
  `offline` int(11) DEFAULT NULL,
  `disabled` int(11) NOT NULL DEFAULT '0',
  `total` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`location`,`sub_location`,`wrep_time`),
  KEY `key_location` (`location`),
  KEY `key_sub_location` (`sub_location`),
  KEY `end_time` (`end_time`),
  KEY `wrep_time` (`wrep_time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (UNIX_TIMESTAMP(wrep_time))
(PARTITION p0 VALUES LESS THAN (1391990400) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1392076800) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (1392163200) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (1392249600) ENGINE = InnoDB,
 PARTITION p492 VALUES LESS THAN (1434499200) ENGINE = InnoDB,
 PARTITION p493 VALUES LESS THAN (1434585600) ENGINE = InnoDB,
 PARTITION p494 VALUES LESS THAN (1434672000) ENGINE = InnoDB,
 PARTITION p495 VALUES LESS THAN (1434758400) ENGINE = InnoDB,
 PARTITION p496 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

I would like to create a temp table which contains a create table like this: 

CREATE TABLE `temp_utilization` (
  `wrep_time` timestamp NULL DEFAULT NULL,
  `end_time` timestamp NULL DEFAULT NULL,
  `location` varchar(64) NOT NULL,
  `sub_location` varchar(64) NOT NULL,
  `model_id` varchar(255) DEFAULT NULL,
  `offline` int(11) DEFAULT NULL,
  `disabled` int(11) NOT NULL DEFAULT '0',
  `total` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`location`,`sub_location`,`wrep_time`),
  KEY `key_location` (`location`),
  KEY `key_sub_location` (`sub_location`),
  KEY `end_time` (`end_time`),
  KEY `wrep_time` (`wrep_time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Solution

  • No, it does not appear that you can create a table like another table without partitions, if it is already partitioned, in one command as you suggested above.

    The partition is part of the table definition and is stored in the metadata. You can check that by executing show create table yourtablename;

    If you just want to create the table over and over again in a loop without the partitions and the data I see three (added one b/c of Cez) options.

    1. have the table definitions hard coded in your script

    2. create the table in the DB without the partitions. So you have one temp table already created and use that as your template to loop through.

    3. run two separate command from your script: A create table like and then an alter table to remove the partitions in a loop.

    You can choose which options best suits you for your environment.

    You can reference your options when creating a table at dev.mysql.