Buy Percona ServicesBuy Now!

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

  • Filter
  • Time
  • Show
Clear All
new posts

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

    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?

  • #2

    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.