Description:
When replicating from an ndb table on master to a non-ndb table on slave and using row-based logging, the slave stops with the following error:
Last_SQL_Errno 1598
Last_SQL_Error Error 'Binary logging not possible. Message: Statement cannot be written atomically since more than one engine involved and at least one engine is self-logging' in Write_rows event: when locking tables
A self-logging table is a table that does its own binlogging, circumventing log.cc.
We get the error because of the following two facts:
(A) Binary logging is limited so that it does not support when a statement contains both (1) modifications to a self-logging and (2) modifications to more than one table.
(B) Statements modifying an ndb table sometimes modify the ndb table mysql.ndb_apply_status too.
When we modify an ndb table on the master, and mysql.ndb_apply_status gets modified too, the master logs the modification of both tables to the binlog, in the same statement. When the slave has the same table using another engine, the statement that was ok on the master is suddenly not ok on the slave (according to (A)).
How to repeat:
==== -master.opt and -slave.opt ====
--innodb --ndbcluster
==== test case ====
--source include/have_ndb.inc
--source include/have_innodb.inc
--source include/master-slave.inc
# setup master
CREATE TABLE ndb_myisam (a INT) ENGINE=NDB;
# setup slave
sync_slave_with_master;
DROP TABLE ndb_myisam;
CREATE TABLE ndb_myisam (a INT) ENGINE=MYISAM;
# do something
connection master;
INSERT INTO ndb_myisam VALUES (1);
sync_slave_with_master;
Suggested fix:
Workarounds:
- Run slave with --skip-table=mysql.ndb_apply_status. (Removes the features of ndb_apply_status).
- Turn off binlogging on the slave. (Removes the features of binlogging on slave.)
- Do not replicate from ndb to non-ndb.
- On the slave, change mysql.ndb_apply_status to use the same engine as the replicated table has on the slave. (Does not work if there are two tables where: on master, both are ndb; on slave, one is ndb and one is non-ndb.)
Fix:
- Go to sql_base.cc, decide_logging_format(), the place where it does:
if (multi_engine &&
(flags_some_set & HA_HAS_OWN_BINLOGGING))
Here, we should replace multi_engine by a flag that is set if there are multiple engines even if we do not count the table mysql.ndb_apply_status.
Description: When replicating from an ndb table on master to a non-ndb table on slave and using row-based logging, the slave stops with the following error: Last_SQL_Errno 1598 Last_SQL_Error Error 'Binary logging not possible. Message: Statement cannot be written atomically since more than one engine involved and at least one engine is self-logging' in Write_rows event: when locking tables A self-logging table is a table that does its own binlogging, circumventing log.cc. We get the error because of the following two facts: (A) Binary logging is limited so that it does not support when a statement contains both (1) modifications to a self-logging and (2) modifications to more than one table. (B) Statements modifying an ndb table sometimes modify the ndb table mysql.ndb_apply_status too. When we modify an ndb table on the master, and mysql.ndb_apply_status gets modified too, the master logs the modification of both tables to the binlog, in the same statement. When the slave has the same table using another engine, the statement that was ok on the master is suddenly not ok on the slave (according to (A)). How to repeat: ==== -master.opt and -slave.opt ==== --innodb --ndbcluster ==== test case ==== --source include/have_ndb.inc --source include/have_innodb.inc --source include/master-slave.inc # setup master CREATE TABLE ndb_myisam (a INT) ENGINE=NDB; # setup slave sync_slave_with_master; DROP TABLE ndb_myisam; CREATE TABLE ndb_myisam (a INT) ENGINE=MYISAM; # do something connection master; INSERT INTO ndb_myisam VALUES (1); sync_slave_with_master; Suggested fix: Workarounds: - Run slave with --skip-table=mysql.ndb_apply_status. (Removes the features of ndb_apply_status). - Turn off binlogging on the slave. (Removes the features of binlogging on slave.) - Do not replicate from ndb to non-ndb. - On the slave, change mysql.ndb_apply_status to use the same engine as the replicated table has on the slave. (Does not work if there are two tables where: on master, both are ndb; on slave, one is ndb and one is non-ndb.) Fix: - Go to sql_base.cc, decide_logging_format(), the place where it does: if (multi_engine && (flags_some_set & HA_HAS_OWN_BINLOGGING)) Here, we should replace multi_engine by a flag that is set if there are multiple engines even if we do not count the table mysql.ndb_apply_status.