[MDEV-34438] The insert trigger does not work while performing pt-osc. - Jira

XML

Word

Printable

Details

Type: Bug
Status: Confirmed (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.5, 10.6, 10.11
Fix Version/s: 10.5, 10.6, 10.11, 11.1, 11.2
Component/s: None
Labels:
None

Description

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,

  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.

If you perform pt-osc first and then perform the procedure, the trigger will operate. However, if you change the order and perform the procedure and then perform pt-osc, the trigger will not operate properly. I want to know the cause.

why! I don't know the cause.

Attachments

Issue Links

relates to

MDEV-5816 MySQL WL#4179 - Stored programs: validation of stored program statements

Closed

Activity

People

Assignee:: Dmitry Shulga

Reporter:: jihyunbahn

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2024-06-21 08:17

Updated:: 2024-06-25 07:23

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.