No cache version.

Caching disabled. Default setting for this page:enabled (code DEF204)
If the display is too slow, you can disable the user mode to view the cached version.

Rechercher dans le manuel MySQL

13.1.20.11 Setting NDB_TABLE Options

Contents

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:

  1. COMMENT="NDB_TABLE=ndb_table_option[,ndb_table_option[,...]]"
  2.  
  3. ndb_table_option:
  4.     NOLOGGING={1|0}
  5.   | READ_BACKUP={1|0}
  6.   | PARTITION_BALANCE={FOR_RP_BY_NODE|FOR_RA_BY_NODE|FOR_RP_BY_LDM
  7.                       |FOR_RA_BY_LDM|FOR_RA_BY_LDM_X_2
  8.                       |FOR_RA_BY_LDM_X_3|FOR_RA_BY_LDM_X_4}
  9.   | 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. You can set READ_BACKUP for an existing table online, using an ALTER TABLE statement similar to one of those shown here:

  1. ALTER TABLE ... ALGORITHM=INPLACE, COMMENT="NDB_TABLE=READ_BACKUP=1";
  2.  
  3. ALTER TABLE ... ALGORITHM=INPLACE, COMMENT="NDB_TABLE=READ_BACKUP=0";

For more information about the ALGORITHM option for ALTER TABLE, see Section 22.5.14, “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:

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

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

  3. FOR_RP_BY_LDM: One partition for each LDM on each node; the default.

    This is the same behavior as prior to MySQL NDB Cluster 7.5.2, except for a slightly different mapping of partitions to LDMs, starting with LDM 0 and placing one partition per node group, then moving on to the next LDM.

    This is the setting used if READ_BACKUP is set to 1.

  4. FOR_RA_BY_LDM: One partition per LDM in each node group.

    These partitions can be primary or backup partitions.

  5. FOR_RA_BY_LDM_X_2: Two partitions per LDM in each node group.

    These partitions can be primary or backup partitions.

  6. FOR_RA_BY_LDM_X_3: Three partitions per LDM in each node group.

    These partitions can be primary or backup partitions.

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

  1. mysql> CREATE TABLE t1 (
  2.      >     c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  3.      >     c2 VARCHAR(100),
  4.      >     c3 VARCHAR(100) )
  5.      > ENGINE=NDB
  6.      >
  7. 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:

  1. mysql> SELECT TABLE_NAME, TABLE_SCHEMA, TABLE_COMMENT
  2.      > FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME="t1";
  3. +------------+--------------+----------------------------------------------------------+
  4. | TABLE_NAME | TABLE_SCHEMA | TABLE_COMMENT                                            |
  5. +------------+--------------+----------------------------------------------------------+
  6. | t1         | c            | NDB_TABLE=READ_BACKUP=0,PARTITION_BALANCE=FOR_RP_BY_NODE |
  7. | t1         | d            |                                                          |
  8. +------------+--------------+----------------------------------------------------------+
  9. 2 rows in set (0.00 sec)

This comment syntax is also supported with ALTER TABLE statements for NDB tables. Keep in mind that a table comment used with ALTER TABLE replaces any existing comment which the table might have.

  1. mysql> ALTER TABLE t1 COMMENT="NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_NODE";
  2. Query OK, 0 rows affected (0.40 sec)
  3. Records: 0  Duplicates: 0  Warnings: 0
  4.  
  5. mysql> SELECT TABLE_NAME, TABLE_SCHEMA, TABLE_COMMENT
  6.      > FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME="t1";
  7. +------------+--------------+--------------------------------------------------+
  8. | TABLE_NAME | TABLE_SCHEMA | TABLE_COMMENT                                    |
  9. +------------+--------------+--------------------------------------------------+
  10. | t1         | c            | NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_NODE       |
  11. | t1         | d            |                                                  |
  12. +------------+--------------+--------------------------------------------------+
  13. 2 rows in set (0.01 sec)

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.

Because the READ_BACKUP value was not carried over to the new comment set by the ALTER TABLE statement, there is no longer a way using SQL to retrieve the value previously set for it. To keep this from happening, it is suggested that you preserve any such values from the existing comment string, like this:

  1. mysql> SELECT TABLE_NAME, TABLE_SCHEMA, TABLE_COMMENT
  2.      > FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME="t1";
  3. +------------+--------------+----------------------------------------------------------+
  4. | TABLE_NAME | TABLE_SCHEMA | TABLE_COMMENT                                            |
  5. +------------+--------------+----------------------------------------------------------+
  6. | t1         | c            | NDB_TABLE=READ_BACKUP=0,PARTITION_BALANCE=FOR_RP_BY_NODE |
  7. | t1         | d            |                                                          |
  8. +------------+--------------+----------------------------------------------------------+
  9. 2 rows in set (0.00 sec)
  10.  
  11. mysql> ALTER TABLE t1 COMMENT="NDB_TABLE=READ_BACKUP=0,PARTITION_BALANCE=FOR_RA_BY_NODE";
  12. Query OK, 0 rows affected (1.56 sec)
  13. Records: 0  Duplicates: 0  Warnings: 0
  14.  
  15. mysql> SELECT TABLE_NAME, TABLE_SCHEMA, TABLE_COMMENT
  16.      > FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME="t1";
  17. +------------+--------------+----------------------------------------------------------------+
  18. | TABLE_NAME | TABLE_SCHEMA | TABLE_COMMENT                                                  |
  19. +------------+--------------+----------------------------------------------------------------+
  20. | t1         | c            | NDB_TABLE=READ_BACKUP=0,PARTITION_BALANCE=FOR_RA_BY_NODE       |
  21. | t1         | d            |                                                                |
  22. +------------+--------------+----------------------------------------------------------------+
  23. 2 rows in set (0.01 sec)

Find a PHP function
Error Infobrol

Can not display this page of the Infobrol website

Type of error (18-01)

Unknown format specifier "&"

Please try again in a few minutes…

Return to the home page




Steph