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.
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.
Comentários