"Got error 4009 'Cluster Failure' from NDB. Could not acquire global schema lock" and "Could not connect to storage engine"
======== Setup: ========
I have installed 4 Oracle Linux 7.3: 2 for data nodes, 1 for management and 1 for SQL API node. The machines can ping each other, SSH working, firewall disbled and "etc/hosts" files are properly filled:
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
172.16.1.101 mysqlcluster-mgmt
172.16.1.102 mysqlcluster-mysql
172.16.1.103 mysqlcluster-data-1
172.16.1.104 mysqlcluster-data-2
I installed the ndb-7.5.7 version, following the official documentation available at the mysql ndb webpage.
The management node has the following config in the "/var/lib/mysql-cluster/config.ini" file:
[ndbd default]
# Options affecting ndbd processes on all data nodes:
NoOfReplicas=2
DataMemory=512M
IndexMemory=256M
ServerPort=2202
[ndb_mgmd]
# Management process options:
HostName=172.16.1.101
DataDir=/var/lib/mysql-cluster
[ndbd]
# Options for data node "A":
HostName=172.16.1.103
NodeId=2
DataDir=/usr/local/mysql/data
[ndbd]
# Options for data node "B":
HostName=172.16.1.104
NodeId=3
DataDir=/usr/local/mysql/data
[mysqld]
# SQL node options:
HostName=172.16.1.102
The two data nodes and the SQL API node has the same config file ("/etc/my.cnf") on each node:
[mysqld]
# Options for mysqld process:
ndbcluster
[mysql_cluster]
# Options for NDB Cluster processes:
ndb-connectstring=172.16.1.101
======== Starting the cluster: ========
1. management: ndb_mgmd -f /var/lib/mysql-cluster/config.ini
(or to erase the cache:)
ndb_mgmd --initial --config-file=/var/lib/mysql-cluster/config.ini
2. data node 1 and 2: ndbd
3. mysql server: service mysql start
======== Testing: ========
Status of the cluster: the first wierd thing, that the ndb_mgm command can only be runned from the SQL API node, however I installed on the management node and also started there. Anyway, I enter the ndb_mgm from the API node, and got this:
Connected to Management Server at: 172.16.1.101:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @172.16.1.103 (mysql-5.7.19 ndb-7.5.7, Nodegroup: 0, *)
id=3 @172.16.1.104 (mysql-5.7.19 ndb-7.5.7, Nodegroup: 0)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @172.16.1.101 (mysql-5.7.19 ndb-7.5.7)
[mysqld(API)] 1 node(s)
id=4 (not connected, accepting connect from 172.16.1.102)
======== Database problem: ========
I can login to MySQL from command line on the API node:
[root@mysqld-mgmt-1 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.37-ndb-7.3.18-cluster-gpl MySQL Cluster Community Server (GPL)
(...)
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ndbcluster | YES | Clustered, fault-tolerant tables | YES | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| ndbinfo | YES | MySQL Cluster system information storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
11 rows in set (0.01 sec)
I created a sample table without "engine" parameter, just to try things. I wanted to alter table to use ndb engine, but I have the following problem:
mysql> use clusterdb;
Database changed
mysql> select * from simples;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
+----+
4 rows in set (0.00 sec)
mysql> alter table simples engine=ndb;
ERROR 157 (HY000): Could not connect to storage engine
mysql> show warnings;
+---------+------+---------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------------------------+
| Warning | 1296 | Got error 4009 'Cluster Failure' from NDB. Could not acquire global schema lock |
| Error | 157 | Could not connect to storage engine |
| Error | 1499 | Too many partitions (including subpartitions) were defined |
+---------+------+---------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> show global status like 'ndb_number_of%';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| Ndb_number_of_data_nodes | 2 |
| Ndb_number_of_ready_data_nodes | 0 |
+--------------------------------+-------+
2 rows in set (0.00 sec)
Also, creating table with "engine=ndb" parameter also fails and give the same error. I see the db working with InnoDB engine fine, but I need to use the cluster with the ndb engine.
Any suggestion?