top of page

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.

How to install it

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


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




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

SET @counter_value = @counter_value + 1;

RETURN @counter_value;



CREATE PROCEDURE sp_one_demo ()




SET j = 0;

WHILE j < 100 DO

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

SET j = j + 1;


SET j = j + 1;




Now type the following command


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.

694 views0 comments

Recent Posts

See All

What are the future prospects of Java

According to a survey conducted, nearly 63% programmers mentioned that they will likely continue to work with Java along with coding with Python, SQL and HTML/CSS. In addition, the giants in the corpo

Deleting Duplicate Rows in MySQL

In this blog, you will study several ways to delete duplicate rows in MySQL. In this blog, I have shown you how to find duplicate values in a table. Once the duplicates rows are recognized, you may ne

Upload Data to MySQL tables using mysqlimport

Uploading quite a lot of rows of data from a text, csv, and excel file into a MySQL table is a repetitive task for sysadmins and DBAs who are handling MySQL database. This blog clarifies 4 applied exa


bottom of page