Slave stops when reaching a LOAD DATA INFILE command.
Hello all,
I would appreciate it very much if anybody can help me with the following problem. I have a replication setup with a 4.0.23 master and a 4.0.24 slave and replication runs fine until it hits a LOAD DATA INFILE statement at which point it just hangs in there without doing anything, not even throwing an error, just like if it had finished the master's binary log which, of course, it hasn't.
Here are the details:
SLAVE:
mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Master_Host: master-name
Master_User: replication-user
Master_Port: 3306
Connect_retry: 60
Master_Log_File: master-name-bin.150
Read_Master_Log_Pos: 14623241
Relay_Log_File: slave-name-relay-bin.001
Relay_Log_Pos: 1113
Relay_Master_Log_File: master-name-bin.150
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_do_db:
Replicate_ignore_db:
Last_errno: 0
Last_error:
Skip_counter: 0
Exec_master_log_pos: 14623241
Relay_log_space: 1113
1 row in set (0.00 sec)
One can wait forever and the value of Exec_master_log_pos will not change. If we try to check what it is doing:
mysql> SHOW PROCESSLIST \G
*************************** 1. row ***************************
Id: 14
User: mysql
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: SHOW PROCESSLIST
*************************** 2. row ***************************
Id: 16
User: system user
Host:
db: NULL
Command: Connect
Time: 221
State: Has read all relay log; waiting for the I/O slave thread to update it
Info: NULL
2 rows in set (0.00 sec)
It says it has read the whole relay log and it may well have, but there is a lot more in the master's binary log. Here it is the output from running the mysqlbinlog command on the master:
MASTER
# at 14623241
#050429 1:39:34 server id 1 log_pos 14623241 Query thread_id=5 exec_time=1
# use DbName;
# LOAD DATA INFILE '/var/webapps/ware/load//datamart/tablename/TableName_new.txt' REPLACE INTO TABLE `TableName` FIELDS TERMINATED BY '|' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' STARTING BY '' (field1,field2,field3,field4,field5,field6,field7,field8,field9,field10,field11);
# file_id: 9 block_len: 2097152
# at 16720740
#050429 1:39:34 server id 1 log_pos 16720740
#Append_block: file_id: 9 block_len: 2097152
# at 18817915
#050429 1:39:34 server id 1 log_pos 18817915
#Append_block: file_id: 9 block_len: 1424969
# at 20242907
#050429 1:39:34 server id 1 log_pos 20242907
#Exec_load: file_id=9
#050429 1:39:34 server id 1 log_pos 14623241 Query thread_id=5 exec_time=1
I can use the CHANGE MASTER TO command to skip to the next event in the log and then the slave will happily replicate until it hits a new LOAD DATA INFILE command, at which point the same thing will happen again.
I am really puzzled by this because before we used to have a similar setup but with the master running 3.23.56 and this problem did not exist. It was supposed to work better now since the whole file to be loaded was supposed to be copied into the binary log in 4.0 (am I missing some setting somewhere?).
Somebody must have had this problem before but I can not find anything like this in the documentation, so I would really appreciate any help that anybody could provide.
Thanks a lot,
Manuel Villasante