Important notice: before reading the Quick Start section, we recommend to acquaint yourself with debugger work basic principles.

Debugging MySQL stored procedures: Quick Start Guide

Although Debugger for MySQL is fairly straightforward, we created this Quick Start Guide to help you learn the basics of the program and throw it into action as soon as possible. Please feel free to use the code samples below to recreate the situations we will be presenting in this guide. Should you have any questions, please don’t hesitate to contact our support department – we will be happy to assist.

Online shop

Let’s use a typical online store as an example. As any serious e-commerce solution, our store uses a database to store product-related information: prices, descriptions, stock levels and so forth. The structure of its database can be extremely simple, such as the one below, for instance:

CREATE TABLE `product` (
  `id` INTEGER NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `price` DECIMAL(5,2) NOT NULL,
  PRIMARY KEY (`id`)
)

Apparently, we need some products to sell and here they are:

INSERT INTO product (id, name, price) VALUES (1, 'Designer t-shirt', 99.99)
INSERT INTO product (id, name, price) VALUES (2, 'Designer shirt', 199.99)
INSERT INTO product (id, name, price) VALUES (3, 'Designer polo', 299.99)
INSERT INTO product (id, name, price) VALUES (4, 'Designer coat', 399.99)
INSERT INTO product (id, name, price) VALUES (5, 'Posh designer shoes', 499.99)

Let’s assume we had been selling it all very successfully for a while until something drastically affected our sales – the global crisis, new competitors or an extra long holiday or post-holiday season – so we decided to boost sales by offering discounts. To do that, we added another table to our database by running the following command:

CREATE TABLE `discount` (
  `product_id` INTEGER NOT NULL,
  `value` TINYINT NOT NULL
)

Initially, we wanted to give a 20% discount on the designer shirt and just 10% off of our ridiculously expensive posh designer shoes:

INSERT INTO discount (product_id, `value`) VALUES (2, 20)
INSERT INTO discount (product_id, `value`) VALUES (5, 10)

We needed to create a list of products with discounted prices and wrote the following procedure for this purpose:

CREATE PROCEDURE price_with_discount ()
BEGIN
  DECLARE p_id INTEGER;
  DECLARE p_name VARCHAR(100);
  DECLARE p_price DECIMAL (5, 2);
  DECLARE done BOOLEAN DEFAULT FALSE;
  DECLARE product_discount SMALLINT;
  DECLARE amount_of_discount DECIMAL (5, 2);

  DECLARE c CURSOR FOR SELECT id, name, price FROM product;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN c;
  FETCH c INTO p_id, p_name, p_price;
  CREATE TEMPORARY TABLE tmp_price(product_name VARCHAR(100), price_with_discount DECIMAL (5, 2), discount_description TEXT);
  WHILE NOT done
  DO
    SELECT d.value INTO product_discount FROM discount d WHERE d.product_id = p_id;
    IF product_discount IS NULL THEN
      SET product_discount = 0;
    END IF;
    IF product_discount = 0 THEN
      INSERT INTO tmp_price VALUES (p_name, p_price, 'Discount is not available');
    ELSE
      SET amount_of_discount = (p_price * (product_discount / 100));
      INSERT INTO tmp_price VALUES (p_name, p_price - amount_of_discount, CONCAT('Discount ', product_discount, '% amount of discount $', amount_of_discount));
    END IF;
    FETCH c INTO p_id, p_name, p_price;
  END WHILE;

  SELECT * FROM tmp_price;
  DROP TABLE tmp_price;
END

Looks great! Let’s now take a look at our new list of products with discounts:

Isn’t it weird? We had 5 products and now there’s only one! A quick look through the code gives us nothing and we have no other choice but to launch the debugger...

Debugging

Let’s now enter the connection parameters and press OK.

We’ll call our procedure and use this code as our test script. This can be accomplished using the Main tab, which works similarly to the anonymous code block in Oracle, which helps developers efficiently prepare the execution context and emulate realistic code execution conditions. The Main tab in Debugger for MySQL serves the same purpose and contains a script that is run by the program when the user hits the Run button. The body of the Main tab is never sent to the server and its syntax is completely identical to that of the bodies of MySQL procedures. Use this tab to prepare the execution context your code will be tested in: start transactions, initialize variables, modify data and much more. This area is perfect as a test ground for new procedures and functions that can also be debugged, which makes Debugger for MySQL both a debugging tool and an editor that will help you create new functionality.

Let’s open the code of our procedure in the editor (hint: if you want to open a module, place the cursor over it and press CTRL+ENTER or double-click on it in the schema browser) and set a breakpoint on line 13 (hint: to set a breakpoint, place the cursor on the necessary line and press F5 or click the gutter area to the left of the line):

Press Run (F9) to start the debugging process.

All right, let’s start moving forward through the code using the Step over (F8) command.

Surprisingly, the cursor does not jump to line 19 after line 18. Instead, it goes to the NOT FOUND handler on line 11!

Bingo! Our first product, designer t-shirt, doesn’t have a discount and MySQL correctly generates a NOT FOUND event.

Delighted, we clap our hands, close the debugger and quickly fix the procedure. Now it looks like that:

CREATE PROCEDURE price_with_discount()
BEGIN
  DECLARE p_id INTEGER;
  DECLARE p_name VARCHAR(100);
  DECLARE p_price DECIMAL (5, 2);
  DECLARE done BOOLEAN DEFAULT FALSE;
  DECLARE product_discount SMALLINT;
  DECLARE amount_of_discount DECIMAL (5, 2);

  DECLARE c CURSOR FOR SELECT id, name, price FROM product;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN c;
  FETCH c INTO p_id, p_name, p_price;
  CREATE TEMPORARY TABLE tmp_price(product_name VARCHAR(100), price_with_discount DECIMAL (5, 2), discount_description TEXT);
  WHILE NOT done
  DO
    BEGIN
      DECLARE CONTINUE HANDLER FOR NOT FOUND
      BEGIN
      END;
      SELECT d.value INTO product_discount FROM discount d WHERE d.product_id = p_id;
    END;
    IF product_discount IS NULL THEN
      SET product_discount = 0;
    END IF;
    IF product_discount = 0 THEN
      INSERT INTO tmp_price VALUES (p_name, p_price, 'Discount is not available');
    ELSE
      SET amount_of_discount = (p_price * (product_discount / 100));
      INSERT INTO tmp_price VALUES (p_name, p_price - amount_of_discount, CONCAT('Discount ', product_discount, '% amount of discount $', amount_of_discount));
    END IF;
    FETCH c INTO p_id, p_name, p_price;
  END WHILE;

  CLOSE c;

  SELECT * FROM tmp_price;
  DROP TABLE tmp_price;
END

Totally convinced of our victory over the bug, we run our procedure once again:

Looking good so far! We’ve got 5 products now and that’s great news. But what in the world happened to our discounts? Why are the designer polo and designer coat on sale now? Maybe we closed the debugger a bit too early, so let’s start it again…

Debugging: Part 2

The good thing is that the program saved our previous debugging session, the Main tab script, the breakpoint and even the code of our procedure in the editor, so we won’t need to waste time typing and configuring it all anew.

Let’s get rid of the breakpoint on line 13 (hint: you can remove breakpoints in the same way you set them – either by pressing F5 or by clicking the gutter to the left of the necessary line) and move it to line 24 instead, as that’s where our discount-related calculations take place.

Now we know that the first two products were processed correctly, so let’s minimize the number of empty manual loop passes and save ourselves some time by setting a condition for our breakpoint: p_id >= 3. To do that, edit the breakpoint properties:

Enter your condition into the corresponding field:

Everything seems to be ready now and we can debug our code by pressing Run(F9).

Let’s make sure that the breakpoint condition was met. To do that, place the mouse cursor over any variable associated with the product – for instance over p_name located on line 28.

Looks like we are in the right iteration. To place this information right in front of our eyes, let’s add p_name to the watch list (hint: to add a watch, place the cursor on the necessary expression or select it and press CTRL+F5).

Let’s also add another variable that we are interested in - product_discount.

What a surprise! The value of the variable has not changed since the previous iteration and is still 20%, which was our discount for product #2, the designer shirt. MySQL did not find a new discount value for the designer polo in the discount table and kept its previous value.

Of course, MySQL gurus must be smiling at these lines now, as it all looks like basic ABC to them, but the rest of us know what a pain in the neck trial and error can be and would rather do it the right way from the very beginning or at least know a reliable way of finding bugs and quickly fixing them. That’s why proper documentation is essential for novice programmers just embarking on their long journey into the world of MySQL development and we highly recommend referring to this manual when you face an unusual or controversial situation.

Debugging: Part 3

So we now know what our problem is. Let’s change the procedure once again, but let’s not close the debugger and make our fixes right here.

Here’s how it looks after we’ve made the changes:

CREATE PROCEDURE price_with_discount()
BEGIN
  DECLARE p_id INTEGER;
  DECLARE p_name VARCHAR(100);
  DECLARE p_price DECIMAL (5, 2);
  DECLARE done BOOLEAN DEFAULT FALSE;
  DECLARE product_discount   SMALLINT;
  DECLARE amount_of_discount DECIMAL (5, 2);

  DECLARE c CURSOR FOR SELECT id, name, price FROM product;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN c;
  FETCH c INTO p_id, p_name, p_price;
  CREATE TEMPORARY TABLE tmp_price(product_name VARCHAR(100), price_with_discount DECIMAL (5, 2), discount_description TEXT);
  WHILE NOT done
  DO
    BEGIN
      DECLARE CONTINUE HANDLER FOR NOT FOUND
      BEGIN
        SET product_discount = 0;
      END;
      SELECT d.value INTO product_discount FROM discount d WHERE d.product_id = p_id;
    END;
    IF product_discount = 0 THEN
      INSERT INTO tmp_price VALUES (p_name, p_price, 'Discount is not available');
    ELSE
      SET amount_of_discount = (p_price * (product_discount / 100));
      INSERT INTO tmp_price VALUES (p_name, p_price - amount_of_discount, CONCAT('Discount ', product_discount, '% amount of discount $', amount_of_discount));
    END IF;
    FETCH c INTO p_id, p_name, p_price;
  END WHILE;

  CLOSE c;

  SELECT * FROM tmp_price;
  DROP TABLE tmp_price;
END

The NOT FOUND handler is not empty now, but contains the product_discount variable that is reset to zero if there is no discount for the currently processed product. We’ve removed the incorrect IF product_discount IS NULL check.

Now it’s high time we saved our work and tested it in action (hint: to save the current module, press CTRL+S or use SHIFT+CTRL+S to save all the modified modules).

To view the results, let’s use the built-in SQL Window (hint: to open SQL Window, press F11). Let’s enter the code that calls our procedure and run the entered command to view the output data (hint: to run a command in the SQL Window, place the cursor over the command and press CTRL+ENTER or SHIFT+CTRL+ENTER if you want to see the result in a new window).

Perfect! We made it! Now it looks exactly as we wanted it to.

Conclusion

As you see, Debugger for MySQL is very straightforward. Just use your familiar debugging techniques and be consistent - the rest will be a matter of minutes. The software will be a perfect choice for developers of all levels – from novices looking for their first debugger to jaded professionals seeking powerful alternatives to their current tools.