Buy Percona ServicesBuy Now!

pt-online-schema-change - alter to

  • Filter
  • Time
  • Show
Clear All
new posts

  • pt-online-schema-change - alter to

    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


  • #2

    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.