MySQL Bugs: #4326: Replicated LOAD DATA INFILE show nothing in processlist on slave

Bug #4326	Replicated LOAD DATA INFILE show nothing in processlist on slave
Submitted:	29 Jun 2004 17:07	Modified:	30 Jun 2004 16:21
Reporter:	[ name withheld ]	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	4.0.20	OS:	Linux (RedHat Linux (MySQL rpms))
Assigned to:	Guilhem Bichot	CPU Architecture:	Any

Description:
When replicating a LOAD DATA INFILE the slave shows nothing in processlist which suggests that the SQL_LOAD*.data temp file is being created.

For example on a large LOAD DATA INFILE being replicated between two servers the processlist contains:

| Id   | User        | Host       | db                | Command | Time   |
State                            | Info             |
+------+-------------+------------+-------------------+---------+--------+------
| 8044 | system user |            | mailer            | Connect | 26106  |
NULL                             | NULL             |
+------+-------------+------------+-------------------+---------+--------+------

At our site this lead someone to believe that MySQL was hung since nothing appeared to be happening for a long period of time and the LOAD DATA INFILE was very resource intensive on the machine which caused them to kill MySQL and create a db inconsistency.

How to repeat:
Replicate a fairly large LOAD DATA INFILE between two 4.0.20 servers. Do a show processlist on the client while it is working on that entry in the binlog.

Suggested fix:
Add some text to the State/Info columns in show processlist stating that $TMPPATH/SQL_LOAD-XX-XX-XX.data is being created or something similar so that the user knows that something is happening.

Ok. It would be quite hard for us to print the exact LOAD DATA INFILE statement being replicated (because for example when the slave is preparing the temp file it does not have this statement under hand), but we can add a simple message like "preparing temporary file for replication of LOAD DATA INFILE". We'll work on it shortly.

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:

Hi,
In MySQL 4.0.21 you'll get this in SHOW PROCESSLIST:
when we are writing to the tmp file:
  | 3  | system user |                 |    | Connect | 6    | Making temp file /tmp/SQL_LOAD-2-1-2.data |
  and when we are actually loading the .data temp file into the table:
  | 3  | system user |                 | test | Connect | 2    | | LOAD DATA INFILE '/tmp/SQL_LOAD-2-1-2.data'
 INTO TABLE `t` <...> |

Thank you for your bug report.

ChangeSet@1.1865.1.2, 2004-06-30 15:41:35+02:00, guilhem@mysql.com