Documentation Home
MySQL Replication
Related Documentation Download this Excerpt
PDF (US Ltr) - 1.5Mb
PDF (A4) - 1.5Mb
HTML Download (TGZ) - 468.2Kb
HTML Download (Zip) - 478.3Kb


4.1.10.1 Replication with More Columns on Master or Slave

You can replicate a table from the master to the slave such that the master and slave copies of the table have differing numbers of columns, subject to the following conditions:

  • Columns common to both versions of the table must be defined in the same order on the master and the slave.

    (This is true even if both tables have the same number of columns.)

  • Columns common to both versions of the table must be defined before any additional columns.

    This means that executing an ALTER TABLE statement on the slave where a new column is inserted into the table within the range of columns common to both tables causes replication to fail, as shown in the following example:

    Suppose that a table t, existing on the master and the slave, is defined by the following CREATE TABLE statement:

    CREATE TABLE t (
        c1 INT,
        c2 INT,
        c3 INT
    );

    Suppose that the ALTER TABLE statement shown here is executed on the slave:

    ALTER TABLE t ADD COLUMN cnew1 INT AFTER c3;

    The previous ALTER TABLE is permitted on the slave because the columns c1, c2, and c3 that are common to both versions of table t remain grouped together in both versions of the table, before any columns that differ.

    However, the following ALTER TABLE statement cannot be executed on the slave without causing replication to break:

    ALTER TABLE t ADD COLUMN cnew2 INT AFTER c2;

    Replication fails after execution on the slave of the ALTER TABLE statement just shown, because the new column cnew2 comes between columns common to both versions of t.

  • Each extra column in the version of the table having more columns must have a default value.

    A column's default value is determined by a number of factors, including its type, whether it is defined with a DEFAULT option, whether it is declared as NULL, and the server SQL mode in effect at the time of its creation; for more information, see Data Type Default Values).

In addition, when the slave's copy of the table has more columns than the master's copy, each column common to the tables must use the same data type in both tables.

Examples.  The following examples illustrate some valid and invalid table definitions:

More columns on the master.  The following table definitions are valid and replicate correctly:

master> CREATE TABLE t1 (c1 INT, c2 INT, c3 INT);
slave>  CREATE TABLE t1 (c1 INT, c2 INT);

The following table definitions would raise an error because the definitions of the columns common to both versions of the table are in a different order on the slave than they are on the master:

master> CREATE TABLE t1 (c1 INT, c2 INT, c3 INT);
slave>  CREATE TABLE t1 (c2 INT, c1 INT);

The following table definitions would also raise an error because the definition of the extra column on the master appears before the definitions of the columns common to both versions of the table:

master> CREATE TABLE t1 (c3 INT, c1 INT, c2 INT);
slave>  CREATE TABLE t1 (c1 INT, c2 INT);

More columns on the slave.  The following table definitions are valid and replicate correctly:

master> CREATE TABLE t1 (c1 INT, c2 INT);
slave>  CREATE TABLE t1 (c1 INT, c2 INT, c3 INT);

The following definitions raise an error because the columns common to both versions of the table are not defined in the same order on both the master and the slave:

master> CREATE TABLE t1 (c1 INT, c2 INT);
slave>  CREATE TABLE t1 (c2 INT, c1 INT, c3 INT);

The following table definitions also raise an error because the definition for the extra column in the slave's version of the table appears before the definitions for the columns which are common to both versions of the table:

master> CREATE TABLE t1 (c1 INT, c2 INT);
slave>  CREATE TABLE t1 (c3 INT, c1 INT, c2 INT);

The following table definitions fail because the slave's version of the table has additional columns compared to the master's version, and the two versions of the table use different data types for the common column c2:

master> CREATE TABLE t1 (c1 INT, c2 BIGINT);
slave>  CREATE TABLE t1 (c1 INT, c2 INT, c3 INT);

User Comments
Sign Up Login You must be logged in to post a comment.