| 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: | |
| 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 |
[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.
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 # ###########################################