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

pt-ocs LOCK TABLE WRITE on --preserve-triggers when db pxc_strict_mode = ENFORCING

joepmeloen12joepmeloen12 ContributorInactive User Role Novice
Using --preserve-triggers on pt-online-schema-change results in this error message:

Percona-XtraDB-Cluster prohibits use of LOCK TABLE/FLUSH TABLE <table> WITH READ LOCK with pxc_strict_mode = ENFORCING

When looking at the script, this seems to be hardcoded:

/usr/bin/pt-online-schema-change:11074: push @$sqls, "LOCK TABLES `$args{db}`.`$args{new_tbl}` WRITE, `$args{db}`. `$args{orig_tbl}` WRITE;";

Commenting out this line seems to work fine. Before we had to drop / create the triggers in our own management scripts and we didn't lock there either. Anyway, two questions:

1) is it safe to use this feature without table write locking?
2) are there plans to fix the pt-osc script? I don't like using hacked scripts; they'll break on the next update

Kind regards,

Ps. 3rd question: is it me, or is it true this forum doesn't have a search option?


  • carlos.salguerocarlos.salguero Percona Toolkit Developer Percona Staff Role

    If you disable locks, there might be writes to the table that won't be catched by the triggers, so there is a chance of data loss.
    Since pxc_strict_mode is global, there is no way to disable it just for pt-online-schema-change.

    Regarding 3rd question, you have a search box in the right top corner. Which browser are you using? Maybe it is not being rendered.

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.