MySQL Bugs: #29569: Error 1595 when replicating ndb->xxx in RBR

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:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1.21	OS:	Any
Assigned to:	Jon Stephens	CPU Architecture:	Any

Description:
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`)) 
    DEFAULT CHARSET=latin1;

--echo t1 on master
SHOW CREATE TABLE t1;
--sync_slave_with_master
--echo t1 on slave
SHOW CREATE TABLE t1;

--connection master
INSERT INTO t1 VALUES(1,"XYZ1","ABC1");
-- echo on master
select * from t1 order by nid;
--sync_slave_with_master
-- echo on slave
select * from t1 order by nid;

Thank you for the report.

Verified as described.

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
  options:

    --replicate-ignore-table=mysql.ndb_apply_status
    --replicate-wild-ignore-table=mysql.ndb_apply_status

  The latter option is only interesting if there are other tables that
  should be ignored.

I'll handle this one. Changed category to Cluster:Replication.

Should be a Docs bug since this is expected behaviour.

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

Pushed into 5.1.23-beta

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.

Thanks!

Please do not set this back to Open or Verified status without answering my questions in the previous comment.

Thank you.

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.