?
Solved

mysql stored procedure resultset limit - how do I get more?

Posted on 2012-09-11
4
Medium Priority
?
662 Views
Last Modified: 2012-09-11
I have a stored procedure which populates a temporary table which I then successively query on different criteria to return different resultsets in the stored procedure.   Everything is working  --except that-- I can only see a maximum of 10 resultsets being returned from the stored procedure when there actually are more than 10.

Here is a dummy stored procedure (not my real one which is more complex) which should give 11 resultsets but which only gives 10 -- with number 11 being "dropped":

BEGIN
select 1 as result;
select 2 as result;
select 3 as result;
select 4 as result;
select 5 as result;
select 6 as result;
select 7 as result;
select 8 as result;
select 9 as result;
select 10 as result;
select 11 as result;
END

Open in new window


If I change the order of things, like:
BEGIN
select 1 as result;
select 2 as result;
select 3 as result;
select 4 as result;
select 5 as result;
select 6 as result;
select 7 as result;
select 8 as result;
select 9 as result;
select 11 as result;
select 10 as result;
END

Open in new window

I now see result 11 but then result 10 goes "missing" --so I am still limited to a total of 10 resultsets.

Is this a mysql thing which I can override (and if so, how)?

In case it matters, I'm creating and viewing the results of the stored procedure using Navicat for Mysql software.  I don't know if this is the cause of the maximum 10 limitation or not.
0
Comment
Question by:ShanghaiD
  • 2
  • 2
4 Comments
 
LVL 24

Expert Comment

by:johanntagle
ID: 38385904
Works for me:

mysql> delimiter $$
mysql> create procedure shanghai()
    -> BEGIN
    -> select 1 as result;
    -> select 2 as result;
    -> select 3 as result;
    -> select 4 as result;
    -> select 5 as result;
    -> select 6 as result;
    -> select 7 as result;
    -> select 8 as result;
    -> select 9 as result;
    -> select 10 as result;
    -> select 11 as result;
    -> END;
    -> $$
Query OK, 0 rows affected (0.06 sec)

mysql> delimiter ;
mysql> call shanghai();
+--------+
| result |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

+--------+
| result |
+--------+
|      2 |
+--------+
1 row in set (0.00 sec)

+--------+
| result |
+--------+
|      3 |
+--------+
1 row in set (0.00 sec)

+--------+
| result |
+--------+
|      4 |
+--------+
1 row in set (0.00 sec)

+--------+
| result |
+--------+
|      5 |
+--------+
1 row in set (0.00 sec)

+--------+
| result |
+--------+
|      6 |
+--------+
1 row in set (0.00 sec)

+--------+
| result |
+--------+
|      7 |
+--------+
1 row in set (0.00 sec)

+--------+
| result |
+--------+
|      8 |
+--------+
1 row in set (0.00 sec)

+--------+
| result |
+--------+
|      9 |
+--------+
1 row in set (0.00 sec)

+--------+
| result |
+--------+
|     10 |
+--------+
1 row in set (0.00 sec)

+--------+
| result |
+--------+
|     11 |
+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Open in new window

0
 

Author Comment

by:ShanghaiD
ID: 38385928
Thanks for the quick reply.  

I'll look into things a little more before closing the question out (and awarding points).  

It seems it may be a Navicat GUI things -- where the resultsets are in fact all being returned but the Navicat front end is limiting its views to a maximum of 10.

I'll double check what is actually returned to my browser with Firebug and post an update.
0
 
LVL 24

Accepted Solution

by:
johanntagle earned 2000 total points
ID: 38385936
Or try calling it from your own MySQL command line client
0
 

Author Closing Comment

by:ShanghaiD
ID: 38385973
Of course!   I just did -- and it shows all 11 resultsets -- so it looks like it is a Navicat GUI limitation (which I can live with).
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month16 days, 7 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question