Uploaded image for project: 'Percona Toolkit'
  1. Percona Toolkit
  2. PT-1790

pt-online-schema-change is failing with preserve-triggers and with pxc_strict_mode = ENFORCING

Details

    • Bug
    • Status: Open
    • Medium
    • Resolution: Unresolved
    • 3.0.12, 3.1.0
    • None
    • None
    • None
    • Yes

    Description

      Hello Team,

      pt-online schema change is failing while locking and preserving triggers, it looks like a limitation of pxc multi-master-setup. https://www.percona.com/doc/percona-xtradb-cluster/LATEST/limitation.html

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

       

      Steps to reproduce:

      1) Create tables and triggers:

      use test;

      CREATE TABLE buildings (
          building_no INT PRIMARY KEY AUTO_INCREMENT,
          building_name VARCHAR(255) NOT NULL,
          address VARCHAR(255) NOT NULL
      );

      > CREATE TABLE test.log (
       ts TIMESTAMP,
      msg VARCHAR(255)
      );

      > CREATE TRIGGER test.aayushi_test
      AFTER
      UPDATE ON test.buildings
      FOR EACH ROW
      INSERT INTO test.log VALUES (NOW(), CONCAT("a"));

      2) Set pxc_strict_mode to "ENFORCING"

       > show variables like “%pxc_strict%“;
      --------------------------+

      Variable_name   Value   

      --------------------------+

      pxc_strict_mode ENFORCING

      --------------------------+

      3) Execute pt-online-schema-change:

      pt-online-schema-change --execute --preserve-triggers --ask-pass --host=127.0.0.1 --user=msandbox --port=26529 --chunk-time=1 --alter-foreign-keys-method=rebuild_constraints --alter "
      add sys_modified2 varchar(100)" D=test,t=buildings;
      Enter MySQL password:
      No slaves found. See --recursion-method if host bm-support01.bm.int.percona.com has slaves.
      Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
      *******************************************************************
      Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
      is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
      possibly with SSL_ca_file|SSL_ca_path for verification.
      If you really don't want to verify the certificate and keep the
      connection open to Man-In-The-Middle attacks please set
      SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
      *******************************************************************
      at /usr/bin/pt-online-schema-change line 6982.
      Operation, tries, wait:
      analyze_table, 10, 1
      copy_rows, 10, 0.25
      create_triggers, 10, 1
      drop_triggers, 10, 1
      swap_tables, 10, 1
      update_foreign_keys, 10, 1
      Child tables:
      `test`.`rooms` (approx. 1 rows)
      Will use the rebuild_constraints method to update foreign keys.
      Altering `test`.`buildings`...
      Creating new table...
      Created new table test._buildings_new OK.
      Altering new table...
      2019-12-19T05:17:48 Dropping new table...
      2019-12-19T05:17:48 Dropped new table OK.
      `test`.`buildings` was not altered.
      Error altering new table `test`.`_buildings_new`: DBD::mysql::db do failed: Duplicate column name 'sys_modified2' [for Statement "ALTER TABLE `test`.`_buildings_new`
      add sys_modified2 varchar(100)"] at /usr/bin/pt-online-schema-change line 9271.

      [email protected]:~/sandboxes/pxc_msb_pxc5_7_28$ pt-online-schema-change --execute --preserve-triggers --ask-pass --host=127.0.0.1 --user=msandbox --port=26529 --chunk-time=1 --alter-foreign-keys-method=rebuild_constraints --alter "
      add sys_modified3 varchar(100)" D=test,t=buildings;
      Enter MySQL password:
      No slaves found. See --recursion-method if host bm-support01.bm.int.percona.com has slaves.
      Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
      *******************************************************************
      Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
      is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
      possibly with SSL_ca_file|SSL_ca_path for verification.
      If you really don't want to verify the certificate and keep the
      connection open to Man-In-The-Middle attacks please set
      SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
      *******************************************************************
      at /usr/bin/pt-online-schema-change line 6982.
      Operation, tries, wait:
      analyze_table, 10, 1
      copy_rows, 10, 0.25
      create_triggers, 10, 1
      drop_triggers, 10, 1
      swap_tables, 10, 1
      update_foreign_keys, 10, 1
      Child tables:
      `test`.`rooms` (approx. 1 rows)
      Will use the rebuild_constraints method to update foreign keys.
      Altering `test`.`buildings`...
      Creating new table...
      Created new table test._buildings_new OK.
      Altering new table...
      Altered `test`.`_buildings_new` OK.
      2019-12-19T05:18:12 Creating triggers...
      2019-12-19T05:18:12 Created triggers OK.
      2019-12-19T05:18:12 Copying approximately 2 rows...
      2019-12-19T05:18:12 Copied rows OK.
      2019-12-19T05:18:12 Adding original triggers to new table.
      2019-12-19T05:18:12 Dropping triggers...
      2019-12-19T05:18:12 Dropped triggers OK.
      Not dropping the new table `test`.`_buildings_new` because --swap-tables failed. To drop the new table, execute:
      DROP TABLE IF EXISTS `test`.`_buildings_new`;
      `test`.`buildings` was not altered.
      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 `test`.`_buildings_new` WRITE, `test`. `buildings` WRITE;"] at /usr/bin/pt-online-schema-change line 9881.

      Attachments

        Activity

          People

            Unassigned Unassigned
            aayushi.mangal Aayushi Mangal (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

            Dates

              Created:
              Updated:

              Smart Checklist