Re: Alter table in 5.0.45 - Jonathan Haddad - com.mysql.lists.cluster

5 messages in com.mysql.lists.clusterRe: Alter table in 5.0.45

From	Sent On	Attachments
Jonathan Haddad	13 Nov 2007 12:06
Matthew Montgomery	13 Nov 2007 17:36
Jonathan Haddad	13 Nov 2007 17:50
Stewart Smith	13 Nov 2007 18:42
Stewart Smith	13 Nov 2007 18:44

Subject:	Re: Alter table in 5.0.45
From:	Jonathan Haddad (jo...@letsgetnuts.com)
Date:	11/13/2007 05:50:38 PM
List:	com.mysql.lists.cluster

So I assume I would need enough data memory to make the temporary table - so if I have 6GB allocated to ndbd, and 3.2 GB used, I will not be able to add another column, even in single user mode?

On 11/13/07, Matthew Montgomery <mmon...@mysql.com> wrote:

Hello Jonathan,

On Tue, 2007-11-13 at 12:07 -0800, Jonathan Haddad wrote:

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.)

http://dev.mysql.com/doc/refman/5.0/en/mysql-cluster-limitations-exclusive-to-cluster.html

However, on the 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.

It's not clear to me whether or not I can alter tables. Can I add columns and indexes once the cluster is being used? Thanks.

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.