MySQL Bugs: #68597: Trigger on slave is not working with master tx_isolation as "READ

Bug #68597	Trigger on slave is not working with master tx_isolation as "READ_COMMITTED"
Submitted:	7 Mar 2013 12:27	Modified:	21 Mar 2013 19:02
Reporter:	kuntal basu	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	5.5.29	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	READ-COMMITTED, triggers

Description:
Trigger on slave is not working with master tx_isolation as "READ_COMMITTED"
Once we change the tx_isolation to "repeatable_read" the triggers are working

How to repeat:
1 .in master : create a table 
mysql> show create table test_trig_orig\G
*************************** 1. row ***************************
       Table: test_trig_orig
Create Table: CREATE TABLE `test_trig_orig` (
  `test_trig_orig` varchar(29) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

2. In SLAVE the same will get replicated.
 3. Apart from that crea the below tables and triggers,
*************************** 1. row ***************************
       Table: test_asu_repl
Create Table: CREATE TABLE `test_asu_repl` (
  `id` smallint(6) DEFAULT NULL,
  `text_txt` varchar(29) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
*****************************************************************

show triggers \G
*************************** 1. row ***************************
             Trigger: test_trig_orig_insert
               Event: INSERT
               Table: test_trig_orig
           Statement: begin
declare t_rpts_cr_id smallint unsigned;

declare t_txn_id varchar(75);

set t_txn_id := new.test_trig_orig;
INSERT INTO test.`test_asu_repl`(
`id`,
`text_txt`)
VALUES
(1,t_txn_id);

end
              Timing: BEFORE
             Created: NULL
            sql_mode: REAL_AS_FLOAT,IGNORE_SPACE,NO_UNSIGNED_SUBTRACTION,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: admindba@%
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.04 sec)

3. Now insert a row in master in table "test_trig_orig"
4. The same row will get replicated in slave "test_trig_orig" but the trigger will not work.
5. You change the transaction isolatuion in master the ssame process will work.

Suggested fix:
We need to have the trigger working even for READ_COMMITTED isolation mode as this very critical forour solution.

Please, send the output of:

show variables like 'binlog_f%';

from your master. If it is MIXED, try to set STATEMENT maybe.

I have already tried that also.It is not working..

Here is the output.. The insert itself is not working 

Database changed
mysql> show variables like 'binlog_f%';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.02 sec)

mysql> insert into test_trig_orig values ('debannjan_read_statement');
ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.
mysql>

Need help on a urgent basis.on the same. Any workaround or changes. Please advice.

Please provide an update

What you see is explainable.  When using ROW based replication, which innodb will use when read committed is enabled, triggers aren't fired on slave.

http://dev.mysql.com/doc/refman/5.6/en/replication-features-triggers.html
"With row-based replication, triggers executed on the master do not execute on the slave."

http://dev.mysql.com/doc/refman/5.6/en/binary-log-setting.html
"If you are using InnoDB tables and the transaction isolation level is READ COMMITTED or READ UNCOMMITTED, only row-based logging can be used."

Hi Shane Bester,

Yes you are correct, but if you have gone through my description, this trigger is not there in the master at all. I am not trying to run a trigger which is there in master, I know that is a replication feature otherwise there could be a duplicate row scenario hence ""With row-based replication, triggers executed on the master do not
execute on the slave."" does not even comes into picture. 
This trigger is PURELY ON SLAVE and I was expecting that this trigger will act on the slave whenever replication insert/update any table in slave.

Regarding your second point yes, I know that also, but just cross check as that one has came as a suggestion.

Hope this clarifies.

Hi MySQL developers,
Can I have an reply, It is stopping me to implement as architecture, If I am sure that I am doing something wrong or this one is a MYSQL bug I can try some other ways, Kindly update status.

Thank you for the feedback.

This is how row-based replication works: it stores in the binary log only modified rows and not the statement which fired them. This behavior is by design.

To solve your problem you can have events on slave which will read modified rows in the table and do same job as your trigger supposed to do.