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 19:07	Modified:	30 Jun 2004 18:21
Reporter:	[ name withheld ]
Status:	Closed
Category:	Server: Replication	Severity:	S2 (Serious)
Version:	4.0.20	OS:	Linux (RedHat Linux (MySQL rpms))
Assigned to:	Guilhem Bichot	Target Version:

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