Source and target tables for replication do not have to be identical. A table on the source can have more or fewer columns than the replica's copy of the table. In addition, corresponding table columns on the source and the replica can use different data types, subject to certain conditions.
Replication between tables which are partitioned differently from one another is not supported. See Section 19.5.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 source and the replica. Additional conditions are discussed, with examples, in the following two sections.
You can replicate a table from the source to the replica such that the source and replica 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 source and the replica. (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 replica 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 source and the replica, is defined by the followingCREATE TABLE
statement:CREATE TABLE t ( c1 INT, c2 INT, c3 INT );
Suppose that the
ALTER TABLE
statement shown here is executed on the replica:ALTER TABLE t ADD COLUMN cnew1 INT AFTER c3;
The previous
ALTER TABLE
is permitted on the replica because the columnsc1
,c2
, andc3
that are common to both versions of tablet
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 replica without causing replication to break:ALTER TABLE t ADD COLUMN cnew2 INT AFTER c2;
Replication fails after execution on the replica of the
ALTER TABLE
statement just shown, because the new columncnew2
comes between columns common to both versions oft
.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 asNULL
, and the server SQL mode in effect at the time of its creation; for more information, see Section 13.6, “Data Type Default Values”).
In addition, when the replica's copy of the table has more columns than the source'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 source. The following table definitions are valid and replicate correctly:
source> CREATE TABLE t1 (c1 INT, c2 INT, c3 INT);
replica> 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 replica than they are on the source:
source> CREATE TABLE t1 (c1 INT, c2 INT, c3 INT);
replica> 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 source appears before the definitions of the columns common to both versions of the table:
source> CREATE TABLE t1 (c3 INT, c1 INT, c2 INT);
replica> CREATE TABLE t1 (c1 INT, c2 INT);
More columns on the replica. The following table definitions are valid and replicate correctly:
source> CREATE TABLE t1 (c1 INT, c2 INT);
replica> 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 source and the replica:
source> CREATE TABLE t1 (c1 INT, c2 INT);
replica> 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 replica's version of the table appears before the definitions for the columns which are common to both versions of the table:
source> CREATE TABLE t1 (c1 INT, c2 INT);
replica> CREATE TABLE t1 (c3 INT, c1 INT, c2 INT);
The following table definitions fail because the replica's
version of the table has additional columns compared to the
source's version, and the two versions of the table use
different data types for the common column
c2
:
source> CREATE TABLE t1 (c1 INT, c2 BIGINT);
replica> CREATE TABLE t1 (c1 INT, c2 INT, c3 INT);
Corresponding columns on the source's and the replica'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 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.
Statement-based replication.
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
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 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
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.
The global value of the system variable
replica_type_conversions
(from MySQL
8.0.26) or slave_type_conversions
(before
MySQL 8.0.26) controls the type conversion mode used on the
replica. This variable takes a set of values from the
following list, which describes the effects of each mode on
the replica'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 toTINYINT
(a lossy conversion), but not aTINYINT
column to anINT
column (non-lossy). Attempting the latter conversion in this case would cause replication to stop with an error on the replica.- 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 onlyALL_NON_LOSSY
is set, but notALL_LOSSY
, then attempting a conversion that would result in the loss of data (such asINT
toTINYINT
, orCHAR(25)
toVARCHAR(20)
) causes the replica 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
replica_type_conversions
orslave_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 replica treats all such values as
signed. You can control this behavior using
ALL_SIGNED
,
ALL_UNSIGNED
, or both.
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
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
replica with the new
replica_type_conversions
or
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
, andBIGINT
.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 toSMALLINT
, but not toTINYINT
.Between any of the decimal types
DECIMAL
,FLOAT
,DOUBLE
, andNUMERIC
.FLOAT
toDOUBLE
is a non-lossy conversion;DOUBLE
toFLOAT
can only be handled lossily. A conversion fromDECIMAL(
toM
,D
)DECIMAL(
whereM'
,D'
)
andD'
>=D
(
) is non-lossy; for any case whereM'
-D'
) >= (M
-D
,M'
<M
, or both, only a lossy conversion can be made.D'
<D
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 14.24.4, “Rounding Behavior”, for information about how this is done for decimal types.
Between any of the string types
CHAR
,VARCHAR
, andTEXT
, including conversions between different widths.Conversion of a
CHAR
,VARCHAR
, orTEXT
to aCHAR
,VARCHAR
, orTEXT
column the same size or larger is never lossy. Lossy conversion is handled by inserting only the firstN
characters of the string on the replica, whereN
is the width of the target column.ImportantReplication between columns using different character sets is not supported.
Between any of the binary data types
BINARY
,VARBINARY
, andBLOB
, including conversions between different widths.Conversion of a
BINARY
,VARBINARY
, orBLOB
to aBINARY
,VARBINARY
, orBLOB
column the same size or larger is never lossy. Lossy conversion is handled by inserting only the firstN
bytes of the string on the replica, whereN
is the width of the target column.Between any 2
BIT
columns of any 2 sizes.When inserting a value from a
BIT(
column into aM
)BIT(
column, whereM'
)
, the most significant bits of theM'
>M
BIT(
columns are cleared (set to zero) and theM'
)M
bits of theBIT(
value are set as the least significant bits of theM
)BIT(
column.M'
)When inserting a value from a source
BIT(
column into a targetM
)BIT(
column, whereM'
)
, the maximum possible value for theM'
<M
BIT(
column is assigned; in other words, an “all-set” value is assigned to the target column.M'
)
Conversions between types not in the previous list are not permitted.