Lost connection to MySQL server during query

Help
Gary Smith
2010-05-25
2012-09-19
  • Gary Smith

    Gary Smith - 2010-05-25

    It's not an error, but a warning, and it happens on each and every
    conn.close() or subsequent conn.cursor() invocation. The first time through
    there is no issue until the conn.close() is called. The second time through it
    happens on conn.cursor(). This happens when using callproc method.

    Here is a simple, but completed example of what I executed in order to create
    the problem. Is this a bug or am I missing something in the docs to prevent
    this error?

    Underlying OS is CentOS 5.4 with current updates. I have also self compiled
    the MySQLdb lib against the currently installed mysql server library.

    python ***

    Python 2.4.3 (#1, Jul 27 2009, 17:57:39)

    on linux2

    Type "help", "copyright", "credits" or "license" for more information.

    !/usr/bin/python

    ...

    """

    ... CREATE DATABASE jack99;

    ... GRANT SELECT ON test.* TO 'jack99'@'%';

    ... DROP USER 'jack99'@'localhost';

    ... CREATE USER 'jack99'@'localhost' IDENTIFIED BY 'crispycreame';

    ... GRANT ALL ON jack99.* TO 'jack99'@'localhost';

    ... GRANT SELECT ON mysql.proc TO 'jack99'@'localhost';

    ... FLUSH PRIVILEGES;

    ...

    ... DELIMITER //

    ... DROP PROCEDURE IF EXISTS jack.sp_addrecord//

    ... CREATE DEFINER='jack99'@'localhost' PROCEDURE jack99.sp_addrecord(

    ... I_email_address VARCHAR(255)

    ... , I_dob DATETIME

    ... , I_count INT

    ... )

    ... BEGIN

    ... DECLARE L_user_id CHAR(36) DEFAULT '00000000-0000-0000-0000-000000000000';

    ...

    ... SELECT UUID() INTO L_user_id;

    ...

    ... SELECT L_user_id;

    ... END//

    ...

    ... DELIMITER ;

    ... """

    "\nCREATE DATABASE jack99;\nGRANT SELECT ON test. TO 'jack99'@'%';\nDROP USER
    'jack99'@'localhost';\nCREATE USER 'jack99'@'localhost' IDENTIFIED BY
    'crispycreame';\nGRANT ALL ON jack99.
    TO 'jack99'@'localhost';\nGRANT SELECT
    ON mysql.proc TO 'jack99'@'localhost';\nFLUSH PRIVILEGES;\n\nDELIMITER
    //\nDROP PROCEDURE IF EXISTS jack.sp_addrecord//\nCREATE
    DEFINER='jack99'@'localhost' PROCEDURE jack99.sp_addrecord(\n I_email_address
    VARCHAR(255)\n , I_dob DATETIME\n , I_count INT\n)\nBEGIN\n DECLARE L_user_id
    CHAR(36) DEFAULT '00000000-0000-0000-0000-000000000000';\n\n SELECT UUID()
    INTO L_user_id;\n\n SELECT L_user_id;\nEND//\n\nDELIMITER ; \n"

    import sys

    import MySQLdb as db

    from datetime import datetime, date, time

    MYSQLHOST = "127.0.0.1"

    MYSQLPORT = "3917"

    MYSQLUSER = "jack99"

    MYSQLPASS = "crispycreame"

    MYSQLDB = "jack99"

    PROCEDURE = "jack99.sp_addrecord"

    def a():

    ... conn = db.connect(host=MYSQLHOST, port=int(MYSQLPORT), user=MYSQLUSER,
    passwd=MYSQLPASS, db=MYSQLDB, connect_timeout=30, init_command="set
    autocommit=0")

    ... conn.show_warnings()

    ... rs = conn.cursor()

    ... rs.callproc(PROCEDURE, ('user@example.com', datetime.now(), 32001))

    ... row = rs.fetchone()

    ... print row

    ... conn.close()

    ...

    print db.version_info

    (1, 2, 3, 'gamma', 1)

    a()

    ('e50e71b2-6835-11df-82f3-005056b11e73',)

    Exception _mysql_exceptions.OperationalError: (2013, 'Lost connection to MySQL
    server during query') in <bound method="" Cursor.__del__="" of="" <MySQLdb.cursors.Cursor="" object="" at="" 0xb7cef50c="">> ignored

    a()

    ('e50ef718-6835-11df-82f3-005056b11e73',)

    Exception _mysql_exceptions.OperationalError: (2013, 'Lost connection to MySQL
    server during query') in <bound method="" Cursor.__del__="" of="" <MySQLdb.cursors.Cursor="" object="" at="" 0xb7cef50c="">> ignored

    a()

    ('e50f41be-6835-11df-82f3-005056b11e73',)

    Exception _mysql_exceptions.OperationalError: (2013, 'Lost connection to MySQL
    server during query') in <bound method="" Cursor.__del__="" of="" <MySQLdb.cursors.Cursor="" object="" at="" 0xb7cef50c="">> ignored

    mysql log ***

    tail -n 0 -f /var/log/mysqld.log

    100525 12:44:12 Aborted connection 81 to db: 'jack99' user: 'jack99' host:
    'localhost' (Got an error reading communication packets)

    100525 12:44:12 Aborted connection 82 to db: 'jack99' user: 'jack99' host:
    'localhost' (Got an error reading communication packets)

    100525 12:44:12 Aborted connection 83 to db: 'jack99' user: 'jack99' host:
    'localhost' (Got an error reading communication packets)

    mysql client ***

    mysql --user=jack99 --database=jack99 --password=crispycreame

    Welcome to the MySQL monitor. Commands end with ; or \g.

    Your MySQL connection id is 84

    Server version: 5.1.35 Source distribution

    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    {jack99} (jack99) >call jack99.sp_addrecord('user@example.com',
    datetime.now(), 32001);

    ERROR 1305 (42000): FUNCTION datetime.now does not exist

    {jack99} (jack99) >call jack99.sp_addrecord('user@example.com', NOW(), 32001);

    +--------------------------------------+

    | L_user_id |

    +--------------------------------------+

    | 2739b3c6-6836-11df-82f3-005056b11e73 |

    +--------------------------------------+

    1 row in set (0.00 sec)

    Query OK, 0 rows affected (0.00 sec)

     
  • Gary Smith

    Gary Smith - 2010-05-26

    After digging through the source code, there is one obscure statement

    YOU MUST call nextset() on the connection prior to closing it when using a
    stored procedure. This should probably be in the documentation in bold letters
    somewhere.

    249 Compatibility warning: The act of calling a stored procedure

    250 itself creates an empty result set. This appears after any

    251 result sets generated by the procedure. This is non-standard

    252 behavior with respect to the DB-API. Be sure to use nextset()

    253 to advance through all result sets; otherwise you may get

    254 disconnected.

     
  • Gary Smith

    Gary Smith - 2010-05-26

    I did start with the documentation, but just missed the little blurb at the
    end of that section for callproc. It's funny how when you find the answer, you
    then easily find the answer. It wasn't until after reading it in the code did
    it finally stand out in the pydocs. If you also reread the comment, I didn't
    say it wasn't in the docs, but rather it should stand out cas it is pretty
    significant for that method.

    Either way, it works now, pretty well for what I'm, doing.

     

Log in to post a comment.