Altering Tables in MySQL Cluster 5.0.45
I’m setting up my first mysql cluster, and just wanted some clarification on a few things.
In the manual, it says:
Online schema changes. It is not possible to make online schema changes such as those accomplished using ALTER TABLE or CREATE INDEX, as the NDB Cluster engine does not support autodiscovery of such changes. (However, you can import or create a table that uses a different storage engine, and then convert it to NDB using ALTER TABLE tbl_name ENGINE=NDBCLUSTER. In such a case, you must issue a FLUSH TABLES statement to force the cluster to pick up the change.)
MySQL Cluster Exclusive Limitations
However, on another cluster limitations page, it says:
DDL operations. DDL operations (such as CREATE TABLE or ALTER TABLE) are not safe from data node failures. If a data node fails while trying to peform one of these, the data dictionary is locked and no further DDL statements can be executed without restarting the cluster.
I put the question to the MySQL mailing list. Within a few hours, I got a response:
Yes, you can alter the table. However, In 5.0 it is not an online operation. NDB will need to lock the target table, create a temporary table, copy all rows into it and rename the new on to the original ones name. Also, The other mysqld nodes in the cluster will not auto-discover the schema changes. You should put the cluster into single user mode when doing schema changes. This will force all the SQL nodes where the change was not issued on to reconnect and regenerate their local cached copy of the table schemas.
Good to know. The next question I had was retarding memory restrictions when altering tables. When you alter a table, you need to have enough memory free to copy the temp table into. so if you’re using 3.4 GB of your 6GB you have allocated to NDB, you won’t be able to perform the alter. Kind of a bummer.
Read through on the mailing list thread.
If you found this post helpful, please consider sharing to your network. I'm also available to help you be successful with your distributed systems! Please reach out if you're interested in working with me, and I'll be happy to schedule a free one-hour consultation.