Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6, 10.11, 11.4, 11.8, 12.0(EOL)
Description
Secondary node executes duplicate transactions when an event is triggered on a MyISAM or ARIA table with multiple triggers on primary node
Following test case inserts 3 records instead of 2 records in the secondary node
Test case
--source include/galera_cluster.inc
|
--source include/have_aria.inc
|
|
|
--connection node_1
|
|
|
SET GLOBAL wsrep_mode=REPLICATE_ARIA; |
|
|
CREATE TABLE t1 (a INT) ENGINE=ARIA ; |
CREATE TABLE t2 (a INT) ENGINE=ARIA ; |
|
|
CREATE TRIGGER t1_insert1 BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (100); |
CREATE TRIGGER t1_insert2 BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (100); |
|
INSERT INTO t1 VALUES (1); |
SELECT * FROM t2; |
|
|
--connection node_2
|
|
|
SELECT * FROM t2; |
Result log
SELECT * FROM t2; |
a
|
100
|
100
|
connection node_2; |
SELECT * FROM t2; |
a
|
100
|
100
|
100
|
Expected log
SELECT * FROM t2; |
a
|
100
|
100
|
connection node_2; |
SELECT * FROM t2; |
a
|
100
|
100
|
Error log from node2
2025-04-16 13:33:53 6 [Note] WSREP: Wsrep_high_priority_service::apply_toi: 4
|
2025-04-16 13:33:53 6 [Note] WSREP: assigned new next query and trx id: 11
|
2025-04-16 13:33:53 6 [Note] WSREP: Query_log_event thread=6 for query=CREATE TABLE t1 (a INT) ENGINE=ARIA
|
2025-04-16 13:33:53 6 [Note] WSREP: Apply TO END: 4: CREATE TABLE t1 (a INT) ENGINE=ARIA
|
2025-04-16 13:33:53 6 [Note] WSREP: Set WSREPXid for InnoDB: 40e5979c-1aae-11f0-9f8a-77ed6624b2b2:4 0-101-2
|
2025-04-16 13:33:53 2 [Note] WSREP: Wsrep_high_priority_service::apply_toi: 5
|
2025-04-16 13:33:53 2 [Note] WSREP: assigned new next query and trx id: 12
|
2025-04-16 13:33:53 2 [Note] WSREP: Query_log_event thread=2 for query=CREATE TABLE t2 (a INT) ENGINE=ARIA
|
2025-04-16 13:33:53 2 [Note] WSREP: Apply TO END: 5: CREATE TABLE t2 (a INT) ENGINE=ARIA
|
2025-04-16 13:33:53 2 [Note] WSREP: Set WSREPXid for InnoDB: 40e5979c-1aae-11f0-9f8a-77ed6624b2b2:5 0-101-3
|
2025-04-16 13:33:53 6 [Note] WSREP: Wsrep_high_priority_service::apply_toi: 6
|
2025-04-16 13:33:53 6 [Note] WSREP: assigned new next query and trx id: 13
|
2025-04-16 13:33:53 6 [Note] WSREP: Query_log_event thread=6 for query=CREATE DEFINER=`root`@`localhost` TRIGGER t1_insert1 BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (100)
|
2025-04-16 13:33:53 6 [Note] WSREP: Apply TOI Begin: CREATE DEFINER=`root`@`localhost` TRIGGER t1_insert1 BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (100)
|
2025-04-16 13:33:53 6 [Note] WSREP: Apply TO END: 6: CREATE DEFINER=`root`@`localhost` TRIGGER t1_insert1 BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (100)
|
2025-04-16 13:33:53 6 [Note] WSREP: Set WSREPXid for InnoDB: 40e5979c-1aae-11f0-9f8a-77ed6624b2b2:6 0-101-4
|
2025-04-16 13:33:53 2 [Note] WSREP: Wsrep_high_priority_service::apply_toi: 7
|
2025-04-16 13:33:53 2 [Note] WSREP: assigned new next query and trx id: 14
|
2025-04-16 13:33:53 2 [Note] WSREP: Query_log_event thread=2 for query=CREATE DEFINER=`root`@`localhost` TRIGGER t1_insert2 BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (100)
|
2025-04-16 13:33:53 2 [Note] WSREP: Apply TOI Begin: CREATE DEFINER=`root`@`localhost` TRIGGER t1_insert2 BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (100)
|
2025-04-16 13:33:53 2 [Note] WSREP: Apply TO END: 7: CREATE DEFINER=`root`@`localhost` TRIGGER t1_insert2 BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (100)
|
2025-04-16 13:33:53 2 [Note] WSREP: Set WSREPXid for InnoDB: 40e5979c-1aae-11f0-9f8a-77ed6624b2b2:7 0-101-5
|
2025-04-16 13:33:53 6 [Note] WSREP: Wsrep_high_priority_service::apply_toi: 8
|
2025-04-16 13:33:53 6 [Note] WSREP: assigned new next query and trx id: 15
|
2025-04-16 13:33:53 6 [Note] WSREP: Query_log_event thread=6 for query=INSERT INTO t1 VALUES (1)
|
2025-04-16 13:33:53 6 [Note] WSREP: assigned new next trx id: 16
|
2025-04-16 13:33:53 6 [Note] WSREP: Apply TO END: 8: INSERT INTO t2 VALUES (100)
|
2025-04-16 13:33:53 6 [Note] WSREP: assigned new next trx id: 15
|
2025-04-16 13:33:53 6 [Note] WSREP: assigned new next trx id: 17
|
2025-04-16 13:33:53 6 [Note] WSREP: Apply TO END: 8: INSERT INTO t2 VALUES (100)
|
2025-04-16 13:33:53 6 [Note] WSREP: assigned new next trx id: 15
|
2025-04-16 13:33:53 6 [Note] WSREP: Apply TO END: 8: INSERT INTO t1 VALUES (1)
|
2025-04-16 13:33:53 6 [Note] WSREP: Set WSREPXid for InnoDB: 40e5979c-1aae-11f0-9f8a-77ed6624b2b2:8 0-101-6
|
2025-04-16 13:33:53 2 [Note] WSREP: Wsrep_high_priority_service::apply_toi: 9
|
2025-04-16 13:33:53 2 [Note] WSREP: assigned new next query and trx id: 18
|
2025-04-16 13:33:53 2 [Note] WSREP: Query_log_event thread=2 for query=INSERT INTO t2 VALUES (100)
|
2025-04-16 13:33:53 2 [Note] WSREP: Apply TO END: 9: INSERT INTO t2 VALUES (100)
|
2025-04-16 13:33:53 2 [Note] WSREP: Set WSREPXid for InnoDB: 40e5979c-1aae-11f0-9f8a-77ed6624b2b2:9 0-101-7
|
Also when using the NEW clause in a trigger, the second trigger execution prints an unknown column error Slave SQL: Error 'Unknown column 'NEW.a' in 'VALUES'' on query in the secondary node error log.
Test case
--source include/galera_cluster.inc
|
--source include/have_aria.inc
|
|
|
--connection node_1
|
|
|
SET GLOBAL wsrep_mode=REPLICATE_ARIA; |
|
|
CREATE TABLE t1 (a INT) ENGINE=ARIA ; |
CREATE TABLE t2 (a INT) ENGINE=ARIA ; |
|
|
CREATE TRIGGER t1_insert1 BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (NEW.a); |
CREATE TRIGGER t1_insert2 BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (NEW.a); |
|
INSERT INTO t1 VALUES (1); |
SELECT * FROM t2; |
|
|
Result set from node1 |
---------------------
|
node1:root@localhost> SELECT * FROM t2; |
+------+ |
| a |
|
+------+ |
| 1 |
|
| 1 |
|
+------+ |
2 rows in set (0.001 sec) |
|
|
node1:root@localhost>
|
|
|
Result set from node2 |
----------------------
|
node2:root@localhost> SELECT * FROM t2; |
+------+ |
| a |
|
+------+ |
| 1 |
|
| 1 |
|
+------+ |
2 rows in set (0.001 sec) |
|
|
node2:root@localhost>
|
Error info
Error log info
|
2025-04-16 12:41:23 2 [Note] WSREP: Wsrep_high_priority_service::apply_toi: 11
|
2025-04-16 12:41:23 2 [Note] WSREP: assigned new next query and trx id: 20
|
2025-04-16 12:41:23 2 [Note] WSREP: Query_log_event thread=2 for query=CREATE TABLE t1 (a INT) ENGINE=ARIA
|
2025-04-16 12:41:23 2 [Note] WSREP: Apply TO END: 11: CREATE TABLE t1 (a INT) ENGINE=ARIA
|
2025-04-16 12:41:23 2 [Note] WSREP: Set WSREPXid for InnoDB: c9c4c94e-1aa6-11f0-8f00-2e468a8d478f:11 0-101-9
|
2025-04-16 12:41:23 6 [Note] WSREP: Wsrep_high_priority_service::apply_toi: 12
|
2025-04-16 12:41:23 6 [Note] WSREP: assigned new next query and trx id: 21
|
2025-04-16 12:41:23 6 [Note] WSREP: Query_log_event thread=6 for query=CREATE TABLE t2 (a INT) ENGINE=ARIA
|
2025-04-16 12:41:23 6 [Note] WSREP: Apply TO END: 12: CREATE TABLE t2 (a INT) ENGINE=ARIA
|
2025-04-16 12:41:23 6 [Note] WSREP: Set WSREPXid for InnoDB: c9c4c94e-1aa6-11f0-8f00-2e468a8d478f:12 0-101-10
|
2025-04-16 12:41:23 2 [Note] WSREP: Wsrep_high_priority_service::apply_toi: 13
|
2025-04-16 12:41:23 2 [Note] WSREP: assigned new next query and trx id: 22
|
2025-04-16 12:41:23 2 [Note] WSREP: Query_log_event thread=2 for query=CREATE DEFINER=`root`@`localhost` TRIGGER t1_insert1 BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (NEW.a)
|
2025-04-16 12:41:23 2 [Note] WSREP: Apply TOI Begin: CREATE DEFINER=`root`@`localhost` TRIGGER t1_insert1 BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (NEW.a)
|
2025-04-16 12:41:23 2 [Note] WSREP: Apply TO END: 13: CREATE DEFINER=`root`@`localhost` TRIGGER t1_insert1 BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (NEW.a)
|
2025-04-16 12:41:23 2 [Note] WSREP: Set WSREPXid for InnoDB: c9c4c94e-1aa6-11f0-8f00-2e468a8d478f:13 0-101-11
|
2025-04-16 12:41:23 6 [Note] WSREP: Wsrep_high_priority_service::apply_toi: 14
|
2025-04-16 12:41:23 6 [Note] WSREP: assigned new next query and trx id: 23
|
2025-04-16 12:41:23 6 [Note] WSREP: Query_log_event thread=6 for query=CREATE DEFINER=`root`@`localhost` TRIGGER t1_insert2 BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (NEW.a)
|
2025-04-16 12:41:23 6 [Note] WSREP: Apply TOI Begin: CREATE DEFINER=`root`@`localhost` TRIGGER t1_insert2 BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (NEW.a)
|
2025-04-16 12:41:23 6 [Note] WSREP: Apply TO END: 14: CREATE DEFINER=`root`@`localhost` TRIGGER t1_insert2 BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (NEW.a)
|
2025-04-16 12:41:23 6 [Note] WSREP: Set WSREPXid for InnoDB: c9c4c94e-1aa6-11f0-8f00-2e468a8d478f:14 0-101-12
|
2025-04-16 12:41:23 2 [Note] WSREP: Wsrep_high_priority_service::apply_toi: 15
|
2025-04-16 12:41:23 2 [Note] WSREP: assigned new next query and trx id: 24
|
2025-04-16 12:41:23 2 [Note] WSREP: Query_log_event thread=2 for query=INSERT INTO t1 VALUES (1)
|
2025-04-16 12:41:23 2 [Note] WSREP: assigned new next trx id: 25
|
2025-04-16 12:41:23 2 [Note] WSREP: Apply TO END: 15: INSERT INTO t2 VALUES (NEW.a)
|
2025-04-16 12:41:23 2 [Note] WSREP: assigned new next trx id: 24
|
2025-04-16 12:41:23 2 [Note] WSREP: assigned new next trx id: 26
|
2025-04-16 12:41:23 2 [Note] WSREP: Apply TO END: 15: INSERT INTO t2 VALUES (NEW.a)
|
2025-04-16 12:41:23 2 [Note] WSREP: assigned new next trx id: 24
|
2025-04-16 12:41:23 2 [Note] WSREP: Apply TO END: 15: INSERT INTO t1 VALUES (1)
|
2025-04-16 12:41:23 2 [Note] WSREP: Set WSREPXid for InnoDB: c9c4c94e-1aa6-11f0-8f00-2e468a8d478f:15 0-101-13
|
2025-04-16 12:41:23 6 [Note] WSREP: Wsrep_high_priority_service::apply_toi: 16
|
2025-04-16 12:41:23 6 [Note] WSREP: assigned new next query and trx id: 27
|
2025-04-16 12:41:23 6 [Note] WSREP: Query_log_event thread=6 for query=INSERT INTO t2 VALUES (NEW.a)
|
2025-04-16 12:41:23 6 [Note] WSREP: ha_rollback_trans(6, FALSE) rolled back: msg Unknown column 'NEW.a' in 'VALUES' is_real 1 wsrep_err success
|
2025-04-16 12:41:23 6 [Note] WSREP: Apply TO END: 16: INSERT INTO t2 VALUES (NEW.a)
|
2025-04-16 12:41:23 6 [ERROR] Slave SQL: Error 'Unknown column 'NEW.a' in 'VALUES'' on query. Default database: 'test'. Query: 'INSERT INTO t2 VALUES (NEW.a)', Internal MariaDB error code: 1054
|
2025-04-16 12:41:23 6 [Warning] WSREP: Ignoring error 'Unknown column 'NEW.a' in 'VALUES'' on query. Default database: 'test'. Query: 'INSERT INTO t2 VALUES (NEW.a)', Error_code: 1054
|
2025-04-16 12:41:23 6 [Note] WSREP: Set WSREPXid for InnoDB: c9c4c94e-1aa6-11f0-8f00-2e468a8d478f:16 0-101-14
|