Not the answer you need?
Register and ask your own question!

pt-online-schema-change - alter to

syg11syg11 EntrantInactive User Role Beginner
I Tested the pt-online-schema-change on a sample database employees
with this schema :
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` enum('M','F') NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`)
i want to change the table to a partitioned table based on hire_date and for that i need to change the pk , otherwise i'm getting an error
Error Code: 1503. A PRIMARY KEY must include all columns in the table's partitioning function

pt-online-schema-change --alter "PARTITION BY RANGE (to_days(hire_date)) (PARTITION p0 VALUES LESS THAN (599616000),PARTITION p1 VALUES LESS THAN (662688000),PARTITION p2 VALUES LESS THAN (725846400),PARTITION p3 VALUES LESS THAN (MAXVALUE) )" D=employees,t=employees --execute

so i need to do a work around before at two stages ( that on big tables can take along time )

first :drop primary key ,ADD PRIMARY KEY (emp_no ,hire_date)
and then do the partition change

my question is there a way in using one of the options , or using plugins
to create the temp table copy with the new PK , or avoid this error in some other way



  • carlos.salguerocarlos.salguero Percona Toolkit Developer Percona Staff Role

    At this moment there is no way to do it.
    In my TODO list I have plans to test the feasibility of creating a new parameter like --use-template-table so you can specify the new CREATE TABLE statement, including partitions.

Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.