pt-online-schema-change - alter to

Polyglot Projects Percona Toolkit
1

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

2

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