Bug #22101 Extra Slave Col: Slave only checks first extra col for default value
Submitted: 7 Sep 2006 23:26 Modified: 19 Feb 2007 11:53
Reporter: Jonathan Miller Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Row Based Replication ( RBR ) Severity:S2 (Serious)
Version:5.1.12-new-rpl OS:Linux (Linux 32 Bit OS)
Assigned to: Mats Kindahl CPU Architecture:Any

[7 Sep 2006 23:26] Jonathan Miller
Description:
The slave is only checking the first extra column for default values. If the second column does not have a default value, the slave inserts null.

This works as expected: Note: (Column d and 3 are extra)
eval CREATE TABLE t2 (a INT KEY, b BLOB, c CHAR(5),
                      d TIMESTAMP, e INT DEFAULT '1')ENGINE=$engine_type;

060908  2:07:18 [ERROR] Slave: Field `d` of table `test`.`t2` has no default value and cannot be NULL, Error_code: 1364
060908  2:07:18 [ERROR] Slave: Error in Write_rows event: error during transaction execution on table test.t2, Error_code: 1364
060908  2:07:18 [ERROR] Slave (additional info): Unknown error Error_code: 1105

But both of these pass:

+ CREATE TABLE t2 (a INT KEY, b BLOB, c CHAR(5),
+ d TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00',
+ e INT)ENGINE='MyISAM';
+ SELECT * FROM t2 ORDER BY a;
+ a     b       c       d       e
+ 1     b1b1b1b1b1b1b1b1        Kyle    0000-00-00 00:00:00     NULL
+ 2     b1b1b1b1b1b1b1b1        JOE     0000-00-00 00:00:00     NULL
+ 3     b1b1b1b1b1b1b1b1        QA      0000-00-00 00:00:00     NULL

+ CREATE TABLE t2 (a INT KEY, b BLOB, c CHAR(5),
+ d TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00',
+ e CHAR(20))ENGINE='MyISAM';

+ SELECT * FROM t2 ORDER BY a;
+ a     b       c       d       e
+ 1     b1b1b1b1b1b1b1b1        Kyle    0000-00-00 00:00:00     NULL
+ 2     b1b1b1b1b1b1b1b1        JOE     0000-00-00 00:00:00     NULL
+ 3     b1b1b1b1b1b1b1b1        QA      0000-00-00 00:00:00     NULL

How to repeat:
See above

Suggested fix:
Per Lars

###########################################
# More columns in slave at end of table,  #
# added columns do not have default values#
# Expect: Proper error message            #
###########################################
[11 Sep 2006 12:36] Jonathan Miller
This test case has been added to ./extra/rpl_tests/rpl_extraSlave_Col.test and has the failure part commented out. If the test case is checked in before a patch is created the devloper will need to uncomment the test case and test the patch.
[19 Feb 2007 11:51] Mats Kindahl
NULL is a default value for a column, if it can be NULL. If the column is of type INT NOT NULL and the sql mode STRICT_ALL_TABLES (otherwise, almost every type has a default value even when it cannot be NULL) is in effect, the result is the following.

master> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `a` int(11) NOT NULL,
  `b` blob,
  `c` char(5) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

slave> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `a` int(11) NOT NULL,
  `b` blob,
  `c` char(5) DEFAULT NULL,
  `e` int(11) NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

master> insert into t2 values (12, 'foo', 'bar');
Query OK, 1 row affected (0.01 sec)

master> select * from t2;
+----+------+------+
| a  | b    | c    |
+----+------+------+
| 12 | foo  | bar  | 
+----+------+------+
1 row in set (0.00 sec)

slave> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
...
           Slave_IO_Running: Yes
          Slave_SQL_Running: No
...
                 Last_Errno: 1364
                 Last_Error: Error in Write_rows event: error during transaction
 execution on table test.t2
...
1 row in set (0.01 sec)

slave> select * from t2;
Empty set (0.00 sec)
[19 Feb 2007 11:53] Mats Kindahl
I'm closing this bug report since the demonstrated behavior is not a bug. If it turns out that there are some bug of this kind, please feel free to reopen the bug report.