MySQL Bugs: #37404: LOAD DATA LOCAL + QUAN makes client lose their connection

Bug #37404	LOAD DATA LOCAL + QUAN makes client lose their connection
Submitted:	13 Jun 2008 19:50	Modified:	23 Oct 2008 17:08
Reporter:	Diego Medina	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Enterprise Monitor: Agent	Severity:	S1 (Critical)
Version:	0.7.0.785	OS:	Linux (Fedora 8)
Assigned to:	Kay Roepke	CPU Architecture:	Any

Description:
Sending a "LOAD DATA LOCAL INFILE" statement to a mysqld daemon going through the agent (with QUAN enabled) returns this error: 
"Lost connection to MySQL server during query"

If I remove the agent and send the same statement to the mysqld daemon directly, it finishes without any problem.

How to repeat:
1- Have mysql server running (I'm using 5.0.51a community)
2- Create a database  load_test and create this table:

*********************

create table flightstats (
AirlineID int not null,
UniqueCarrier char(3) not null,
Carrier char(3) not null,
FlightDate date not null,
FlightNum char(5) not null,
TailNum char(8) not null,
ArrDelay double not null,
ArrTime datetime not null,
DepDelay double not null,
DepTime datetime not null,
Origin char(3) not null,
Dest char(3) not null,
Distance int not null,
Cancelled char(1) default 'n'
)
*********************

3- Install the agent to monitor this mysqld
4- Download the files I will be uploading
   *On_Time_On_Time_Performance_1997_10.zip
   *load.sh
   *load.sql

Adjust the username and password in the file  load.sh to meet your setup.

5- Copy all those 3 files to the same folder and from that folder run

[shell] # ./load.sh On_Time_On_Time_Performance_1997_10.zip

wait and see the error before it finishes.

actual load data local infile statement

Attachment: load.sql (text/x-sql), 1.22 KiB.

Referenced in MEM beta README.

Easier steps:

CREATE DATABASE test_1;

CREATE TABLE `t1` (
  `dt` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 LOAD DATA LOCAL INFILE "/Users/wizard/bug37404.txt" INTO TABLE test_1.t1;

This turns out to be rather tricky to fix, because LOAD DATA LOCAL INFILE sends two packets (one with the data but without any indicator of what type the packet is, and one zero-length packet).

To detect this packet we need to place the connection into a special state, read the two packets and pass them on.
Currently the zero-length packet, which is the end-of-data indicator for the server, is silently swallowed, causing the server connection to eventually time out (the client connection hangs).
Combining the two packets to one (slightly illegally) is not possible, because of a packet sanity check in the proxy plugin.

With the fix, the proxy scripts never get to see the LOAD DATA LOCAL INFILE packets, because the handling is just too tricky for now.
Instead we pass all the packets on to the server (behind the back of the script). As far as it is concerned there never was that statement.

Verified fixed on 2.0.0.7083