[MDEV-7039] Impact of slave_run_trigger_for_rbr Created: 2014-11-07  Updated: 2015-01-28  Resolved: 2015-01-28

Status: Closed
Project: MariaDB Server
Component/s: Replication
Affects Version/s: 10.1.1
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Daisy Yan Assignee: Oleksandr Byelkin
Resolution: Won't Fix Votes: 0
Labels: None
Environment:

Linux 2.6.32-220.17.1.el6.x86_64 #1 SMP Wed May 16 00:01:37 BST 2012 x86_64 x86_64 x86_64 GNU/Linux


 Description   

When slave_run_trigger_for_rbr is set to logging on the slave, does it mean triggers can be fired only on the slave? In our test case it works when the trigger is on the slave, but we do have users who has triggers on master as well and we hope it can work on both cases, but right now it looks like the change made by triggers on master can not be replicated to the slave, we get an error
Last_SQL_Error: Could not execute Write_rows_v1 event on table tsom.z; Can't update table 'xx' in stored function/trigger because it is already used by statement which invoked this stored function/trigger., Error_code: 1442; handler error HA_ERR_GENERIC; the event's master log mysql-bin.000045, end_log_pos 2776

the master is on MySQL 5.5.39 while the slave is on MariaDB 10.1.1.

mysql 00:08:45 [PA1 DEV 001] [tsom] [3101]> select @@version;
 
+---------------------------------+
 
| @@version                       |
 
+---------------------------------+
 
| 5.5.39-tokudb-7.5.0-MariaDB-log |
 
+---------------------------------+
 
mysql 00:09:02 [PA1 DEV 001] [tsom] [3108]> select @@version;
 
+--------------------------+
 
| @@version                |
 
+--------------------------+
 
| 10.1.1-MariaDB-wsrep-log |
 
+--------------------------+

Is this working as designed?

Thank you.

 Comments   
Comment by Oleksandr Byelkin [ 2014-11-07 ]

Actually, RBR triggers do not run for tables which already have any trigger run on master but it is done silently. Could you provide more detailed test suite for the error then I'll be more certain if it is bug or not.

Comment by Daisy Yan [ 2014-11-07 ]

It's just a regular trigger on the master. What we were trying to test is, since we do have some only-on-slave triggers, we want to turn on slave_run_trigger_for_rbr, but we also want to make sure the current running triggers on master would still be able to replicate their data changes to the slave through rbr.

The test case is, on master a trigger on table z

 CREATE DEFINER=`root`@`localhost` trigger tsom.insertz after insert on tsom.z FOR EACH ROW insert  tsom.xx values(0) 
 
 
mysql 16:43:45 [PA1 DEV 001] [tsom] [3101]> show create table z;
 
+-------+------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                       |
 
+-------+------------------------------------------------------------------------------------+
 
| z     | CREATE TABLE `z` (
 
  `c` int(11) DEFAULT NULL
 
) ENGINE=TokuDB DEFAULT CHARSET=utf8 |
 
+-------+------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
mysql 16:43:51 [PA1 DEV 001] [tsom] [3101]> show create table xx;
 
+-------+-------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                        |
 
+-------+-------------------------------------------------------------------------------------+
 
| xx    | CREATE TABLE `xx` (
 
  `a` int(11) DEFAULT NULL
 
) ENGINE=TokuDB DEFAULT CHARSET=utf8 |
 
+-------+-------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
table z and xx are also on the slave already
 
 
 
insert into z values(1);

on the slave, we got

               Last_SQL_Error: Could not execute Write_rows_v1 event on table tsom.z; Can't update table 'xx' in stored function/trigger because it is already used by statement which invoked this stored function/trigger., Error_code: 1442; handler error HA_ERR_GENERIC; the event's master log mysql-bin.000045, end_log_pos 2776

On slave we got no insert on either z or xx

Comment by Daisy Yan [ 2014-11-07 ]

The insert into z values(1) was executed on the master

Comment by Elena Stepanova [ 2014-11-08 ]

sanja, please note that the master is 5.5.39, so if the decision whether to run RBR triggers or not depends on some new flag the master is supposed to write in the binlog, this master is probably not capable of it.

Comment by Daisy Yan [ 2014-11-10 ]

I've changed the master to 10.0.13, I'm still getting the same error. Does this mean we have to upgrade master to 10.1.1 as well? We were hoping to only have this version run on slave first.

Comment by Daisy Yan [ 2014-11-18 ]

Is there any update on this? Thanks.

Comment by Oleksandr Byelkin [ 2015-01-28 ]

RBR triggers was implemented in 10.1.1 so versions before it can't somehow inform slave that they had triggers run. So I doubts that the problem can be fixed.

Generated at Thu Feb 08 07:16:30 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.