MySQL Bugs: #67878: Replication failure on loading data into a view with binlog

Bug #67878	Replication failure on loading data into a view with binlog_format=STATEMENT
Submitted:	11 Dec 2012 21:32	Modified:	25 Apr 2013 17:22
Reporter:	Elena Stepanova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.1, 5.5, 5.6	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
When LOAD DATA statement is written into a binary log, on some reason it explicitly lists all fields of the base table, even those that aren't used in the view. The replayed statement fails with ER_BAD_FIELD_ERROR (Unknown column).

As far as I can see, the view in the example meets all requirements for updatable/insertable views (http://dev.mysql.com/doc/refman/5.6/en/view-updatability.html). 
The server error log doesn't contain any warnings about the statement being unsafe.

CREATE TABLE t1 (i INT, j INT DEFAULT 0);
CREATE VIEW v1 AS SELECT i FROM t1;
LOAD DATA LOCAL INFILE 'l.data' INTO TABLE v1;
Warnings:
Warning	1261	Row 1 doesn't contain data for all columns
SHOW BINLOG EVENTS;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
...
master-bin.000001	234	Query	1	428	use `test`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS SELECT i FROM t1
master-bin.000001	428	Query	1	507	BEGIN
master-bin.000001	507	Begin_load_query	1	538	;file_id=1;block_len=4
master-bin.000001	538	Execute_load_query	1	810	use `test`; LOAD DATA LOCAL INFILE 'l.data' IGNORE INTO TABLE `v1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (`i`, `j`) ;file_id=1
master-bin.000001	810	Query	1	890	COMMIT

How to repeat:
--source include/master-slave.inc
--source include/have_binlog_format_statement.inc

CREATE TABLE t1 (i INT, j INT DEFAULT 0);
--write_file $MYSQLTEST_VARDIR/l.data
1\n
EOF

CREATE VIEW v1 AS SELECT i FROM t1;

eval LOAD DATA LOCAL INFILE '$MYSQLTEST_VARDIR/l.data' INTO TABLE v1;

--sync_slave_with_master

This is what we have in 5.6.8:

[openxs@chief mysql-test]$ ./mtr bug67878 | more
Logging: ./mtr  bug67878
121212  9:49:48 [Warning] Changed limits: max_open_files: 1024  max_connections: 151  table_cache: 431
121212  9:49:48 [Note] Plugin 'FEDERATED' is disabled.
121212  9:49:48 [Note] Binlog end
121212  9:49:48 [Note] Shutting down plugin 'MyISAM'
121212  9:49:48 [Note] Shutting down plugin 'CSV'
MySQL Version 5.6.8
Checking supported features...
 - skipping ndbcluster
 - SSL connections supported
Collecting tests...
Checking leftover processes...
Removing old var directory...
Creating var directory '/home/openxs/dbs/5.6/mysql-test/var'...
Installing system database...
Using server port 35642

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
main.bug67878                            [ fail ]
        Test ended at 2012-12-12 09:49:59

CURRENT_TEST: main.bug67878
=== SHOW MASTER STATUS ===
---- 1. ----
File    slave-bin.000001
Position        412
Binlog_Do_DB
Binlog_Ignore_DB
Executed_Gtid_Set
==========================

=== SHOW SLAVE STATUS ===
---- 1. ----
Slave_IO_State  Waiting for master to send event
Master_Host     127.0.0.1
Master_User     root
Master_Port     13000
Connect_Retry   1
Master_Log_File master-bin.000001
Read_Master_Log_Pos     888
Relay_Log_File  slave-relay-bin.000002
Relay_Log_Pos   592
Relay_Master_Log_File   master-bin.000001
Slave_IO_Running        Yes
Slave_SQL_Running       No
Replicate_Do_DB
Replicate_Ignore_DB
Replicate_Do_Table
Replicate_Ignore_Table
Replicate_Wild_Do_Table
Replicate_Wild_Ignore_Table
Last_Errno      1054
Last_Error      Error 'Unknown column 'j' in 'field list'' on query. Default dat
abase: 'test'. Query: 'LOAD DATA INFILE '../../tmp/SQL_LOAD-8631be3b-4430-11e2-b
a2d-485b3952834f-1-1.data' IGNORE INTO  TABLE `v1` FIELDS TERMINATED BY '\t' ENC
LOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (`i`, `j`)'
...

Thank you for the report.

Verified as described. Problem is not repeatable with version 5.0

A slightly different and I think somewhat more vicious scenario, which does not even require a different list of fields in the table and the view:

--source include/master-slave.inc
--source include/have_binlog_format_statement.inc

--write_file $MYSQLTEST_VARDIR/l.data 
1
EOF

CREATE TABLE t1 (i INT);
CREATE VIEW v1 AS SELECT i FROM t1;
SHOW CREATE VIEW v1;
--eval LOAD DATA LOCAL INFILE '$MYSQLTEST_VARDIR/l.data' INTO TABLE v1 (i)

--sync_slave_with_master

# End of test case

It also fails, because in the binary log the LOAD statement is written as

LOAD DATA LOCAL INFILE '/home/elenst/mysql-5.5/mysql-test/var/l.data' IGNORE INTO TABLE `v1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (`test`.`t1`.`i`) 

and replication aborts with 

Last_Errno	1054
Last_Error	Error 'Unknown column 'test.t1.i' in 'field list'' on query.

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/

Documented fix in the 5.7.1 changelog as follows:

      Replication failed when a replicated LOAD DATA statement inserted rows
      into a view. 

Closed.