HowTo: Using the MySQL debugger inside the Visual Studio IDE

One of the most used tools that we, developers look up for, when fixing bugs is: a good debugging tool. MySQL for Visual Studio offers a really good integration with all of the debugging tools that Visual Studio already delivers for the .NET framework code.

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.

  • Visual Studio 2013 Professional edition or higher.
  • MySQL Connector/Net 6.9.x driver available on the MySQL Developer Zone
  • MySQL for Visual Studio 1.2.2 available on the MySQL Developer Zone
  • A MySQL server instance running. It can be use 5.5.x, 5.6.x or 5.7.x
How the debugger works

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'

proc: BEGIN

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';

        LEAVE proc;

    END IF;

    IF min_dollar_amount_purchased = 0.00 THEN

        SELECT 'Minimum monthly dollar amount purchased parameter must be > $0.00';

        LEAVE proc;

    END IF;

/* 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

        Customer IDs.

    */

    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;

END

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.

Conclusion

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.

Quick links

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

Here's where you'll hear about all the new and fun stuff we are doing with MySQL on Windows.

Search

Categories
Archives
« May 2015
SunMonTueWedThuFriSat
     
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
      
Today