Replication When the Master and Slave Have Different Table Definitions

You are viewing an old version of this article. View the current version here.

This article is currently incomplete.

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

In most cases, it is possible to replicate to a slave that has a column of the same or a larger type than the master, for example a column defined as VARCHAR(10) will successfully be replicated on a slave with a definition of VARCHAR(12).

Statement-Based Replication

When using statement-based replication, generally, if a statement can run successfully on the slave, it will be replicated. 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')'
...

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.