Lost connection to MySQL server during query
MySQL database connector for Python programming
Brought to you by:
adustman
Menu
▾
▴
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 2.4.3 (#1, Jul 27 2009, 17:57:39)
on linux2
Type "help", "copyright", "credits" or "license" for more information.
...
... 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"
... 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()
...
(1, 2, 3, 'gamma', 1)
('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
('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
('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)
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.
If by "osbcure" you mean "documented", then yes.
http://www.python.org/dev/peps/pep-0249/
http://mysql-python.sourceforge.net/MySQLdb.html#cursor-objects
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.