** Description changed: 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) + 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 + 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; + 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); + 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. - + 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. + 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.
-- 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 [email protected] https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
