Bug #1378 mysqlbinlog for remote host is broken
Submitted: 23 Sep 2003 7:40 Modified: 6 Feb 2004 5:19
Reporter: Dmitry Lenev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S3 (Non-critical)
Version:4.0 OS:
Assigned to: Bugs System CPU Architecture:Any

[23 Sep 2003 7:40] Dmitry Lenev
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.
[29 Sep 2003 4:53] Victor Vagin
changeset to mysql-4.0 1.1582 from vva on september 29 2003
for use remote mode with server 3.23
changeset to mysql-3.23 1.11418 from vva on september 29 2003
[5 Feb 2004 0:40] Dmitry Lenev
Added version because without we successfully have forgotten about this problem.
[5 Feb 2004 0:52] Victor Vagin
second version of patch has subj
"bk commit - 4.0 tree (1.1609)"
and was sent for review on October 10 2003
[6 Feb 2004 5:19] Victor Vagin
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Fix will be in 4.0.18