[MDEV-21578] CREATE OR REPLACE TRIGGER in Galera cluster not replicating Created: 2020-01-27  Updated: 2020-10-06  Resolved: 2020-08-28

Status: Closed
Project: MariaDB Server
Component/s: Galera, Triggers
Affects Version/s: 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.2.35, 10.3.26, 10.4.16, 10.5.7

Type: Bug Priority: Major
Reporter: Immanuel Assignee: Jan Lindström (Inactive)
Resolution: Fixed Votes: 2
Labels: create, galera, replace, replication, triggers
Environment:

Ubuntu, Galera cluster with 3 nodes, MariaDB version 10.4.1, wsrep_patch_version: wsrep_622


Issue Links:
Relates
relates to MDEV-23638 DROP TRIGGER in Galera Cluster not re... Closed

 Description   

When I execute this Statements:
CREATE DATABASE IF NOT EXISTS ReplicationBug;
CREATE TABLE IF NOT EXISTS ReplicationBug.Test (id int(10));
CREATE OR REPLACE TRIGGER ReplicationBug.NotReplicatingTrigger
BEFORE INSERT
ON ReplicationBug.Test
FOR EACH ROW
BEGIN
SET NEW.id = 100;
END;

And than (I change the Value which i assign to the NEW.id to 200):
CREATE OR REPLACE TRIGGER ReplicationBug.NotReplicatingTrigger
BEFORE INSERT
ON ReplicationBug.Test
FOR EACH ROW
BEGIN
SET NEW.id = 200;
END;

The change to the trigger is only on one node of the galera cluster. The other nodes still have the old trigger definition, which assignes NEW.id the value of 100.

Looking in the mysqld.log of one of the nodes which did not replicate, I get the following error message:
2020-01-27 14:43:54 13 [ERROR] Slave SQL: Error 'Trigger 'ReplicationBug.NotReplicatingTrigger' already exists' on query.
It seems that "CREATE OR REPLACE TRIGGER" is transformed to "CREATE TRIGGER" on the other nodes.

TL/TD:
The replication of "CREATE OR REPLACE TRIGGER" statements is not working on our galera cluster.



 Comments   
Comment by Fabrykowski [ 2020-08-28 ]

Any news on this bug?
Even though it has a "Fix Version" of 10.4, it is still present in 10.5.5 and severely impacting a DB-cluster deployment I am working on.

The same phenomenon can be observed, when trying to drop a trigger. I have set up an MWE demonstrating it: fabrykowski/galera_drop_trigger_mwe

Generated at Thu Feb 08 09:08:12 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.