Debug a stored procedure

The guide provides a step-by-step procedure how to debug a MySQL stored procedure by stepping into it and illustrates different debugging techniques, such as setting breakpoints, viewing data items, etc.

Note

Prior to debugging, it is recommended to execute the Compile for Debugging operation, which adds debugging information to the procedure. After debugging, execute the Compile operation. These steps are detailed in the Debug the stored procedure section of this topic.

It is important to note that before performing these operations, it is advisable to back up the database.

Create a test database and populate it with tables

Before you start debugging a stored procedure, it’s required to create a test database in dbForge Studio for MySQL and then fill it with tables. Also, note that if the debug engine has not been deployed on the server, you will get such a notification. In this case, click Deploy.

Debug-engine

1. To create the database, refer to the Creating and Dropping Databases topic.

2. In Database Explorer, right-click the created database and select New SQL.

3. Copy this script and paste it into the SQL document. The script creates three tables - schedule, scheduledetail, and scheduletemplatedetail.

CREATE TABLE scheduledetail (
  ScheduleId int(11) NOT NULL,
  DateOut date NOT NULL,
  TimeSheetDate date NOT NULL,
  AbsenceCode varchar(25) DEFAULT NULL,
  PRIMARY KEY (ScheduleId, DateOut)
);
 
CREATE TABLE scheduletemplatedetail (
  TemplateId int(11) NOT NULL,
  DayOrder int(11) NOT NULL,
  AbsenceCode varchar(25) DEFAULT NULL,
  WorkShiftCd varchar(25) DEFAULT NULL,
  PRIMARY KEY (TemplateId, DayOrder)
);
 
INSERT INTO scheduletemplatedetail (TemplateId, DayOrder, AbsenceCode, WorkShiftCd)
  VALUES (1, 1, NULL, '1/10');
INSERT INTO scheduletemplatedetail (TemplateId, DayOrder, AbsenceCode, WorkShiftCd)
  VALUES (1, 2, NULL, '1/11,5');
INSERT INTO scheduletemplatedetail (TemplateId, DayOrder, AbsenceCode, WorkShiftCd)
  VALUES (1, 3, NULL, '1/10');
INSERT INTO scheduletemplatedetail (TemplateId, DayOrder, AbsenceCode, WorkShiftCd)
  VALUES (1, 4, NULL, '1/11,5');
INSERT INTO scheduletemplatedetail (TemplateId, DayOrder, AbsenceCode, WorkShiftCd)
  VALUES (1, 5, NULL, '1/10');
INSERT INTO scheduletemplatedetail (TemplateId, DayOrder, AbsenceCode, WorkShiftCd)
  VALUES (1, 6, 'offdays', NULL);
INSERT INTO scheduletemplatedetail (TemplateId, DayOrder, AbsenceCode, WorkShiftCd)
  VALUES (1, 7, 'offdays', NULL);
INSERT INTO scheduletemplatedetail (TemplateId, DayOrder, AbsenceCode, WorkShiftCd)
  VALUES (2, 1, NULL, '2/11,5');
INSERT INTO scheduletemplatedetail (TemplateId, DayOrder, AbsenceCode, WorkShiftCd)
  VALUES (2, 2, NULL, '1/11,5');
INSERT INTO scheduletemplatedetail (TemplateId, DayOrder, AbsenceCode, WorkShiftCd)
  VALUES (2, 3, NULL, '2/11,5');
INSERT INTO scheduletemplatedetail (TemplateId, DayOrder, AbsenceCode, WorkShiftCd)
  VALUES (2, 4, NULL, '1|11.5');
INSERT INTO scheduletemplatedetail (TemplateId, DayOrder, AbsenceCode, WorkShiftCd)
  VALUES (2, 5, NULL, '1/11,5');
INSERT INTO scheduletemplatedetail (TemplateId, DayOrder, AbsenceCode, WorkShiftCd)
  VALUES (2, 6, 'offdays', NULL);
INSERT INTO scheduletemplatedetail (TemplateId, DayOrder, AbsenceCode, WorkShiftCd)
  VALUES (2, 7, 'offdays', NULL);
INSERT INTO scheduletemplatedetail (TemplateId, DayOrder, AbsenceCode, WorkShiftCd)
  VALUES (3, 1, NULL, '1#8');
INSERT INTO scheduletemplatedetail (TemplateId, DayOrder, AbsenceCode, WorkShiftCd)
  VALUES (3, 2, NULL, '1.0');
INSERT INTO scheduletemplatedetail (TemplateId, DayOrder, AbsenceCode, WorkShiftCd)
  VALUES (3, 3, 'business_trip', NULL);
 
CREATE TABLE schedule (
  ScheduleId int(11) NOT NULL AUTO_INCREMENT,
  TemplateId int(11) DEFAULT NULL,
  PRIMARY KEY (ScheduleId)
);
 
INSERT INTO schedule (ScheduleId, TemplateId)
  VALUES (1, 5);
INSERT INTO schedule (ScheduleId, TemplateId)
  VALUES (2, 1);
INSERT INTO schedule (ScheduleId, TemplateId)
  VALUES (4, 2);
INSERT INTO schedule (ScheduleId, TemplateId)
  VALUES (3, 3);
INSERT INTO schedule (ScheduleId, TemplateId)
  VALUES (5, 4);

4. On the Standard toolbar, click Execute.

Create a stored procedure

1. In Database Explorer, right-click your test database and select New SQL.

2. Copy this script and paste it into the SQL document. The script creates the FillSchedule procedure that takes three input parameters (DateFrom, DateTo, and ScheduleId):

DELIMITER$$
CREATE PROCEDURE FillSchedule (IN DateFrom date
, IN DateTo date
, IN ScheduleId int)
SQL SECURITY INVOKER
READS SQL DATA
BEGIN
 
  DECLARE DateOut date;
  DECLARE TemplateCount tinyint;
  DECLARE RowNumber tinyint;
  DECLARE TimeSheetDate date;
 
  SET
  TemplateCount = (SELECT
      COUNT(*)
    FROM `schedule` s
      JOIN scheduletemplatedetail sdt
        ON s.TemplateId = sdt.TemplateId
    WHERE s.ScheduleId = ScheduleId), DateOut = DateFrom
  , RowNumber = 0;
   SET TimeSheetDate = GetFirstDayOfMonth(DateFrom);
 
  DROP TABLE IF EXISTS enumdays;
  CREATE TEMPORARY TABLE enumdays (
    DayOrder tinyint,
    DateOut date,
    TimeSheetDate date
  );
 
  WHILE (DateOut <= DateTo) DO
 
    IF RowNumber = TemplateCount
      THEN
      SET RowNumber = 1;
    ELSE
      SET RowNumber := RowNumber + 1;
    END IF;
 
    INSERT INTO enumdays (DateOut
    , DayOrder
    , TimeSheetDate)
      SELECT
        DateOut,
        RowNumber,
        TimeSheetDate;
 
    SET DateOut := DATE_ADD(DateOut, INTERVAL 1 DAY);
    SET TimeSheetDate = GetFirstDayOfMonth(DateOut);
 
  END WHILE;
 
  DELETE
    FROM s
  USING scheduledetail s
  WHERE s.ScheduleId = ScheduleId
    AND s.DateOut BETWEEN DateFrom AND DateTo;
 
  INSERT INTO scheduledetail (DateOut
  , TimeSheetDate
  , ScheduleId
  , AbsenceCode)
    SELECT
      d.DateOut,
      d.TimeSheetDate,
      ScheduleId,
      s.AbsenceCode
    FROM enumdays d
      CROSS JOIN (SELECT
          s.TemplateId
        FROM `schedule` s
        WHERE s.ScheduleId = ScheduleId) s2
      JOIN scheduletemplatedetail s
        ON d.DayOrder = s.DayOrder
        AND s.TemplateId = s2.TemplateId;
 
END$$

3. On the Standard toolbar, click Execute.

Debug the stored procedure

Functions, unlike procedures, return a value as a result of their execution that you can use in a statement. For example, you can call a function and get a result with the SELECT statement. However, you cannot run this statement for a procedure or use a procedure value in it. That’s why it’s required to create a function and assign its value to the TimeSheetDate variable, located in the FillSchedule procedure.

1. To create a function, you can use the script from the Debug MySQL functions topic.

Note

The function should be created in the same database where you have previously created the stored procedure.

2. In Database Explorer, right-click the FillSchedule procedure and select Compile > Compile for Debugging. If you skip this step, you will get the following notification - click Yes.

Debug notification

3. Click OK and double-click the procedure. The editor of procedures will open.

4. Press F11 to open the parameters window. Then, enter the required parameters and click OK:

Parameters window

You can see that the arrow identifies the stack frame where the execution pointer is currently located.

Stack frame

5. In the editor of procedures, right-click SET TimeSheetDate = GetFirstDayOfMonth(DateFrom) and select Insert Breakpoint.

6. To track the TimeSheetDate value while debugging, right-click the TimeSheetDate word in the variable and click Add Watch.

Now, you can see that the TimeSheetDate variable is visible in the Watches list.

Add watch

7. Insert the breakpoint for this block:

INSERT INTO enumdays (DateOut
    , DayOrder
    , TimeSheetDate)
      SELECT
        DateOut,
        RowNumber,
        TimeSheetDate;

For this, right-click any word in the statement and select Insert Breakpoint.

Insert connection

8. On the Debug toolbar, click Step Into or press F11 until this block is highlighted in yellow.

9. Initialize the TimeSheetDate variable before inserting it into the table.

Hitting breakpoint

As the date period covers two months, all dates of the first month with a day interval take part in the cycle, and then all dates of the second month take part in the cycle. In the Watch list, you can monitor all changes of the variable while debugging.

10. Press F11 again until SET TimeSheetDate = GetFirstDayOfMonth(DateOut); is highlighted in yellow.

We specified the period for thirty-five days and thus after the debugging process is over, you will see thirty-five records in the scheduledetail table. Let’s check it.

11. In Database Explorer, right-click the scheduledetail table and select Select All Rows. As you can see, we have thirty-five records in the table:

Records

12. To finish the debugging process, on the Debug toolbar, click Stop Debugging.

Want to Find out More?

Overview

Overview

Take a quick tour to learn all about the key benefits delivered by dbForge Studio for MySQL.
All Features

All features

Get acquainted with the rich features and capabilities of the Studio in less than 5 minutes.
Request a demo

Request a demo

If you consider employing the Studio for your business, request a demo to see it in action.
Ready to start using dbForge Studio for MySQL?
Download a free 30-day trial