Calling a stored procedure says rows returned but won't let me fetch results.

Bug #1004991 reported by Jonathan Bastnagel on 2012-05-26
8
This bug affects 1 person
Affects Status Importance Assigned to Milestone
oursql
Undecided
Unassigned

Bug Description

Calling a stored procedure shows a positive row count but won't let me fetch results.

Client running Ubuntu 12.04 LTS, Python 2.7.3, OurSQL 0.9.3, and libmysqlclient-dev 5.5

Server is an Amazon RDS MySQL instance running MySQL 5.5.12

Create Table:
delimiter $$

CREATE TABLE `test` (
  `idtest` int(11) NOT NULL AUTO_INCREMENT,
  `data` int(11) DEFAULT NULL,
  PRIMARY KEY (`idtest`),
  UNIQUE KEY `idtest_UNIQUE` (`idtest`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1$$

Populate this table. I have 189 entries all with random numbers for `data`.

Create Procedure:
delimiter $$

CREATE DEFINER=`db`@`%` PROCEDURE `foo`(
IN a INT
,IN b INT
,IN c INT
)
BEGIN
SELECT SQL_CACHE
`data`
FROM `db`.`test`
ORDER BY a ASC;
END$$

Python Code (test.py):
import oursql

conn = oursql.connect(host='www.example.com', user='foo', passwd='bar', port=3306)
curs = conn.cursor()

curs.execute("CALL db.foo(?, ?, ?)", (1, 2, 3))
print("Row count: " + str(curs.rowcount))
print(curs.nextset())
res = curs.fetchone()
print(res)
curs.close()

Output:
Row count: 189
Traceback (most recent call last):
  File "test.py", line 8, in <module>
    print(curs.nextset())
  File "cursor.pyx", line 169, in oursql.Cursor.nextset (oursqlx/oursql.c:16436)
  File "cursor.pyx", line 161, in oursql.Cursor._check_statements (oursqlx/oursql.c:16392)
oursql.ProgrammingError: (None, 'no results available', None)

description: updated
description: updated
Changed in oursql:
status: New → Confirmed
Stephen Thorne (jerub) wrote :

At this point, I've traced this down to 'mysql_stmt_result_metadata(stmt)' returning NULL when a stored procedure is called.

According to some bug reports,
 http://bugs.mysql.com/bug.php?id=42490
http://bugs.mysql.com/bug.php?id=19008
http://bugs.mysql.com/bug.php?id=11918

The relevant section from the manual:
"The multiple statement and result capabilities can be used only with mysql_query() or mysql_real_query(). They cannot be used with the prepared statement interface. Prepared statement handles are defined to work only with strings that contain a single statement. See Section 22.8.4, “C API Prepared Statements”."
http://dev.mysql.com/doc/refman/5.5/en/c-api-multiple-queries.html

So the answer is "you cannot call a stored procedure from mysql_stmt_query() if you want any results" :(

In order to do what you want to do (use CALL) you have to do the escaping manually yourself, and use curs.execute("CALL foo(1,2,3)", plain_query=True)

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.