MySQL Cluster 5.0 ALTER TABLE In-Depth
by Martin Skold
In this Developers Zone article we take an in-depth look at how the ALTER TABLE command is implemented in MySQL Cluster 5.0.
MySQL's ALTER TABLE is a powerful SQL command that allows you to modify all parts of a table definition. This includes basic operations like adding and deleting columns, but also advanced functionality, like migrating your table data between storage engines.
At the end of the article we’ll also introduce some improvements that have made to the ALTER TABLE command in the upcoming 5.1 release.
The ALTER TABLE syntax
MySQL’s ALTER TABLE enables you to change the structure of an existing table. As mentioned, you can use this functionality to add or delete columns, but also perform operations like: creating or deleting indexes, changing the data type of an existing column, or renaming columns or tables. You can also change the comment for the table, as well as the storage engine (type) of the table.
The syntax for many of the allowable alterations is similar to the clauses supported by the CREATE TABLE statement. This includes table_option modifications, for options such as ENGINE, AUTO_INCREMENT, and AVG_ROW_LENGTH.
In most cases, ALTER TABLE works by making a temporary copy of the original table. The alteration is performed on the “copy”, and then the original table is deleted and the new one is renamed. While an ALTER TABLE is executing, the original table is readable by other clients. Updates and writes to the table are stalled until the new table is ready, and then are automatically redirected to the new table without any failed updates.
If you use ALTER TABLE tbl_name RENAME TO new_tbl_name without any other options, MySQL simply renames any files that correspond to the table tbl_name. There is no need to create a temporary table.
The complete syntax and options are shown below:
ALTER [IGNORE] TABLE tbl_name
alter_specification [, alter_specification] ...
alter_specification:
ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
| ADD [COLUMN] (column_definition,...)
| ADD {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
| ADD [CONSTRAINT [symbol]]
PRIMARY KEY [index_type] (index_col_name,...)
| ADD [CONSTRAINT [symbol]]
UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...)
| ADD [FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (index_col_name,...)
| ADD [CONSTRAINT [symbol]]
FOREIGN KEY [index_name] (index_col_name,...)
[reference_definition]
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
| CHANGE [COLUMN] old_col_name column_definition
[FIRST|AFTER col_name]
| MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
| DROP [COLUMN] col_name
| DROP PRIMARY KEY
| DROP {INDEX|KEY} index_name
| DROP FOREIGN KEY fk_symbol
| DISABLE KEYS
| ENABLE KEYS
| RENAME [TO] new_tbl_name
| ORDER BY col_name
| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
| [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]
| DISCARD TABLESPACE
| IMPORT TABLESPACE
| table_option ...
How does ALTER TABLE work in MySQL Cluster 5.0?
The current ALTER TABLE in MySQL Cluster 5.0 is basically a table copy mechanism where the new table is given the desired alteration(s). To demonstrate the ALTER TABLE functionality, let’s add an int column to a table. (Please note that for the following examples we assume that the default table type to be NDB.)
mysql> CREATE TABLE table1(col1 INT NOT NULL, col2 VARCHAR(10), PRIMARY KEY(col1)); mysql> INSERT INTO table1 VALUES (1,”One”), (2,”Two”), (3,”Three”); mysql> SELECT * FROM table1; +------+-------+ | col1 | col2 | +------+-------+ | 1 | One | | 2 | Two | | 3 | Three | +------+-------+ mysql> ALTER TABLE table1 ADD col3 int;
The generalized pseudo code for how MySQL internally processes this sequence of events is described below:
- LOCK TABLE table1 READ
- CREATE TABLE #sql-5901_1(col1 int not null, col2 varchar(10), col3 int, primary key (col1))
- INSERT INTO #sql-5901_1 (col11, col21) SELECT col1 AS col11, col2 AS col22 FROM TABLE table1
- DROP TABLE table1
- RENAME TABLE #sql-5901_1 TO table1
mysql> SELECT * FROM table1; +------+-------+------+ | col1 | col2 | col3 | +------+-------+------+ | 3 | Three | NULL | | 1 | One | NULL | | 2 | Two | NULL | +------+-------+------+
Since the new column we added is NULL, this will be the initial value.
Alternatively, if the new attribute is not allowed to be NULL, we get a default value, depending on the column type specified. For example:
mysql> ALTER TABLE table1 MODIFY col3 INT NOT NULL; mysql> SELECT * from table1; +------+-------+------+ | col1 | col2 | col3 | +------+-------+------+ | 1 | One | 0 | | 2 | Two | 0 | | 3 | Three | 0 | +------+-------+------+
The generalized pseudo code of how MySQL internally processes this sequence of events is described below:
- LOCK TABLE table1 READ
- CREATE TABLE #sql-5901_1(col1 INT NOT NULL, col2 VARCHAR(10), col3 INT, primary key (col1))
- INSERT INTO #sql-5901_1 (col11, col22, 0) SELECT col1 AS col11, col2 AS col22 FROM table1
- DROP TABLE table1
- RENAME table #sql-5901_1 TO table1
An ALTER that specified VARCHAR would be described as such:
mysql> ALTER TABLE table1 MODIFY col3 VARCHAR(10) NOT NULL; mysql> SELECT * FROM table1; +------+-------+------+ | col1 | col2 | col3 | +------+-------+------+ | 3 | Three | “” | | 1 | One | “” | | 2 | Two | “” | +------+-------+------+
Below is a list of the default values for various data types:
Changing the table type/storage engine copies a table from one storage engine to another. For example:
mysql> CREATE TABLE table2(col1 INT NOT NULL, col2 VARCHAR(10), PRIMARY KEY(col1)) ENGINE = innodb; mysql> INSERT INTO table2 VALUES (1,"One"),(2,"Two"),(3,"Three"); mysql> ALTER TABLE table2 ENGINE = ndb; mysql> SELECT * FROM table2; +------+-------+ | col1 | col2 | +------+-------+ | 3 | Three | | 1 | One | | 2 | Two | +------+-------+
If a table is altered and it has a secondary index, it will be copied as well:
mysql> CREATE TABLE table3(col1 INT NOT NULL, col2 INT NOT NULL, col3 INT NOT NULL, PRIMARY KEY (col2), UNIQUE key1 (col1,col3));
Using the ndb_show_tables utility we see:
id type state logging database schema name 3 IndexTrigger Online - NDB $INDEX_11_CUSTOM 3 UserTable Online Yes cluster def NDB$BLOB_2_3 5 HashIndexTrigger Online - NDB $INDEX_12_UPDATE 4 HashIndexTrigger Online - NDB $INDEX_12_INSERT 1 SystemTable Online Yes sys def NDB$EVENTS_0 8 OrderedIndex Online No db1 def PRIMARY 1 IndexTrigger Online - NDB $INDEX_8_CUSTOM 11 OrderedIndex Online No db1 def key1 2 UserTable Online Yes cluster def schema 6 HashIndexTrigger Online - NDB $INDEX_12_DELETE 4 UserTable Online Yes cluster def apply_status 6 OrderedIndex Online No db1 def PRIMARY 2 IndexTrigger Online - NDB $INDEX_10_CUSTOM 12 UniqueHashIndex Online Yes db1 def key1$unique 7 UserTable Online Yes db1 def table1 10 OrderedIndex Online No db1 def PRIMARY 5 UserTable Online Yes db1 def table2 9 UserTable Online Yes db1 def table3 0 SystemTable Online Yes sys def SYSTAB_0 0 IndexTrigger Online - NDB $INDEX_6_CUSTOM mysql> ALTER TABLE table3 ADD col4 INT NOT NULL;
Again, using the ndb_show_tables utility we are able to see that the index has migrated to the new table, but as a new rebuilt table:
id type state logging database schema name 8 IndexTrigger Online - NDB $INDEX_15_CUSTOM 3 UserTable Online Yes cluster def NDB$BLOB_2_3 10 HashIndexTrigger Online - NDB $INDEX_16_UPDATE 1 SystemTable Online Yes sys def NDB$EVENTS_0 9 HashIndexTrigger Online - NDB $INDEX_16_INSERT 8 OrderedIndex Online No db1 def PRIMARY 15 OrderedIndex Online No db1 def key1 1 IndexTrigger Online - NDB $INDEX_8_CUSTOM 14 OrderedIndex Online No db1 def PRIMARY 2 UserTable Online Yes cluster def schema 11 HashIndexTrigger Online - NDB $INDEX_16_DELETE 4 UserTable Online Yes cluster def apply_status 6 OrderedIndex Online No db1 def PRIMARY 7 IndexTrigger Online - NDB $INDEX_14_CUSTOM 7 UserTable Online Yes db1 def table1 5 UserTable Online Yes db1 def table2 13 UserTable Online Yes db1 def table3 0 SystemTable Online Yes sys def SYSTAB_0 16 UniqueHashIndex Online Yes db1 def key1$unique 0 IndexTrigger Online - NDB $INDEX_6_CUSTOM
Improvements to ALTER TABLE in MySQL 5.1
In MySQL 5.1, simple ALTER TABLE operations, such as the renaming of fields, is done without copying the table. This is possible because only the table definition is affected, not the data. Please note that MySQL Cluster does not support this operation in an online manner, since it retains a local data dictionary which needs to updated with the new field name.
mysql> CREATE TABLE table4(col1 INT NOT NULL, col2 VARCHAR(10), PRIMARY KEY(col1)) ENGINE = innodb; mysql> alter table table4 change col2 col2_new VARCHAR(10);
The field name is changed by modifying the meta-data in the MySQL server (and .frm file on the disk). The table itself is unmodified in the storage engine.
For cluster tables, 5.1 will introduce support for the adding and dropping of an index to and from an existing table without any copying.
mysql> CREATE TABLE table5(col1 INT NOT NULL, col2 VARCHAR(10), PRIMARY KEY(col1)) ENGINE = ndb;
Using ndb_show_tables we see:
id type state logging database schema name 5 UserTable Online Yes test def table5 3 UserTable Online Yes cluster def NDB$BLOB_2_3 6 OrderedIndex Online No sys def PRIMARY 1 SystemTable Online Yes sys def NDB$EVENTS_0 2 UserTable Online Yes cluster def schema 4 UserTable Online Yes cluster def apply_status 0 SystemTable Online Yes sys def SYSTAB_0 0 IndexTrigger Online - NDB$INDEX_6_CUSTOM
Now, we’ll add an index to field col2:
mysql> CREATE INDEX i2 on table5(col2);
Using ndb_show_tables again we see the new index created and that “table5” and the associated PRIMARY key index remain unchanged:
id type state logging database schema name 5 UserTable Online Yes test def table5 3 UserTable Online Yes cluster def NDB$BLOB_2_3 1 IndexTrigger Online - NDB$INDEX_7_CUSTOM 6 OrderedIndex Online No sys def PRIMARY 1 SystemTable Online Yes sys def NDB$EVENTS_0 7 OrderedIndex Online No sys def i2 2 UserTable Online Yes cluster def schema 4 UserTable Online Yes cluster def apply_status 0 SystemTable Online Yes sys def SYSTAB_0 0 IndexTrigger Online - NDB$INDEX_6_CUSTOM
Dropping indexes is also done without copying the table and indexes.
mysql> ALTER TABLE table5 drop index i2;
ndb_show_tables shows that the table and indexes are unchanged:
id type state logging database schema name 5 UserTable Online Yes test def table5 3 UserTable Online Yes cluster def NDB$BLOB_2_3 6 OrderedIndex Online No sys def PRIMARY 1 SystemTable Online Yes sys def NDB$EVENTS_0 2 UserTable Online Yes cluster def schema 4 UserTable Online Yes cluster def apply_status 0 SystemTable Online Yes sys def SYSTAB_0 0 IndexTrigger Online - NDB$INDEX_6_CUSTOM
Conclusion
In this article we took an in-depth look at how the ALTER TABLE command is implemented in MySQL Cluster 5.0. MySQL's ALTER TABLE is a powerful command that allows you to modify all parts of a table definition, including the migration of table data to a new table format. The operation is based internally on copying the existing table to a new table with the new specified format. All the necessary data is migrated automatically with the operation being transparent to the user.
We also introduced some improvements made to the ALTER TABLE command in the coming 5.1 release. In 5.1, simple alterations such as renaming a field, are done without copying the table and corresponding indexes. MySQL Cluster (ndbcluster) also adds support for adding/dropping indexes without copying the table.
© 2010, Oracle Corporation and/or its affiliates