0

I have a MySQL stored procedure that runs fine and does its job quite well. Yesterday, I edited it in MySQL Workbench and introduced a spelling mistake on purpose. For example: -

Valid statement: insert into dept (dept_id, dept_name) values (1,'Development');

Statement with intentional typo: insert into dept (dept_id, dept_names /*Note plural*/) values (1,'Development');

My dept table does not contain a column called dept_names (plural) - correct column is dept_name (singular). Upon compiling the procedure, I was expecting MySQL Workbench to show an error message indicating non-existent column, but no such error occurred. The error message was shown when I executed the procedure. Coming from Oracle background, this is somewhat unusual for me. Oracle performs all schema checking (i.e. bad column names) when we Save stored procedures.

Now comes the question: What can I do to make MySQL Workbench (or even mysql server command line prompt) show any and all error messages when I save stored procedures and not wait until I run/execute/call them?

Thanks!

Additional Info 1: Just wanted to add that the "bad" INSERT statement above is wrapped within a IF statement. This caused stored procedure to continue executing without errors for many months until one fine day it entered the IF statement. Error message Unknown column 'dept_names' in 'field list' was received immediately after entering IF statement upon executing the "bad" INSERT.

Additional Info 2: Of course, runtime errors can wait until I actually run/execute the stored procedure. My "bad column" example above is not a runtime error so I would like to see the error when saving/compiling the stored procedure.

Improve this question

1 Answer 1

Reset to default
0

When you right click on stored procedures, you hit create stroed procedures. workbench

this should show spelling errors like INsRT and systax erors

But logic errors it will not find.

So start using error handling and user defined variables to debug it properly.

But you can test all queries in a normal Query tab this would also check for syntax errors

Improve this answer
3
  • Thanks for the answer. My example in original question has misspelled column name which the Stored Procedure Editor cleanly accepts without showing error message when I hit "Apply". So it appears the misspelled column name does not constitute a "syntax error". Secondly, correct me if this is wrong, the "SIGNAL" statement you linked above seems to engage only if I execute the procedure which defeats my purpose. I would like the error message when I hit "Apply" button if possible. Thoughts? Commented Jun 10, 2020 at 20:56
  • no it helps to find certain errors. You can't find such error like false columns, mysql will only find syntax errors. that is why stired procedures need help often help. And for that is needed the data processed. sql is not that hard. you have a result in mind and then you build layer after lyer to get to teh result. Storred procedured functions give you more "normal" methods of programming nd enhance so sql Commented Jun 10, 2020 at 22:17
  • [... You can't find such error like false columns, mysql will only find syntax errors. ...] Thanks for this. Commented Jun 12, 2020 at 14:06

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.