How do you debug MySQL stored procedures?

Question

My current process for debugging stored procedures is very simple. I create a table called "debug" where I insert variable values from the stored procedure as it runs. This allows me to see the value of any variable at a given point in the script, but is there a better way to debug MySQL stored procedures?

Answer 1

I just simply place select statements in key areas of the stored procedure to check on current status of data sets, and then comment them out (--select...) or remove them before production.

Answer 2

I do something very similar to you.

I'll usually include a DEBUG param that defaults to false and I can set to true at run time. Then wrap the debug statements into an "If DEBUG" block.

I also use a logging table with many of my jobs so that I can review processes and timing. My Debug code gets output there as well. I include the calling param name, a brief description, row counts affected (if appropriate), a comments field and a time stamp.

Good debugging tools is one of the sad failings of all SQL platforms.

Answer 3

Yes, there is a specialized tools for this kind of thing - MySQL Debugger.

Answer 4

There are GUI tools for debugging stored procedures / functions and scripts in MySQL. A decent tool that dbForge Studio for MySQL, has rich functionality and stability.

Answer 5

The first and stable debugger for MySQL is in dbForge Studio for MySQL