Socket hang on very long SQL statements

[Brent Noorda]

When SQL statements get very long, there are problems with what is
sent through the socket to the mysql server, and worse problems in
what comes back, or does not. A minor problem I find on an Ubuntu
server (which didn't snow on Mac) was a seeming limit on the size of
packets can go out the socket. A much bigger problem that should show
up on any system is the calculation of headers that are sent over the
socket.

Here's the patch I'm using for connections.py that works real good for
me. I'd appreciate hearing from anyone for whom it works or doesn't.


--- connections.py.old 2011-04-14 15:08:37.000000000 -0700
+++ connections.py.new 2011-04-14 15:09:50.000000000 -0700
@@ -50,7 +50,7 @@
UNSIGNED_INT64_LENGTH = 8

DEFAULT_CHARSET = 'latin1'
-MAX_PACKET_LENGTH = 256*256*256-1
+MAX_PACKET_LENGTH = 256*64


def dump_packet(data):
@@ -698,18 +698,14 @@
if isinstance(sql, unicode):
sql = sql.encode(self.charset)

- buf = int2byte(command) + sql
- pckt_no = 0
- while len(buf) >= MAX_PACKET_LENGTH:
- header = struct.pack('<i', MAX_PACKET_LENGTH)
[:-1]+int2byte(pckt_no)
- send_data = header + buf[:MAX_PACKET_LENGTH]
- self.socket.send(send_data)
- if DEBUG: dump_packet(send_data)
- buf = buf[MAX_PACKET_LENGTH:]
- pckt_no += 1
- header = struct.pack('<i', len(buf))[:-1]+int2byte(pckt_no)
- self.socket.send(header+buf)
-
+ if len(sql) <= (MAX_PACKET_LENGTH-5):
+ self.socket.send( struct.pack('<i', len(sql)+1) +
int2byte(command) + sql )
+ else:
+ self.socket.send( struct.pack('<i', len(sql)+1) +
int2byte(command) )
+ while len(sql) > MAX_PACKET_LENGTH:
+ self.socket.send(sql[:MAX_PACKET_LENGTH])
+ sql = sql[MAX_PACKET_LENGTH:]
+ self.socket.send(sql)

#sock = self.socket
#sock.send(send_data)

[Pete Hunt]

I will check this out later today - thanks for the heads up!

Pete

[Pete Hunt]

Hi Brent - I can't reproduce this behavior. Can you submit a test case?

Pete