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`)
) ENGINE=InnoDB
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

Thanks
Syg

Comments

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

    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.

    Regards
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.