Triggers from Innodb to MyISAM Table will not correctly apply on slave nodes

[Jay Janssen]

Here's a test case on a two node cluster: PXC 5.5.29, wsrep_provider_version - 2.3(r143)

create database if not exists bugs;

use bugs;

drop table if exists parent;
drop table if exists child_innodb;
drop table if exists child_myisam;

CREATE TABLE parent (id INT PRIMARY KEY, data VARCHAR(100) ) ENGINE=Innodb;
CREATE TABLE child_innodb (id INT PRIMARY KEY, data VARCHAR(100) ) ENGINE=Innodb;
CREATE TABLE child_myisam (id INT PRIMARY KEY, data VARCHAR(100) ) ENGINE=MyISAM;

# Innodb trigger

DROP TRIGGER IF EXISTS `bugs`.`after_insert_trigger`;
DELIMITER &&
CREATE TRIGGER `bugs`.`after_insert_trigger` AFTER INSERT ON `parent` FOR EACH ROW
BEGIN INSERT INTO child_innodb (id, data) VALUES (NEW.id, NEW.data); END&&
DELIMITER ;

insert into parent (id, data) VALUES( 1, 'child_innodb trigger');

# MyISAM trigger

DROP TRIGGER IF EXISTS `bugs`.`after_insert_trigger`;
DELIMITER &&
CREATE TRIGGER `bugs`.`after_insert_trigger` AFTER INSERT ON `parent` FOR EACH ROW
BEGIN INSERT INTO child_myisam (id, data) VALUES (NEW.id, NEW.data); END&&
DELIMITER ;

insert into parent (id, data) VALUES( 2, 'child_myisam trigger');

# Trigger on both (RBR)

DROP TRIGGER IF EXISTS `bugs`.`after_insert_trigger`;
DELIMITER &&
CREATE TRIGGER `bugs`.`after_insert_trigger` AFTER INSERT ON `parent` FOR EACH ROW
BEGIN
    INSERT INTO child_myisam (id, data) VALUES (NEW.id, NEW.data);
    INSERT INTO child_innodb (id, data) VALUES (NEW.id, NEW.data);
END&&
DELIMITER ;

insert into parent (id, data) VALUES( 3, 'trigger into both tables');

# Same, but with SBR

set binlog_format='STATEMENT';
insert into parent (id, data) VALUES( 4, 'both tables -- SBR');

# on the master node

node1 mysql> select * from parent;
+----+--------------------------+
| id | data |
+----+--------------------------+
| 1 | child_innodb trigger |
| 2 | child_myisam trigger |
| 3 | trigger into both tables |
| 4 | both tables -- SBR |
+----+--------------------------+
4 rows in set (0.00 sec)

node1 mysql> select * from child_myisam;
+----+--------------------------+
| id | data |
+----+--------------------------+
| 2 | child_myisam trigger |
| 3 | trigger into both tables |
| 4 | both tables -- SBR |
+----+--------------------------+
3 rows in set (0.00 sec)

node1 mysql> select * from child_innodb;
+----+--------------------------+
| id | data |
+----+--------------------------+
| 1 | child_innodb trigger |
| 3 | trigger into both tables |
| 4 | both tables -- SBR |
+----+--------------------------+
3 rows in set (0.00 sec)

# On other node in the cluster:
node3 mysql> select * from parent;
+----+--------------------------+
| id | data |
+----+--------------------------+
| 1 | child_innodb trigger |
| 2 | child_myisam trigger |
| 3 | trigger into both tables |
| 4 | both tables -- SBR |
+----+--------------------------+
4 rows in set (0.00 sec)

node3 mysql> select * from child_myisam;
+----+--------------------+
| id | data |
+----+--------------------+
| 4 | both tables -- SBR |
+----+--------------------+
1 row in set (0.00 sec)

node3 mysql> select * from child_innodb;
+----+--------------------------+
| id | data |
+----+--------------------------+
| 1 | child_innodb trigger |
| 3 | trigger into both tables |
| 4 | both tables -- SBR |
+----+--------------------------+
3 rows in set (0.00 sec)

So, the bug is that Innodb tables with triggers will not replicate DML into non-Innodb tables (i.e., TOI tables), unless I switch the entire transaction STATEMENT replication (which isn't safe for Innodb)

[Jay Janssen (jay-janssen)]

binary log on each node (with log-bin and log-slave-updates running) for test #2 captured with 'mysqlbinlog -vvv'.

On the master node:

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#130205 13:13:52 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.29-log created 130205 13:13:52
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
4EsRUQ8BAAAAZwAAAGsAAAABAAQANS41LjI5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#130205 13:14:01 server id 1 end_log_pos 180 Query thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1360088041/*!*/;
SET @@session.pseudo_thread_id=9/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=2, @@session.auto_increment_offset=2/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 180
# at 234
#130205 13:14:01 server id 1 end_log_pos 234 Table_map: `bugs`.`child_myisam` mapped to number 42
#130205 13:14:01 server id 1 end_log_pos 289 Write_rows: table id 42 flags: STMT_END_F

BINLOG '
6UsRURMBAAAANgAAAOoAAAAAACoAAAAAAAEABGJ1Z3MADGNoaWxkX215aXNhbQACAw8CZAAC
6UsRURcBAAAANwAAACEBAAAAACoAAAAAAAEAAv/8AgAAABRjaGlsZF9teWlzYW0gdHJpZ2dlcg==
'/*!*/;
### INSERT INTO bugs.child_myisam
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='child_myisam trigger' /* VARSTRING(100) meta=100 nullable=1 is_null=0 */
# at 289
#130205 13:14:01 server id 1 end_log_pos 363 Query thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1360088041/*!*/;
COMMIT
/*!*/;
# at 363
#130205 13:14:01 server id 1 end_log_pos 436 Query thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1360088041/*!*/;
BEGIN
/*!*/;
# at 436
# at 484
#130205 13:14:01 server id 1 end_log_pos 484 Table_map: `bugs`.`parent` mapped to number 41
#130205 13:14:01 server id 1 end_log_pos 539 Write_rows: table id 41 flags: STMT_END_F

BINLOG '
6UsRURMBAAAAMAAAAOQBAAAAACkAAAAAAAEABGJ1Z3MABnBhcmVudAACAw8CZAAC
6UsRURcBAAAANwAAABsCAAAAACkAAAAAAAEAAv/8AgAAABRjaGlsZF9teWlzYW0gdHJpZ2dlcg==
'/*!*/;
### INSERT INTO bugs.parent
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='child_myisam trigger' /* VARSTRING(100) meta=100 nullable=1 is_null=0 */
# at 539
#130205 13:14:01 server id 1 end_log_pos 566 Xid = 455045
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

On the slave node:

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#130205 13:13:52 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.29-log created 130205 13:13:52
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
4EsRUQ8BAAAAZwAAA...