MAX_PACKET_LEN is too big and can cause connection aborts on large LOAD DATA LOCAL

[John-Nagle]

I tried a large LOAD DATA LOCAL, and got "An existing connection was forcibly closed by the remote host" from the place where connection.py writes the data to the connection. Looking at the code, the LOAD DATA handler breaks up the data into chunks of size MAX_PACKET_LEN, which is 2**24-1, or about 16MB. So it's trying to ship the file to the server in 16MB chunks. Max packet size is a server configuration parameter (max_allowed_packet), and the default is 1MB. Sending too big a packet causes a connection abort.

"max_allowed_packet" can be configured larger on the server (gigabytes if you have the RAM) but you can't hard-code 16MB and be confident it's going to be that big.

I monkey-patched pymysql with

pymysql.connections.MAX_PACKET_LEN = 65535    

to get around the problem, and that worked. It's probably reasonable to set it to 1048576, the MySQL default, minus some tolerance (like 1024) for headers. Few people will reduce max_allowed_packet below the default. You can also ask the server for the value of "max_allowed_packet".

Stack backtrace follows:

24:49.31 [MainThread] Loading database file: "
24:49.31 [MainThread] LOAD DATA LOCAL INFILE "c:\users\nagle\appdata\local\temp\tmpv7sfb1.txt" REPLACE INTO TABLE companyindex FIELDS ENCLOSED BY '"' ESCAPED BY '' TERMINATED BY ','
24:49.31 [MainThread](conformed_company_name, domain, street_number, street_name, parser_name, location, state, postal_code, country_code, database_name, record_id)
24:49.31 [MainThread] "
24:49.35 [MainThread] LOAD DATA LOCAL failed: (2006, "MySQL server has gone away (error(10054, 'An existing connection was forcibly closed by the remote host'))")
24:49.35 [MainThread] Traceback (most recent call last):
24:49.35 [MainThread] File "C:\projects\sitetruth\InfoCompanyDb.py", line 315, in databaseload
24:49.35 [MainThread] outcursor.execute(s) # do it
24:49.35 [MainThread] File "C:\python27\lib\site-packages\pymysql\cursors.py", line 134, in execute
24:49.35 [MainThread] result = self._query(query)
24:49.35 [MainThread] File "C:\python27\lib\site-packages\pymysql\cursors.py", line 282, in _query
24:49.35 [MainThread] conn.query(q)
24:49.35 [MainThread] File "C:\python27\lib\site-packages\pymysql\connections.py", line 768, in query
24:49.35 [MainThread] self._affected_rows = self._read_query_result(unbuffered=unbuffered)
24:49.35 [MainThread] File "C:\python27\lib\site-packages\pymysql\connections.py", line 929, in _read_query_result
24:49.35 [MainThread] result.read()
24:49.35 [MainThread] File "C:\python27\lib\site-packages\pymysql\connections.py", line 1130, in read
24:49.35 [MainThread] self._read_load_local_packet(first_packet)
24:49.35 [MainThread] File "C:\python27\lib\site-packages\pymysql\connections.py", line 1165, in _read_load_local_packet
24:49.35 [MainThread] sender.send_data()
24:49.35 [MainThread] File "C:\python27\lib\site-packages\pymysql\connections.py", line 1306, in send_data
24:49.35 [MainThread] self.connection._write_bytes(packet)
24:49.35 [MainThread] File "C:\python27\lib\site-packages\pymysql\connections.py", line 916, in _write_bytes
24:49.35 [MainThread] raise err.OperationalError(2006, "MySQL server has gone away (%r)" % (e,))
24:49.35 [MainThread] OperationalError: (2006, "MySQL server has gone away (error(10054, 'An existing connection was forcibly closed by the remote host'))")

[John-Nagle]

Thanks for the quick fix.