Description:
SQL nodes in a MySQL Cluster will propagate certain DDL statements to the other SQL nodes in the cluster, allowing 'autodiscovery' of NDB tables, but also databases.
It stands to reason that removing NDB tables and databases is likewise propagated, however, there seems to be a rather nasty twist to DROP-ping a data base in some cases that is inconsistent with the behaviour one would expect for simply dropping a table.
A DROP DATABASE of a database that contains only local table definitions will (in addition to dropping the local database) remove all NDB tables in other SQL nodes that happen to have a name equal to one of the tables in the dropped database, although the databases on other SQL nodes themselves will not be dropped if they still contain local table definitions.
How to repeat:
Set up a MySQL Cluster with 2 data nodes.
in SQL node #1, issue a CREATE DATABASE statement:
create database world;
(a database named world is now available on both SQL nodes)
Import the world database tables into the world database on each SQL node:
on SQL node #1:
use world;
source /opt/mysql/sample-databases/world/world.sql
on SQL node #2:
use world;
source /opt/mysql/sample-databases/world/world.sql
Now, both SQL nodes have a world database containing only MyISAM tables.
On SQL node #1, change the City table to use the NDB storage engine:
ALTER TABLE City ENGINE=NDB
On SQL node #2, verify that the City table is still a MyISAM table (or at least, not a NDB table)
show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| City |
| Country |
| CountryLanguage |
+-----------------+
3 rows in set, 1 warning (0.01 sec)
Warning (Code 1050): Local table world.City shadows ndb table
(this is expected and desired behaviour: ALTER TABLE will not destroy any existing local tables, but issue a warning instead)
Now, still on SQL node #2, DROP the local City table:
DROP TABLE City;
This in fact destroys the local 'Shadow' and allows the NDB table with the same name to become visible into the world database at SQL node #2:
show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| Country |
| CountryLanguage |
| City |
+-----------------+
3 rows in set (0.02 sec)
(Show create table proves City is now really an NDB table instead of a MyISAM table)
On SQL Node #1, a show tables will reveal that nothing has changed there - the DROP TABLE of the local table on SQL Node #2 was not propagated to DROP the NDB table visible to SQL node #1.
The test up to this point shows the -desired- behaviour: DROPping a local table does not affect a table by the same name on another SQL node (but does result in sudden visibility of the clustered table).
Now we arrive to witness inconsistency #1
on SQL Node #2, (the one where we just dropped the MyISAM table to see the NDB table appear instead) we ALTER the table back to use MyISAM again:
ALTER TABLE City ENGINE=MyISAM;
at this point, SQL Node #2 contains only MyISAM tables again.
Doing a SHOW TABLES in SQL node #1 however suddenly reveals the City table has dissappeared:
show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| Country |
| CountryLanguage |
+-----------------+
2 rows in set (0.00 sec)
so, the ALTER TABLE ENGINE = MyISAM destroyed the (NDB) City table previously visible to SQL node #1. It was expected that either of the following would happen:
1) the ALTER TABLE ENGINE = MYISAM statement would be propagated completely (effectively turning the NDB table into local MyISAM tables all over the cluster) (this is undesirable but consistent).
2) The ALTER TABLE ENINGE = MyISAM statement would be executed only locally, effectively shadowing the existing NDB table (this is desirable and consistent).
To witness inconsistency #2 we best recreate the environment up to (but not including) the last DROP TABLE City; situation. So, sql node #1 now sees an NDB City table (and the rest is MyISAM) and SQL node #2 shadows the NDB City table with a MyISAM table.
show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| City |
| Country |
| CountryLanguage |
+-----------------+
3 rows in set, 1 warning (0.01 sec)
Warning (Code 1050): Local table world.City shadows ndb table
(I could file a separate bug report but I feel all this is the same bug - general inconsistent behaviour handling conflicting situations)
Now, on SQL node #2, we drop the world database:
drop database world;
This will in fact drop the database on sql node #2 as expected. However, We now see that on SQL node #1, the database is still there, except that the City table is dropped:
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| Country |
| CountryLanguage |
+-----------------+
2 rows in set (0.00 sec)
This is inconsitency #2. It is not expected and not desirable behaviour. Expected was either one of the following:
1) the DROP DATABASE world statement would be propagated completely (effectively dropping all world databases in the cluster) (this is undesirable but consistent).
2) The DROP DATABASE world statement would be executed only locally, not touching any NDB tables (as there are no NDB tables in the database being dropped) (this is desirable and consistent).
3) As a variant of 2 It is thinkable to allow the database to not be dropped as long as there are NDB tables that may be 'unshadowed' although this seems to make less sense, as the statement really does order the database (which is itself non-clustered, unlike tables) to be removed.
Suggested fix:
My expectation is that:
Table DDL:
for NDB table should propagate to any table in any other SQL node if the table has the same name as the target table and its containing database has the same name as the database containing the target table.
for local table, no propagation of the statement should occur to any of the other tables
Database DDL:
should always result in a DROP of the local database and local tables
should propagate DROP of any NDB tables to other SQL Nodes in the manner described under Table DDL, but *never* result in DROP of local tables
should result in DROP of database in other SQL nodes if the DROPPING of NDB tables would result in an empty database being left behind (i.e, propagate DROP DATABASE in other SQL nodes only if it contained only NDB tables)
