Not the answer you need?
Register and ask your own question!

pt-online-schema-change: alter not working because of pxc_strict_mode = ENFORCING

Ronny GörnerRonny Görner Current User Role Supporter
first, thanks to all supporting this cool piece of software.
However, we have a small issue (maybe not even a small but a major) on changing tables using Alter commands or scheme changes. This would normally result in locking the cluster, so we deciced to use pt-online-schema-change to avoid those problems.
pt-online-schema-change --execute --statistics --preserve-triggers --alter "ADD COLUMN test varchar(255) DEFAULT 1 AFTER abfragescore_id" D=webhaus_live,t=scoring_bankverb_score
results in
2020-04-07T10:06:18 Creating triggers...
2020-04-07T10:06:18 Created triggers OK.
2020-04-07T10:06:18 Copying approximately 1506132 rows...
2020-04-07T10:06:45 Copied rows OK.
2020-04-07T10:06:45 Adding original triggers to new table.
Exiting due to errors while restoring triggers: DBD::mysql::db do failed: Percona-XtraDB-Cluster prohibits use of LOCK TABLE/FLUSH TABLE <table> WITH READ LOCK/FOR EXPORT with pxc_strict_mode = ENFORCING [for Statement "LOCK TABLES `webhaus_live`.`______scoring_bankverb_score_new` WRITE, `webhaus_live`. `scoring_bankverb_score` WRITE;"] at /usr/bin/pt-online-schema-change line 9929.
2020-04-07T10:06:45 Dropping triggers...
2020-04-07T10:06:45 Dropped triggers OK.
Not dropping the new table `webhaus_live`.`______scoring_bankverb_score_new` because --swap-tables failed.  To drop the new table, execute:
DROP TABLE IF EXISTS `webhaus_live`.`______scoring_bankverb_score_new`;
# Event  Count
# ====== =====
# INSERT    57
`webhaus_live`.`scoring_bankverb_score` was not altered.

For me, it seems, this is not working because of using ENFORCING mode in the cluster.
Is there any workaround on this to let it work?
Best to all and keep healthy,


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.