Public bug reported:
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`.`mm_mshprestpnt_mgr` (
`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`),
CONSTRAINT `CHK_RESTPNT_RANGE` CHECK (abs(`REST_PNT`) < 1000000000)
) 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.
** Affects: percona-toolkit (Ubuntu)
Importance: Undecided
Status: New
--
You received this bug notification because you are a member of Ubuntu
Bugs, which is subscribed to Ubuntu.
https://bugs.launchpad.net/bugs/2069980
Title:
The insert trigger does not work while performing pt-osc.
To manage notifications about this bug go to:
https://bugs.launchpad.net/ubuntu/+source/percona-toolkit/+bug/2069980/+subscriptions
--
ubuntu-bugs mailing list
ubuntu-bugs@lists.ubuntu.com
https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
