id is primary key in transaction_log table. Is there anyway i can create the partition without modifying the primary key (id , created) ? If not in the same command (mentioned below) i can drop primary key, modify primary key and add partition?
Please advise on this.
pt-online-schema-change D=subscription,t=transaction_log,h=stm-partition-poc.us-east-2.rds.amazonaws.com,u=root --dry-run --alter “PARTITION BY RANGE(YEAR(created))(PARTITION p2014 VALUES LESS THAN (2015) ,PARTITION p2015 VALUES LESS THAN (2016) , PARTITION p2016 VALUES LESS THAN (2017) ,PARTITION p2017 VALUES LESS THAN (2018) ,PARTITION p2018 VALUES LESS THAN (2019) , PARTITION p2019 VALUES LESS THAN (2020) ,PARTITION p2020 VALUES LESS THAN (2021) ,PARTITION p2021 VALUES LESS THAN (2022) ,PARTITION p2022 VALUES LESS THAN (2023) ,PARTITION pMAXVALUE VALUES LESS THAN (MAXVALUE) );” --no-drop-new-table --no-drop-old-table --ask-pass
Enter MySQL password:
A software update is available:
Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Starting a dry run. subscription
.transaction_log
will not be altered. Specify --execute instead of --dry-run to alter the table.
Creating new table…
Created new table subscription._transaction_log_new OK.
Altering new table…
Not dropping the new table subscription
._transaction_log_new
because --no-drop-new-table was specified. To drop the new table, execute:
DROP TABLE IF EXISTS subscription
._transaction_log_new
;
Dry run complete. subscription
.transaction_log
was not altered.
(in cleanup) Error altering new table subscription
._transaction_log_new
: DBD::mysql::db do failed: A PRIMARY KEY must include all columns in the table’s partitioning function [for Statement “ALTER TABLE subscription
._transaction_log_new
PARTITION BY RANGE(YEAR(created))(PARTITION p2014 VALUES LESS THAN (2015) ,PARTITION p2015 VALUES LESS THAN (2016) , PARTITION p2016 VALUES LESS THAN (2017) ,PARTITION p2017 VALUES LESS THAN (2018) ,PARTITION p2018 VALUES LESS THAN (2019) , PARTITION p2019 VALUES LESS THAN (2020) ,PARTITION p2020 VALUES LESS THAN (2021) ,PARTITION p2021 VALUES LESS THAN (2022) ,PARTITION p2022 VALUES LESS THAN (2023) ,PARTITION pMAXVALUE VALUES LESS THAN (MAXVALUE) );”] at /usr/bin/pt-online-schema-change line 9425.
Error altering new table subscription
._transaction_log_new
: DBD::mysql::db do failed: A PRIMARY KEY must include all columns in the table’s partitioning function [for Statement “ALTER TABLE subscription
._transaction_log_new
PARTITION BY RANGE(YEAR(created))(PARTITION p2014 VALUES LESS THAN (2015) ,PARTITION p2015 VALUES LESS THAN (2016) , PARTITION p2016 VALUES LESS THAN (2017) ,PARTITION p2017 VALUES LESS THAN (2018) ,PARTITION p2018 VALUES LESS THAN (2019) , PARTITION p2019 VALUES LESS THAN (2020) ,PARTITION p2020 VALUES LESS THAN (2021) ,PARTITION p2021 VALUES LESS THAN (2022) ,PARTITION p2022 VALUES LESS THAN (2023) ,PARTITION pMAXVALUE VALUES LESS THAN (MAXVALUE) );”] at /usr/bin/pt-online-schema-change line 9425.