Skip navigation links
MySQL 5.1 Reference Manual :: 16 Replication :: 16.3 Replication Notes and Tips :: 16.3.1 Replication Features and Issues :: 16.3.1.5 Replication with Differing Tables on Master and Slave
16.3.1.6 Replication and DIRECTORY Table Options »
Section Navigation      [Toggle]

16.3.1.5. Replication with Differing Tables on Master and Slave

[+/-]

16.3.1.5.1. Replication with More Columns on Master or Slave
16.3.1.5.2. Replication of Columns Having Different Data Types

Starting with MySQL 5.1.21, 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 — subject to certain conditions — corresponding table columns on the master and the slave can use different data types.

In all cases where the source and target tables do not have identical definitions, the following must be true in order for replication to work:

  • You must be using row-based replication. (Using MIXED for the binary logging format does not work.)

  • The database and table names must be the same on both the master and the slave.

Additional conditions are discussed (and examples provided) in the following two sections.

16.3.1.5.1. Replication with More Columns on Master or Slave

Starting with MySQL 5.1.21, you can replicate a table from the master to the slave such that the master's copy of the table and the slave's copy of the table do not have the same number of columns, subject to the following conditions:

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

    Note

    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; see Section 10.1.4, “Data Type Default Values”), for more information.

  • Matching columns must be defined in the same order on both the master and the slave.

  • Any additional columns must be defined following the matching columns.

In addition, when the slave's copy of the table has more columns than the master's copy, then each matching column must use the same data type.

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

  • More columns on the master.  The following table definitions are valid: 

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

    The following table definitions would raise Error 1532 (ER_BINLOG_ROW_RBR_TO_SBR) 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: 

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

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

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

  • More columns on the slave.  The following definitions replicate correctly: 

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

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

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

    The following table definitions also raise Error 1532 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: 

    master> CREATE TABLE t1 (c1 INT, c2 INT);
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 define column c2 as a different data type. 

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

16.3.1.5.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, beginning with MySQL 5.1.21, this is not always strictly enforced, as long as certain conditions are met.

All other things being equal, it is always 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); this is sometimes known as attribute promotion, because 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.

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 allowed; 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.  For row-based replication, the case is not so simple, due to the fact that changes rather than statements are replicated, and these changes are transmitted from master to slave using formats that do not always map directly to MySQL server column datatypes. For example, when using the row-based format, you cannot replicate between different INT subtypes, such as from TINYINT to BIGINT, because changes to columns of these type are represented differently from one another in the binary log when using row-based logging. However, you can replicate from BLOB to TEXT using row-based replication because changes to BLOB and TEXT columns are represented using the same format in the binary log.

Supported conversions for attribute promotion when using row-based replication are shown in the following table:

From (Master) To (Slave)
BINARY CHAR
BLOB TEXT
CHAR BINARY
DECIMAL NUMERIC
NUMERIC DECIMAL
TEXT BLOB
VARBINARY VARCHAR
VARCHAR VARBINARY

Note

In all cases, the size or width of the column on the slave must be equal to or greater than that of the column on the master. For example, you can replicate from a CHAR(10) column on the master to a column that uses BINARY(10) or BINARY(25) on the slave, but you cannot replicate from a CHAR(10) column on the master to BINARY(5) column on the slave.

For DECIMAL and NUMERIC columns, both the mantissa (M) and the number of decimals (D) must be the same size or larger on the slave as compared with the master. For example, replication from a NUMERIC(5,4) to a DECIMAL(6,4) works, but not from a NUMERIC(5,4) to a DECIMAL(5,3).

We do not support attribute promotion of any of the following data types to or from any other data type when using row-based replication:

  • INT (including TINYINT, SMALLINT, MEDIUMINT, BIGINT).

    Promotion between INT subtypes — for example, from SMALLINT to BIGINT — is also not supported.

  • SET or ENUM.

  • FLOAT or DOUBLE.

  • All of the data types relating to dates, times, or both: DATE, TIME, DATETIME, TIMESTAMP, and YEAR.