| 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: | |
| 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 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 $
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