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
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_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,
Ronny

Answers

  • Ronny GörnerRonny Görner Current User Role Supporter
    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.
  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    edited April 21
    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.

  • Ronny GörnerRonny Görner Current User Role Supporter
    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!

  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    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. 
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.