Description:
Database and table discovery does not later function as expected if the database was created against a SQL node that was disconnected from the cluster at create time.
It expected that the other SQL nodes in the cluster will not see this database until that original SQL node is connected. Also, another schema operation need be performed which affects tables in that new database or the other SQL nodes also disconnect/reconnect and attempt table discovery. Neither of these triggers database discovery for existing tables.
This is similar to bug #39612 but dealing with the SQL node used to create the database being off-line preventing expected database/table discovery later by other nodes that were on-line at create time.
How to repeat:
matt@silo:/data/mysql/sandbox/7.1.6$ mysql -S /tmp/mysql71.sock -e "show databases";
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| ndbinfo |
| test |
+--------------------+
matt@silo:/data/mysql/sandbox/7.1.6$ mysql -S /tmp/mysql72.sock -e "show databases";
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| ndbinfo |
| test |
+--------------------+
matt@silo:/data/mysql/sandbox/7.1.6$ mysql -S /tmp/mysql71.sock -e "show status like 'ndb%'";
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| Ndb_cluster_node_id | 8 |
| Ndb_config_from_host | silo |
| Ndb_config_from_port | 1186 |
| Ndb_number_of_data_nodes | 2 |
| Ndb_number_of_ready_data_nodes | 2 |
etc...
+--------------------------------+-------+
matt@silo:/data/mysql/sandbox/7.1.6$ mysql -S /tmp/mysql72.sock -e "show status like 'ndb%'";
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| Ndb_cluster_node_id | 9 |
| Ndb_config_from_host | silo |
| Ndb_config_from_port | 1186 |
| Ndb_number_of_data_nodes | 2 |
| Ndb_number_of_ready_data_nodes | 2 |
etc...
+--------------------------------+-------+
-- comment out "ndbcluster" from my.cnf --
-- Restart mysqld --
matt@silo:/data/mysql/sandbox/7.1.6$ mysqladmin -S /tmp/mysql71.sock shutdown ; mysqld_safe --defaults-file=./my.cnf &
100915 14:23:32 mysqld_safe mysqld from pid file /data/mysql/sandbox/7.1.6/data/silo.pid ended
[3]+ Done mysqld_safe --defaults-file=./my.cnf
[3] 14043
matt@silo:/data/mysql/sandbox/7.1.6$ 100915 14:23:32 mysqld_safe Logging to '/data/mysql/sandbox/7.1.6/data/silo.err'.
100915 14:23:32 mysqld_safe Starting mysqld daemon with databases from /data/mysql/sandbox/7.1.6/data
matt@silo:/data/mysql/sandbox/7.1.6$ mysql -S /tmp/mysql71.sock -e "show status like 'ndb%'";
matt@silo:/data/mysql/sandbox/7.1.6$ mysql -S /tmp/mysql71.sock -e "create database db1";
matt@silo:/data/mysql/sandbox/7.1.6$ mysql -S /tmp/mysql71.sock -e "show databases";
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| test |
+--------------------+
matt@silo:/data/mysql/sandbox/7.1.6$ mysql -S /tmp/mysql72.sock -e "show databases";
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| ndbinfo |
| test |
+--------------------+
-- So far so good --
-- re-enable ndbcluster --
matt@silo:/data/mysql/sandbox/7.1.6$ vi my.cnf
matt@silo:/data/mysql/sandbox/7.1.6$ mysqladmin -S /tmp/mysql71.sock shutdown ; mysqld_safe --defaults-file=./my.cnf &
100915 14:26:03 mysqld_safe mysqld from pid file /data/mysql/sandbox/7.1.6/data/silo.pid ended
[3]+ Done mysqld_safe --defaults-file=./my.cnf
[3] 14168
matt@silo:/data/mysql/sandbox/7.1.6$ 100915 14:26:03 mysqld_safe Logging to '/data/mysql/sandbox/7.1.6/data/silo.err'.
100915 14:26:03 mysqld_safe Starting mysqld daemon with databases from /data/mysql/sandbox/7.1.6/data
matt@silo:/data/mysql/sandbox/7.1.6$ mysql -S /tmp/mysql71.sock -e "create table db1.t1 (a int) engine = ndbcluster";
matt@silo:/data/mysql/sandbox/7.1.6$ mysql -S /tmp/mysql71.sock -e "show tables" db1
+---------------+
| Tables_in_db1 |
+---------------+
| t1 |
+---------------+
matt@silo:/data/mysql/sandbox/7.1.6$ mysql -S /tmp/mysql72.sock -e "show tables" db1
ERROR 1049 (42000): Unknown database 'db1'
-- try restarting 2nd sql node --
matt@silo:/data/mysql/sandbox/7.1.6$ mysqladmin -S /tmp/mysql72.sock shutdown ; mysqld_safe --defaults-file=./my.cnf2 &
100915 14:29:24 mysqld_safe mysqld from pid file /data/mysql/sandbox/7.1.6/data2/silo.pid ended
[2]- Done mysqld_safe --defaults-file=./my.cnf2
[4] 14315
matt@silo:/data/mysql/sandbox/7.1.6$ 100915 14:29:24 mysqld_safe Logging to '/data/mysql/sandbox/7.1.6/data2/silo.err'.
100915 14:29:24 mysqld_safe Starting mysqld daemon with databases from /data/mysql/sandbox/7.1.6/data2
matt@silo:/data/mysql/sandbox/7.1.6$ mysql -S /tmp/mysql72.sock -e "show tables" db1
ERROR 1049 (42000): Unknown database 'db1'
-- still no db1 after SQL node restart --
Still must create database independently on every other node to see ndbcluster tables.
matt@silo:/data/mysql/sandbox/7.1.6$ mysql -S /tmp/mysql72.sock -e "create database db1" ;
matt@silo:/data/mysql/sandbox/7.1.6$ mysql -S /tmp/mysql72.sock -e "show tables" db1
+---------------+
| Tables_in_db1 |
+---------------+
| t1 |
+---------------+
Suggested fix:
Though it is documentation that:
Fully automatic database discovery. It is no longer a requirement for database autodiscovery that an SQL node already be connected to the cluster at the time that a database is created on another SQL node, or for a CREATE DATABASE or CREATE SCHEMA statement to be issued on the new SQL node after it joins the cluster.
-- http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-development-5-1-ndb-6-3.html
It does not explicitly cover the case where the SQL node where the database was originally created was also not connected to the cluster, I do not think this follows the intent of this feature either.