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

[7 Mar 2013 12:27] kuntal basu
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.
[7 Mar 2013 12:37] Valeriy Kravchuk
Please, send the output of:

show variables like 'binlog_f%';

from your master. If it is MIXED, try to set STATEMENT maybe.
[7 Mar 2013 12:51] kuntal basu
I have already tried that also.It is not working..
[7 Mar 2013 12:54] kuntal basu
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>
[8 Mar 2013 10:49] kuntal basu
Need help on a urgent basis.on the same. Any workaround or changes. Please advice.
[13 Mar 2013 10:31] kuntal basu
Please provide an update
[16 Mar 2013 17:58] Shane Bester
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."
[16 Mar 2013 21:22] kuntal basu
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.
[16 Mar 2013 21:25] kuntal basu
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.
[21 Mar 2013 19:02] Sveta Smirnova
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.