Search

How to Debug MySQL stored procedure

Updated: Mar 26, 2019

There are several tools available on the net for debugging the MySQL stored procedure. But I am going to discuss the open source tool by using it we can debug MySQL stored procedure and remove errors in it and can reduce our development time and figure out the errors in the PL/SQL used in creating the stored procedure.

Here is the pre-requisite we need to create the environment for debugging of stored procedure.

Ubuntu 16.04

MySQL server installed on it

Here is the tool which I am going to use for debugging the stored procedure.

https://github.com/ocelot-inc/ocelotgui


How to install it


Can start the ocelot by typing the following command in console of ubuntu OS

ocelotgui


Now a graphical interface comes up like this


Now you can click on the menu file->connect and give the required credentials of mysql if is installed locally or remotely.

Now create a stored procedure for debugging in it. type the following PL/SQL


DELIMITER ;;

CREATE FUNCTION sp_counter_demo () RETURNS INT

BEGIN

IF @counter_value IS NULL THEN SET @counter_value = 0; END IF;

SET @counter_value = @counter_value + 1;

RETURN @counter_value;

END

;;

CREATE PROCEDURE sp_one_demo ()

BEGIN

DECLARE j INT;

CREATE TABLE IF NOT EXISTS table_demo (s1 INT);

SET j = 0;

WHILE j < 100 DO

INSERT INTO table_demo VALUES (sp_counter_demo ()());

SET j = j + 1;

END WHILE;

SET j = j + 1;

END

;;

DELIMITER ;


Now type the following command


$install;


Next type this command


$setup sp_counter_demo, sp_one_demo;


Now type this command to start the debuging process. now you can see that first line of the sp_one_demo is highlighted, and it always stops at the first line of the stored procedure or function.

As you can see in the debug menu different hot keys to check the variables in the stored procedure and see in details how stored procedure is going to work.

Debugger commands may be complete via the menu, via Alt keys, or via command-line declarations. For sample, to enter a Next teaching one may now click Debug or Next, or type Alt+3, or type the declaration: "$next;"

Enter a Next instruction frequently, viewing how the decorated line changes, while waiting for the INSERT line is highlighted.

Here are the commands we can also type in for checking the values

"$breakpoint sp_one_demo 9;"

"$refresh variables;" and "select old_value, value from xxxmdbug.variables;"

Enter an $exit training. This stops the debugging conference, so the things of the previous $debug coaching are invalid. The belongings of the previous $install and $setup commands are not invalid, so they will not have to be recurrent for the following debugging session connecting sp_counter_demo and sp_one_demo. This is the end of this blog.

105 views

Call

T: +44 7825018241

Follow me

 

© 2019 by GoplarDB 

All Rights Reserved

Powered by: Goplar LTD

  • LinkedIn Social Icon
  • Twitter Clean