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 |
+-----------+
| 8.0.35 |
+-----------+
1 row in set (0.00 sec)
mysql> select count(*) from bjh.test1 where source='TRIGGER';
+----------+
| count(*) |
+----------+
| 457 |
+----------+
1 row in set (0.04 sec)
As a result, clear is that the pt-osc trigger operates normally in
mysql, but the table reflection by the trigger is not properly performed
in mariadb. I need to check if this is an internal logic problem in
mariadb.
--
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
