Leftover trailing OK packet for "CALL my_sp" queries #95

Closed
aigr opened this Issue Dec 21, 2011 · 4 comments

Comments

Projects
None yet
5 participants

aigr commented Dec 21, 2011

How to reproduce

Create a stored procedure along the lines of

DELIMITER ;;
CREATE PROCEDURE `foo` ()
BEGIN
    SELECT * FROM bar;
END
;;
DELIMITER ;

Now run the query along the lines of

cnxn = connect(*args, **kwargs)
cursor = cnxn.cursor()
cursor.execute("CALL foo()")
# This will not work as expected since the previous call to execute()
# left a trailing OK packet on the socket. Thus we will get an error here.
cursor.execute("SELECT * FROM bar")

The MySQL server first sends a result set packet (for the SELECt) and then also an OK packet (for the CALL). The OK packet is not read from the socket and is therefore left over there for the next command to unexpectedly find.

What to do

I really don't know what the correct response to this is.

A temporary work-around might be:

from socket import MSG_DONTWAIT, MSG_PEEK, error
from struct import unpack

try:
    from pymysql.connections import MySQLResult
    from pymysql.err import Error, OperationalError
    from pymysql.util import byte2int
except (ImportError, TypeError):

    class MySQLResult(object):
        pass

    class Error(Exception):
        pass

    class OperationalError(Error):
        pass

    def byte2int(b):
        if isinstance(b, int):
            return b
        else:
            return unpack("!B", b)[0]


def _read_result_packet(self):
    self.field_count = byte2int(self.first_packet.read(1))
    self._get_descriptions()
    self._read_rowdata_packet()
    # This is the local adaptation.
    self._check_for_trailing_ok()


def _check_for_trailing_ok(self):
    try:
        buf = self.connection.socket.recv(4, MSG_DONTWAIT | MSG_PEEK)
    except error:
        # No data available on socket. All is well.
        return
    if len(buf) != 4:
        raise Error("Strange trailing data", 1, buf)
    try:
        packet = self.connection.read_packet()
    except OperationalError:
        raise Error("Strange trailing data", 2, buf)
    if not packet.is_ok_packet():
        raise Error("Unexpected packet type", packet._MysqlPacket__data)
    # Gobbled up the trailer. All is well.


MySQLResult._read_result_packet = _read_result_packet
MySQLResult._check_for_trailing_ok = _check_for_trailing_ok

damoxc commented Dec 31, 2011

Sadly this method doesn't play nicely with gevent. A workaround I've found is to call next_result manually on the connection, but that's not very elegant unfortunately. The comments for callproc in the cursor source do state that there will be an empty resultset created by each stored procedure call so you should call nextset() after it, however calling that on the cursor doesn't work properly as _has_next is set to 0.

Contributor

pdunnigan commented Apr 30, 2012

+1, had to add next_result() call as well. Snippet:
....
c = self.conn.cursor(pymysql.cursors.DictCursor)
c.execute(sql, params)
self.conn.commit()
result = c.fetchall()
c.close()
self.conn.next_result()
....

I haven't managed to get this to work myself. I'm performing something like :

cursor.callproc("SelectRows",(1,10))
rows = cursor.fetchall()
cursor.callproc("SelectRows",(11,20))
rows = cursor.fetchall()
followed by
cursor.execute("SELECT 0")
rows = cursor.fetchall()

The "execute" stalls because of a lost connection.

I've tried inserting lines as follows after each 'fetchall' but without success.
while cursor.nextset():
pass

and also

connection.next_result()

..

I've found that I can use my Stored Procedures to "UPDATE" and "INSERT" but selecting rows and getting results is the problem. I can also use parameters to "SELECT" results "INTO". It is simply "SELECT"ing rows and then using "fetchall" that has a problem.

@methane methane added a commit that referenced this issue Sep 30, 2013

@methane methane Add test for #95. 181d37b

@methane methane added a commit that referenced this issue Sep 30, 2013

@methane methane Fix test for issue #95 b0d9a8d
Contributor

methane commented Sep 30, 2013

This issue was fixed via f099813

methane closed this Sep 30, 2013

@czerwingithub czerwingithub pushed a commit to scalyr/PyMySQL that referenced this issue Sep 20, 2014

@methane methane Add test for #95. 8eeb703

@czerwingithub czerwingithub pushed a commit to scalyr/PyMySQL that referenced this issue Sep 20, 2014

@methane methane Fix test for issue #95 50b17ca
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment