The insert trigger does not work while performing pt-osc.

[jihyunbahn]

0. mariadb version (aws rds) :

mysql> select @@version;
+---------------------+
| @@version |
+---------------------+
| 10.11.7-MariaDB-log |
+---------------------+
1 row in set (0.00 sec)

pt-osc version
[ec2-user@ip-10-0-0-32 ~]$ pt-online-schema-change --version
pt-online-schema-change 3.6.0

1. Table DDL Script
CREATE TABLE `bjh`.`test` (
  `MSHP_ID` varchar(16) NOT NULL ,
  `STR_CD` varchar(7) NOT NULL ,
  `SAVE_PNT` decimal(15,3) DEFAULT NULL ,
  `USE_PNT` decimal(15,3) DEFAULT NULL ,
  `REST_PNT` decimal(15,3) DEFAULT NULL ,
  `BAL_PNT` decimal(15,3) DEFAULT NULL ,
  `TRF_PNT` decimal(15,3) DEFAULT 0.000 ,
  `LAST_TRAN_TP` varchar(1) DEFAULT NULL ,
  `LAST_TRAN_DT` varchar(8) DEFAULT NULL ,
  `LAST_CBIL_PBC_DT` varchar(8) DEFAULT NULL ,
  `LAST_CREDIT_TRAN_DT` varchar(8) DEFAULT NULL ,
  `JOIN_PNT_SUPY_YN` varchar(1) DEFAULT '0' ,
  `APP_JOIN_PNT_SUPY_YN` varchar(1) DEFAULT '0',
  `ADD_JOIN_PNT_SUPY_YN` varchar(1) DEFAULT '0' ,
  `REMK` varchar(400) DEFAULT NULL ,
  `REG_DTM` varchar(25) DEFAULT NULL ,
  `REG_EMP_ID` varchar(20) DEFAULT NULL ,
  `MOD_DTM` varchar(25) DEFAULT NULL ,
  `MOD_EMP_ID` varchar(20) DEFAULT NULL ,
  `IP` varchar(30) DEFAULT NULL COMMENT ,
  PRIMARY KEY (`MSHP_ID`,`STR_CD`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

2. Procedure Script
DELIMITER $$
CREATE or replace DEFINER=`admin`@`%` PROCEDURE `bjh`.`test`(IN num INT)
BEGIN
 DECLARE i INT DEFAULT 0;
 DECLARE LAST_MSHP_ID INT DEFAULT 0;

 SELECT
  count(1)
 INTO LAST_MSHP_ID
  FROM bjh.test mmm
 WHERE mmm.STR_CD = '012002'
    AND mshp_id LIKE '%test85';

 WHILE i < num
 DO
 INSERT INTO bjh.test
 (
 MSHP_ID,
 STR_CD,
 reg_dtm,
 IP
 )
 values(
 CONCAT(LAST_MSHP_ID, 'test85')
 ,'012002'
 ,now()
 ,i
 );
 SET i = i+1;
 SET LAST_MSHP_ID = LAST_MSHP_ID + 1;
END WHILE;

END$$

DELIMITER ;

3. Full scenario
1) Perform the procedure.
 mariadb> call bjh.test(5000000);

2) Perform pt-osc while the procedure is being performed.
pt-online-schema-change --alter "ADD CONSTRAINT CHK_RESTPNT_RANGE CHECK (ABS(REST_PNT) < 1000000000)" D=bjh,t=test,P=3306 \
--no-drop-old-table \
--host=bjh-test-maria.czeeuhi5hmdb.us-east-1.rds.amazonaws.com \
--user=admin \
--password='qkswlgus' \
--progress=time,30 \
--max-load="Threads_running=200" \
--critical-load="Threads_running=1000" \
--recursion-method=none \
--chunk-index=PRIMARY \
--preserve-triggers \
--execute

3) When pt-osc completes, the procedure aborts.

4) Compares the rows of the test table and test_old table from the point when pt-osc starts copying.
select reg_dtm, count(*) from bjh.test where reg_dtm>='2024-06-21 11:03:57' group by reg_dtm;
+---------------------+----------+
| reg_dtm | count(*) |
+---------------------+----------+
| 2024-06-21 11:03:57 | 2464 |
| 2024-06-21 11:03:58 | 2356 |
| 2024-06-21 11:03:59 | 1897 |
| 2024-06-21 11:04:00 | 1296 |
| 2024-06-21 11:04:01 | 1907 |
| 2024-06-21 11:04:02 | 2333 |
| 2024-06-21 11:04:03 | 1755 |
| 2024-06-21 11:04:04 | 1206 |
| 2024-06-21 11:04:05 | 1714 |
| 2024-06-21 11:04:06 | 2035 |
| 2024-06-21 11:04:07 | 1854 |
| 2024-06-21 11:04:08 | 2365 |
| 2024-06-21 11:04:09 | 2091 |
| 2024-06-21 11:04:10 | 1579 |
| 2024-06-21 11:04:11 | 1740 |
| 2024-06-21 11:04:12 | 2042 |
| 2024-06-21 11:04:13 | 1995 |
| 2024-06-21 11:04:14 | 2139 |
| 2024-06-21 11:04:15 | 1966 |
| 2024-06-21 11:04:16 | 1670 |
| 2024-06-21 11:04:17 | 1891 |
| 2024-06-21 11:04:18 | 1640 |
| 2024-06-21 11:04:19 | 1644 |
| 2024-06-21 11:04:20 | 2653 |
| 2024-06-21 11:04:21 | 3110 |
| 2024-06-21 11:04:22 | 809 |
| 2024-06-21 11:04:23 | 1933 |
| 2024-06-21 11:04:24 | 1959 |
| 2024-06-21 11:04:25 | 1875 |
| 2024-06-21 11:04:26 | 1850 |
| 2024-06-21 11:04:27 | 1797 |
| 2024-06-21 11:04:28 | 1922 |
| 2024-06-21 11:04:29 | 1576 |
| 2024-06-21 11:04:30 | 1695 |
| 2024-06-21 11:04:31 | 2011 |
| 2024-06-21 11:04:32 | 2074 |
| 2024-06-21 11:04:33 | 1756 |
| 2024-06-21 11:04:34 | 1638 |
| 2024-06-21 11:04:35 | 3723 |
| 2024-06-21 11:04:36 | 3298 |
| 2024-06-21 11:04:37 | 3118 |
| 2024-06-21 11:04:38 | 3381 |
| 2024-06-21 11:04:39 | 2408 |
| 2024-06-21 11:04:40 | 1575 |
| 2024-06-21 11:04:41 | 1510 |
| 2024-06-21 11:04:42 | 893 |
| 2024-06-21 11:04:43 | 757 |
| 2024-06-21 11:04:44 | 665 |
| 2024-06-21 11:04:45 | 707 |
| 2024-06-21 11:04:46 | 703 |
| 2024-06-21 11:04:47 | 656 |
| 2024-06-21 11:04:48 | 670 |
| 2024-06-21 11:04:49 | 1827 |
| 2024-06-21 11:04:50 | 1405 |
| 2024-06-21 11:06:35 | 6292 |
| 2024-06-21 11:06:36 | 18619 |
| 2024-06-21 11:06:37 | 15863 |
| 2024-06-21 11:06:38 | 13624 |
| 2024-06-21 11:06:39 | 14577 |
| 2024-06-21 11:06:40 | 14568 |
| 2024-06-21 11:06:41 | 14594 |
| 2024-06-21 11:06:42 | 14425 |
| 2024-06-21 11:06:43 | 14554 |
| 2024-06-21 11:06:44 | 14886 |
| 2024-06-21 11:06:45 | 14568 |
| 2024-06-21 11:06:46 | 14493 |
| 2024-06-21 11:06:47 | 14444 |
| 2024-06-21 11:06:48 | 15071 |
| 2024-06-21 11:06:49 | 14612 |
| 2024-06-21 11:06:50 | 14602 |
| 2024-06-21 11:06:51 | 14898 |
| 2024-06-21 11:06:52 | 14983 |
| 2024-06-21 11:06:53 | 14769 |
| 2024-06-21 11:06:54 | 14785 |
| 2024-06-21 11:06:55 | 14906 |
| 2024-06-21 11:06:56 | 15011 |
| 2024-06-21 11:06:57 | 14943 |
| 2024-06-21 11:06:58 | 14681 |
| 2024-06-21 11:06:59 | 14593 |
| 2024-06-21 11:07:00 | 12591 |
| 2024-06-21 11:07:01 | 12630 |
| 2024-06-21 11:07:02 | 13402 |
| 2024-06-21 11:07:03 | 13256 |
| 2024-06-21 11:07:04 | 13198 |
| 2024-06-21 11:07:05 | 13152 |
| 2024-06-21 11:07:06 | 13145 |
| 2024-06-21 11:07:07 | 13304 |
| 2024-06-21 11:07:08 | 12943 |
| 2024-06-21 11:07:09 | 8269 |
| 2024-06-21 11:07:10 | 20463 |
| 2024-06-21 11:07:11 | 15996 |
| 2024-06-21 11:07:12 | 17741 |
| 2024-06-21 11:07:13 | 18233 |
| 2024-06-21 11:07:14 | 17019 |
| 2024-06-21 11:07:15 | 14876 |
| 2024-06-21 11:07:16 | 11370 |
| 2024-06-21 11:07:17 | 13720 |
| 2024-06-21 11:07:18 | 13300 |
| 2024-06-21 11:07:19 | 13402 |
| 2024-06-21 11:07:20 | 10228 |
| 2024-06-21 11:07:21 | 13989 |
| 2024-06-21 11:07:22 | 13818 |
| 2024-06-21 11:07:23 | 13148 |
| 2024-06-21 11:07:24 | 6872 |
| 2024-06-21 11:07:25 | 2405 |
| 2024-06-21 11:07:26 | 2311 |
| 2024-06-21 11:07:27 | 2418 |
| 2024-06-21 11:07:28 | 2371 |
| 2024-06-21 11:07:29 | 2305 |
| 2024-06-21 11:07:30 | 2097 |
| 2024-06-21 11:07:31 | 2191 |
| 2024-06-21 11:07:32 | 2165 |
| 2024-06-21 11:07:33 | 2076 |
| 2024-06-21 11:07:34 | 2073 |
| 2024-06-21 11:07:35 | 4279 |
| 2024-06-21 11:07:36 | 3163 |
| 2024-06-21 11:07:37 | 2547 |
| 2024-06-21 11:07:38 | 2383 |
| 2024-06-21 11:07:39 | 2075 |
| 2024-06-21 11:07:40 | 2235 |
| 2024-06-21 11:07:41 | 2248 |
| 2024-06-21 11:07:42 | 2183 |
| 2024-06-21 11:07:43 | 2037 |
| 2024-06-21 11:07:44 | 2300 |
| 2024-06-21 11:07:45 | 2376 |
| 2024-06-21 11:07:46 | 2294 |
| 2024-06-21 11:07:47 | 2232 |
| 2024-06-21 11:07:48 | 2145 |
| 2024-06-21 11:07:49 | 2332 |
| 2024-06-21 11:07:50 | 2339 |
| 2024-06-21 11:07:51 | 1954 |
| 2024-06-21 11:07:52 | 2285 |
| 2024-06-21 11:07:53 | 2092 |
| 2024-06-21 11:07:54 | 2258 |
| 2024-06-21 11:07:55 | 2166 |
| 2024-06-21 11:07:56 | 2426 |
| 2024-06-21 11:07:57 | 2291 |
| 2024-06-21 11:07:58 | 2090 |
| 2024-06-21 11:07:59 | 2176 |
| 2024-06-21 11:08:00 | 2035 |
| 2024-06-21 11:08:01 | 1952 |
+---------------------+----------+

select reg_dtm, count(*) from bjh._test_old where reg_dtm>='2024-06-21 11:03:57' group by reg_dtm;
+---------------------+----------+
| reg_dtm | count(*) |
+---------------------+----------+
| 2024-06-21 11:03:57 | 2464 |
| 2024-06-21 11:03:58 | 2356 |
| 2024-06-21 11:03:59 | 1897 |
| 2024-06-21 11:04:00 | 1296 |
| 2024-06-21 11:04:01 | 1907 |
| 2024-06-21 11:04:02 | 2800 |
| 2024-06-21 11:04:03 | 1755 |
| 2024-06-21 11:04:04 | 1206 |
| 2024-06-21 11:04:05 | 1714 |
| 2024-06-21 11:04:06 | 2035 |
| 2024-06-21 11:04:07 | 1854 |
| 2024-06-21 11:04:08 | 2365 |
| 2024-06-21 11:04:09 | 2091 |
| 2024-06-21 11:04:10 | 1579 |
| 2024-06-21 11:04:11 | 1740 |
| 2024-06-21 11:04:12 | 2042 |
| 2024-06-21 11:04:13 | 1995 |
| 2024-06-21 11:04:14 | 2139 |
| 2024-06-21 11:04:15 | 1966 |
| 2024-06-21 11:04:16 | 1670 |
| 2024-06-21 11:04:17 | 1891 |
| 2024-06-21 11:04:18 | 1640 |
| 2024-06-21 11:04:19 | 1644 |
| 2024-06-21 11:04:20 | 2653 |
| 2024-06-21 11:04:21 | 3110 |
| 2024-06-21 11:04:22 | 809 |
| 2024-06-21 11:04:23 | 1933 |
| 2024-06-21 11:04:24 | 1959 |
| 2024-06-21 11:04:25 | 1875 |
| 2024-06-21 11:04:26 | 1850 |
| 2024-06-21 11:04:27 | 1797 |
| 2024-06-21 11:04:28 | 1922 |
| 2024-06-21 11:04:29 | 1576 |
| 2024-06-21 11:04:30 | 1695 |
| 2024-06-21 11:04:31 | 2011 |
| 2024-06-21 11:04:32 | 2074 |
| 2024-06-21 11:04:33 | 1756 |
| 2024-06-21 11:04:34 | 1638 |
| 2024-06-21 11:04:35 | 3723 |
| 2024-06-21 11:04:36 | 3298 |
| 2024-06-21 11:04:37 | 3118 |
| 2024-06-21 11:04:38 | 3381 |
| 2024-06-21 11:04:39 | 2408 |
| 2024-06-21 11:04:40 | 1575 |
| 2024-06-21 11:04:41 | 1510 |
| 2024-06-21 11:04:42 | 893 |
| 2024-06-21 11:04:43 | 757 |
| 2024-06-21 11:04:44 | 665 |
| 2024-06-21 11:04:45 | 707 |
| 2024-06-21 11:04:46 | 703 |
| 2024-06-21 11:04:47 | 656 |
| 2024-06-21 11:04:48 | 670 |
| 2024-06-21 11:04:49 | 1827 |
| 2024-06-21 11:04:50 | 1462 |
| 2024-06-21 11:04:51 | 2000 |
| 2024-06-21 11:04:52 | 921 |
| 2024-06-21 11:04:53 | 964 |
| 2024-06-21 11:04:54 | 1486 |
| 2024-06-21 11:04:55 | 1628 |
| 2024-06-21 11:04:56 | 993 |
| 2024-06-21 11:04:57 | 1752 |
| 2024-06-21 11:04:58 | 2388 |
| 2024-06-21 11:04:59 | 701 |
| 2024-06-21 11:05:00 | 834 |
| 2024-06-21 11:05:01 | 1452 |
| 2024-06-21 11:05:02 | 1555 |
| 2024-06-21 11:05:03 | 1529 |
| 2024-06-21 11:05:04 | 2653 |
| 2024-06-21 11:05:05 | 717 |
| 2024-06-21 11:05:06 | 793 |
| 2024-06-21 11:05:07 | 1826 |
| 2024-06-21 11:05:08 | 1883 |
| 2024-06-21 11:05:09 | 2005 |
| 2024-06-21 11:05:10 | 1839 |
| 2024-06-21 11:05:11 | 1854 |
| 2024-06-21 11:05:12 | 1403 |
| 2024-06-21 11:05:13 | 2834 |
| 2024-06-21 11:05:14 | 1458 |
| 2024-06-21 11:05:15 | 1866 |
| 2024-06-21 11:05:16 | 1598 |
| 2024-06-21 11:05:17 | 1829 |
| 2024-06-21 11:05:18 | 1887 |
| 2024-06-21 11:05:19 | 1761 |
| 2024-06-21 11:05:20 | 1505 |
| 2024-06-21 11:05:21 | 1508 |
| 2024-06-21 11:05:22 | 1802 |
| 2024-06-21 11:05:23 | 1719 |
| 2024-06-21 11:05:24 | 2049 |
| 2024-06-21 11:05:25 | 1183 |
| 2024-06-21 11:05:26 | 573 |
| 2024-06-21 11:05:27 | 632 |
| 2024-06-21 11:05:28 | 472 |
| 2024-06-21 11:05:29 | 568 |
| 2024-06-21 11:05:30 | 567 |
| 2024-06-21 11:05:31 | 430 |
| 2024-06-21 11:05:32 | 1361 |
| 2024-06-21 11:05:33 | 384 |
| 2024-06-21 11:05:34 | 140 |
| 2024-06-21 11:05:35 | 315 |
| 2024-06-21 11:05:36 | 872 |
| 2024-06-21 11:05:37 | 607 |
| 2024-06-21 11:05:38 | 756 |
| 2024-06-21 11:05:39 | 214 |
| 2024-06-21 11:05:40 | 572 |
| 2024-06-21 11:05:41 | 583 |
| 2024-06-21 11:05:42 | 604 |
| 2024-06-21 11:05:43 | 327 |
| 2024-06-21 11:05:44 | 187 |
| 2024-06-21 11:05:45 | 456 |
| 2024-06-21 11:05:46 | 837 |
| 2024-06-21 11:05:52 | 568 |
| 2024-06-21 11:05:53 | 2062 |
| 2024-06-21 11:05:54 | 1988 |
| 2024-06-21 11:05:55 | 1505 |
| 2024-06-21 11:05:56 | 300 |
| 2024-06-21 11:05:57 | 207 |
| 2024-06-21 11:05:58 | 342 |
| 2024-06-21 11:05:59 | 536 |
| 2024-06-21 11:06:00 | 175 |
| 2024-06-21 11:06:01 | 464 |
| 2024-06-21 11:06:02 | 507 |
| 2024-06-21 11:06:03 | 442 |
| 2024-06-21 11:06:04 | 379 |
| 2024-06-21 11:06:05 | 453 |
| 2024-06-21 11:06:06 | 630 |
| 2024-06-21 11:06:07 | 900 |
| 2024-06-21 11:06:08 | 452 |
| 2024-06-21 11:06:09 | 665 |
| 2024-06-21 11:06:10 | 362 |
| 2024-06-21 11:06:11 | 574 |
| 2024-06-21 11:06:12 | 385 |
| 2024-06-21 11:06:13 | 289 |
| 2024-06-21 11:06:14 | 561 |
| 2024-06-21 11:06:15 | 138 |
| 2024-06-21 11:06:16 | 457 |
| 2024-06-21 11:06:17 | 217 |
| 2024-06-21 11:06:18 | 736 |
| 2024-06-21 11:06:19 | 488 |
| 2024-06-21 11:06:20 | 1001 |
| 2024-06-21 11:06:21 | 605 |
| 2024-06-21 11:06:22 | 460 |
| 2024-06-21 11:06:23 | 378 |
| 2024-06-21 11:06:24 | 611 |
| 2024-06-21 11:06:25 | 373 |
| 2024-06-21 11:06:26 | 2043 |
| 2024-06-21 11:06:27 | 1567 |
| 2024-06-21 11:06:28 | 1692 |
| 2024-06-21 11:06:29 | 1690 |
| 2024-06-21 11:06:30 | 1515 |
| 2024-06-21 11:06:31 | 1657 |
| 2024-06-21 11:06:32 | 1839 |
| 2024-06-21 11:06:33 | 1907 |
| 2024-06-21 11:06:34 | 2015 |
| 2024-06-21 11:06:35 | 1304 |
+---------------------+----------+

5) In the test table, omissions were confirmed from 2024-06-21 11:04:50 to 2024-06-21 11:06:34. Additionally, the count did not match at 2024-06-21 11:04:50.

4. mariadb parameter special features
tx_isolation level : read-uncommitted
innodb_flush_log_at_trx_commit : 2

5. As a result, the insert trigger did not work properly while pt-osc was in progress. I looked at the log file in debug mode, but I didn't see any trigger-related logs.
why! I don't know the cause.

[jihyunbahn (jihyunbahn)]

I modified the above test scenario to make it simpler and modified the DDL, procedure, and pt-online-schema-change script to specifically check whether the trigger really working during the pt-osc process operates.

1-1. Table DDL Script
CREATE TABLE `test1` (
  `seq` bigint unsigned not null AUTO_INCREMENT,
  `MSHP_ID` varchar(16) NOT NULL ,
  `STR_CD` varchar(7) NOT NULL ,
  `REST_PNT` decimal(15,3) DEFAULT NULL,
  `REG_DTM` varchar(25) DEFAULT NULL ,
  `source` varchar(30) DEFAULT NULL ,
  PRIMARY KEY (`seq`,`MSHP_ID`,`STR_CD`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

By adding the 'source' column to the table,
'PROCEDURE' is inserted when a procedure is introduced,
and 'TRIGGER' is inserted when the trigger of pt-osc is activated.

1-2. Procedure DDL Script
DELIMITER $$
CREATE or replace DEFINER=`admin`@`%` PROCEDURE `bjh`.`ptosctest`(IN num INT)
BEGIN
 DECLARE i INT DEFAULT 0;
 DECLARE LAST_MSHP_ID INT DEFAULT 0;

 SELECT
  count(1)
 INTO LAST_MSHP_ID
  FROM bjh.test1 mmm
 WHERE mmm.STR_CD = '012002'
    AND mshp_id LIKE '%test84';

 WHILE i < num
 DO
 INSERT INTO bjh.test1
 (
 MSHP_ID,
 STR_CD,
 reg_dtm,
 source
 )
 values(
 CONCAT(LAST_MSHP_ID, 'test84')
 ,'012002'
 ,now()
 ,'PROCEDURE'
 );
 SET i = i+1;
 SET LAST_MSHP_ID = LAST_MSHP_ID + 1;
END WHILE;

2. Modifying pt-osc script
I modified pt-online-schema-change's script at line number 11860
(as-is)
    my $insert_trigger
       = "CREATE TRIGGER `${prefix}_ins` AFTER INSERT ON $orig_tbl->{name} "
       . "FOR EACH ROW "
       . "BEGIN "
       . "DECLARE CONTINUE HANDLER FOR 1146 begin end; "
       . "REPLACE INTO $new_tbl->{name} ($qcols) VALUES ($new_vals);"
       . "END ";
(modified script)
my $insert_trigger = "CREATE TRIGGER `${prefix}_ins` BEFORE INSERT ON $orig_tbl->{name} "
                   . "FOR EACH ROW "
                   . "BEGIN "
                   . "DECLARE CONTINUE HANDLER FOR 1146 begin end; "
                   . "REPLACE INTO $new_tbl->{name} "
                   . "(`seq`, `mshp_id`, `str_cd`, `rest_pnt`, `reg_dtm`, `source`) "
                   . "VALUES (NEW.`seq`, NEW.`mshp_id`, NEW.`str_cd`, NEW.`rest_pnt`, NEW.`reg_dtm`, 'TRIGGER');"
                   . "END ";

3. Operate Procedure & pt-osc

In Session A :
call bjh. ptosctest(500000);

(5~10 Seconds later ) In Session B :

pt-online-schema-change --alter "ADD CONSTRAINT CHK_RESTPNT_RANGE CHECK (ABS(REST_PNT) < 1000000000)" D=bjh,t=test1,P=3306 \
--no-drop-old-table \
--host=bjh-test-ptosc.czeeuhi5hmdb.us-east-1.rds.amazonaws.com \
--user=admin \
--password='qkswlgus' \
--progress=time,30 \
--max-load="Threads_running=200" \
--critical-load="Threads_running=1000" \
--recursion-method=none \
--chunk-index=PRIMARY \
--preserve-triggers \
--execute

4. Select Result
In mariadb

mysql> select @@version;
+---------------------+
| @@version |
+---------------------+
| 10.11.7-MariaDB-log |
+---------------------+
1 row in set (0.00 sec)

mysql> select count(*) from bjh.test1 where source='TRIGGER';
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (3.28 sec)

In mysql

mysql> select @@version;
+-----------+
| @@version |
+...