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:
None 
Category:Connector / Python Severity:S3 (Non-critical)
Version:2.0.1 OS:Any
Assigned to: CPU Architecture:Any
Tags: callproc, stored procedures, warnings

[7 Oct 2014 15:32] Olle Nilsson
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 ()
[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.