MySQL Errors and Warnings

[expired user #5015]'s profile image [expired user #5015] posted 11 years ago in General Permalink
One thing that I've struggled with in recent builds of HeidiSQL is the way it handles errors and warnings coming back from MySQL.

HeidiSQL used to pop up an alert window for me when a SQL error occurred... for example in the case of a syntax error in a query. It doesn't seem to be doing that any longer, and I can't find an option in Preferences to change that behavior. Having that alert has been tremendously helpful. Without it, the automatic assumption is that a query is just not returning any rows, not that it has failed to execute.

The other one that has been bugging me is the handling of warnings. It does seem to be popping up alert windows for warnings, even in cases where there isn't anything that I can do about it. For example, if I run a query that calls a stored function which itself contains a query that returns no rows, I get warnings popping up in HeidiSQL. In those cases, an empty result set for those queries is the right result. I know that HeidiSQL has no control over what warnings are generated by MySQL, but having a popup window for warnings (especially when there is none for errors) creates a great deal of confusion.

So as it stands, HeidiSQL is popping up annoying windows for warnings that are occurring by design, but not popping up windows for critical errors which prevent queries from being run. This has been a major source of frustration for me.

Am I missing a setting somewhere? Or has the behavior been changed and there is nothing I can do about this?
ansgar's profile image ansgar posted 11 years ago Permalink
You have the button with the yellow warning sign on the main toolbar unpressed. Which means: Do not stop on errors, do not pop up.

The other thing is of course that warnings still pop up.
[expired user #5015]'s profile image [expired user #5015] posted 11 years ago Permalink
Thanks for the tip on the errors. I had turned that off so I could execute a large number of queries, some of which I knew ahead of time were going to fail, without having to go through and remove those by hand. I didn't realize it would also turn off popup errors for single queries as well.

The issue that gets me (and this is entirely MySQL's fault) is that the following query generates a warning when the table doesn't exist:

DROP TABLE IF EXISTS blahblah;

What's the point of MySQL issuing a warning? I intentionally included the "IF EXISTS" to prevent an error/warning from occurring.

I have numerous stored procedures that have a line just like that to make sure that a CREATE TABLE occurring right after that won't fail... and every time I call one of those procedures it generates the warnings. And of course since HeidiSQL is displaying warnings, my employees and I all see those warnings popping up. It's especially bad for stored procedures with loops, which could generate dozens or hundreds of warnings.

An option to turn off the popup for warnings would be very much appreciated.
ansgar's profile image ansgar posted 11 years ago Permalink
Warning pop ups occur once at the end of a SQL batch execution, so this should not harm you a lot when executing a large number of queries, or?

On the other hand - and thank you for pointing that out - errors are hidden efficiently when the "Stop on errors in batch mode" is unpressed, even they are not mentioned after batch execution. This is inconsistent in HeidiSQL.

The mentioned button was meant to ensure a whole script can just run to the end without interference. The button was not meant to efficiently hide all errors for the user. As it is for warnings, it could also be for errors now, showing these along with the warning popup after batch execution.

I guess your wish is more to turn this button into a "Silence errors and warnings" button, which really hides errors (like now) + warnings inbetween and after a batch.

I'm hesitating to hide errors or warnings completely. From my experience with bug reports I see many users tend to be mislead if they try to find a row, table, database or whatever after batch execution. Some do even blame HeidiSQL in such cases. In the end, after spending hours on such problems, we find out that their script had some error.

Showing errors in the SQL log is also quite hidden, especially in long scripts where these scroll away quickly. But probably this is the way to go, probably with an additional warning icon somewhere in the status bar.
[expired user #7036]'s profile image [expired user #7036] posted 11 years ago Permalink
Actually I find this *very* annoying as well for the same reason OP does. I have a lot of stored procs with DROP...IF EXISTS and I would like to turn of warnings. Popups in any app now kill user experience, it's like shouting. To demonstrate I will post the rest of this email in APP CAPS. ;-)

ONE OPTION WOULD BE TO CREATE ANOTHER RESULT SET TAB TITLED "warnings" WITH A RED OR YELLOW ICON AND PUT MESSAGES THERE AS ROWS. THAT WOULD BE awesome!!!

MYSQL SAYS IN DOCS THAT drop...if exists ONLY TURNS THE USUAL ERROR INTO A WARNING...SO THEY AREN'T GOING TO STOP PRODUCING THE ERROR.

BTW...THANK YOU SO MUCH FOR HEIDI, GREAT WORK!...IT'S SO MUCH BETTER THAN BLOATED TOAD. HEIDI IS SIMPLE AND FAST! I'VE FINALLY MOVED OFF PLAIN OL' COMMAND PROMPT.

I hope you see the humor in my ALL CAPS. I don't intend to be offensive. :)

lieszkol's profile image lieszkol posted 6 years ago Permalink

I agree completely with the previous comments, this should not cause a warning, even if it's a bug (or "feature request") in MySQL. It would just be so nice if Heidi had an option to filter this out.

Note that there is a workaround that works in some situations, although it is cumbersome to implement in existing code.

SET sql_notes = 0;
... IF NOT EXISTS;
SET sql_notes = 1;

Please login to leave a reply, or register at first.