1

I am trying to make a stored procedure in MYSQL(v5.7.21) with phpmyadmin(v4.7.9) that will sometimes return an empty result set. 

CREATE DEFINER=`my_database`@`%` PROCEDURE `emptytest`(IN `_id` INT(11))
NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER
    select * from my_table where id=_id

and next call it with:

call emptytest(1);

this works fine when the id exists in the table:

id  name
--------
1   bob

but throws an error when there are no rows to return:

call emptytest(11);
#2014 - Commands out of sync; you can't run this command now

However i would expect it to return the same as running the SQL statement:

select * from my_table where id=11

Which is an empty list:

id  name
--------

  • I've been looking on StackOverflow for similar questions but most of them address issues with multiple queries which is not my case.

  • As far as I know MySQL documentation states that procedures should be able to return tables.

  • For the example I am showing the default options used by phpmyadmin, but i tried to tweak them to no avail.

What am I missing?

JesusTCS
  • 158
  • 10

2 Answers2

2

Nothing wrong with the procedure.

It is phpMyAdmin which does not handle stored procedures when executed using CALL in SQL tab.

You can run the procedure by clicking the icon in front of the procedure name in the left navigation tree.

slaakso
  • 8,331
  • 2
  • 16
  • 27
  • Hello Slaakso, thanks for your contribution, I am not completely sure if i understand you. Are you telling me that when called by, for example, mysqli::query in PHP. it will return an empty resultset with no errors, even though i am not handling any errors explicitly within the procedure? I am using phpMyAdmin only as an development aid, so i am worried if it will work outside of it. – JesusTCS Sep 27 '18 at 14:53
  • An empty query will work just fine with mysqli::query. No need to set exit handler's in the procedure. The problem you see is just a limitation of phpMyAdmin's SQL query editor, nothing to do with mysqli. phpMyAdmin is not an ideal tool for procedure editing. – slaakso Sep 27 '18 at 15:26
  • If I've understood what the OP is saying, that can't be quite right - it's only an issue with an empty result set (which is not to say that I might not be leading the OP up the garden path as well... ;) ). If what you are saying is the correct diagnosis of the problem, then it would affect all calls to the SP. – Tom Melly Sep 28 '18 at 15:06
  • The "Commands out of sync" error comes when a connection is used and a new query is executed before the data is consumed from previous one (i.e. it's a client error). phpMyAdmin does much more than just execute the query when SQL input is processed. phpMyAdmin has separate UI for executing stored procedures which will handle the empty result set call correctly. – slaakso Sep 28 '18 at 17:40
  • Indeed, somehow phpMyAdmin translates my simple query into 25 lines of SQL. The faulty one seems to be related to: "SET FOREIGN_KEY_CHECKS = ON;". Anyway, i realize now that it was a limitation of phpMyAdmin, and it runs perfectly in MySQL console. Thank you both for the insights. – JesusTCS Oct 01 '18 at 14:22
0

Stored procedures tend to be 'fussier' about errors than simple statements (see below), so you need to put a handler in for that error.

DECLARE EXIT HANDLER FOR 2014 BEGIN select 'empty set' as `Error`; END;

The principle here is that you can't really handle these sort of errors in a simple query, so, from necessity, they are ignored; you can handle them in an SP, so, well, handle them...

Tom Melly
  • 353
  • 1
  • 8
  • This will help phpMyAdmin to go around it's issue. Not really needed for the procedure. – slaakso Sep 27 '18 at 13:39
  • @Tom: i tried your suggestion which i believe it's an step in the right direction, however 2 doubts still remain: 1) I would like to return an empty resultset (with all the table columns but no rows) , must i manually define it like in your example? 2) Should i handle 2014 when i only want to handle empty resultset? it sounds like it may later catch non-related "Commands out of sync" errors. – JesusTCS Sep 27 '18 at 14:39
  • Leave out the exit handler and you will get the empty result set. You can still get the column titles / types from the query even if there is no rows returned. – slaakso Sep 27 '18 at 15:28
  • Well, I've run a test using mysql workbench, and I get back an empty results set (i.e. what you want - the column names, but no data). So, I can only assume that slaakso is on the right path above, and phpmyadmin doesn't like an empty result set for some reason. My solution may 'work', but I think it's hiding the real issue (apart from phpmyadmin being a bit rubbish?). Have a read of: https://stackoverflow.com/questions/8891179/mysql-error-2014-commands-out-of-sync-you-cant-run-this-command-now?lq=1 The last comment looks quite pertinent (if not particularly helpful ;) ) – Tom Melly Sep 28 '18 at 15:26
  • Yes, I read it before, Anwser by Eric Leschinski in particular seems to be quite pertienent though, aparently we both stumbled on the same phpmyadmin limitation from diferent queries. though both the original question and their solutions are diferent, i too believe both questions should be linked :) – JesusTCS Oct 01 '18 at 14:54