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

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

Howdy,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_scoreresults 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,Ronny

2

Hi
The workaround is to set the pxc-strict-mode to “MASTER” https://www.percona.com/doc/percona-xtradb-cluster/LATEST/features/pxc-strict-mode.html#explicit-table-locking
The LOCK TABLES … WRITE is used to create the triggers, and was introduced in version 3 of the toolkit (version 2.2 don’t have it)
Remember to rollback pxc-strict-mode to ENFORCING onces the pt-osc task ends.

3
DGB said: Hi
The workaround is to set the pxc-strict-mode to "MASTER" https://www.percona.com/doc/percona-xtradb-cluster/LATEST/features/pxc-strict-mode.html#explicit-table-locking
The LOCK TABLES ... WRITE is used to create the triggers, and was introduced in version 3 of the toolkit (version 2.2 don't have it)
Remember to rollback pxc-strict-mode to ENFORCING onces the pt-osc task ends.

Is it possible to set pxc mode on the fly without restart? -> Seems so. So maybe, we could use a flag in the script? Or maybe we can check the BEGIN/COMMIT option -> this would not infect any setting and not break Group replication (if enabled) by MySQL