Debug a MySQL function
In this topic, you will learn how to debug a stored function in dbForge Studio for MySQL.
Note that before you start debugging the function, you need to create a test database with tables and a stored procedure. For this, refer to the Debug a stored procedure topic, sections Create a test database and populate it with tables and Create a stored procedure.
Create a stored function
1. In Database Explorer, right-click the required database and select New SQL.
2. To the GetFirstDayOfMonth function that returns the first day of a calendar month, copy this script and paste it into the SQL document:
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 Standard toolbar, click Execute.
After you run the script, you may see the following error:
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)
In this case, you need to execute:
SET GLOBAL log_bin_trust_function_creators = 1;
Debug the function
Note that if the debug engine is not deployed on the server, you will get such a notification. In this case, just click Deploy.
1. In Database Explorer, right-click the GetFirstDayOfMonth function, point to Compile, and click Compile for Debugging. Then click OK.
2. In Database Explorer, right-click the FillSchedule procedure and point to Compile. Then click Compile for Debugging and click OK. If you skip this step, you will get the following notification. Just click Yes.
3. In Database Explorer, double-click the procedure and right-click SET TimeSheetDate = GetFirstDayOfMonth(DateFrom); in the editor of procedures. Then click Insert Breakpoint:
4. In the editor of procedures, right-click DROP TABLE IF EXISTS enumdays; and click Insert Breakpoint.
5. On the Debug toolbar, click 
Start Debugging and enter the necessary values of the input parameters for the procedure. And then click OK:
6. To monitor the DateFrom and TimeSheetDate values while stepping through the code, you can add these variables to the Watches list.
To do it, in the editor of procedures, right-click the DateFrom word in SET TimeSheetDate = GetFirstDayOfMonth(DateFrom); and select Add Watch:
Right-click the TimeSheetDate word in the same variable and click Add Watch again. As a result, in the Watches list, you will see the added variables:
7. Press F11 until a new tab with the stored function gets opened:
Note
If you want to continue debugging the stored procedure, on the Debug toolbar, click
Step Out
There are still no values for the DateFrom and TimeSheetDate variables in the Watches list. It’s because you declared both variables for the FillSchedule procedure. You can add any variable to the Watches list from the function context.
For example, add the breakpoint for RETURN DATE_ADD(DATE_ADD(LAST_DAY(date), INTERVAL - 1 MONTH), INTERVAL 1 DAY); and right-click the date word in it, and then select Add Watch. You can see that the date parameter is visible in the Watches list for the stored function:
Pay attention that the value of the variable is identified.
But also the parameter is 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 get back to the tab with the stored procedure. Notice that there are the specified values for the DateFrom and TimeSheetDate variables in the Watches list:
9. To finish the debugging process, on the Debug toolbar, click 
Stop Debugging.
Want to Find out More?
Overview
All features
Request a demo
