MySQL Bugs: #19662: LOAD DATA INFILE replicates incorrectly

Bug #19662	LOAD DATA INFILE replicates incorrectly
Submitted:	10 May 2006 4:49	Modified:	4 Aug 2006 9:23
Reporter:	Kolbe Kegel	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	4.1.19, 4.1.20-BK	OS:	Linux (Linux)
Assigned to:	MC Brown	CPU Architecture:	Any

Description:
LOAD DATA INFILE is written to the binary log in such a way that it doesn't behave as expected when executed while the default database is one *not* specified in a replication slave's replicate-do-db rule.

If a replicate-do-db rule is present and the default database is not specified in any such rule, statements are ignored by the slave until the default database is changed.

When LOAD DATA INFILE is written to the binary log in MySQL 4.1, it is rewritten so that any database component of the destination table is rewritten as a "use" statement above the actual LOAD DATA INFILE statement. This causes the problem described above.

This behavior does not appear to exist in MySQL 5.0.

How to repeat:
- Start slave with --replicate-do-db=test
- Create file on master:
cat > /tmp/load_data_infile.txt
1
2
3

- Execute on master:

CREATE DATABASE norepl;
use norepl;
CREATE TABLE test.table1 (id int);
LOAD DATA INFILE '/tmp/load_data_infile.txt' INTO TABLE test.table1;

The binary log will contain this:

#060509 21:45:23 server id 1  log_pos 610       Query   thread_id=34    exec_time=0
# use test;
# LOAD DATA INFILE '/tmp/load_data_infile.txt' INTO TABLE `table1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' STARTING BY '' (id);
# file_id: 3  block_len: 6
# at 708

Replication will stop with the following error message:

                 Last_Error: Error 'Table 'other.foo' doesn't exist' running LOAD DATA INFILE on table 'foo'. Default database: 'other'. Failed executing load from '/tmp/SQL_LOAD-2-1-2.info'

Suggested fix:
This statement should be logged in 4.1 the same way it is logged in 5.0, like this:

#Begin_load_query: file_id: 3  block_len: 6
# at 1003
#060509 21:38:31 server id 1  end_log_pos 1148  Execute_load_query      thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1147235911;
LOAD DATA LOCAL INFILE '/tmp/SQL_LOAD_MB-3-1' INTO TABLE test.table1;
# file_id: 3
# at 1148

Thank you for a bug report. Verified just as described with 4.0.20-BK on Linux:

openxs@suse:~/dbs/4.1> cat /tmp/load_data_infile.txt
1
2
3
.
openxs@suse:~/dbs/4.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.1.20-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE DATABASE norepl;
Query OK, 1 row affected (0.06 sec)

mysql> use norepl;
CDatabase changed
mysql> CREATE TABLE test.table1 (id int);
Query OK, 0 rows affected (0.02 sec)

mysql> LOAD DATA INFILE '/tmp/load_data_infile.txt' INTO TABLE test.table1;
Query OK, 4 rows affected, 1 warning (0.00 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 1

mysql> exit
Bye
openxs@suse:~/dbs/4.1> bin/mysqlbinlog var/suse-bin.000001
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
# at 4
#060510 13:44:18 server id 2  log_pos 4         Start: binlog v 3, server v 4.1.
20-debug-log created 060510 13:44:18 at startup
# at 79
#060510 13:45:01 server id 2  log_pos 79        Query   thread_id=4     exec_time=1     error_code=0
SET TIMESTAMP=1147257901;
CREATE DATABASE norepl;
# at 138
#060510 13:45:02 server id 2  log_pos 138       Query   thread_id=4     exec_time=0     error_code=0
use norepl;
SET TIMESTAMP=1147257902;
CREATE TABLE test.table1 (id int);
# at 208
#060510 13:45:03 server id 2  log_pos 208       Query   thread_id=4     exec_time=0
# use test;
# LOAD DATA INFILE '/tmp/load_data_infile.txt' INTO TABLE `table1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' STARTING BY
 '' (id);
# file_id: 1  block_len: 8
# at 308
#060510 13:45:03 server id 2  log_pos 308
#Exec_load: file_id=1
#060510 13:45:03 server id 2  log_pos 208       Query   thread_id=4     exec_time=0
use test;
LOAD DATA LOCAL INFILE '/tmp/load_data_infile.txt-1-0' INTO TABLE `table1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' STARTING BY '' (id);
# file_id: 1  block_len: 8

I've added the following note to the manual for 4.1: 

<literal>LOAD DATA INFILE</literal> does not replicate correctly when <literal>--binlog-do-db</literal> is used. (Bug #19662)