MySQL Bugs: #23907: Extra Slave Col is not erroring on extra col with no default values.

Bug #23907	Extra Slave Col is not erroring on extra col with no default values.
Submitted:	2 Nov 2006 20:11	Modified:	30 Oct 2007 9:22
Reporter:	Jonathan Miller	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.1.13	OS:	Linux (Linux)
Assigned to:	Mats Kindahl	CPU Architecture:	Any

Description:
Hi,

In trying to get ready to pust the new slave w/ extra col tests, I found that the server response to extra columns with no default value has changed.

I am not getting an error as I should. There was already a bug opened for returning the wrong error, but now no error is returned and no data is inserted into the slave.

*** Create t9 on slave  ***
STOP SLAVE;
RESET SLAVE;
CREATE TABLE t9 (a INT KEY, b BLOB, c CHAR(5),
d TIMESTAMP,
e INT DEFAULT '1')ENGINE='InnoDB';
*** Create t9 on Master ***
CREATE TABLE t9 (a INT PRIMARY KEY, b BLOB, c CHAR(5)
) ENGINE='InnoDB';
RESET MASTER;
*** Start Slave ***
START SLAVE;
*** Master Data Insert ***
set @b1 = 'b1b1b1b1';
set @b1 = concat(@b1,@b1);
INSERT INTO t9 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA');
*************************************************
** Currently giving wrong error see bug#22234 ***
*************************************************
*** Select from T9 on the slave ***
SELECT * FROM t9;
a       b       c       d       e
SHOW SLAVE STATUS;
Slave_IO_State  #
Master_Host     127.0.0.1
Master_User     root
Master_Port     MASTER_PORT
Connect_Retry   1
Master_Log_File master-bin.000001
Read_Master_Log_Pos     #
Relay_Log_File  #
Relay_Log_Pos   #
Relay_Master_Log_File
Slave_IO_Running        Yes
Slave_SQL_Running       Yes
Replicate_Do_DB
Replicate_Ignore_DB
Replicate_Do_Table
Replicate_Ignore_Table
Replicate_Wild_Do_Table
Replicate_Wild_Ignore_Table
Last_Errno      0
Last_Error
Skip_Counter    0
Exec_Master_Log_Pos     #
Relay_Log_Space #
Until_Condition None
Until_Log_File
Until_Log_Pos   0
Master_SSL_Allowed      No
Master_SSL_CA_File
Master_SSL_CA_Path
Master_SSL_Cert
Master_SSL_Cipher
Master_SSL_Key
Seconds_Behind_Master   #

How to repeat:
###########################################
############# Continued ###################
# More columns in slave at end of table,  #
# added columns do not have default values#
# Expect: Proper error message            #
###########################################
--echo *** Create t9 on slave  ***
STOP SLAVE;
RESET SLAVE;
eval CREATE TABLE t9 (a INT KEY, b BLOB, c CHAR(5),
                      d TIMESTAMP,
                      e INT DEFAULT '1')ENGINE=$engine_type;

--echo *** Create t9 on Master ***
connection master;
eval CREATE TABLE t9 (a INT PRIMARY KEY, b BLOB, c CHAR(5)
                       ) ENGINE=$engine_type;
RESET MASTER;

--echo *** Start Slave ***
connection slave;
START SLAVE;

--echo *** Master Data Insert ***
connection master;
set @b1 = 'b1b1b1b1';
set @b1 = concat(@b1,@b1);
INSERT INTO t9 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA');

--echo *************************************************
--echo ** Currently giving wrong error see bug#22234 ***
--echo *************************************************
#sync_slave_with_master;
connection slave;

--echo *** Select from T9 ***
SELECT * FROM t9;
#wait_for_slave_to_stop;
--replace_result $MASTER_MYPORT MASTER_PORT
--replace_column 1 # 7 # 8 # 9 # 22 # 23 # 33 #
--query_vertical SHOW SLAVE STATUS
#SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2;
#START SLAVE;

--echo *** Drop t9  ***
connection master;
DROP TABLE t9;
connection slave;
DROP TABLE t9;
#START SLAVE;
#sync_slave_with_master;

Suggested fix:
Retrun an error, and the right error.

What are the extra column with no default value here? The only extra fields I can see is a TIMESTAMP column and a INT with default to 1. Both have defaults (the timestamp column has an implicit default).

Here's an example::

  CREATE TABLE t1 (a INT, b TIMESTAMP);
  INSERT INTO t1 VALUES (1,DEFAULT);
  SELECT * FROM t1;

with the result::

  +------+---------------------+
  | a    | b                   |
  +------+---------------------+
  |    1 | 2007-01-24 12:45:53 | 
  +------+---------------------+

As a matter of fact, most data type has a default value, unless strict SQL mode is enabled.

SLAVE:
CREATE TABLE t9 (a INT KEY, b BLOB, c CHAR(5), d TIMESTAMP, e INT DEFAULT '1')ENGINE='InnoDB';

Extra = d TIMESTAMP, e INT DEFAULT 

MASTER
CREATE TABLE t9 (a INT PRIMARY KEY, b BLOB, c CHAR(5)) ENGINE='InnoDB';

Hi Mats,

I have tried a couple of different tests today with the current -new-rpl tree.

What I found is that "SET SQL_MODE = 'STRICT_ALL_TABLES';" still allowed the time to be inserted into the column even though nothing was passed from the master.

So with that in mind, I switch it out for a double as shown below. The double did not have a default value and is the first of the extra slave table columns. No error was returned and the column actually contains an "NULL" value. I do not think this is the behavior that we are looking for. From the specs I believe that an error should be returned. Please take a look and let me know if you need more help from me.

Best wishes,
/Jeb 

+ SET SQL_MODE = 'STRICT_ALL_TABLES';
+ CREATE TABLE t9 (a INT KEY, b BLOB, c CHAR(5),
+ d DOUBLE,
+ e INT DEFAULT '1')ENGINE='InnoDB';
+ *** Create t9 on Master ***
+ CREATE TABLE t9 (a INT PRIMARY KEY, b BLOB, c CHAR(5)
+ ) ENGINE='InnoDB';
+ RESET MASTER;
+ *** Start Slave ***
+ START SLAVE;
+ *** Master Data Insert ***
+ set @b1 = 'b1b1b1b1';
+ set @b1 = concat(@b1,@b1);
+ INSERT INTO t9 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA')
+ *************************************************
+ ** Currently giving wrong error see bug#22234 ***
+ *************************************************
+ *** Select from T9 ***
+ SELECT * FROM t9;
+ a     b       c       d       e
+ 1     b1b1b1b1b1b1b1b1        Kyle    NULL    1
+ 2     b1b1b1b1b1b1b1b1        JOE     NULL    1
+ 3     b1b1b1b1b1b1b1b1        QA      NULL    1
+ SHOW SLAVE STATUS;
+ Slave_IO_State        #
+ Master_Host   127.0.0.1
+ Master_User   root
+ Master_Port   MASTER_PORT
+ Connect_Retry 1
+ Master_Log_File       master-bin.000001
+ Read_Master_Log_Pos   #
+ Relay_Log_File        #
+ Relay_Log_Pos #
+ Relay_Master_Log_File master-bin.000001
+ Slave_IO_Running      Yes
+ Slave_SQL_Running     Yes
+ Replicate_Do_DB
+ Replicate_Ignore_DB
+ Replicate_Do_Table
+ Replicate_Ignore_Table
+ Replicate_Wild_Do_Table
+ Replicate_Wild_Ignore_Table
+ Last_Errno    0
+ Last_Error
+ Skip_Counter  0
+ Exec_Master_Log_Pos   #
+ Relay_Log_Space       #
+ Until_Condition       None
+ Until_Log_File
+ Until_Log_Pos 0
+ Master_SSL_Allowed    No
+ Master_SSL_CA_File
+ Master_SSL_CA_Path
+ Master_SSL_Cert
+ Master_SSL_Cipher
+ Master_SSL_Key
+ Seconds_Behind_Master #
+ *** Drop t9  ***
+ DROP TABLE t9;
  *** Create t10 on slave  ***
  STOP SLAVE;
  RESET SLAVE;

I see no bug. In standard SQL, and in MySQL, if a column 
has no explicit default value, its default is NULL unless 
the column is also defined with NOT NULL. So, on the slave:

+ SET SQL_MODE = 'STRICT_ALL_TABLES';
+ CREATE TABLE t9 (a INT KEY, b BLOB, c CHAR(5),
+ d DOUBLE,
+ e INT DEFAULT '1')ENGINE='InnoDB';

you are defining a table with column d that has a default
of NULL and a column e that has a default of 1. If I'm
reading this report correctly, Jeb is expecting an error
on the slave because column d has no default (but it has)
and is not expecting NULL to be inserted into column d
(but that is the correct behaviour; inserting the default
value because no explicit value is provided by INSERT).

Setting this bug back to "open" to be reverified, in case 
I'm mistaken about the problem.

It must be possible to replicate using row-based replication from master table
T1 to slave table T2, if:
1. T1 and T2 have same (table) name
2. T2 contain additional columns as compared to T1. The additional columns of T2
   appear after all the columns of T1.
3. For each column in T1 the corresponding column in T2 has the same type. 
4. Every column in T2 but not in T1 has a type with default value

Not sure if NULL counts as a default value.

NULL counts as a default value for columns that can be NULL. See http://dev.mysql.com/doc/refman/5.1/en/data-type-defaults.html where we have: 

    If a column definition includes no explicit DEFAULT value, MySQL determines
    the default value as follows:

    If the column can take NULL as a value, the column is defined with an
    explicit DEFAULT NULL clause.

    [...]

The following testcase::

  -- source include/master-slave.inc

  connection slave;
  STOP SLAVE;
  SET SQL_MODE = 'STRICT_ALL_TABLES';
  CREATE TABLE t9 (a INT KEY, b BLOB, c CHAR(5), d DOUBLE NOT NULL, e INT DEFAULT '1');

  connection master;
  --echo *** Create t9 on Master ***
  CREATE TABLE t9 (a INT PRIMARY KEY, b BLOB, c CHAR(5));
  RESET MASTER; 
  connection slave;
  --echo *** Start Slave ***
  START SLAVE;

  connection master;
  --echo *** Master Data Insert ***
  set @b1 = 'b1b1b1b1';
  set @b1 = concat(@b1,@b1);
  INSERT INTO t9 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA');

  connection slave;
  --wait_for_slave_to_stop
  --query_vertical SHOW SLAVE STATUS

Is a correct version. This test case should cause the slave to stop because the extra column on the slave does not have a default value (i.e., not even NULL).

Setting status to Open for reverification.

Hi,

Have spent some time on this one today. Pulled the latest mysql-5.1-new-rpl clone and ran several tests. Using the test that Matts posted, under SBR and RBR using the double, you do get the following errors:

SBR:
Error 'Column count doesn't match value count at row 1' on query. Default database: 'test'. Query: 'INSERT INTO t9
RBR:
Error in Write_rows event: error during transaction execution on table test.t9

I am not sure that either of these error messages are good for this problem.

Now, if I change the double to a blob or TEXT or CHAR, under SBR, I get:
'Column count doesn't match value count at row 1' on query. Default database: 'test'. Query: 'INSERT INTO t9 () V

But with RBR it goes right through.

  CREATE TABLE t9 (a INT KEY, b BLOB, c CHAR(5), d BLOB, e INT
DEFAULT '1');
  CREATE TABLE t9 (a INT KEY, b BLOB, c CHAR(5), d TEXT, e INT
DEFAULT '1');
  CREATE TABLE t9 (a INT KEY, b BLOB, c CHAR(5), d CHAR(1), e INT
DEFAULT '1');

Following up on Jeb's comments, I have the following results. In summary, I can not see any case where I can repeat the failure as indicated by the title of the bug report.

Assuming that we have a table on the master with two columns and a table on the slave with one extra column, RBR replicates the statement

  INSERT INTO t1 VALUES(1, 'foo');

to a slave as if the statement was written (on the slave) in one of the following forms:

  INSERT INTO t1(a,b) VALUES (1, 'foo');
  INSERT INTO t1 VALUES (1, 'foo', DEFAULT);

This is different from how statement-based replication works, since it just tries to execute the statement on the slave. In general, an indication of an error in RBR is if the corresponding statement as described above can be executed on the slave but not via the SQL thread, or cannot be executed on the slave but via the SQL thread.

Pushed into 5.1.24-rc

Pushed into 6.0.5-alpha