Bug #56807 Database discovery not working when database created with offline mysqld
Submitted: 15 Sep 2010 19:45
Reporter: Matthew Montgomery Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S3 (Non-critical)
Version:ndb-7.1 OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D4 (Minor) / R4 (High) / E4 (High)

[15 Sep 2010 19:45] Matthew Montgomery
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.
[16 Sep 2010 13:55] Matthew Montgomery
Workaround: Issuing CREATE DATABASE `db1`; on *any* connected SQL node that does not currently show the database is sufficient trigger database/table discovery on *all* SQL nodes that are likewise missing it.