In addition to what we guarantee in (P-rpl-correct), we also guarantee correct replication in the following scenarios:
(P-rpl-different-file-system-case-sensitivities) TODO: allowed differences in file system case sensitivity
(P-rpl-different-versions) Replication shall be correct even if master has version a.b.c and slave has version A.B.C, where A.B.C ≥ a.b.c and A ≤ a+1.
(P-rpl-different-table-definitions) Table options may differ in the following fields: TODO: (comments, data/index directories, various hints)
(P-rpl-different-engines) TODO: allowed differences in storage engines
(P-rpl-different-column-definitions) Replication shall be correct even if the table definitions differ in one or more of the following ways:
(rpl-extra-slave-columns) binlog_format=ROW and the slave has extra columns after the columns of the master. Notes:
Extra indexes still must follow the rules for indexes - see below.
For purposes of defining "correct replication", the tables are considered equal if they are equal on the common columns.
(rpl-missing-slave-columns) binlog_format=ROW and the master has extra columns after the columns of the slave, as long as the following rules apply:
The slave cannot have both missing columns and extra columns (see above) at the same time.
If the master uses --binlog_row_image=minimal or binlog_row_image=noblob, then the BI must contain at least one column that exists on the slave. Moreover, the set of columns that are logged in BI must not match two different rows on the slave (but it may match two or more identical rows). This can be ensured, for example, by one of the following strategies:
The master has a PK that only includes columns that the slave has.
The master has a PK that includes all columns that the slave has, and possibly other columns too.
Note: For purposes of defining "correct replication", the tables are considered equal if they are equal on the common columns.
(rpl-type-promotion) The data type of a column differs as allowed in Replication with Differing Table Definitions on Master and Slave.
(rpl-different-keys) Keys, indexes, and NOT NULL attributes may differ freely between master and slave, as long as the follwing rule applies:
If the slave has an enabled key, and the master does not have an enabled key of the same type over the exact same set of columns (for example, because the key is missing/disabled or of a different type; or because the columns only exist on the slave), then the semantics of the slave's key must be ensured before the rows are inserted on the slave. Specifically:
If the slave has a uniqueness constraint (PK or UK), then uniqueness must be guaranteed before a row is inserted on the slave. This can be done, for example, through the following strategies:
Have a uniqueness constraint (UK or PK) on the master, over the same columns or over a subset of the columns.
If a column only exists on the slave (or if application-specific logic ensures that only NULL values are inserted on the master), then the AUTOINCREMENT attribute can be used on the slave.
Use application-specific logic on the master that ensures that rows inserted are unique in the key's columns.
Use BEFORE INSERT and/or BEFORE UPDATE triggers on the slave that ensure (through application-specific logic) that the rows are unique.
If the slave has a non-NULL constraint (PK or NOT NULL), then the absence of NULL values must be ensured before a row is inserted on the slave. This can be done analogously to how uniqueness constraints are satisfied above:
Have non-NULL constraints (PK or NOT NULL) on the master covering all the columns.
Use AUTOINCREMENT as above.
Use application-specific logic on the master that ensures no NULL values are inserted.
Use BEFORE INSERT and/or BEFORE UPDATE triggers on the slave that ensure (through application-specific logic) that no NULL values are inserted.
Note: There are no restrictions on extra keys on the master.
(rpl-column-names) If binlog_format=ROW, then column names may differ: columns are identified only by their position.
(P-rpl-different-rows) TODO: allowed extra rows on master or slave