Bug #29569 Error 1595 when replicating ndb->xxx in RBR
Submitted: 5 Jul 2007 9:51 Modified: 9 Oct 2007 18:52
Reporter: Rafal Somla Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.1.21 OS:Any
Assigned to: Jon Stephens CPU Architecture:Any

[5 Jul 2007 9:51] Rafal Somla
Table t1 on master uses ndb engine. It is replicated to slave but on slave uses non-ndb engine, say myisam.

Since t1 uses ndb, whenever a Write/Update_rows binlog entry is created for t1, it also contains updates to ndb internal table mysql.ndb_apply_status. For example:

#775 11:42:54 server id 1  end_log_pos 41       Table_map: `test`.`t1` mapped to number 21
#775 11:42:54 server id 1  end_log_pos 99       Table_map: `mysql`.`ndb_apply_status` mapped to number 18
#775 11:42:54 server id 1  end_log_pos 158      Write_rows: table id 18
#775 11:42:54 server id 1  end_log_pos 202      Write_rows: table id 21 flags: STMT_END_F

When this entry is replicated to the slave, on which t1 uses myisam engine, slave recognizes a Write_rows event with both myisam and ndb tables involved. This triggers the error:

070705 12:42:54 [ERROR] Slave SQL: 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, Error_code: 1595
070705 12:42:54 [Warning] Slave: Binary logging not possible. Message: Statement cannot be written atomically since more than one engine involved and at least one engine is self-logging Error_code: 1595

and breaks replication.

How to repeat:
Run the following test case in row binlog mode:

-- source include/have_ndb.inc
-- source include/have_log_bin.inc
-- source include/ndb_master-slave.inc

--connection master
SET SESSION storage_engine=ndbcluster;

CREATE TABLE `t1` ( `nid` int(11) NOT NULL default '0',
 	            `nom` char(4) default NULL,
  		    `prenom` char(4) default NULL,
		    PRIMARY KEY  (`nid`)) 

--echo t1 on master
--echo t1 on slave

--connection master
-- echo on master
select * from t1 order by nid;
-- echo on slave
select * from t1 order by nid;
[6 Jul 2007 11:30] Sveta Smirnova
Thank you for the report.

Verified as described.
[7 Aug 2007 13:57] Mats Kindahl
This behavior is not a bug.

Mixing engines that has their own binlogging with engines that do not handle their own binlogging in a transaction is not allowed and will abort with this error. In order to replication from NDB to, for example, MyISAM, there are three workarounds:

- Turn of the binary logging on the slave by setting ``SQL_BIN_LOG`` to 0

- Alter the engine for the ``mysql.ndb_apply_status`` to be an engine that does
  not handle it's own binlogging, e.g., ``ALTER TABLE tbl ENGINE=MyISAM``

- Filter out changes to that table on the slave side using either of the


  The latter option is only interesting if there are other tables that
  should be ignored.
[9 Aug 2007 7:30] Jon Stephens
I'll handle this one. Changed category to Cluster:Replication.
[13 Aug 2007 7:27] Jon Stephens
Should be a Docs bug since this is expected behaviour.
[13 Aug 2007 13:36] Jon Stephens
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Added suggested workarounds to http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-replication-issues.html
[4 Sep 2007 17:11] Bugs System
Pushed into 5.1.23-beta
[4 Sep 2007 21:50] Jon Stephens
Can somebody please tell me what (if anything) was pushed to 5.1.23?

This was a Docs bug, right? The documentation was fixed three weeks ago.

If there was a server patch, there's no mention here of it or what it does...?

If the "patch pushed" note was in error, please add a comment to this effect as close the bug again.

[11 Sep 2007 11:10] Jon Stephens
Please do not set this back to Open or Verified status without answering my questions in the previous comment.

Thank you.
[9 Oct 2007 18:49] Jon Stephens
No feedback was provided. The bug is being suspended because we assume that you are no longer experiencing the problem. If this is not the case and you are able to provide the information that was requested earlier, please do so and change the status of the bug back to "Open". Thank you.