"SQL Editor" procedure has multiple result sets re

"SQL Editor" will report an error when the stored procedure has multiple result sets returned.

This is a known compatibility issue with some versions of MySQL native clients, I believe it regressed after MySQL 8.x
As a workaround, please change your connection in the Connection dialog to use ADO.NET driver or ODBC driver.

Just to add another two cents (or whatever it is in China ;): Depending on the version of MySQL (or MariaDB) and the connector you use, you can expect to run into all kinds of issues that come in different flavors (and levels of funny).

- native: command out of sync stored procedures with multiple result sets. Ahh, change the connection type to...

- ODBC: no command out of sync for stored procedures. Yeah! No multiple result sets returned either. What? You get the first one, the rest is silently swallowed somewhere, and that's all for you. You should be happy with that one, lonely result set. Keep it fed. Again, change the connection type to...

- ADO: you get multiple result sets. What you don't get is the ability to use user-defined variables (the ones with the @). Well, not unless you add "Allow User Variables=True;" to the raw connection string, but I guess SA has you covered there as it is automatically added to new connections (older versions did not do that). Otherwise, you only get an error message, stating: "Fatal error encountered during command execution." Another thing you also won't get is any kind of error messages or warnings if something fails after the first result set. If the fishy part is before the first result set, everything is fine, it fails and you get the error message. If it happens after, you get all of the results sets until the crash-and-burn, a message that all went well (a hideous lie, if you ask me), and an ever-increasing headache when trying to figure out where have all the other/missing results gone. In this case, you should execute SHOW WARNINGS immediately after the semi-failed call. It might tell something useful.

Last edited by gemisigo on Thu Jul 15, 2021 3:00 am; edited 1 time in total

Thank you for the very useful details. Just to complete the picture, the issue with multiple result sets from a stored procedure not showing when using native client library is going to be resolved in version 12.0. That fix is already in the queue and confirmed. But there is no confirmed release date yet for 12.0. So far we expect beta version available at the end of August / beginning of September.

I knew this day had something beautiful coming! Can you spread some rumors, what is to be expected with v12? (also, I'm sorry for temporarily hijacking this thread with this question).

There is no confirmed list yet for what is going to make to 12.0 or what would need to wait for later. What I've seen so far (big tickets, listed in no particular order)

Task-oriented SQL profilers for Oracle and PostgreSQL
Full featured report engine and designer
Reworking of source control interfaces supporting different projects for different databases and more flexibility
Significant intellisense updates, better support for all sorts of DDL operations, engine updated for all latest db versions
Preset load testing templates for testing performance, generating loads, and benchmarking various databases
Parquet files supported in data import/export operations
Richer set of ETL tools
Additional graphical database management tools
Better support for high resolution monitors and font scaling