Bug #47742 Extra-columns on the slave do not work with ndb as expected
Submitted: 30 Sep 2009 12:52 Modified: 16 Feb 2012 4:28
Reporter: Alfranio Junior Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S3 (Non-critical)
Version:mysql-5.1 OS:Any
Assigned to: Martin Skold CPU Architecture:Any
Tags: 5.1+, ndb, RBR, replication

[30 Sep 2009 12:52] Alfranio Junior
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;
[30 Sep 2009 12:52] Alfranio Junior
This also happens in 5.4.
[30 Sep 2009 19:36] Alfranio Junior
After fixing this bug, create the following test case:

--------------------------------------------------
--source include/have_ndb.inc
--source include/have_binlog_format_row.inc
--source include/ndb_master-slave.inc

let $engine=ndb;
--source extra/rpl_tests/rpl_not_null.test
--------------------------------------------------

Similar to rpl_not_null_innodb and rpl_not_null_myisam.
[5 Nov 2009 9:29] Lars Thalmann
See also BUG#30529.

My current opinion is that these should hold regardless of SQL mode:

R1. If a field is defined as NOT NULL and there is a default value,
    then a value of the field must be passed via write_row.
R2. If a field is defined as NOT NULL and there is not a default value,
    then write_row must fail with internal error code.
R3. If write_row fails in a slave, then it must stop with an error.
[16 Feb 2012 4:28] Jon Stephens
Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/
[16 Feb 2012 4:30] Jon Stephens
Fixed in NDB 7.0+. Documented in the NDB 7.0.28 and 7.1.17 changelogs as follows:

      Replication of NDB tables having more columns on the slave than 
      on the master did not always work correctly when any of the extra 
      columns were NOT NULL, did not have a default value, or both.

Closed.