Bug #57550 Ignore missing columns and tables

• Submitted: 19 Oct 2010 6:05
• Reporter: Geert Vanderkelen
• Status: Verified
• Category: MySQL Server: Row Based Replication ( RBR )
• Severity: S4 (Feature request)
• OS: Any
• Assigned to: Assigned Account
• CPU Architecture: Any

[19 Oct 2010 6:05] Geert Vanderkelen

Description:
Two new Slave options for (Row-based) replication:
(1) --exclude-missing-columns
(2) --exclude-missing-tables

(1) Columns which are not existing on Slave side, but were available on Master, should be ignored.
(2) Any row coming from Master for which the table doesn't exists on the Slave side should be ignored.

Maybe it's better to call them --ignore-missing-* or even better --slave-skip-unknown-*

(2) would be similar to using 
  slave_skip_errors = 1146

We can't ignore error 1054 (Unknown column) because that would ignore the complete row.

How to repeat:
.

[1 Dec 2010 10:57] Geert Vanderkelen

Note that excluding missing tables is already possible skipping the error. It's more or less a hack, but works.

Excluding missing columns is not possible and using the 'feature' that columns at then end are ignore, is not an option.
Consider a Slave being upgraded and the schema needs a change. A column needs to be dropped. The Master is still running with the previous schema:

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` varchar(20) DEFAULT NULL,
  `c2` varchar(20) DEFAULT NULL,
  `c3` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=ndbcluster;

master> INSERT INTO t1 (c1,c2,c3) VALUES ('a','b','c');

slave> SELECT * FROM t1;
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 | a    | b    | c    |
+----+------+------+------+

slave> ALTER TABLE t1 DROP COLUMN c2;
slave> SELECT * FROM t1;
+----+------+------+
| id | c1   | c3   |
+----+------+------+
|  1 | a    | c    |
+----+------+------+

master> INSERT INTO t1 (c1,c2,c3) VALUES ('aa','bb','cc');

slave> SELECT * FROM t1;
+----+------+------+
| id | c1   | c3   |
+----+------+------+
|  1 | a    | c    |
|  2 | aa   | bb   |
+----+------+------+

With an option like --exclude-missing-columns we would expect:

slave> SELECT * FROM t1;
+----+------+------+
| id | c1   | c3   |
+----+------+------+
|  1 | a    | c    |
|  2 | aa   | cc   |
+----+------+------+