Bug #48719 Incorrect error occur "PROCEDURE %s can't return a result set in th"

• Submitted: 12 Nov 2009 6:37
• Modified: 14 Nov 2009 4:47
• Reporter: Anton Pribora
• Status: Not a Bug
• Category: MySQL Server: Stored Routines
• Severity: S2 (Serious)
• Version: 5.1.38-log
• OS: FreeBSD (7.2)
• CPU Architecture: Any
• Tags: php, prepared statements, stored procedure

[12 Nov 2009 6:37] Anton Pribora

Description:
Error #1312 occur when using prepared statements in the procedure and invoke from PHP.

How to repeat:
Create a some procedure within PREPARE statament like this:

CREATE PROCEDURE proc_test()
BEGIN
	CREATE TEMPORARY TABLE t (`foo` VARCHAR(255));
	
	SET @a = 'INSERT INTO `t` SELECT ?';
	SET @b = 'bar';
	
	PREPARE stm FROM @a;
	EXECUTE stm USING @b;
	DROP PREPARE stm;
END

From console client:
mysql> call proc_test();
Query OK, 0 rows affected (0.00 sec)

mysql> select * from `t`;
+------+
| foo  |
+------+
| bar  |
+------+
1 row in set (0.00 sec)

From php:
#1312: PROCEDURE db_name.proc_test can't return a result set in the given context

PHP code:
<?php

mysql_connect('localhost', 'root', '');
mysql_query('CALL db_name.proc_test()') or die('#'. mysql_errno() .': '. mysql_error());

?>

[12 Nov 2009 7:21] Valeriy Kravchuk

You should set CLIENT_MULTI_RESULTS flag for connection. Check http://dev.mysql.com/doc/refman/5.1/en/c-api-multiple-queries.html.

[12 Nov 2009 8:03] Anton Pribora

Thanks!

<?php
// Connect to database with CLIENT_MULTI_RESULTS flag
mysql_connect('localhost', 'root', '', null, 131072);
...
?>

[12 Nov 2009 11:31] Valeriy Kravchuk

So, I assume it helped and this problem was not a result of any bug in MySQL. Correct me if I am wrong.

[14 Nov 2009 4:47] Anton Pribora

Perhaps it is not bug, but it looks very strange.
In any case, thank you!