# Replication When the Primary and Replica Have Different Table Definitions {% hint style="info" %} The terms *master* and *slave* have historically been used in replication, and MariaDB has begun the process of adding *primary* and *replica* synonyms. The old terms will continue to be used to maintain backward compatibility - see [MDEV-18777](https://jira.mariadb.org/browse/MDEV-18777) to follow progress on this effort. {% endhint %} 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](https://mariadb.com/docs/server/server-usage/storage-engines/myrocks/myrocks-and-replication) to take place. There can be differing numbers of columns, or differing data definitions and, in certain cases, replication can still proceed. ## Different Column Definitions - Attribute Promotion and Demotion 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 [statement-based](https://mariadb.com/docs/server/server-management/server-monitoring-logs/binary-log/binary-log-formats#statement-based) or [row-based replication](https://mariadb.com/docs/server/server-management/server-monitoring-logs/binary-log/binary-log-formats#row-based) is used. ### Statement-Based Replication When using [statement-based replication](https://mariadb.com/docs/server/server-management/server-monitoring-logs/binary-log/binary-log-formats#statement-based), 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 [VARCHAR(10)](https://mariadb.com/docs/server/reference/data-types/string-data-types/varchar) 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: ```sql DESC r; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | tinyint(4) | YES | | NULL | | | v | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ ``` Slave ```sql DESC r; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | tinyint(4) | YES | | NULL | | | v | varchar(8) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ ``` the statement ```sql INSERT INTO r VALUES (6,'hi'); ``` 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: ```sql INSERT INTO r VALUES (7,'abcdefghi') ``` In this case, the value fits in the primary definition, but is too long for the replica field, and so replication will fail. ```sql 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')' ... ``` ### Row-Based Replication When using [row-based replication](https://mariadb.com/docs/server/server-management/server-monitoring-logs/binary-log/binary-log-formats#row-based), the value of the [slave\_type\_conversions](https://mariadb.com/docs/server/ha-and-performance/standard-replication/replication-and-binary-log-system-variables) 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: ```sql DESC r; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | smallint(6) | YES | | NULL | | | v | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ ``` Slave: ```sql 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 | | +-------+------------+------+-----+---------+-------+ ``` The following query fails: ```sql INSERT INTO r VALUES (3,'c'); ``` ```sql 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)' ... ``` By changing the value of the [slave\_type\_conversions](https://mariadb.com/docs/server/ha-and-performance/standard-replication/replication-and-binary-log-system-variables), replication can proceed: ```sql SET GLOBAL slave_type_conversions='ALL_NON_LOSSY,ALL_LOSSY'; START SLAVE; ``` ```sql SHOW SLAVE STATUS\G; *************************** 1. row *************************** ... Slave_IO_Running: Yes Slave_SQL_Running: Yes ... ``` #### Supported Conversions * Between [TINYINT](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/tinyint), [SMALLINT](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/smallint), [MEDIUMINT](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/mediumint), [INT](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/int) and [BIGINT](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/bigint). 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`. ## Different Number or Order of Columns Replication can also take place when the primary and replica have a different number of columns if the following criteria are met: * Columns are in the same order on the primary and replica. * Common columns are defined with the same data type. * Extra columns are defined after the common columns. * The primary is configured with `binlog_row_metadata=FULL` and the replica runs on MariaDB 12.3 or higher (note option `slave_type_conversions=ERROR_IF_MISSING_FIELD`). ### Row-Based The following example replicates incorrectly (replication proceeds, but the data is corrupted), as the columns are not in the same order. Master: ```sql CREATE OR REPLACE TABLE r (i1 INT, i2 INT); ``` Slave: ```sql ALTER TABLE r ADD i3 INT AFTER i1; ``` Master: ```sql INSERT INTO r (i1,i2) VALUES (1,1); SELECT * FROM r; +------+------+ | i1 | i2 | +------+------+ | 1 | 1 | +------+------+ ``` Slave: ```sql SELECT * FROM r; +------+------+------+ | i1 | i3 | i2 | +------+------+------+ | 1 | 1 | NULL | +------+------+------+ ``` ### Statement-Based Using statement-based replication, the same example may work, even though the columns are not in the same order. Master: ```sql CREATE OR REPLACE TABLE r (i1 INT, i2 INT); ``` Slave: ```sql ALTER TABLE r ADD i3 INT AFTER i1; ``` Master: ```sql INSERT INTO r (i1,i2) VALUES (1,1); SELECT * FROM r; +------+------+ | i1 | i2 | +------+------+ | 1 | 1 | +------+------+ ``` Slave: ```sql SELECT * FROM r; +------+------+------+ | i1 | i3 | i2 | +------+------+------+ | 1 | NULL | 1 | +------+------+------+ ``` ## ALTER TABLE Issues ### Row-Based Replication In row-based multi-master replication, [`ALTER TABLE`](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table) statements can make replication fail. `ALTER TABLE` works fine when columns are added to the end of the table definition. When columns are deleted or added in the middle of a table, though, this causes subsequent DML[^1] queries to fail. {% hint style="info" %} The following functionality is available from MariaDB 12.3. {% endhint %} The solution to this problem is to **start master servers with** [**`--binlog_row_metadata`**](https://mariadb.com/docs/server/ha-and-performance/replication-and-binary-log-system-variables#binlog_row_metadata)**`=full`**. With that setting, the master writes column names to the [binary log](https://mariadb.com/docs/server/server-management/server-monitoring-logs/binary-log). The replicas receiving binlog events can then look up the columns using column names. The solution to the other problem, deleting columns in the middle of a table, is addressed by **configuring the replica with** [**`--slave_type_conversions`**](https://mariadb.com/docs/server/ha-and-performance/replication-and-binary-log-system-variables#slave_type_conversions)**`=ignore_missing_columns`**. *This page is licensed: CC BY-SA / Gnu FDL* {% @marketo/form formId="4316" %} [^1]: DML (Data Manipulation Language): The subset of SQL commands used to add, modify, retrieve, or delete data within existing database tables.