16.7.9.2. Initiating Discovery of Schema Changes
The NDB Cluster storage engine does not at present
automatically detect structural changes in databases or
tables. When a database or table is created or dropped, or
when a table is altered using ALTER TABLE,
the cluster must be made aware of the change. When a database
is created or dropped, the appropriate CREATE
SCHEMA or DROP SCHEMA statement
should be issued on each storage node in the cluster to induce
discovery of the change, that is:
mysqlS*> CREATE SCHEMA db_name;
mysqlS*> DROP SCHEMA db_name;
Dropping Tables:
When dropping a table that uses the NDB
Cluster storage engine, it is necessary to allow any
unfinished transactions to be completed and then not to begin
any new transactions before performing the DROP operation:
Stop performing transactions on the slave.
-
Drop the table:
mysqlS& DROP TABLE [db_name.]table_name;
-
Make all slave mysqld processes aware
of the drop:
mysqlS*> SHOW TABLES [FROM db_name];
All of the MySQL slave servers can now “see” that
the table has been dropped from the database.
Creating Tables
When creating a new table, you should perform the following
steps:
-
Create the table:
mysqlS> CREATE TABLE [db_name.]table_name (
# column and index definitions...) ENGINE=NDB;
-
Make all SQL nodes in the slave cluster aware of the new
table:
mysqlS*> SHOW TABLES [FROM db_name];
You can now start using the table as normal. When creating
a new table, note that — unlike the case when
dropping tables — it is not
necessary to stop performing any transactions beforehand.
Altering tables
When altering tables, you should perform the following steps
in the order shown:
Ensure that all pending transactions have been completed,
and do not initiate any new transactions at this time.
-
Issue any desired ALTER TABLE
statements that add or remove columns to or from an
existing table. For example:
mysqlS> ALTER TABLE table_name /* column definition, ... */;
-
Force all slave SQL nodes to become aware of the changed
table definition. The recommended way to do this is by
issuing a “throwaway” SHOW
TABLES statement on each slave
mysqld:
mysqlS*> SHOW TABLES;
You may now resume normal operations. These include
transactions involving records in the changed table.
Note that when you create a new NDB Cluster
table on the master cluster, if you do so using the
mysqld that acts as the replication master,
you must execute a SHOW TABLES, also on the
master mysqld, to initiate discovery
properly. Otherwise, the new table and any data it contains
cannot be seen by the replication master
mysqld, nor by the slave (that is, neither
the new table nor its data is replicated). If the table is
created on a mysqld that is not acting as
the replication master, it does not matter which
mysqld issues the SHOW
TABLES.
It is also possible to force discovery by issuing a
“dummy” SELECT statement using
the new or altered table in the statement's
FROM clause. Although the statement fails,
it causes the change to be recognized by the cluster. However,
issuing a SHOW TABLES is the preferred
method.
We are working to implement automatic discovery of schema
changes in a future MySQL Cluster release. For more
information about this and other Cluster issues, see
Section 16.9, “Known Limitations of MySQL Cluster”.
