Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

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:

  1. Stop performing transactions on the slave.

  2. Drop the table:

    mysqlS& DROP TABLE [db_name.]table_name;
    
  3. 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:

  1. Create the table:

    mysqlS> CREATE TABLE [db_name.]table_name (
            #  column and index definitions...
            ) ENGINE=NDB;
    
  2. 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:

  1. Ensure that all pending transactions have been completed, and do not initiate any new transactions at this time.

  2. 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, ... */;
    
  3. Force all slave SQL nodes to become aware of the changed table definition. The recommended way to do this is by issuing a “throwawaySHOW 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 “dummySELECT 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”.


 
 
  Published under the terms of the GNU General Public License Design by Interspire