89493

Replication with Morę 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 rangę 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 (

Cl INT, c2 INT, c3 INT );

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

ALTER TABLE t ADD COLUMN cnewl INT AFTER c3;

The previous ALTER TABLE is permitted on the slave because the columns cl, 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 cnevr2 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 morę 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 modę in effect at the

time of its creation; for morę information, see Section 11.5, “Data Type Default Values”).

In addition, when the slave's copy of the table has morę columns than the master’s copy, each column

common to the tables must use the same data type in both tables.