A Warning about warnings.

Somebody recently asked a question about warnings in the MySQL Stored Procedure forum, the question its self was pretty easy but it raised another issue.

The question was "How can I see warnings when MySQL reports them". The easy answer is to simply type "SHOW WARNINGS;" on the MySQL command line, however the show warnings command only shows the last error message, thats fine if you actually type "SHOW WARNINGS;" immediately after the statement that had the problem but if you make a mistake or wait for something else to go wrong then the warning message is removed and replaced by something else.

The answer of course is not to make a mistake, but for people like me who come from a different background, in my case Oracle, the commands are different. I'm forever typing "SHO WARNINGS;" or "SHOW ERROR;" both of which result in another error and hence I lose the original message.

But here's a tip I used when working with stored procedures with Oracle. Because of their nature I'd write stored procedures in a file and then load them using the @ command, this can be recreated in MySQL using the source command. I'd also, in the early days, make a lot of mistakes, well not alot but enough that I had to write the SHOW ERRORS command on a regular basis. Rather than type it each time I added the SHOW ERRORS command to all of my procedure creation scripts, if there was an error it was displayed and if there wasn't I'd get a rather good message simply saying No Errors.

So my advice is if you're creating procedures or complex SQL just add a SHOW WARNINGS; command to the end of your script. Then you can just run the command from the script rather than the command line, if you do get any warnings they will display once the SQL has been run.... just don't make any mistakes writing show warnings in you file :).