MySQL Forums
Forum List  »  NDB clusters

MySQL Cluster 7.5.5 'create table' hangs on master SQL node when master/slave replication set up between to two db clusters
Posted by: Phil Somers
Date: March 03, 2017 11:43AM

Description:
-----------

With MySQL Cluster v7.5.5, when running replication between two db clusters, 'create database <new_db>' and 'use database <new_db>' run clean but 'create table' hangs in the newly replicated db until a subsequent transaction (create table, insert into table, etc) is run on any other database in the cluster.


Any suggestions as to what is causing the create table to hang until a subsequent DDL/DML statement is run would be more than helpful.


Things of Note:
--------------
- MySQL Cluster was just upgraded to v5.5.5 from v4.7.12

- 'mysql_upgrade --force --upgrade-system-tables' was run on all SQL nodes

- as per the online MySQL Cluster 7.5.5 documentation:

Beginning with NDB 7.5.2, the ndb_binlog_index table uses the InnoDB storage engine. (Use of the MyISAM storage engine for this table continues to be supported for backward compatibility.)

When upgrading a previous release to NDB 7.5.2 or later, you can use the --force --upgrade-system-tables options with mysql_upgrade so that it performs ALTER TABLE ... ENGINE=INNODB on the ndb_binlog_index table.


Environment:
-----------

-----
uname -a:
Linux <hostname> 3.10.0-514.6.1.el7.x86_64 #1 SMP Wed Jan 18 13:06:36 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux

-----
cat /etc/centos-release:
CentOS Linux release 7.3.1611 (Core)


-----
master db cluster:
-----------------

ndb_mgm> show
Connected to Management Server at: <VM1 IP>:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=1 @<VM3 IP> (mysql-5.7.17 ndb-7.5.5, Nodegroup: 0, *)
id=2 @<VM4 IP> (mysql-5.7.17 ndb-7.5.5, Nodegroup: 0)

[ndb_mgmd(MGM)] 2 node(s)
id=50 @<VM1 IP> (mysql-5.7.17 ndb-7.5.5)
id=51 @<VM2 IP> (mysql-5.7.17 ndb-7.5.5)

[mysqld(API)] 18 node(s)
id=52 @<VM1 IP> (mysql-5.7.17 ndb-7.5.5)
id=53 @<VM2 IP> (mysql-5.7.17 ndb-7.5.5)


replicated db cluster:
--------------------

ndb_mgm> show
Connected to Management Server at: <VM5 IP>:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=1 @<VM7 IP> (mysql-5.7.17 ndb-7.5.5, Nodegroup: 0, *)
id=2 @<VM8 IP> (mysql-5.7.17 ndb-7.5.5, Nodegroup: 0)

[ndb_mgmd(MGM)] 2 node(s)
id=50 @<VM5 IP> (mysql-5.7.17 ndb-7.5.5)
id=51 @<VM6 IP> (mysql-5.7.17 ndb-7.5.5)

[mysqld(API)] 18 node(s)
id=52 @<VM5 IP> (mysql-5.7.17 ndb-7.5.5)
id=53 @<VM6 IP> (mysql-5.7.17 ndb-7.5.5)

-----
MySQL Cluster 1
- v 7.5.5

VM1:
- MGMT node
- SQL node

VM2:
- MGMT node
- SQL node

VM3:
- Data node

VM4:
- Data node





-----
MySQL Cluster 2
- v 7.5.5

VM5:
- MGMT node
- SQL node

VM6:
- MGMT node
- SQL node

VM7:
- Data node

VM8:
- Data node


-----
Primary Replication Channel:

- VM1 SQL node (master) to VM5 SQL node (slave)

- VM1 SQL node my.cnf replication related parameters:

#Rep Related
server-id=100
log-bin=<dir path>/repl_bin
binlog-format=row
binlog-ignore-db='<DB_B>'

- One of the custom databases (DB_B) on this cluster is ignored, the rest are replicated.


- VM5 SQL node my.cnf replication related parameters:

#Rep Related
relay-log=repl-relay-bin
server-id=200




Issue:
-----
-----

on VM1 SQL node:

mysql> create database ABC;
Query OK, 1 row affected (0.05 sec)


mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ABC |
| DB_A |
| DB_B |
| mysql |
| ndbinfo |
| performance_schema |
| sys |
| test |
+--------------------+
9 rows in set (0.00 sec)

- database ABC gets replicated to replicated db cluster
- see the same output when run 'show databases' on VM5 SQL node.



mysql> use ABC;
Database changed

- this command runs without issue


mysql> create table test (col_A int, col_B int);

- this command hangs


mysql> show processlist;
+-------+--------------+-----------------------+------+-------------+------+---------------------------------------------------------------+-----------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+--------------+-----------------------+------+-------------+------+---------------------------------------------------------------+-----------------------------------------+
| 1 | system user | | | Daemon | 0 | Waiting for event from ndbcluster | NULL |
| 8298 | root | localhost | ABC | Query | 14 | creating table | create table test(col_A int, col_B int) |
| 9986 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 10878 | c_loc_rep_01 | <VM1 SQL node>:58376 | NULL | Binlog Dump | 21 | Master has sent all binlog to slave; waiting for more updates | NULL |
+-------+--------------+-----------------------+------+-------------+------+---------------------------------------------------------------+-----------------------------------------+


- with every subsequent execution of 'show processlist', for ID 8289, the time steadily increases and the state remains consistent with 'creating table'

mysql> show processlist;
+-------+--------------+-----------------------+------+-------------+------+---------------------------------------------------------------+-----------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+--------------+-----------------------+------+-------------+------+---------------------------------------------------------------+-----------------------------------------+
| 1 | system user | | | Daemon | 0 | Waiting for event from ndbcluster | NULL |
| 8298 | root | localhost | ABC | Query | 71 | creating table | create table test(col_A int, col_B int) |
| 9986 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 10879 | c_loc_rep_01 | <VM1 SQL node>:58378 | NULL | Binlog Dump | 18 | Master has sent all binlog to slave; waiting for more updates | NULL |
+-------+--------------+-----------------------+------+-------------+------+---------------------------------------------------------------+-----------------------------------------+


- mysqlbinlog output on VM1 does not show the 'create table' command



- when a create table is run in the non replicated db on VM1 SQL node using a new mysql session:

mysql> use DB_B; *** (this is the db that is ignored by rep)
Database changed

mysql> create table test (col_A int, col_B int);
Query OK, 0 rows affected (0.13 sec)

- this create table runs immediate and without issue



- returning to mysql prompt for database ABC, the create table has now executed and has been replicated to VM5 SQL node

mysql> create table test(col_A int, col_B int);
Query OK, 0 rows affected (6 min 23.63 sec)


- mysqlbinlog output now shows the 'create table' command

Options: ReplyQuote


Subject
Views
Written By
Posted
MySQL Cluster 7.5.5 'create table' hangs on master SQL node when master/slave replication set up between to two db clusters
325
March 03, 2017 11:43AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.