Monday, September 26, 2011

Can't open stored proc in MySql Workbench

I recently upgraded MySql versions to the latest version. When I did that I found I couldn't open some of my stored procs in Workbench. The issue was due to having a field in a table named "signal". I was referencing that field in statements in the procs that wouldn't open. It turned out that "signal" is a keyword in the latest release and that was causing Workbench to not be able to open the proc. I would have thought it would open it then fail on saving???.

The fix:
1. Rename the field on the table to something that is not a keyword.
2. In Workbench execute Select * from mysql.proc and locate the problem proc.
3. Create a new proc in Workbench by going to the Routines section of your database, right clicking and selecting "Create Routine"
4. Copy the param_list and body fields from the resultset in 2, into the new proc shell by right clicking on the fields and selecting "Copy Field Content". ie, recreate the proc.
5. Edit the re-created proc to modify the old field reference to the new field name
6. Give the proc a suitable name and apply it.
7. You should now be able to open the new proc using "Alter Routine". If so, drop the old proc, and change the name of the new proc to the name of the old proc.

No comments:

Post a Comment