Keine Cache-Version


Caching deaktiviert Standardeinstellung für diese Seite:aktiviert (code DEF204)
Wenn die Anzeige zu langsam ist, können Sie den Benutzermodus deaktivieren, um die zwischengespeicherte Version anzuzeigen.

Rechercher dans le manuel MySQL

13.1.20.11 Setting NDB_TABLE Options

Inhaltsverzeichnis

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)

Suchen Sie im MySQL-Handbuch

Deutsche Übersetzung

Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.

Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.

Vielen Dank im Voraus.

Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-create-table-ndb-table-comment-options.html

Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.

Referenzen

  1. Zeigen Sie - html-Dokument Sprache des Dokuments:en Manuel MySQL : https://dev.mysql.com/

Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.

Inhaltsverzeichnis Haut