Corresponding columns on the source's and the replica'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 source) to a column having a
different data type (on the replica); when the data type of
the source's version of the column is promoted to a type
that is the same size or larger on the replica, 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 source or the replica. 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.
Whether you use statement-based or row-based replication, the replica's copy of the table cannot contain more columns than the source's copy if you wish to employ attribute promotion.
When using statement-based replication, a simple rule of
thumb to follow is, “If the statement run on the
source would also execute successfully on the replica, 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 replica, the statement can be
replicated. For example, you can insert any value that fits
TINYINT column into a
BIGINT column as well; it follows that,
even if you change the type of a
column in the replica's copy of a table to
BIGINT, any insert into that column on
the source that succeeds should also succeed on the replica,
since it is impossible to have a legal
TINYINT value that is large enough to
Prior to MySQL 5.6.10, when using statement-based replication,
AUTO_INCREMENT columns were required to be
the same on both the source and the replica; otherwise,
updates could be applied to the wrong table on the replica.
Row-based replication: attribute promotion and demotion. Row-based replication in MySQL 5.6 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
global server variable controls the type conversion mode
used on the replica. This variable takes a set of values
from the following table, which shows the effects of each
mode on the replica's type-conversion behavior:
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
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
When this mode is set, all supported type conversions are permitted, whether or not they are lossy conversions.
Treat promoted integer types as signed values (the default behavior).
Treat promoted integer types as unsigned values.
Treat promoted integer types as signed if possible, otherwise as unsigned.
This mode is the default.
When an integer type is promoted, its signedness is not
preserved. By default, the replica treats all such values as
signed. Beginning with MySQL 5.6.13, you can control this
ALL_UNSIGNED, or both. (Bug#15831300)
ALL_SIGNED tells the replica to treat all
promoted integer types as signed;
ALL_UNSIGNED instructs it to treat these as
unsigned. Specifying both causes the replica to treat the
value as signed if possible, otherwise to treat it as
unsigned; the order in which they are listed is not
ALL_UNSIGNED has any effect if at least one
ALL_NONLOSSY is not also used.
Changing the type conversion mode requires restarting the
replica with the new
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 UNSIGNEDnon-lossily to
SMALLINT, but not to
DOUBLEis a non-lossy conversion;
FLOATcan only be handled lossily. A conversion from
() is non-lossy; for any case where
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.
Conversion of a
TEXTcolumn the same size or larger is never lossy. Lossy conversion is handled by inserting only the first
Ncharacters of the string on the replica, where
Nis the width of the target column.Important
Replication between columns using different character sets is not supported.
Conversion of a
BLOBcolumn the same size or larger is never lossy. Lossy conversion is handled by inserting only the first
Nbytes of the string on the replica, where
Nis the width of the target column.
Between any 2
BITcolumns of any 2 sizes.
When inserting a value from a
BIT(column into a
, the most significant bits of the
BIT(columns are cleared (set to zero) and the
Mbits of the
BIT(value are set as the least significant bits of the
When inserting a value from a source
BIT(column into a target
, the maximum possible value for the
BIT(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.