Description:
mysqlbinlog behavior is essentialy different in case when data read locally and in the case when data are fetched from server.
For example when fetching from server --database option is ignored, also in remote case LOAD DATA info is not shown properly and temporary files are not created.
How to repeat:
Let us assume that we have table people in database test (exact table stucture is not essential).
Start MySQL server (assume 192.168.100.1 is localhost and ../../mysql_data is data directory), and perform next queries:
[dlenev@dlenev mysql-4.0-clean]$ client/mysql -u root -h 192.168.100.1
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.16-debug-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> load data infile './people.txt' into table test.people;
Query OK, 4 rows affected (0.01 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
mysql> update test.people set fullname='test'+id;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)
mysql> Bye
[dlenev@dlenev mysql-4.0-clean]$ client/mysqlbinlog -u root -h 192.168.100.1 dlenevrepl.001
#700101 3:00:00 server id 1 log_pos 0 Rotate to dlenevrepl.001 pos: 4
#030923 18:30:28 server id 1 log_pos 4 Start: binlog v 3, server v 4.0.16-debug-log created 030923 18:30:28 at startup
file_id: 1 block_len: 38
#030923 18:30:32 server id 1 log_pos 206
#Exec_load: file_id=1
#030923 18:30:36 server id 1 log_pos 229 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1064327436;
update test.people set fullname='test'+id;
#030923 18:30:42 server id 1 log_pos 301 Rotate to dlenevrepl.002 pos: 4
#700101 3:00:00 server id 1 log_pos 0 Rotate to dlenevrepl.002 pos: 4
[dlenev@dlenev mysql-4.0-clean]$ client/mysqlbinlog -u root -h 192.168.100.1 --database=test dlenevrepl.001
#700101 3:00:00 server id 1 log_pos 0 Rotate to dlenevrepl.001 pos: 4
#030923 18:30:28 server id 1 log_pos 4 Start: binlog v 3, server v 4.0.16-debug-log created 030923 18:30:28 at startup
file_id: 1 block_len: 38
#030923 18:30:32 server id 1 log_pos 206
#Exec_load: file_id=1
#030923 18:30:36 server id 1 log_pos 229 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1064327436;
update test.people set fullname='test'+id;
#030923 18:30:42 server id 1 log_pos 301 Rotate to dlenevrepl.002 pos: 4
#700101 3:00:00 server id 1 log_pos 0 Rotate to dlenevrepl.002 pos: 4
[dlenev@dlenev mysql-4.0-clean]$ client/mysqlbinlog ../../mysql_data/dlenevrepl.001
# at 4
#030923 18:30:28 server id 1 log_pos 4 Start: binlog v 3, server v 4.0.16-debug-log created 030923 18:30:28 at startup
# at 79
# use test;
# LOAD DATA INFILE './people.txt' INTO TABLE people FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' STARTING BY '' (id,fullname);
# file_id: 1 block_len: 38
# at 206
#030923 18:30:32 server id 1 log_pos 206
#Exec_load: file_id=1
LOAD DATA LOCAL INFILE '/home/dlenev/tmp/people.txt-1-2 ' INTO TABLE people FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' STARTING BY '' (id,fullname);
# file_id: 1 block_len: 38
# at 229
#030923 18:30:36 server id 1 log_pos 229 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1064327436;
update test.people set fullname='test'+id;
# at 301
#030923 18:30:42 server id 1 log_pos 301 Rotate to dlenevrepl.002 pos: 4
[dlenev@dlenev mysql-4.0-clean]$ client/mysqlbinlog --database=test ../../mysql_data/dlenevrepl.001
# at 4
#030923 18:30:28 server id 1 log_pos 4 Start: binlog v 3, server v 4.0.16-debug-log created 030923 18:30:28 at startup
# at 79
# use test;
# LOAD DATA INFILE './people.txt' INTO TABLE people FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' STARTING BY '' (id,fullname);
# file_id: 1 block_len: 38
# at 206
#030923 18:30:32 server id 1 log_pos 206
#Exec_load: file_id=1
LOAD DATA LOCAL INFILE '/home/dlenev/tmp/people.txt-1-3 ' INTO TABLE people FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' STARTING BY '' (id,fullname);
# file_id: 1 block_len: 38
# at 229
# at 301
#030923 18:30:42 server id 1 log_pos 301 Rotate to dlenevrepl.002 pos: 4
As you see output is quite different (local vs remote)
Suggested fix:
All event processing logic in the mysqlbinlog.cc should be extracted in one function and should be used in both remote and local cases.
