Learn › MySQL › Arjun debugs the procedure that gave wrong answers

MySQL Ch 11 / 18 Advanced

🔬

Arjun debugs the procedure that gave wrong answers

Debug selects, log tables, NULL traps, parameter shadowing — systematic procedure debugging

⏱ 13 min 6 commands 5 takeaways

🔬

In this chapter

Arjun

Developer debugging a faulty stored procedure

The story

Arjun's app had a critical bug. The order total was wrong for some users. The calculation happened in a stored procedure. He had never debugged a procedure before.

Priya showed him a systematic approach.

SEEING WHAT IS INSIDE A PROCEDURE

-- See the full procedure code:
SHOW CREATE PROCEDURE CalculateOrderTotal\G

-- List all procedures in the database:
SELECT routine_name, created, last_altered
FROM information_schema.ROUTINES
WHERE routine_schema = 'myapp_db'
AND routine_type = 'PROCEDURE';

THE PROBLEM WITH DEBUGGING PROCEDURES

MySQL does not have a built-in debugger like Python's pdb. The professional way to debug procedures is:

1. Add temporary SELECT statements inside the procedure to print intermediate values

2. Test with known inputs and verify each step

3. Use a debug log table

TECHNIQUE 1: ADD DEBUG SELECTS

DROP PROCEDURE IF EXISTS CalculateOrderTotal;

DELIMITER //

CREATE PROCEDURE CalculateOrderTotal(IN user_id_param INT)
BEGIN
    DECLARE subtotal   DECIMAL(12,2);
    DECLARE discount   DECIMAL(12,2);
    DECLARE final_total DECIMAL(12,2);

    -- Step 1: get subtotal
    SELECT SUM(amount) INTO subtotal
    FROM orders
    WHERE user_id = user_id_param AND status = 'paid';

    SELECT 'DEBUG Step 1' AS step, subtotal AS value;    -- debug print

    -- Step 2: calculate discount
    IF subtotal > 50000 THEN
        SET discount = subtotal * 0.10;
    ELSEIF subtotal > 20000 THEN
        SET discount = subtotal * 0.05;
    ELSE
        SET discount = 0;
    END IF;

    SELECT 'DEBUG Step 2' AS step, discount AS value;    -- debug print

    -- Step 3: final total
    SET final_total = subtotal - discount;

    SELECT 'DEBUG Step 3' AS step, final_total AS value; -- debug print

    -- Final result
    SELECT subtotal, discount, final_total;
END //

DELIMITER ;

CALL CalculateOrderTotal(1);

This prints the value at each step. You can see exactly where the logic goes wrong.

TECHNIQUE 2: DEBUG LOG TABLE

For production debugging where you cannot add SELECT statements:

CREATE TABLE IF NOT EXISTS debug_log (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    proc_name  VARCHAR(100),
    step       VARCHAR(100),
    message    TEXT,
    logged_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Inside your procedure:
INSERT INTO debug_log (proc_name, step, message)
VALUES ('CalculateOrderTotal', 'Step1_subtotal', CONCAT('user_id=', user_id_param, ' subtotal=', subtotal));

-- Check the log after calling:
SELECT * FROM debug_log ORDER BY logged_at DESC LIMIT 20;
TRUNCATE TABLE debug_log;   -- clean up when done debugging

COMMON PROCEDURE BUGS AND HOW TO SPOT THEM

Bug 1: Variable shadowing -- procedure parameter has the same name as a column

CREATE PROCEDURE GetOrders(IN user_id INT)
BEGIN
    -- PROBLEM: user_id is both the parameter AND a column in orders
    -- MySQL may get confused which one you mean
    SELECT * FROM orders WHERE user_id = user_id;
    -- This returns ALL rows because user_id = user_id is always true!

    -- FIX: use a different parameter name:
    -- IN p_user_id INT  ... WHERE user_id = p_user_id
END

Always prefix parameters with p_ to avoid this: p_user_id, p_city, p_amount.

Bug 2: NULL not handled

IF total > 50000 THEN ...   -- if total is NULL this condition is FALSE, not an error
-- Use IFNULL or COALESCE:
SET total = IFNULL(subtotal, 0);

Bug 3: Division by zero

SET avg = total / count;   -- crashes if count = 0
SET avg = total / NULLIF(count, 0);  -- returns NULL instead of error

TESTING A PROCEDURE SYSTEMATICALLY

-- Test with a user who has no orders (edge case):
CALL CalculateOrderTotal(9999);     -- user that does not exist

-- Test with a user who has exactly one order:
CALL CalculateOrderTotal(3);

-- Test with a user who should qualify for a discount:
CALL CalculateOrderTotal(1);   -- Rahul who spent 164999

-- Verify result manually:
SELECT SUM(amount) FROM orders WHERE user_id = 1 AND status = 'paid';

Arjun found the bug: his procedure parameter was named user_id -- same as the column. The WHERE clause was always true. He renamed the parameter to p_user_id and all calculations became correct instantly.

Key takeaways

Add SELECT 'step name', variable_value; inside a procedure to print values at each step — remove them when done

Parameter shadowing: if a parameter has the same name as a column, MySQL may compare the column to itself — always prefix params with p_

NULL in a comparison returns UNKNOWN not TRUE or FALSE — use IFNULL(value, 0) or COALESCE for safe comparisons

Use NULLIF(denominator, 0) in division to return NULL instead of crashing on divide by zero

Test procedures with edge cases: no matching rows, NULL inputs, boundary values — not just the happy path

Commands from this chapter

$ SHOW CREATE PROCEDURE CalculateOrderTotal\G

See the full code of a stored procedure

$ SELECT routine_name, created FROM information_schema.ROUTINES WHERE routine_schema = 'myapp_db';

List all procedures and their creation date

$ SELECT 'DEBUG' AS step, variable_name AS value;

Add inside procedure to print intermediate values

$ SET total = IFNULL(subtotal, 0);

Handle NULL safely inside a procedure

$ SET avg = total / NULLIF(count_val, 0);

Prevent divide-by-zero crash

$ INSERT INTO debug_log (proc_name, step, message) VALUES ('ProcName', 'Step1', CONCAT('val=', my_var));

Write to a debug log table