MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0
In MySQL NDB Cluster, the table comment in a CREATE
TABLE
or ALTER TABLE
statement can also be used to specify an
NDB_TABLE
option, which consists of one or
more name-value pairs, separated by commas if need be, following
the string NDB_TABLE=
. Complete syntax for
names and values syntax is shown here:
COMMENT="NDB_TABLE=ndb_table_option
[,ndb_table_option
[,...]]"ndb_table_option
: { NOLOGGING={1 | 0} | READ_BACKUP={1 | 0} | PARTITION_BALANCE={FOR_RP_BY_NODE | FOR_RA_BY_NODE | FOR_RP_BY_LDM | FOR_RA_BY_LDM | FOR_RA_BY_LDM_X_2 | FOR_RA_BY_LDM_X_3 | FOR_RA_BY_LDM_X_4} | FULLY_REPLICATED={1 | 0} }
Spaces are not permitted within the quoted string. The string is case-insensitive.
The four NDB
table options that can be set as
part of a comment in this way are described in more detail in
the next few paragraphs.
NOLOGGING
: Using 1 corresponds to having
ndb_table_no_logging
enabled,
but has no actual effect. Provided as a placeholder, mostly for
completeness of ALTER TABLE
statements.
READ_BACKUP
: Setting this option to 1 has the
same effect as though
ndb_read_backup
were enabled;
enables reading from any replica. Doing so greatly improves the
performance of reads from the table at a relatively small cost
to write performance. Beginning with NDB 8.0.19, 1 is the
default for READ_BACKUP
, and the default for
ndb_read_backup
is
ON
(previously, read from any replica was
disabled by default).
You can set READ_BACKUP
for an existing table
online, using an ALTER TABLE
statement
similar to one of those shown here:
ALTER TABLE ... ALGORITHM=INPLACE, COMMENT="NDB_TABLE=READ_BACKUP=1"; ALTER TABLE ... ALGORITHM=INPLACE, COMMENT="NDB_TABLE=READ_BACKUP=0";
For more information about the ALGORITHM
option for ALTER TABLE
, see
Section 23.5.11, “Online Operations with ALTER TABLE in NDB Cluster”.
PARTITION_BALANCE
: Provides additional
control over assignment and placement of partitions. The
following four schemes are supported:
FOR_RP_BY_NODE
: One partition per node.
Only one LDM on each node stores a primary partition. Each partition is stored in the same LDM (same ID) on all nodes.
FOR_RA_BY_NODE
: One partition per node
group.
Each node stores a single partition, which can be either a primary replica or a backup replica. Each partition is stored in the same LDM on all nodes.
FOR_RP_BY_LDM
: One partition for each LDM
on each node; the default.
This is the setting used if READ_BACKUP
is set to 1.
FOR_RA_BY_LDM
: One partition per LDM in
each node group.
These partitions can be primary or backup partitions.
FOR_RA_BY_LDM_X_2
: Two partitions per LDM
in each node group.
These partitions can be primary or backup partitions.
FOR_RA_BY_LDM_X_3
: Three partitions per
LDM in each node group.
These partitions can be primary or backup partitions.
FOR_RA_BY_LDM_X_4
: Four partitions per
LDM in each node group.
These partitions can be primary or backup partitions.
PARTITION_BALANCE
is the preferred interface
for setting the number of partitions per table. Using
MAX_ROWS
to force the number of partitions is
deprecated but continues to be supported for backward
compatibility; it is subject to removal in a future release of
MySQL NDB Cluster. (Bug #81759, Bug #23544301)
FULLY_REPLICATED
controls whether the table
is fully replicated, that is, whether each data node has a
complete copy of the table. To enable full replication of the
table, use FULLY_REPLICATED=1
.
This setting can also be controlled using the
ndb_fully_replicated
system variable. Setting
it to ON
enables the option by default for
all new NDB
tables; the default is
OFF
. The
ndb_data_node_neighbour
system
variable is also used for fully replicated tables, to ensure
that when a fully replicated table is accessed, we access the
data node which is local to this MySQL Server.
An example of a CREATE TABLE
statement using
such a comment when creating an NDB
table is
shown here:
mysql>CREATE TABLE t1 (
>c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
>c2 VARCHAR(100),
>c3 VARCHAR(100) )
>ENGINE=NDB
>COMMENT="NDB_TABLE=READ_BACKUP=0,PARTITION_BALANCE=FOR_RP_BY_NODE";
The comment is displayed as part of the ouput of
SHOW CREATE TABLE
. The text of
the comment is also available from querying the MySQL
Information Schema TABLES
table, as
in this example:
mysql>SELECT TABLE_NAME, TABLE_SCHEMA, TABLE_COMMENT
>FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME="t1"\G
*************************** 1. row *************************** TABLE_NAME: t1 TABLE_SCHEMA: test TABLE_COMMENT: NDB_TABLE=READ_BACKUP=0,PARTITION_BALANCE=FOR_RP_BY_NODE 1 row in set (0.01 sec)
This comment syntax is also supported with
ALTER TABLE
statements for
NDB
tables, as shown here:
mysql> ALTER TABLE t1 COMMENT="NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_NODE";
Query OK, 0 rows affected (0.40 sec)
Records: 0 Duplicates: 0 Warnings: 0
Beginning with NDB 8.0.21, the TABLE_COMMENT
column displays the comment that is required to re-create the
table as it is following the ALTER TABLE
statement, like this:
mysql>SELECT TABLE_NAME, TABLE_SCHEMA, TABLE_COMMENT
->FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME="t1"\G
*************************** 1. row *************************** TABLE_NAME: t1 TABLE_SCHEMA: test TABLE_COMMENT: NDB_TABLE=READ_BACKUP=0,PARTITION_BALANCE=FOR_RP_BY_NODE 1 row in set (0.01 sec)
mysql>SELECT TABLE_NAME, TABLE_SCHEMA, TABLE_COMMENT
>FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME="t1";
+------------+--------------+--------------------------------------------------+ | TABLE_NAME | TABLE_SCHEMA | TABLE_COMMENT | +------------+--------------+--------------------------------------------------+ | t1 | c | NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_NODE | | t1 | d | | +------------+--------------+--------------------------------------------------+ 2 rows in set (0.01 sec)
Keep in mind that a table comment used with ALTER
TABLE
replaces any existing comment which the table
might have.
mysql>ALTER TABLE t1 COMMENT="NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_NODE";
Query OK, 0 rows affected (0.40 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql>SELECT TABLE_NAME, TABLE_SCHEMA, TABLE_COMMENT
>FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME="t1";
+------------+--------------+--------------------------------------------------+ | TABLE_NAME | TABLE_SCHEMA | TABLE_COMMENT | +------------+--------------+--------------------------------------------------+ | t1 | c | NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_NODE | | t1 | d | | +------------+--------------+--------------------------------------------------+ 2 rows in set (0.01 sec)
Prior to NDB 8.0.21, the table comment used with ALTER
TABLE
replaced any existing comment which the table
might have had. This meant that (for example) the
READ_BACKUP
value was not carried over to the
new comment set by the ALTER TABLE
statement,
and that any unspecified values reverted to their defaults.
(BUG#30428829) There was thus no longer any way using SQL to
retrieve the value previously set for the comment. To keep
comment values from reverting to their defaults, it was necessry
to preserve any such values from the existing comment string and
include them in the comment passed to ALTER
TABLE
.
You can also see the value of the
PARTITION_BALANCE
option in the output of
ndb_desc. ndb_desc also
shows whether the READ_BACKUP
and
FULLY_REPLICATED
options are set for the
table. See the description of this program for more information.