A good interactive set of tools are available for debugging any application code. These same tools are ready to be used from a routine with MySQL code.
In this post we will be showing how to use the debugger of MySQL for Visual Studio plugin in a very simple routine, but it will be more than enough to show all the tools that exist within this MySQL Debugging tool.
To have visibility into the internal workings of a stored routine, the debugger prepares a special version of the procedure, function, or trigger being debugged, instrumented with extra code to keep track of the current line being stepped into, and the values of all the local variables. Any other stored procedures, functions, or triggers called from the routine being debugged are instrumented the same way. The debug versions of the routines are prepared for debugging automatically, and when the debug session ends (by either pressing F5 or Shift + F5), the original versions of the routines are automatically restored.
More information about the internals of the debugger are documented here: http://dev.mysql.com/doc/connector-net/en/connector-net-visual-studio-debugger.html
Image 1. Debugger main modules.
How to start
There are some considerations to take before starting using the debugger with MySQL for Visual Studio. First is necessary to have a database connection with an account user credentials that hold the following privileges: ALTER ROUTINE, CREATE DATABASE, SELECT, INSERT AND UPDATE. Secondly this is not recommended to be done inside a production environment for a better performance and speed. Instead, it could be good to create a copy of the database where the routine exists in. And work from there.
For this quick demo is necessary to have created the Sakila database. The steps for creating it are really simple. You can follow the first part of this tutorial to get this done. https://blogs.oracle.com/MySqlOnWindows/entry/howto_using_mysql_for_visual
Once the Sakila database has been created, it includes some routines that will be used with the MySQL debugger.
At the time when I downloaded and created the Sakila database, I noticed that the data has some out of date data in the DateTime fields. In order to get some good sample data, we'll be updating some datetime column values. This way we get a good report with the routines used.
The routine to use will be the "rewards_report" routine. This routine selects all the purchases done based on a criteria that takes: a minimum monthly number of purchases and a minimum monthly dollar amount purchased. Both values should be more than zero.
We'll take this "rewards_report" as a base and from than we'll create a new routine.
Step 1: Create a new MySQL procedure.
Open the Server Explorer window and create a new connection to your MySQL server instance. The server should be ready to stablish a connection. You can follow the steps to create a new connection from this quick tutorial http://dev.mysql.com/doc/connector-net/en/connector-net-visual-studio-making-a-connection.html. Click on the Sakila connection node and then expand the Database objects tree. Then you should select the Stored Procedures node. There you'll see three stored procedures. Select the "rewards_report" and right click on the node of this procedure, and click Alter Routine.
Img. 2 Alter routine menu from the Server explorer window.
As you can see in the code of this routine, it selects the payments done between the start of the last month and the end of the last month. Since the data that we have has some old dates in this table (you can check the data by selecting the table and then use the option Retrieve data from the context menu), let's update the datatime fields. First select the Sakila connection from the list of connection at the MySQL Script button in the MySQL toolbar. As is shown in the image.
Img. 3 List of MySQL Connections in the MySQL toolbar
At this new MySQL Script window, the Sakila connection is already selected so all the MySQL sentences executed on it will be done on the Sakila database. Now type the following sentence in order to update the payment_date column.
Img. 4 MySQL Script window with an update sentence.
Then click on Run SQL button for executing the sentence. This way the selecting we'll do within the payments table will have a result.
Img. 5 Running MySQL Code.
Now copy and paste the code from the "rewards_report" routine, and then we'll do some changes on it.
In order to create the new routine, select the Stored Procedures node and select the Create Stored Procedure option from the context menu. As is shown in the next image.
Img. 6 Create Store Procedure menu
First change would be to put a new name to the new routine. In this case we'll use the "my_rewards_report" name. The whole final routine is listed here:
CREATE PROCEDURE `my_rewards_report`(IN min_monthly_purchases TINYINT UNSIGNED, IN min_dollar_amount_purchased DECIMAL(10,2) UNSIGNED, OUT count_rewardees INT)
READS SQL DATA
COMMENT 'Provides a customizable report on best customers'
DECLARE last_month_start DATE;
DECLARE last_month_end DATE;
/* Some sanity checks... */
IF min_monthly_purchases = 0 THEN
SELECT 'Minimum monthly purchases parameter must be > 0';
IF min_dollar_amount_purchased = 0.00 THEN
SELECT 'Minimum monthly dollar amount purchased parameter must be > $0.00';
/* Determine start and end time periods */
SET last_month_start = DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH);
SET last_month_start = STR_TO_DATE(CONCAT(YEAR(last_month_start),'-',MONTH(last_month_start),'-01'),'%Y-%m-%d');
SET last_month_end = LAST_DAY(last_month_start);
Create a temporary storage area for
CREATE TEMPORARY TABLE tmpCustomer (customer_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY, amount_purchased DECIMAL, monthly_purchases INTEGER);
Find all customers meeting the
monthly purchase requirements
INSERT INTO tmpCustomer (customer_id, amount_purchased, monthly_purchases)
SELECT p.customer_id, SUM(p.amount) AS amount_purchased, COUNT(customer_id) as monthly_purchases
FROM payment AS p
WHERE DATE(p.payment_date) BETWEEN last_month_start AND last_month_end
GROUP BY customer_id
HAVING SUM(p.amount) > min_dollar_amount_purchased
AND COUNT(customer_id) > min_monthly_purchases;
/* Populate OUT parameter with count of found customers */
SELECT COUNT(*) FROM tmpCustomer INTO count_rewardees;
Output ALL customer information of matching rewardees.
Customize output as needed.
DROP TABLE IF EXISTS REPORT;
CREATE TABLE REPORT
SELECT c.*, t.amount_purchased, t.monthly_purchases
FROM tmpCustomer AS t
INNER JOIN customer AS c ON t.customer_id = c.customer_id;
/* Clean up */
DROP TABLE tmpCustomer;
Once the final code is paste or written in the new stored procedure window, right click at the tab of the window and select Save.
Img. 7 Save the new MySQL Routine.
Step 2: Debug the routine
Select the routine from the list of nodes in the Server Explorer window. Right click on it and select the Debug MySQL Routine option:
Img. 8 Debug MySQL Routine menu.
First thing is to assign a value for each parameter as is shown in the image. The value we used here is 1 for both so the query grabs any customer with at least one purchase from 1 dollar minimum.
Img. 9 Arguments window when starting to debug a MySQL routine
Now that the debugger has started let's add a new breakpoint to one of the lines so we can add some watches.
I added a breakpoint to the 31 line. A breakpoint can be added by doing a left clic on the gray column at the start of the edition line. Or by using F9 function key.
Img. 10. Adding a breakpoint at the MySQL routine code.
Press F5 and the execution should stop on the breakpoint. Now let's add some watches.
Adding variable watches
The first watch is added by selecting the text of the variable's name, then open the menu with a right click and selecting the option Add Watch.
Img. 11. Add watch option menu.
The last_month_start and last_month_end should appear at the watches window.
Img. 12 Watch 1 window with watches
On the watches window can be seen what value is being stored into these two variables.
The locals window is also available during the debugging session.
Img. 13. Locals window inside Visual Studio IDE
To make stepping inside the routine use F10 key function.
Now let's add the count_rewardees variable to the watches window. Stop the stepping on the DROP TABLE IF EXISTS REPORT; that way we can add the count_rewardees watch and see the number of records that the table Report will have.
Img. 14 Watches for variables.
Now press F5 to complete the debugging.
Go to the connection node of Sakila database and press Refresh.
Step 3. Retrieve data
Select the Report table and do a right click on the table's node. Select the retrieve data option from this menu.
Img. 15 Retrieve data option from table context's menu.
The reports table has now all the records that matched the criteria defined at the routine.
Img. 16. Listing records from Report table.
As it was shown, it is very easy to debug any MySQL routine just by using the MySQL for Visual Studio plugin. The stepping into, stepping out and stepping over are also available when debugging a routine that calls another routine. This way makes a lot easier to identify bugs or unexpected results when using MySQL routines.
Hope you found this information useful.
Happy MySQL/.Net coding!