Debug a MySQL function

This topic explains how to debug a stored function in dbForge Studio for MySQL.

Note

Before you start debugging the function, you need to create a test database with tables and a stored procedure.

Create a stored function

1. In Database Explorer, right-click the required database and select New SQL.

2. Create the GetFirstDayOfMonth function that returns the first day of a calendar month.

CREATE FUNCTION GetFirstDayOfMonth (`date` datetime)
RETURNS datetime
BEGIN
  RETURN DATE_ADD(DATE_ADD(LAST_DAY(`date`), INTERVAL - 1 MONTH), INTERVAL 1 DAY);
END

3. On the SQL toolbar, click Execute, or press F5.

If the following error appears:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE FUNCTION GetFirstDayOfMonth (`date` datetime) RETURNS datetime' 
B' at line 1
This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

Execute the following script:

SET GLOBAL log_bin_trust_function_creators = 1;

Debug the function

Note

If the debug engine is not deployed on the server, the following notification appears. To proceed, click Deploy.

To debug a function:

1. In Database Explorer, right-click the GetFirstDayOfMonth function and select Compile > Compile for Debugging. Then click OK.

2. In Database Explorer, right-click the FillSchedule procedure and select Compile > Compile for Debugging. Then click OK.

If you skip these steps, the following notification appears. To proceed, click Yes.

3. In Database Explorer, double-click the procedure and navigate to the SQL tab of the stored procedure editor.

4. Right-click SET TimeSheetDate = GetFirstDayOfMonth(DateFrom); and select Insert Breakpoint.

5. Right-click DROP TABLE IF EXISTS enumdays; and select Insert Breakpoint.

6. On the Debug toolbar, click and enter the necessary values for the input parameters. Then click OK.

7. To monitor the DateFrom and TimeSheetDate values while stepping through the code, add these variables to the Watches list:

• In SET TimeSheetDate = GetFirstDayOfMonth(DateFrom); right-click the DateFrom value and select Add Watch.

• In SET TimeSheetDate = GetFirstDayOfMonth(DateFrom);, right-click the TimeSheetDate value and select Add Watch.

The Watches list displays the added variables.

8. Press F11 until a new tab with the stored function opens.

Note

To continue debugging the stored procedure, on the Debug toolbar, click .

The Watches list still shows no values for the DateFrom and TimeSheetDate variables. This is because both variables are declared in the FillSchedule procedure. You can add variables to the Watches list only from the function context.

For example, add the breakpoint for RETURN DATE_ADD(DATE_ADD(LAST_DAY(date), INTERVAL - 1 MONTH), INTERVAL 1 DAY);. Then right-click the date value in it and select Add Watch. The date parameter is added to the Watches list for the stored function:

The value of the variable is identified. The parameter is also transmitted to the stored procedure. It means that you can see date in the Watches list of the FillSchedule procedure:

8. Press F11 until you return to the stored procedure. The Watches list now shows values for the DateFrom and TimeSheetDate.

9. To finish the debugging process, on the Debug toolbar, click .