pt-online-schema-change resulted in missing MySQL triggers ?

Jan 14, 2023

Mins to Read

All

Recently, while doing a test process to examine resource usage and the time required to alter the table using pt-osc, it leads to a loss of triggers even though the –preserve-triggers option is specified. I made the decision to recreate the identical circumstance so that everyone could see it.

Test environment :

OS: Amazon Linux 2

MySQL version: 5.7.40

pt-online-schema-change version: 3.1.0

Before digging deeper, we must first obtain the whole picture. Here is an illustration of the table’s structure.

Table structure:

 
CREATE TABLE `country` (
  `Code` char(3) NOT NULL DEFAULT '',
  `Name` char(52) NOT NULL DEFAULT '',
  `Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
  `Region` char(26) NOT NULL DEFAULT '',
  `SurfaceArea` decimal(10,2) NOT NULL DEFAULT '0.00',
  `IndepYear` smallint(6) DEFAULT NULL,
  `Population` int(11) NOT NULL DEFAULT '0',
  `LifeExpectancy` decimal(3,1) DEFAULT NULL,
  `GNP` decimal(10,2) DEFAULT NULL,
  `GNPOld` decimal(10,2) DEFAULT NULL,
  `LocalName` char(45) NOT NULL DEFAULT '',
  `GovernmentForm` char(45) NOT NULL DEFAULT '',
  `HeadOfState` char(60) DEFAULT NULL,
  `Capital` int(11) DEFAULT NULL,
  `Code2` char(2) NOT NULL DEFAULT '',
  PRIMARY KEY (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Triggers :

 
mysql> show triggers from world \G
*************************** 1. row ***************************
             Trigger: before__country
               Event: INSERT
               Table: country
           Statement: BEGIN  
IF NEW.Population > 0 THEN SET NEW.Population = 0;  
END IF;  
END
              Timing: BEFORE
             Created: 2023-01-08 09:21:45.35
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: utf8mb4_general_ci
1 row in set (0.01 sec)

Using the command listed below, I attempted to add the column by pt-online-schema.

 
pt-online-schema-change D=world,t=country --recursion-method=none --host=localhost --no-check-alter --critical-load Threads_running=1000 --preserve-triggers --alter-foreign-keys-method=drop_swap --chunk-time=2 --no-check-plan --alter "ADD COLUMN new DECIMAL(16,2)" --dry-run

Note: I am using –alter-foreign-keys-method=drop_swap to reproduce the same scenario.

Examining the pt-osc operation used, we found that the options used were logical according to the alter criteria and the table structure. No warnings or errors are shown during the dry run, either. These are the pt-osc options used.

 
[root@mydbopslab]# pt-online-schema-change D=world,t=country --recursion-method=none --host=localhost --no-check-alter --critical-load Threads_
running=1000 --preserve-triggers --alter-foreign-keys-method=drop_swap --chunk-time=2 --no-check-plan --alter "ADD COLUMN new DECIMAL(16,2)" --dry-run
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LC_CTYPE = "UTF-8",
        LANG = "en_US.UTF-8"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
No slaves found.  See --recursion-method if host ip-172-31-46-222.ap-south-1.compute.internal has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
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:
  `world`.`city` (approx. 4046 rows)
  `world`.`countrylanguage` (approx. 984 rows)
Will use the drop_swap method to update foreign keys.
Altering `world`.`country`...
Creating new table...
Created new table world._country_new OK.
Altering new table...
Altered `world`.`_country_new` OK.
2023-01-04T17:14:42 Creating triggers...
2023-01-04T17:14:42 Created triggers OK.
2023-01-04T17:14:42 Copying approximately 239 rows...
2023-01-04T17:14:42 Copied rows OK.
2023-01-04T17:14:42 Skipping triggers creation since --no-swap-tables was specified along with --drop-new-table
2023-01-04T17:14:42 Drop-swapping tables...
2023-01-04T17:14:42 Analyzing new table...
2023-01-04T17:14:42 Dropped and swapped tables OK.
Not dropping old table because --no-drop-old-table was specified.
2023-01-04T17:14:42 Dropping triggers...
2023-01-04T17:14:42 Dropped triggers OK.
Successfully altered `world`.`country`.

I have executed the pt-osc for the particular table, and it got successfully completed.

 
[root@mydbopslab]# pt-online-schema-change D=world,t=country --recursion-method=none --host=localhost --no-check-alter --critical-load Threads_running=1000 --preserve-triggers --alter-foreign-keys-method=drop_swap --chunk-time=2 --no-check-plan --alter "ADD COLUMN new DECIMAL(16,2)" --execute
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
	LANGUAGE = (unset),
	LC_ALL = (unset),
	LC_CTYPE = "UTF-8",
	LANG = "en_US.UTF-8"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
No slaves found.  See --recursion-method if host ip-172-31-46-222.ap-south-1.compute.internal has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
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:
  `world`.`city` (approx. 4046 rows)
  `world`.`countrylanguage` (approx. 984 rows)
Will use the drop_swap method to update foreign keys.
Altering `world`.`country`...
Creating new table...
Created new table world._country_new OK.
Altering new table...
Altered `world`.`_country_new` OK.
2023-01-05T17:02:01 Creating triggers...
2023-01-05T17:02:01 Created triggers OK.
2023-01-05T17:02:01 Copying approximately 239 rows...
2023-01-05T17:02:01 Copied rows OK.
2023-01-05T17:02:01 Skipping triggers creation since --no-swap-tables was specified along with --drop-new-table
2023-01-05T17:02:01 Drop-swapping tables...
2023-01-05T17:02:01 Analyzing new table...
2023-01-05T17:02:01 Dropped and swapped tables OK.
Not dropping old table because --no-drop-old-table was specified.
2023-01-05T17:02:01 Dropping triggers...
2023-01-05T17:02:01 Dropped triggers OK.
Successfully altered `world`.`country`.

Post execution of the pt-osc, as a sanity check, I have validated the table structure and the triggers.

 
mysql> show create table country\G
*************************** 1. row ***************************
       Table: country
Create Table: CREATE TABLE `country` (
  `Code` char(3) NOT NULL DEFAULT '',
  `Name` char(52) NOT NULL DEFAULT '',
  `Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
  `Region` char(26) NOT NULL DEFAULT '',
  `SurfaceArea` decimal(10,2) NOT NULL DEFAULT '0.00',
  `IndepYear` smallint(6) DEFAULT NULL,
  `Population` int(11) NOT NULL DEFAULT '0',
  `LifeExpectancy` decimal(3,1) DEFAULT NULL,
  `GNP` decimal(10,2) DEFAULT NULL,
  `GNPOld` decimal(10,2) DEFAULT NULL,
  `LocalName` char(45) NOT NULL DEFAULT '',
  `GovernmentForm` char(45) NOT NULL DEFAULT '',
  `HeadOfState` char(60) DEFAULT NULL,
  `Capital` int(11) DEFAULT NULL,
  `Code2` char(2) NOT NULL DEFAULT '',
  `new` decimal(16,2) DEFAULT NULL,
  PRIMARY KEY (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> show triggers from world;
Empty set (0.00 sec)

The column was added successfully, but the trigger went missing.

After some troubleshooting, I’ve discovered that there is a bug (PT-19191) in pt-online-schema-change on the 3.1.0 version while performing an alter on the table that has foreign key reference, triggers and foreign_key_method as drop_swap.

The bug has been fixed on version 3.3.1.

This event can rarely occur based on Bayes’ theorem while using Percona toolkit. Though the chances are negligible, it can happen.

Summary:

Foreign key-related issues are prevalent when dealing with DDL changes in MySQL using the Percona toolkit. We need to do pre-checks not only on the DB end before executing the alter, but we also need to make sure the tool that we are using is up-to-date to avoid hitting the bug to cause an issue.

More Reference:

Online Schema Change for Tables with Triggers.

‍

https://docs.percona.com/percona-toolkit/pt-online-schema-change.html

No items found.