Rechercher dans le manuel MySQL

13.1.20.11 Setting NDB_TABLE Options

Table des matières

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)

Rechercher dans le manuel MySQL

Traduction non disponible

Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.

Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-create-table-ndb-table-comment-options.html

L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.

Références

  1. Consulter le document html Langue du document :en Manuel MySQL : https://dev.mysql.com/

Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.

Table des matières Haut