| Bug #74252 | callproc does not support warnings | ||
|---|---|---|---|
| Submitted: | 7 Oct 2014 15:32 | Modified: | 16 Dec 2014 0:37 |
| Reporter: | Olle Nilsson | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / Python | Severity: | S3 (Non-critical) |
| Version: | 2.0.1 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | callproc, stored procedures, warnings | ||
[8 Oct 2014 6:46]
MySQL Verification Team
Hello Olle, Thank you for the report and test case. Thanks, Umesh
[8 Oct 2014 6:48]
MySQL Verification Team
//
mysql> use test
Database changed
mysql>
mysql> DELIMITER //
mysql> CREATE PROCEDURE sp() BEGIN SIGNAL SQLSTATE '01000' SET MESSAGE_TEXT = 'TEST WARNING' ; END //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql>
mysql> call sp();
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+--------------+
| Level | Code | Message |
+---------+------+--------------+
| Warning | 1642 | TEST WARNING |
+---------+------+--------------+
1 row in set (0.00 sec)
[root@cluster-repo mysql-advanced-5.6.22]# rpm -qa|grep mysql-connector
mysql-connector-python-commercial-2.0.1-1.el6.noarch
[root@cluster-repo downloads]# python
Python 2.6.6 (r266:84292, Jan 22 2014, 01:49:05)
[GCC 4.4.7 20120313 (Red Hat 4.4.7-4)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>>
>>> import mysql.connector as mc
>>> cnx = mc.connect(user='root', password='', database='test')
>>>
>>> cur = cnx.cursor()
>>> cnx.get_warnings = True
>>> cur.callproc('sp')
()
>>> w = cur.fetchwarnings()
>>> w
>>>
[16 Dec 2014 0:37]
Paul Dubois
Noted in 2.0.3, 2.1.2 changelogs. When using the callproc() cursor method, warnings generated by statements executed within the procedure or generated by the procedure itself were not available to the client.
Description: When using mysql.connector and the callproc method in the cursor class, warnings generated by statements (or SIGNALed from the Stored Procedure itself) are not available to the client. It might work for warnings generated from statements run in the stored procedure, for example a stray SELECT, but it does not work for warnings generated for queries that does not generate result sets and it does not work for warnings generated in the Stored Procedure using SIGNAL. It seems quite trivial to add, and the naive implementation below works for warnings issued using SIGNAL (but possibly creates duplicates for warnings generated by statements that generate result sets inside the Stored Procedure). How to repeat: mysql> USE test ; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> DELIMITER // mysql> CREATE PROCEDURE sp() BEGIN SIGNAL SQLSTATE '01000' SET MESSAGE_TEXT = 'TEST WARNING' ; END // Query OK, 0 rows affected (0.02 sec) mysql> DELIMITER ; Python 2.7.5+ (default, Feb 27 2014, 19:37:08) [GCC 4.8.1] on linux2 Type "help", "copyright", "credits" or "license" for more information. >>> import mysql.connector as mc >>> cnx = mc.connect(user='root', password='root', database='test') >>> cur = cnx.cursor() >>> cnx.get_warnings = True >>> cur.callproc('sp') () >>> w = cur.fetchwarnings() >>> w >>> With the POC fix below instead: ... >>> w = cur.fetchwarnings() >>> w [[(u'Warning', 1642, u'TEST WARNING')]] ... Suggested fix: try: argnames = [] argtypes = [] warnings = [] if args: for idx, arg in enumerate(args): argname = argfmt.format(name=procname, index=idx + 1) argnames.append(argname) if isinstance(arg, tuple): argtypes.append(" CAST({0} AS {1})".format(argname, arg[1])) self.execute("SET {0}=%s".format(argname), (arg[0],)) else: argtypes.append(argname) self.execute("SET {0}=%s".format(argname), (arg,)) call = "CALL {0}({1})".format(procname, ','.join(argnames)) for result in self._connection.cmd_query_iter(call): if 'columns' in result: # pylint: disable=W0212 tmp = MySQLCursorBuffered(self._connection._get_self()) tmp._handle_result(result) results.append(tmp) # pylint: enable=W0212 if self._connection.get_warnings is True and result['warning_count']: warnings.append(self._fetch_warnings()) if argnames: select = "SELECT {0}".format(','.join(argtypes)) self.execute(select) self._stored_results = results self._warnings = warnings return self.fetchone() else: self._stored_results = results self._warnings = warnings return ()