Description:
I was going through <a href="https://dev.mysql.com/doc/refman/5.5/en/replication-features-differing-tables.html" target=_blank>this documentation page</a> which reads <i>"Columns common to both versions of the table must be defined in the same order on the master and the slave."</i>
This is not exactly the truth or say half true. You can actually make this work by providing proper insert statement!
How to repeat:
(root@localhost) [(none)]>use kedar
Database changed
(root@localhost) [kedar]>CREATE TABLE t1 (c1 INT, c2 INT);
Query OK, 0 rows affected (0.05 sec)
(root@localhost) [kedar]>insert into t1 (c1,c2) values (1,1);
Query OK, 1 row affected (0.01 sec)
(root@localhost) [kedar]>insert into t1 (c1,c2) values (1,1);
Query OK, 1 row affected (0.01 sec)
On Slave:
(kedar@localhost) [kedar]>alter table t1 add column (c3 int);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
kedar@localhost) [kedar]>show create table t1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`c1` int(11) DEFAULT NULL,
`c2` int(11) DEFAULT NULL,
`c3` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
(kedar@localhost) [kedar]>select * from t1;
+------+------+------+
| c1 | c2 | c3 |
+------+------+------+
| 1 | 1 | NULL |
+------+------+------+
1 row in set (0.00 sec)
kedar@localhost) [kedar]>alter table t1 modify column c3 int after c1;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
(kedar@localhost) [kedar]>select * from t1;
+------+------+------+
| c1 | c3 | c2 |
+------+------+------+
| 1 | NULL | 1 |
+------+------+------+
1 row in set (0.00 sec)
(kedar@localhost) [kedar]>select * from t1;
+------+------+------+
| c1 | c3 | c2 |
+------+------+------+
| 1 | NULL | 1 |
| 1 | NULL | 1 |
+------+------+------+
2 rows in set (0.00 sec)
Suggested fix:
This should be a documentation bug and it is not necessary that master-slave MUST have same column order or even more or less columns. If required, we can get it worked with the use of right inserts and default/nonull column values.
