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

MySQL & MariaDB Percona Server for MySQL 5.7
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

Problem is, triggers exist already on table, and if you use xtradb cluster, Lock fails, of course. I will post a workaround/idea in the xtradb cluster subforum.

3

Hi @“Ronny Görner”
I am just wondering if this is related to this known issue recorded in our JIRA system? https://jira.percona.com/browse/PT-1790? 
If so, then it would be worth adding a watch to that for you so that you can follow progress on a fix. If that looks like the same issue, let me know and I will see if we can find more details about it.

4

@“lorraine.pocklington”
Thanks for your hint, I already raised PT-1827 on this, maybe, you can close PT-1827, and I added my “workaround” in PT-1790. Can you give this “workaround” to one of your engineers to check for a possible workaround, if my idea could fit on this?Thank you very much!

5

Hi @“Ronny Görner” 
Thanks for raising that Jira, it’s very much appreciated. I will definitely talk with the PT team and see if they can comment on your workaround, and check in on if it’s the same underlying issue before they close PT-1827.