pxc node acting as async slave fails to replicate when binlog_format=MIXED

[Derek Downey]

Description:
Given a PXC node acting as an async slave to a master, the PXC node fails to replicate STATEMENT DML when both nodes operating in binlog_format=MIXED.

SHOW SLAVE STATUS error message:

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.55.100
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1294
               Relay_Log_File: relay.000007
                Relay_Log_Pos: 606
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1047
                   Last_Error: Node has dropped from cluster
*snip*

Error message in error log:
2017-02-08 08:30:10 13513 [Warning] WSREP: SQL statement was ineffective, THD: 3, buf: 216
schema: (null)
QUERY: (null)
 => Skipping replication
2017-02-08 08:30:10 13513 [Note] WSREP: cluster conflict due to certification failure for threads:
2017-02-08 08:30:10 13513 [Note] WSREP: Victim thread:
   THD: 3, mode: local, state: executing, conflict: cert failure, seqno: -1
   SQL: (null)
2017-02-08 08:30:10 13513 [Warning] Slave SQL: Error in Xid_log_event: Commit could not be completed, 'Deadlock found when trying to get lock; try restarting transaction', Error_code: 1213
2017-02-08 08:30:10 13513 [ERROR] Slave SQL: Node has dropped from cluster, Error_code: 1047

OS: Ubuntu Trusty
PXC Version: PXC 5.6.34-79.1-56-log

Test Case:
- configure a galera node as an async slave to normal MySQL master
- ensure galera node is running as binlog_format=MIXED
- on master, execute:
root@master:~# mysql -e 'create database inplace_upgrade_test; create table inplace_upgrade_test.test (id tinyint unsigned not null primary key) engine=innodb;insert into inplace_upgrade_test.test values(0);'

- on pxc slave node, validate insert replicated: mysql -e 'select * FROM inplace_upgrade_test.test'

Expected results:

 ###DDL and DML replicated fine
 mysql -e 'select * FROM inplace_upgrade_test.test'
 +----+
 | id |
 +----+
 | 0 |
 +----+

Actual results:

 DDL replicates fine using TOI.
 Obscure error message about skipping replication on the INSERT statement.
 slave error 'Slave SQL: Node has dropped from cluster, Error_code: 1047'

I've attached a log file with my.cnf of PXC node and output log.

[Derek Downey (derekj-downey)]

I actually thought the expected result would be an error on startup of not using RBR, but apparently that has changed in some recent release.

I tested on upstream galera, and everything replicates fine in MIXED replication. (attaching log)

[Krunal Bauskar (krunal-bauskar)]

1. PXC/Galera doesn't support STATEMENT/MIXED row format.

https://groups.google.com/forum/#!topic/codership-team/Op7Vv4Xh7ts

https://mariadb.com/kb/en/mariadb/mariadb-galera-cluster-known-limitations/
While binlog_format is checked on startup and can only be ROW (see Binary Log Formats), it can be changed at runtime. Do NOT change binlog_format at runtime, it is likely not only cause replication failure, but make all other nodes crash.

https://www.percona.com/doc/percona-xtradb-cluster/5.6/howtos/ubuntu_howto.html

--------------

2. 5.6 has the check but it not triggered. (This is an upstream bug).

If triggered it would looks something like this.

2017-02-21T09:55:02.059964Z 0 [ERROR] WSREP: Only binlog_format = 'ROW' is currently supported. Configured value: 'MIXED'. Please adjust your configuration.

--------------

3. PXC-5.7 we have simplified this confusion by adding pxc_strict_mode which help in proper checks and timely error.

2017-02-21T09:58:19.180690Z 0 [ERROR] WSREP: Percona-XtraDB-Cluster prohibits setting binlog_format to STATEMENT or MIXED at global level

--------------

4. Why does MIXED mode works in upstream and not in PXC ?

* As quoted above PXC or Galera variant doesn't support MIXED mode. If upstream is supporting it then it is bug or experimental feature that is left out in upstream.

(Ideally it wouldn't as it has a checks to restrict the use but because of bug in check if condition the check is never triggered).

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXC-1953