Description:
When the slave has more columns/fields than the master and such fields do not accept null or do not have a default value, it must fail if in strict mode. Otherwise, it should succeed and the extra fields should be populated with automatic values. If there is a default value, it should be used by default. However, this is not happening.
CURRENT RESULTS:
----------------
1 - FIRST CASE (NOT NULL / NO DEFAULT VALUE)
master:
CREATE TABLE t1(`a` INT, `b` INT) ENGINE=NDB;
slave:
CREATE TABLE t1(`a` INT NOT NULL, `b` INT NOT NULL,
`c` INT NOT NULL) ENGINE=NDB;
INSERT INTO t1(a) VALUES (1); produces the following result:
master
SELECT * FROM t1 ORDER BY a;
a b
1 NULL
SELECT * FROM t1 ORDER BY a;
a b c
2 - SECOND CASE (NOT NULL / DEFAULT VALUE)
master:
CREATE TABLE t2(`a` INT, `b` INT) ENGINE=NDB;
slave:
CREATE TABLE t2(`a` INT NOT NULL, `b` INT NOT NULL,
`c` INT NOT NULL DEFAULT 500) ENGINE=NDB;
INSERT INTO t2(a) VALUES (1); produces the following result:
master
SELECT * FROM t2 ORDER BY a;
a b
1 NULL
SELECT * FROM t2 ORDER BY a;
a b c
3 - SECOND CASE (NULL / DEFAULT VALUE)
master:
CREATE TABLE t3(`a` INT, `b` INT) ENGINE=NDB;
slave:
CREATE TABLE t3(`a` INT NOT NULL, `b` INT NOT NULL,
`c` INT DEFAULT 500) ENGINE=NDB;
INSERT INTO t3(a) VALUES (1); produces the following result:
master
SELECT * FROM t3 ORDER BY a;
a b
1 NULL
SELECT * FROM t3 ORDER BY a;
a b c
1 0 NULL
4 - SECOND CASE (NULL / NO DEFAULT VALUE) (OK)
master:
CREATE TABLE t4(`a` INT, `b` INT) ENGINE=NDB;
slave:
CREATE TABLE t4(`a` INT NOT NULL, `b` INT NOT NULL,
`c` INT) ENGINE=NDB;
INSERT INTO t4(a) VALUES (1); produces the following result:
master
SELECT * FROM t3 ORDER BY a;
a b
1 NULL
SELECT * FROM t4 ORDER BY a;
a b c
1 0 NULL
CORRECT RESULTS:
----------------
1 - FIRST CASE (NOT NULL / NO DEFAULT VALUE)
master:
SELECT * FROM t1 ORDER BY a;
a b
1 NULL
slave:
SELECT * FROM t1 ORDER BY a;
a b c
1 0 0
2 - FIRST CASE (NOT NULL / DEFAULT VALUE)
master:
SELECT * FROM t2 ORDER BY a;
a b
1 NULL
slave:
SELECT * FROM t2 ORDER BY a;
a b c
1 0 500
3 - FIRST CASE (NULL / DEFAULT VALUE)
master:
SELECT * FROM t3 ORDER BY a;
a b
1 NULL
slave:
SELECT * FROM t3 ORDER BY a;
a b c
1 0 500
4 - FIRST CASE (NULL / NO DEFAULT VALUE)
master:
SELECT * FROM t4 ORDER BY a;
a b
1 NULL
slave:
SELECT * FROM t4 ORDER BY a;
a b c
1 0 NULL
How to repeat:
-- source include/have_ndb.inc
--source include/have_binlog_format_row.inc
-- source include/ndb_master-slave.inc
let $engine=ndb;
connection master;
SET SQL_LOG_BIN= 0;
eval CREATE TABLE t1(`a` INT NOT NULL, `b` INT,
PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1;
eval CREATE TABLE t2(`a` INT NOT NULL, `b` INT,
PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1;
eval CREATE TABLE t3(`a` INT NOT NULL, `b` INT,
PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1;
eval CREATE TABLE t4(`a` INT NOT NULL, `b` INT,
PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1;
SET SQL_LOG_BIN= 1;
connection slave;
STOP SLAVE;
--source include/wait_for_slave_to_stop.inc
SET sql_mode='';
SHOW VARIABLES LIKE 'sql_mode';
START SLAVE;
--source include/wait_for_slave_to_start.inc
eval CREATE TABLE t1(`a` INT NOT NULL, `b` INT NOT NULL,
`c` INT NOT NULL,
PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1;
eval CREATE TABLE t2(`a` INT NOT NULL, `b` INT NOT NULL,
`c` INT NOT NULL DEFAULT 500,
PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1;
eval CREATE TABLE t3(`a` INT NOT NULL, `b` INT NOT NULL,
`c` INT DEFAULT 500,
PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1;
eval CREATE TABLE t4(`a` INT NOT NULL, `b` INT NOT NULL,
`c` INT,
PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1;
--echo ************* EXECUTION WITH INSERTS *************
connection master;
INSERT INTO t1(a) VALUES (1);
INSERT INTO t1(a, b) VALUES (2, NULL);
INSERT INTO t1(a, b) VALUES (3, 1);
INSERT INTO t2(a) VALUES (1);
INSERT INTO t2(a, b) VALUES (2, NULL);
INSERT INTO t2(a, b) VALUES (3, 1);
INSERT INTO t3(a) VALUES (1);
INSERT INTO t3(a, b) VALUES (2, NULL);
INSERT INTO t3(a, b) VALUES (3, 1);
INSERT INTO t3(a, b) VALUES (4, 1);
REPLACE INTO t3(a, b) VALUES (5, null);
REPLACE INTO t3(a, b) VALUES (3, null);
UPDATE t3 SET b = NULL where a = 4;
INSERT INTO t4(a) VALUES (1);
INSERT INTO t4(a, b) VALUES (2, NULL);
INSERT INTO t4(a, b) VALUES (3, 1);
INSERT INTO t4(a, b) VALUES (4, 1);
REPLACE INTO t4(a, b) VALUES (5, null);
REPLACE INTO t4(a, b) VALUES (3, null);
UPDATE t4 SET b = NULL where a = 4;
--echo ************* SHOWING THE RESULT SETS *************
connection master;
sync_slave_with_master;
connection master;
SELECT * FROM t1 ORDER BY a;
connection slave;
SELECT * FROM t1 ORDER BY a;
connection master;
SELECT * FROM t2 ORDER BY a;
connection slave;
SELECT * FROM t2 ORDER BY a;
connection master;
SELECT * FROM t3 ORDER BY a;
connection slave;
SELECT * FROM t3 ORDER BY a;
connection master;
SELECT * FROM t4 ORDER BY a;
connection slave;
SELECT * FROM t4 ORDER BY a;
connection master;
DROP TABLE t1, t2, t3, t4;
sync_slave_with_master;