Hi, I am trying to do the following in one shot using the pt-online-schema-change and it doesn’t seem to work, for some reason, it complains about the syntax error, but there is none as I can split them and run them in two pieces without any changes.
pt-online-schema-change --critical-load Threads_running=100 --execute -F /root/.my.cnf --alter "
DROP PRIMARY KEY, ADD CONSTRAINT pk PRIMARY KEY(ID,WHEN_VIEWED_TLM), MODIFY ID INT(10) NOT NULL AUTO_INCREMENT,
PARTITION BY RANGE(to_days(WHEN_VIEWED_TLM))
(PARTITION p200703 VALUES LESS THAN (to_days(‘2007-03-01’)) ENGINE = InnoDB,
PARTITION p200704 VALUES LESS THAN (to_days(‘2007-04-01’)) ENGINE = InnoDB,
PARTITION p200705 VALUES LESS THAN (to_days(‘2007-05-01’)) ENGINE = InnoDB,
PARTITION p200706 VALUES LESS THAN (to_days(‘2007-06-01’)) ENGINE = InnoDB,
PARTITION pMAX VALUES LESS THAN MAXVALUE ENGINE = InnoDB) " D=test,t=T1
I can make “DROP PRIMARY KEY, ADD CONSTRAINT pk PRIMARY KEY(ID,WHEN_VIEWED_TLM), MODIFY ID INT(10) NOT NULL AUTO_INCREMENT” as one group and the partitioning piece as the second.
Is it possible to make all of these changes in one shot or does these need to be split.
Here is the error I get when I try to make all the changes at once.
Error altering new table test._T1_new: DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘PARTITION BY RANGE(to_days(WHEN_VIEWED_TLM)) (PARTITION p200703 VALUES LESS THAN’ at line 1 at /usr/bin/pt-online-schema-change line 8336.
Thanks in Advance.
Ramki