“can't return a result set in the given context” error when calling stored procedure - differences between mysql 5.0 and 5.5?

Question

I have a stored procedure to regenerate leafs (nleft, nright) in simple (id, id_parent) tree. (sample table)

When I'am calling

CALL EdgeListToNestedSet( 'category', 'id_category', 'id_parent');

it works great with version 5.5.29 of mysql but on 5.0.26 I've got an error:

#1312 - PROCEDURE test.EdgeListToNestedSet can't return a result set in the given context

Any ideas what can be wrong and how to make it working?

Answer 1

Older versions of phpMyAdmin have issues with MySQL. This has been asked 8 times in StackOverflow with the tag mysql-error-1312.

This bug was seen and addressed in MySQL 5.0.09 beta about 6 weeks after MySQL 5.0.26 was released Oct 09, 2006. Still, you got this error.

According to http://qasimbadami.wordpress.com/tag/mysql-error-1312/, you should make sure you are using mysqli / PDO.

Answer 2

I had this problem too; calling the procedure worked on my local xampp instalation but not on the live server.

Tried a lot of things and pulled a lot of hair on this one.

I was concatenating some strings in the procedure, on different rows for clarity; after i put all the text of the concat statement on a single row it worked (deleted only white space, no code); so it might be that some special new line characters break something in MySQL concatenations