Bug #49652 LOAD DATA - missing schema name in binlog can break replication
Submitted: 14 Dec 2009 0:32 Modified: 14 Dec 2009 12:41
Reporter: Victor Kirkebo Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:6.0-backup, 6.0.14 OS:Any
Assigned to: Luis Soares CPU Architecture:Any
Tags: binlog, LOAD DATA LOCAL INFILE

[14 Dec 2009 0:32] Victor Kirkebo
Description:
When issuing a LOAD DATA LOCAL INFILE statement - e.g LOAD DATA LOCAL INFILE '/tmp/data.txt' into table mydb1.tb1; the schema name (mydb1) is missing in the binlog in the 6.0-backup branch. This can break replication as shown in "How to repeat" below.

How to repeat:
mysql> show master status;
+-------------------+-----------+--------------+------------------+
| File              | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+-----------+--------------+------------------+
| master-bin.000001 | 124712692 |              |                  |
+-------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> create database mydb1;
Query OK, 1 row affected (0.00 sec)

mysql> use mydb1;
Database changed
mysql> create table tb1(f1 varchar(10));
Query OK, 0 rows affected (0.00 sec)

mysql> create database mydb2;
Query OK, 1 row affected (0.00 sec)

mysql> use mydb2;
Database changed
mysql> load data local infile '/tmp/data.txt' into table mydb1.tb1;
Query OK, 1 row affected (0.00 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

mysql> show binlog events from 124712692 limit 5;
+-------------------+-----------+--------------------+-----------+-------------+----------------------------------------
------------------------------------------------------------------------------------------------------------------------
----------------+
| Log_name          | Pos       | Event_type         | Server_id | End_log_pos | Info

                |
+-------------------+-----------+--------------------+-----------+-------------+----------------------------------------
------------------------------------------------------------------------------------------------------------------------
----------------+
| master-bin.000001 | 124712692 | Query              |         1 |   124712777 | create database mydb1

                |
| master-bin.000001 | 124712777 | Query              |         1 |   124712873 | use `mydb1`; create table tb1(f1 varcha
r(10))
                |
| master-bin.000001 | 124712873 | Query              |         1 |   124712958 | create database mydb2

                |
| master-bin.000001 | 124712958 | Begin_load_query   |         1 |   124712985 | ;file_id=43;block_len=4

                |
| master-bin.000001 | 124712985 | Execute_load_query |         1 |   124713211 | use `mydb2`; LOAD DATA LOCAL INFILE '/t
mp/data.txt' IGNORE INTO TABLE `tb1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (
f1) ;file_id=43 |
+-------------------+-----------+--------------------+-----------+-------------+----------------------------------------
------------------------------------------------------------------------------------------------------------------------
----------------+
5 rows in set (0.00 sec)

slave error log:
-----------------
091214  2:33:00 [ERROR] Slave SQL: Error 'Table 'mydb2.tb1' doesn't exist' on query. Default database: 'mydb2'. Query: '
LOAD DATA INFILE '../../tmp/SQL_LOAD-2-1-43.data' IGNORE INTO  TABLE `tb1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCA
PED BY '\\' LINES TERMINATED BY '\n' (f1)', Error_code: 1146
091214  2:33:00 [ERROR] Slave: Table 'mydb2.tb1' doesn't exist Error_code: 1146
091214  2:33:00 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread
 with "SLAVE START". We stopped at log 'master-bin.000001' position 124712985
[14 Dec 2009 6:37] Sveta Smirnova
Thank you for the report.

Verified as described using latest 6.0-backup tree. Workaround: use binlog_format=row
[14 Dec 2009 11:45] Luis Soares
Most likely a duplicate of BUG#48297.
[14 Dec 2009 12:41] Luis Soares
I have built latest mysql-6.0-backup:
revision-id: charles.bell@sun.com-20091212171821-aofqeg53jcwvag7a

Then, I created a test (based on the one reported):

-- source include/master-slave.inc
-- source include/have_binlog_format_statement.inc
create database mydb1;
use mydb1;
create table tb1(f1 varchar(10));
create database mydb2;
use mydb2;
-- eval load data local infile '$MYSQLTEST_VARDIR/std_data/words.dat' into table mydb1.tb1
-- sync_slave_with_master
-- exit

Then, I run the test against the built mysql-6.0-backup tree. I got this:

Last_SQL_Error	Error 'Table 'mydb2.tb1' doesn't exist' on query. Default database: 'mydb2'. Query: 'LOAD DATA INFILE '../../tmp/SQL_LOAD-2-1-1.data' IGNORE INTO  TABLE `tb1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (f1)'

At this point I patched mysql-6.0-backup with the patch for BUG#48297, and
rebuilt it. Then, I run the same test again:

rpl.rpl_bug49652 'stmt'                  [ pass ]    157

Conclusion: This is a duplicate of BUG#48297.
[14 Dec 2009 12:45] Luis Soares
Indeed, BUG#48297 cset does not appear in revision history for mysql-6.0-backup (in revision-id: charles.bell@sun.com-20091212171821-aofqeg53jcwvag7a):

$ bzr log -n0 --line | grep 48297
$