4.1.10.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.

Prior to MySQL 5.7.1, when using statement-based replication,
AUTO_INCREMENT columns were required to be
the same on both the master and the slave; otherwise, updates
could be applied to the wrong table on the slave. (Bug
#12669186)

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 table, which shows the effects of each mode on
the slave's type-conversion behavior:

Mode

Effect

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. Beginning with MySQL 5.7.2, you can control this
behavior using ALL_SIGNED,
ALL_UNSIGNED, or both. (Bug#15831300)
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:

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.

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
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.

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.