17.4.1.9 Replication with Differing Table Definitions on Master and 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:

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:

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:

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:

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

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:

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:

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:

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.

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:

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:

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