MySQL Stored Procedure Debugging

At the moment I am working on a very high volume / high concurrency web application using MySQL 5.5. We are starting to use stored procedures and functions to push some of the load down to the DB. Coming from an Oracle background I am coming to terms with some of the limitation is MySQL SP’s.

Today I needed to debug a complicated proc and after a bit of googling came across Debugger for MySQL. Normally with a MySQL tool I would expect many hours of annoying installation problems then when I finally get it running it falls short in many areas. Debugger for MySQL broke my normal expectations once I had spun up a Windows VM it was a straight one click install. When it was installed I just pointed it to the test instance I wanted, selected the SP I needed to debug and it automatically generated the test harness for me to execute the proc in debug mode. About 10min later I had fixed the problem using it’s easy to navigate debugging interface.

Give it a try if you are using stored procedures or functions. If only there was a Mac version

Advertisements
MySQL Stored Procedure Debugging

MySQL Function Example

Using a function to update MySQL data

 delimiter //


CREATE FUNCTION reduction (n1 INT, n2 INT)
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE avg INT;
SET avg = n1+n2;
RETURN avg;
END//


delimiter;

create table a (a1 int, a2 int);


insert into a (a1) values(1);
insert into a (a1) values(2);


update a set a1 = reduction(a1,2);


select * from a;

+------+------+


| a1         | a2         |


+------+------+


|           3 | NULL    |


|           4 | NULL    |


+------+------+


2 rows in set (0.00 sec)
MySQL Function Example