PHP/MySQL: Warning: mysql_query(): Unable to save result set ...
Today i found a error in a tiny script i was working on.
The error Message looked like this:
Warning: mysql_query() [function.mysql-query]: Unable to save result set in /var/www/example.php on line 55
First i found a few hints about repairing or optimizing MySQL tables, but this did not work out.
After a few debugging outputs a realized that the query was not properly written.
The original query looked like this:
SELECT id
FROM `table`
WHERE `order` >
(
SELECT `order`
FROM `table`
WHERE active = 1
)
LIMIT 1;
FROM `table`
WHERE `order` >
(
SELECT `order`
FROM `table`
WHERE active = 1
)
LIMIT 1;
As you can see the query as a non correlated subquery. It worked fine nearly all the time, but only when the subquery returns only zero or one lines. If there are more lines MySQL generates the "Unable to save result set", because `order` cant be bigger then 2 resultvalues at the same time.
The right query looks like this:
SELECT id
FROM `table`
WHERE `order` >
(
SELECT `order`
FROM `table`
WHERE active = 1
LIMIT 1
)
LIMIT 1;
FROM `table`
WHERE `order` >
(
SELECT `order`
FROM `table`
WHERE active = 1
LIMIT 1
)
LIMIT 1;
A extra LIMIT 1 makes the deal perfect, and saved a little bit of my day.
