Replication When the Master and Slave Have Different Table Definitions
This article is currently incomplete.
Contents
Tables on the slave and the master 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.
Different Data Definitions - Attribute Promotion and Demotion
It is possible in some cases to replicate to a slave that has a column of a different type on the slave and the master. This process is called attribute promotion (to a larger type) or attribute demotion (to a smaller type).
The conditions differ depending on whether statement-based or row-based replication is used.
Statement-Based Replication
When using statement-based replication, generally, if a statement can run successfully on the slave, it will be replicated. If a column definition is the same or a larger type on the slave than on the master, it can replicate successfully. For example a column defined as VARCHAR(10)
will successfully be replicated on a slave with a definition of VARCHAR(12)
.
Replicating to a slave where the column is defined as smaller than on the master can also work. For example, given the following table definitions:
Master:
DESC r; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | tinyint(4) | YES | | NULL | | | v | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+
Slave
DESC r; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | tinyint(4) | YES | | NULL | | | v | varchar(8) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+
the statement
INSERT INTO r VALUES (6,'hi');
would successfully replicate because the value inserted into the v
field can successfully be inserted on both the master and the smaller slave equivalent.
However, the following statement would fail:
INSERT INTO r VALUES (7,'abcdefghi')
In this case, the value fits in the master definition, but is too long for the slave field, and so replication will fail.
SHOW SLAVE STATUS\G *************************** 1. row *************************** ... 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')' ...
Row-Based Replication
When using row-based replication, the value of the slave_type_conversions variable is important. The default value of this variable is empty, in which case MariaDB will not perform attribute