NDB Cluster circular replication
Hi everyone,
I am trying the POC of NDB Circular replication dual channel with 3 clusters,where every master(SQL node) is slave. Please find below environment details per cluster;
3 Node Cluster
==============
1. Host A with management node.
2. Host B with SQL/DATANODE.
3. Host C with SQL/DATANODE.
my.cnf parameters for slave/binlog.
server-id=6 #THIS ID VARIES ON ALL SERVERS
log-bin=mysql-bin
binlog_format = MIXED
expire_logs_days = 7
max_binlog_size = 100M
max_allowed_packet = 32M
slave-exec-mode=IDEMPOTENT #ADDED THIS FROM A BLOGPOST
log-slave-updates=true #ADDED THIS FROM A BLOGPOST earlier kept it as ON instead of true.
Problem 1:
DB creation logging to Cluster1:
================================
If i create a database on 1st cluster, mysqld log on 3rd cluster's either node shows "database already exists. Sometimes it shows on 2nd node of 1st cluster, most probably because node 2 of 1st cluster is slave of node2 of 3rd cluster.
Problem 2:
mysqld.log on cluster 1 is being contantly flushed by this error;
2018-11-10T16:49:39.602575Z 58 [Warning] Slave SQL for channel '': Could not execute Write_rows event on table samad.t1; Got temporary error 266 'Time-out in NDB, probably caused by deadlock' from NDB, Error_code: 1297; Lock wait timeout exceeded; try restarting transaction, Error_code: 1205; handler error HA_ERR_LOCK_WAIT_TIMEOUT; the event's master log mysql-bin.000001, end_log_pos 21724247, Error_code: 1205
i have only 1 table t1, please find below its structure.
show create table samad.t1;
---------------------------------+
| t1 | CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`changed_on` datetime DEFAULT NULL,
`client_id` varchar(75) DEFAULT NULL,
`code_str` varchar(50) DEFAULT NULL,
`created_on` datetime DEFAULT NULL,
`is_used` bit(1) DEFAULT NULL,
`msisdn` varchar(20) DEFAULT NULL,
`nonce` varchar(255) DEFAULT NULL,
`redirect_uri` varchar(255) DEFAULT NULL,
`response_type` varchar(25) DEFAULT NULL,
`scope` varchar(25) DEFAULT NULL,
`state` varchar(125) DEFAULT NULL,
`correlation_id` varchar(25) DEFAULT NULL,
`is_new_user` bit(1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=ndbcluster AUTO_INCREMENT=62 DEFAULT CHARSET=latin1
this table has only 61 rows, which i've added using different SQL nodes to check ring replication.
Kindly advise if i am missing any parameter in "my.cnf" for ring replication or it is something else.
Regards
Samad