Rechercher dans le manuel MySQL

17.4.1.9 Replication with Differing Table Definitions on Master and Slave

Table des matières

Source and target tables for replication do not have to be identical. A table on the master can have more or fewer columns than the slave's copy of the table. In addition, corresponding table columns on the master and the slave can use different data types, subject to certain conditions.

Note

Replication between tables which are partitioned differently from one another is not supported. See Section 17.4.1.24, “Replication and Partitioning”.

In all cases where the source and target tables do not have identical definitions, the database and table names must be the same on both the master and the slave. Additional conditions are discussed, with examples, in the following two sections.

17.4.1.9.1 Replication with More Columns on Master or Slave

You can replicate a table from the master to the slave such that the master and slave copies of the table have differing numbers of columns, subject to the following conditions:

  • Columns common to both versions of the table must be defined in the same order on the master and the slave.

    (This is true even if both tables have the same number of columns.)

  • Columns common to both versions of the table must be defined before any additional columns.

    This means that executing an ALTER TABLE statement on the slave where a new column is inserted into the table within the range of columns common to both tables causes replication to fail, as shown in the following example:

    Suppose that a table t, existing on the master and the slave, is defined by the following CREATE TABLE statement:

    1. CREATE TABLE t (
    2.     c1 INT,
    3.     c2 INT,
    4.     c3 INT
    5. );

    Suppose that the ALTER TABLE statement shown here is executed on the slave:

    1. ALTER TABLE t ADD COLUMN cnew1 INT AFTER c3;

    The previous ALTER TABLE is permitted on the slave because the columns c1, c2, and c3 that are common to both versions of table t remain grouped together in both versions of the table, before any columns that differ.

    However, the following ALTER TABLE statement cannot be executed on the slave without causing replication to break:

    1. ALTER TABLE t ADD COLUMN cnew2 INT AFTER c2;

    Replication fails after execution on the slave of the ALTER TABLE statement just shown, because the new column cnew2 comes between columns common to both versions of t.

  • Each “extra” column in the version of the table having more columns must have a default value.

    A column's default value is determined by a number of factors, including its type, whether it is defined with a DEFAULT option, whether it is declared as NULL, and the server SQL mode in effect at the time of its creation; for more information, see Section 11.7, “Data Type Default Values”).

In addition, when the slave's copy of the table has more columns than the master's copy, each column common to the tables must use the same data type in both tables.

Examples.  The following examples illustrate some valid and invalid table definitions:

More columns on the master.  The following table definitions are valid and replicate correctly:

  1. master> CREATE TABLE t1 (c1 INT, c2 INT, c3 INT);
  2. slave>  CREATE TABLE t1 (c1 INT, c2 INT);

The following table definitions would raise an error because the definitions of the columns common to both versions of the table are in a different order on the slave than they are on the master:

  1. master> CREATE TABLE t1 (c1 INT, c2 INT, c3 INT);
  2. slave>  CREATE TABLE t1 (c2 INT, c1 INT);

The following table definitions would also raise an error because the definition of the extra column on the master appears before the definitions of the columns common to both versions of the table:

  1. master> CREATE TABLE t1 (c3 INT, c1 INT, c2 INT);
  2. slave>  CREATE TABLE t1 (c1 INT, c2 INT);

More columns on the slave.  The following table definitions are valid and replicate correctly:

  1. master> CREATE TABLE t1 (c1 INT, c2 INT);
  2. slave>  CREATE TABLE t1 (c1 INT, c2 INT, c3 INT);

The following definitions raise an error because the columns common to both versions of the table are not defined in the same order on both the master and the slave:

  1. master> CREATE TABLE t1 (c1 INT, c2 INT);
  2. slave>  CREATE TABLE t1 (c2 INT, c1 INT, c3 INT);

The following table definitions also raise an error because the definition for the extra column in the slave's version of the table appears before the definitions for the columns which are common to both versions of the table:

  1. master> CREATE TABLE t1 (c1 INT, c2 INT);
  2. slave>  CREATE TABLE t1 (c3 INT, c1 INT, c2 INT);

The following table definitions fail because the slave's version of the table has additional columns compared to the master's version, and the two versions of the table use different data types for the common column c2:

  1. master> CREATE TABLE t1 (c1 INT, c2 BIGINT);
  2. slave>  CREATE TABLE t1 (c1 INT, c2 INT, c3 INT);

Table des matières Haut

17.4.1.9.2 Replication of Columns Having Different Data Types

Corresponding columns on the master's and the slave's copies of the same table ideally should have the same data type. However, this is not always strictly enforced, as long as certain conditions are met.

It is usually possible to replicate from a column of a given data type to another column of the same type and same size or width, where applicable, or larger. For example, you can replicate from a CHAR(10) column to another CHAR(10), or from a CHAR(10) column to a CHAR(25) column without any problems. In certain cases, it also possible to replicate from a column having one data type (on the master) to a column having a different data type (on the slave); when the data type of the master's version of the column is promoted to a type that is the same size or larger on the slave, this is known as attribute promotion.

Attribute promotion can be used with both statement-based and row-based replication, and is not dependent on the storage engine used by either the master or the slave. However, the choice of logging format does have an effect on the type conversions that are permitted; the particulars are discussed later in this section.

Important

Whether you use statement-based or row-based replication, the slave's copy of the table cannot contain more columns than the master's copy if you wish to employ attribute promotion.

Statement-based replication.  When using statement-based replication, a simple rule of thumb to follow is, “If the statement run on the master would also execute successfully on the slave, it should also replicate successfully”. In other words, if the statement uses a value that is compatible with the type of a given column on the slave, the statement can be replicated. For example, you can insert any value that fits in a TINYINT column into a BIGINT column as well; it follows that, even if you change the type of a TINYINT column in the slave's copy of a table to BIGINT, any insert into that column on the master that succeeds should also succeed on the slave, since it is impossible to have a legal TINYINT value that is large enough to exceed a BIGINT column.

Row-based replication: attribute promotion and demotion.  Row-based replication supports attribute promotion and demotion between smaller data types and larger types. It is also possible to specify whether or not to permit lossy (truncated) or non-lossy conversions of demoted column values, as explained later in this section.

Lossy and non-lossy conversions.  In the event that the target type cannot represent the value being inserted, a decision must be made on how to handle the conversion. If we permit the conversion but truncate (or otherwise modify) the source value to achieve a “fit” in the target column, we make what is known as a lossy conversion. A conversion which does not require truncation or similar modifications to fit the source column value in the target column is a non-lossy conversion.

Type conversion modes (slave_type_conversions variable).  The setting of the slave_type_conversions global server variable controls the type conversion mode used on the slave. This variable takes a set of values from the following list, which describes the effects of each mode on the slave's type-conversion behavior:

ALL_LOSSY

In this mode, type conversions that would mean loss of information are permitted.

This does not imply that non-lossy conversions are permitted, merely that only cases requiring either lossy conversions or no conversion at all are permitted; for example, enabling only this mode permits an INT column to be converted to TINYINT (a lossy conversion), but not a TINYINT column to an INT column (non-lossy). Attempting the latter conversion in this case would cause replication to stop with an error on the slave.

ALL_NON_LOSSY

This mode permits conversions that do not require truncation or other special handling of the source value; that is, it permits conversions where the target type has a wider range than the source type.

Setting this mode has no bearing on whether lossy conversions are permitted; this is controlled with the ALL_LOSSY mode. If only ALL_NON_LOSSY is set, but not ALL_LOSSY, then attempting a conversion that would result in the loss of data (such as INT to TINYINT, or CHAR(25) to VARCHAR(20)) causes the slave to stop with an error.

ALL_LOSSY,ALL_NON_LOSSY

When this mode is set, all supported type conversions are permitted, whether or not they are lossy conversions.

ALL_SIGNED

Treat promoted integer types as signed values (the default behavior).

ALL_UNSIGNED

Treat promoted integer types as unsigned values.

ALL_SIGNED,ALL_UNSIGNED

Treat promoted integer types as signed if possible, otherwise as unsigned.

[empty]

When slave_type_conversions is not set, no attribute promotion or demotion is permitted; this means that all columns in the source and target tables must be of the same types.

This mode is the default.

When an integer type is promoted, its signedness is not preserved. By default, the slave treats all such values as signed. You can control this behavior using ALL_SIGNED, ALL_UNSIGNED, or both. ALL_SIGNED tells the slave to treat all promoted integer types as signed; ALL_UNSIGNED instructs it to treat these as unsigned. Specifying both causes the slave to treat the value as signed if possible, otherwise to treat it as unsigned; the order in which they are listed is not significant. Neither ALL_SIGNED nor ALL_UNSIGNED has any effect if at least one of ALL_LOSSY or ALL_NONLOSSY is not also used.

Changing the type conversion mode requires restarting the slave with the new slave_type_conversions setting.

Supported conversions.  Supported conversions between different but similar data types are shown in the following list:

  • Between any of the integer types TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT.

    This includes conversions between the signed and unsigned versions of these types.

    Lossy conversions are made by truncating the source value to the maximum (or minimum) permitted by the target column. For ensuring non-lossy conversions when going from unsigned to signed types, the target column must be large enough to accommodate the range of values in the source column. For example, you can demote TINYINT UNSIGNED non-lossily to SMALLINT, but not to TINYINT.

  • Between any of the decimal types DECIMAL, FLOAT, DOUBLE, and NUMERIC.

    FLOAT to DOUBLE is a non-lossy conversion; DOUBLE to FLOAT can only be handled lossily. A conversion from DECIMAL(M,D) to DECIMAL(M',D') where D' >= D and (M'-D') >= (M-D) is non-lossy; for any case where M' < M, D' < D, or both, only a lossy conversion can be made.

    For any of the decimal types, if a value to be stored cannot be fit in the target type, the value is rounded down according to the rounding rules defined for the server elsewhere in the documentation. See Section 12.25.4, “Rounding Behavior”, for information about how this is done for decimal types.

  • Between any of the string types CHAR, VARCHAR, and TEXT, including conversions between different widths.

    Conversion of a CHAR, VARCHAR, or TEXT to a CHAR, VARCHAR, or TEXT column the same size or larger is never lossy. Lossy conversion is handled by inserting only the first N characters of the string on the slave, where N is the width of the target column.

    Important

    Replication between columns using different character sets is not supported.

  • Between any of the binary data types BINARY, VARBINARY, and BLOB, including conversions between different widths.

    Conversion of a BINARY, VARBINARY, or BLOB to a BINARY, VARBINARY, or BLOB column the same size or larger is never lossy. Lossy conversion is handled by inserting only the first N bytes of the string on the slave, where N is the width of the target column.

  • Between any 2 BIT columns of any 2 sizes.

    When inserting a value from a BIT(M) column into a BIT(M') column, where M' > M, the most significant bits of the BIT(M') columns are cleared (set to zero) and the M bits of the BIT(M) value are set as the least significant bits of the BIT(M') column.

    When inserting a value from a source BIT(M) column into a target BIT(M') column, where M' < M, the maximum possible value for the BIT(M') column is assigned; in other words, an “all-set” value is assigned to the target column.

Conversions between types not in the previous list are not permitted.


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-replication-features-differing-tables.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