Broken pipe for large blobs

[fabiansinz]

When inserting a large blob (encoded as a hexadecimal string) via pymysql, I get a broken pipe error.

This is the stack trace:

--> 115         cur.execute(query, args)
    116 
    117         return cur

/home/sinz/computations/aod_cell_detection/aod/lib/python3.4/site-packages/pymysql/cursors.py in execute(self, query, args)
    144         query = self.mogrify(query, args)
    145 
--> 146         result = self._query(query)
    147         self._executed = query
    148         return result

/home/sinz/computations/aod_cell_detection/aod/lib/python3.4/site-packages/pymysql/cursors.py in _query(self, q)
    294         conn = self._get_db()
    295         self._last_executed = q
--> 296         conn.query(q)
    297         self._do_get_result()
    298         return self.rowcount

/home/sinz/computations/aod_cell_detection/aod/lib/python3.4/site-packages/pymysql/connections.py in query(self, sql, unbuffered)
    778             else:
    779                 sql = sql.encode(self.encoding, 'surrogateescape')
--> 780         self._execute_command(COMMAND.COM_QUERY, sql)
    781         self._affected_rows = self._read_query_result(unbuffered=unbuffered)
    782         return self._affected_rows

/home/sinz/computations/aod_cell_detection/aod/lib/python3.4/site-packages/pymysql/connections.py in _execute_command(self, command, sql)
    968 
    969         prelude = struct.pack('<iB', chunk_size, command)
--> 970         self._write_bytes(prelude + sql[:chunk_size-1])
    971         if DEBUG: dump_packet(prelude + sql)
    972 

/home/sinz/computations/aod_cell_detection/aod/lib/python3.4/site-packages/pymysql/connections.py in _write_bytes(self, data)
    927             self.socket.sendall(data)
    928         except IOError as e:
--> 929             raise err.OperationalError(2006, "MySQL server has gone away (%r)" % (e,))
    930 
    931     def _read_query_result(self, unbuffered=False):

OperationalError: (2006, "MySQL server has gone away (BrokenPipeError(32, 'Broken pipe'))")

I set max_allowed_packet=1024M for the mysql server/client and max_allowed_packet=1024 ** 3 for pymysql. Therefore, this cannot be the problem. Also, when I dump the query in an SQL file and pipe that into the mysql client, the insert works fine.

I also read about the previous issue with broken pipe, but this seemed to apply to an older version. I have pymysql.__version__=='0.6.7.None'.

Any idea what could cause this?

[methane]

Is there any logs in mysql.err log?

[fabiansinz]

No, nothing. Neither in the mysql.log nor in the error.log. I am also not sure whether it is a mysql problem because I can insert the same blob from the commandline (without pymysql). Can it be a problem with the socket? I am asking because it happens exactly at the socket.sendall command. I don't know much about sockets though.

[methane]

"Broken Pipe" means TCP connection has been broken.
OS or MySQL server may kill the connection in some reason.

Could you create a script to reproduce your problem?
How large your hex data?
How long it happens after you start the insert statement?

[fabiansinz]

Sure, here's the script to reproduce my problem. Uncompressed, the numpy array should be about 30M large. It is a bit, but it is not that large.

import pymysql as client
import binascii
import zlib
import numpy as np

conn_info = {'host': 'localhost',
             'max_allowed_packet': 1073741824,
             'passwd': '****',
             'port': 3306,
             'user': 'user'}

conn = client.connect(**conn_info)
with conn.cursor() as cur:
    cur.execute("CREATE DATABASE IF NOT EXISTS `bigblob`")
    cur.execute('CREATE TABLE IF NOT EXISTS `bigblob`.`test` (`idx` int NOT NULL, `values` longblob NOT NULL, PRIMARY KEY (`idx`)) ENGINE = InnoDB, COMMENT ""')
    value = zlib.compress(np.random.randn(250*250*60))
    blob = '0x' + binascii.b2a_hex(value).decode('ascii')
    query = """INSERT INTO `bigblob`.`test` (`idx`,`values`) VALUES (%%s,%s)""" % (blob,)

    cur.execute(query, args=(0,))
conn.commit()
conn.close()

[jenstroeger]

Check that your innodb_log_file_size is large enough. A packet should not be larger than 10% of the transaction log. I had similar issues and stumbled over this. (I used CyMySQL and filed a similar issue there, not sure this might be related.)

[fabiansinz]

Thanks for the hint, but I don't think that is the problem. I increased my innodb_log_file_size to 1024M and I still have the same problem.

[fabiansinz]

With pymysql 0.7.2. it works. Could you confirm that your max_allowed_packet is the packet size for the TCP socket and has nothing to do with the max_allowed_packet in the MySQL server settings? Just so I understand what was happening. Thanks.