Re: Alter table in 5.0.45

[Matthew Montgomery]

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.


-- 
Matthew Montgomery, Support Engineer
San Antonio, TX
MySQL Inc, www.mysql.com