MySQL Bugs: #80700: Compression causes some queries to crash.

Bug #80700	Compression causes some queries to crash.
Submitted:	11 Mar 2016 5:42	Modified:	11 Aug 2016 12:41
Reporter:	Sam King	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / Python	Severity:	S3 (Non-critical)
Version:	2.1.3	OS:	Ubuntu
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	compression

Description:
I have a large database and a query that returns a lot of results.  The query looks like:

>>> query = 'SELECT a, b, c, d, e, f, g, h, i, j, k FROM mytable WHERE m IN (1,2) AND n=2014 AND o=3 ORDER BY i DESC'

(variable and table names changed, of course)

If I run this query in a compressed connection, it crashes.  If I run this query on a non-compressed connection, it works perfectly.  

I am using MySQL 5.6 and Python Connector 2.1.3.  

The stack trace is below.

>>> conn = mysql.connector.connect(user='root', db='mydb', host='localhost', compress=True)
>>> cur = conn.cursor(buffered=True, dictionary=True)
>>> cur.execute(query)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/lib/python2.7/dist-packages/mysql/connector/cursor.py", line 515, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/usr/lib/python2.7/dist-packages/mysql/connector/connection.py", line 488, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "/usr/lib/python2.7/dist-packages/mysql/connector/connection.py", line 385, in _handle_result
    return self._handle_ok(packet)
  File "/usr/lib/python2.7/dist-packages/mysql/connector/connection.py", line 327, in _handle_ok
    ok_pkt = self._protocol.parse_ok(packet)
  File "/usr/lib/python2.7/dist-packages/mysql/connector/protocol.py", line 215, in parse_ok
    (packet, ok_packet['insert_id']) = utils.read_lc_int(packet)
  File "/usr/lib/python2.7/dist-packages/mysql/connector/utils.py", line 315, in read_lc_int
    return (buf[9:], struct_unpack('<xQ', buf[0:9])[0])
  File "/usr/lib/python2.7/dist-packages/mysql/connector/catch23.py", line 89, in struct_unpack
    return struct.unpack_from(fmt, buffer(buf))
struct.error: unpack_from requires a buffer of at least 9 bytes

I'm sorry that I don't have the time right now to investigate what the root cause might be or to provide a sample database that reproduces the issue -- if you have trouble finding the issue, let me know, and I can try to make a synthetic data set that reproduces the issue.

How to repeat:
Use a particular type of query with a compressed connection.

Suggested fix:
No idea.

Hello  Sam King,

Thank you for the bug report.
Could you please provide repeatable test case (create table statements, sample data etc. - please make it as private if you prefer) to confirm this issue at our end?

Thanks,
Chiranjeevi.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I can't find a way to re-open the ticket, I hope adding a comment will poke someone who can re-open it.
Here is a snipet of code you can do on a genomic public database.

Take note that when I made this the error i was getting is different from the one in the original post but it seems a bit random about that but both are raised when querying in compressed mode. When I came here I was getting the same error which is "struct.error: unpack_from requires a buffer of at least X bytes".

At first I was using the pure python connector and then changed for the compiled one version 2.1.3 and I was having the same issue.

# Python 2.7.11 [GCC 5.3.0]
# mysql-connector-python 2.0.3 "I hope you're already know we can't install 2.0.4 with pip without using the git repository"
# Code {{{
from mysql.connector import MySQLConnection

long_query = ("SELECT species_id,"
              "    db_name,"
              "    db_assembly,"
              "    db_type,"
              "    production_name,"
              "    web_name,"
              "    scientific_name "
              "FROM ensembl_production_84.species "
              "JOIN ensembl_production_84.db "
              "  USING(species_id) "
              "WHERE db_release = 84")
simple_query = "SELECT * from ensembl_production_84.db"
db_infos = {'host':'useastdb.ensembl.org',
            'user':'anonymous'}

def long_query_uncompressed():
    print "Executing a long query in uncompressed mode"
    print long_query
    conn = MySQLConnection(**db_infos)
    cursor = conn.cursor()
    cursor.execute(long_query)
    print cursor.fetchall()
    # ... Result are printed

def long_query_compressed():
    print "Executing a long query in compressed mode"
    print long_query
    conn = MySQLConnection(compress=True, buffered=True, **db_infos)
    cursor = conn.cursor()
    cursor.execute(long_query)
    print cursor.fetchall()
    #Traceback (most recent call last):
    #  File "<input>", line 1, in <module>
    #    long_query_compressed()
    #  File "<input>", line 6, in long_query_compressed
    #    cursor.execute(long_query)
    #  File "/usr/lib/python2.7/site-packages/mysql/connector/cursor.py", line 507, in execute
    #    self._handle_result(self._connection.cmd_query(stmt))
    #  File "/usr/lib/python2.7/site-packages/mysql/connector/cursor.py", line 421, in _handle_result
    #    self._handle_resultset()
    #  File "/usr/lib/python2.7/site-packages/mysql/connector/cursor.py", line 897, in _handle_resultset
    #    (self._rows, eof) = self._connection.get_rows()
    #  File "/usr/lib/python2.7/site-packages/mysql/connector/connection.py", line 671, in get_rows
    #    rows = self._protocol.read_text_result(self._socket, count)
    #  File "/usr/lib/python2.7/site-packages/mysql/connector/protocol.py", line 310, in read_text_result
    #    if packet.startswith(b'\xff\xff\xff'):
    #AttributeError: 'NoneType' object has no attribute 'startswi    

def simple_query_uncompressed():
    print "Executing a simple query in uncompressed mode"
    print simple_query
    conn = MySQLConnection(**db_infos)
    cursor = conn.cursor()
    cursor.execute(simple_query)
    print cursor.fetchall()
    # ... Results are printed

def simple_query_compressed():
    print "Executing a simple query in compressed mode"
    print simple_query
    conn = MySQLConnection(compress=True, **db_infos)
    cursor = conn.cursor()
    cursor.execute(simple_query)
    print cursor.fetchall()
    #Traceback (most recent call last):
    #  File "<input>", line 1, in <module>
    #    cursor.fetchall()
    #  File "/usr/lib/python2.7/site-packages/mysql/connector/cursor.py", line 825, in fetchall
    #    (rows, eof) = self._connection.get_rows()
    #  File "/usr/lib/python2.7/site-packages/mysql/connector/connection.py", line 671, in get_rows
    #    rows = self._protocol.read_text_result(self._socket, count)
    #  File "/usr/lib/python2.7/site-packages/mysql/connector/protocol.py", line 310, in read_text_result
    #    if packet.startswith(b'\xff\xff\xff'):
    #AttributeError: 'NoneType' object has no attribute 'startswith'

Hello  Naus Caligo,

Thank you for your feedback.
Could you please provide repeatable test case (create table statements, sample data etc. - please make it as private if you prefer) to confirm this issue at our end?

Thanks,
Chiranjeevi.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".