Understand the rules and limitations when replicating between tables with differing structures. Learn how attribute promotion and column handling work in row-based replication.
While replication is usually meant to take place between primaries and replicas with the same table definitions and this is recommended, in certain cases replication can still take place even if the definitions are identical.
Tables on the replica and the primary do not need to have the same definition in order for replication to take place. There can be differing numbers of columns, or differing data definitions and, in certain cases, replication can still proceed.
It is possible in some cases to replicate to a replica that has a column of a different type on the replica and the primary. This process is called attribute promotion (to a larger type) or attribute demotion (to a smaller type).
The conditions differ depending on whether or is used.
When using , generally, if a statement can run successfully on the replica, it will be replicated. If a column definition is the same or a larger type on the replica than on the primary, it can replicate successfully. For example, a column defined as will successfully be replicated on a replica with a definition of VARCHAR(12).
Replicating to a replica where the column is defined as smaller than on the primary can also work. For example, given the following table definitions:
Master:
Slave
the statement
would successfully replicate because the value inserted into the v field can successfully be inserted on both the primary and the smaller replica equivalent.
However, the following statement would fail:
In this case, the value fits in the primary definition, but is too long for the replica field, and so replication will fail.
When using , the value of the variable is important. The default value of this variable is empty, in which case MariaDB will not perform attribute promotion or demotion. If the column definitions do not match, replication will stop. If set to ALL_NON_LOSSY, safe replication is permitted. If set to ALL_LOSSY as well, replication will be permitted even if data loss takes place.
For example:
Master:
Slave:
The following query will fail:
By changing the value of the , replication can proceed:
Between , , , and . If lossy conversion is supported, the value from the primary will be converted to the maximum or minimum permitted on the replica, which non-lossy conversions require the replica column to be large enough. For example, SMALLINT UNSIGNED can be converted to MEDIUMINT, but not SMALLINT SIGNED.
Replication can also take place when the primary and replica have a different number of columns if the following criteria are met:
columns must be in the same order on the primary and replica
common columns must be defined with the same data type
extra columns must be defined after the common columns
The following example replicates incorrectly (replication proceeds, but the data is corrupted), as the columns are not in the same order.
Master:
Slave:
Master:
Slave:
Using statement-based replication, the same example may work, even though the columns are not in the same order.
Master:
Slave:
Master:
Slave:
This page is licensed: CC BY-SA / Gnu FDL
DESC r;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | tinyint(4) | YES | | NULL | |
| v | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+DESC r;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | tinyint(4) | YES | | NULL | |
| v | varchar(8) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+INSERT INTO r VALUES (6,'hi');INSERT INTO r VALUES (7,'abcdefghi')SHOW SLAVE STATUS\G
*************************** 1. row ***************************
...
Slave_IO_Running: Yes
Slave_SQL_Running: No
...
Last_Errno: 1406
Last_Error: Error 'Data too long for column 'v' at row 1' on query.
Default database: 'test'. Query: 'INSERT INTO r VALUES (7,'abcdefghi')'
...DESC r;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | smallint(6) | YES | | NULL | |
| v | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+SHOW VARIABLES LIKE 'slave_ty%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| slave_type_conversions | |
+------------------------+-------+
DESC r;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | tinyint(4) | YES | | NULL | |
| v | varchar(1) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+INSERT INTO r VALUES (3,'c');SHOW SLAVE STATUS\G;
...
Slave_IO_Running: Yes
Slave_SQL_Running: No
...
Last_Errno: 1677
Last_Error: Column 0 of table 'test.r' cannot be converted from
type 'smallint' to type 'tinyint(4)'
...SET GLOBAL slave_type_conversions='ALL_NON_LOSSY,ALL_LOSSY';
START SLAVE;SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...CREATE OR REPLACE TABLE r (i1 INT, i2 INT);ALTER TABLE r ADD i3 INT AFTER i1;INSERT INTO r (i1,i2) VALUES (1,1);
SELECT * FROM r;
+------+------+
| i1 | i2 |
+------+------+
| 1 | 1 |
+------+------+SELECT * FROM r;
+------+------+------+
| i1 | i3 | i2 |
+------+------+------+
| 1 | 1 | NULL |
+------+------+------+CREATE OR REPLACE TABLE r (i1 INT, i2 INT);ALTER TABLE r ADD i3 INT AFTER i1;INSERT INTO r (i1,i2) VALUES (1,1);
SELECT * FROM r;
+------+------+
| i1 | i2 |
+------+------+
| 1 | 1 |
+------+------+SELECT * FROM r;
+------+------+------+
| i1 | i3 | i2 |
+------+------+------+
| 1 | NULL | 1 |
+------+------+------+