Practice Now
MySQL
debugging
stored procedures
database troubleshooting
SQL debugging

How do you debug MySQL stored procedures?

System Design practice on Codemia

Work through 120+ system design problems with detailed solutions, from rate limiters to multi-region storage.

Practice system design
DatabasesDebugging & Errors

Introduction

MySQL does not provide a built-in step debugger for stored procedures like SQL Server Management Studio does for T-SQL. Debugging MySQL stored procedures relies on techniques like inserting SELECT statements, using handler-based error logging, writing to debug tables, and leveraging tools like MySQL Workbench or dbForge Studio. Understanding these approaches lets you track variable values, identify logic errors, and diagnose runtime failures.

Method 1: SELECT Statements (Print Debugging)

Insert SELECT statements at key points to output variable values:

sql
1DELIMITER //
2CREATE PROCEDURE calculate_discount(IN customer_id INT)
3BEGIN
4    DECLARE total_orders DECIMAL(10,2);
5    DECLARE discount_rate DECIMAL(3,2);
6
7    SELECT SUM(amount) INTO total_orders
8    FROM orders WHERE cust_id = customer_id;
9
10    SELECT 'DEBUG: total_orders =', total_orders;  -- Debug output
11
12    IF total_orders > 1000 THEN
13        SET discount_rate = 0.15;
14    ELSEIF total_orders > 500 THEN
15        SET discount_rate = 0.10;
16    ELSE
17        SET discount_rate = 0.05;
18    END IF;
19
20    SELECT 'DEBUG: discount_rate =', discount_rate;  -- Debug output
21
22    UPDATE customers SET discount = discount_rate WHERE id = customer_id;
23END //
24DELIMITER ;

Call the procedure and read the debug output:

sql
CALL calculate_discount(42);
-- Returns result sets with debug values

Remove the SELECT debug lines before deploying to production.

Method 2: Debug Log Table

Write debug messages to a persistent table for complex procedures:

sql
1CREATE TABLE debug_log (
2    id INT AUTO_INCREMENT PRIMARY KEY,
3    proc_name VARCHAR(100),
4    message TEXT,
5    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
6);
7
8DELIMITER //
9CREATE PROCEDURE process_batch(IN batch_id INT)
10BEGIN
11    DECLARE row_count INT DEFAULT 0;
12
13    INSERT INTO debug_log (proc_name, message)
14    VALUES ('process_batch', CONCAT('Started with batch_id=', batch_id));
15
16    SELECT COUNT(*) INTO row_count FROM batch_items WHERE batch = batch_id;
17
18    INSERT INTO debug_log (proc_name, message)
19    VALUES ('process_batch', CONCAT('Found ', row_count, ' items'));
20
21    -- ... business logic ...
22
23    INSERT INTO debug_log (proc_name, message)
24    VALUES ('process_batch', 'Completed successfully');
25END //
26DELIMITER ;
27
28-- Review debug output
29SELECT * FROM debug_log ORDER BY created_at DESC LIMIT 20;
30
31-- Clean up
32TRUNCATE TABLE debug_log;

This persists across calls and is useful for procedures called by triggers or events where you cannot see SELECT output.

Method 3: DECLARE HANDLER for Error Trapping

Use condition handlers to catch and log errors:

sql
1DELIMITER //
2CREATE PROCEDURE safe_insert(IN user_name VARCHAR(100), IN user_email VARCHAR(100))
3BEGIN
4    DECLARE EXIT HANDLER FOR SQLEXCEPTION
5    BEGIN
6        GET DIAGNOSTICS CONDITION 1
7            @err_no = MYSQL_ERRNO,
8            @err_msg = MESSAGE_TEXT;
9
10        INSERT INTO debug_log (proc_name, message)
11        VALUES ('safe_insert', CONCAT('ERROR ', @err_no, ': ', @err_msg));
12
13        -- Re-signal the error to the caller
14        RESIGNAL;
15    END;
16
17    DECLARE EXIT HANDLER FOR SQLWARNING
18    BEGIN
19        GET DIAGNOSTICS CONDITION 1
20            @warn_msg = MESSAGE_TEXT;
21
22        INSERT INTO debug_log (proc_name, message)
23        VALUES ('safe_insert', CONCAT('WARNING: ', @warn_msg));
24    END;
25
26    INSERT INTO users (name, email) VALUES (user_name, user_email);
27END //
28DELIMITER ;

Common Handler Types

sql
1-- Catch specific error
2DECLARE CONTINUE HANDLER FOR 1062  -- Duplicate key
3    SET @duplicate_found = TRUE;
4
5-- Catch not-found (for cursors)
6DECLARE CONTINUE HANDLER FOR NOT FOUND
7    SET @done = TRUE;
8
9-- Catch all SQL exceptions
10DECLARE EXIT HANDLER FOR SQLEXCEPTION
11    BEGIN /* error handling */ END;

Method 4: User-Defined Variables for Tracing

Use session variables (@var) to trace execution without affecting procedure logic:

sql
1DELIMITER //
2CREATE PROCEDURE transfer_funds(IN from_id INT, IN to_id INT, IN amount DECIMAL(10,2))
3BEGIN
4    SET @debug_step = 'start';
5
6    SET @debug_step = 'check_balance';
7    SELECT balance INTO @from_balance FROM accounts WHERE id = from_id;
8
9    IF @from_balance < amount THEN
10        SET @debug_step = 'insufficient_funds';
11        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds';
12    END IF;
13
14    SET @debug_step = 'debit';
15    UPDATE accounts SET balance = balance - amount WHERE id = from_id;
16
17    SET @debug_step = 'credit';
18    UPDATE accounts SET balance = balance + amount WHERE id = to_id;
19
20    SET @debug_step = 'complete';
21END //
22DELIMITER ;
23
24-- After calling, check where it got to:
25CALL transfer_funds(1, 2, 100.00);
26SELECT @debug_step;  -- Shows last completed step

Method 5: MySQL Workbench Debugger

MySQL Workbench (versions 6.3+) includes a visual stored procedure debugger:

  1. Open MySQL Workbench and connect to your server
  2. Navigate to the stored procedure in the Schema panel
  3. Right-click the procedure > Debug Routine
  4. Set breakpoints by clicking line numbers
  5. Step through with F7 (Step Into), F8 (Step Over), F9 (Continue)
  6. Watch variables in the Variables panel

Requirements:

  • MySQL server must have the debug privilege granted
  • The INSTALL PLUGIN debugger statement may be needed
  • Works best with local or trusted connections

Method 6: SHOW WARNINGS and SHOW ERRORS

After calling a procedure, check for warnings:

sql
CALL my_procedure();
SHOW WARNINGS;
SHOW ERRORS;

Common Pitfalls

  • SELECT debug output in triggers: Triggers cannot return result sets (SELECT for output). Use the debug log table approach instead.
  • Forgetting to remove debug statements: Debug SELECT statements left in production procedures return extra result sets that break application code. Use the log table approach or conditional debug flags.
  • Transaction rollback erases debug logs: If your procedure uses ROLLBACK, debug log INSERT statements within the transaction are also rolled back. Write to the debug table in a separate connection or use session variables.
  • SIGNAL terminates execution: SIGNAL SQLSTATE '45000' immediately exits the procedure (like throwing an exception). Log the error before signaling.
  • Cursor debugging: When debugging cursors, the NOT FOUND handler fires after the last row. A common bug is processing the last row twice — add SELECT 'cursor done' in the handler to verify.

Summary

  • Use SELECT statements for quick variable inspection during development
  • Use a debug log table for persistent tracing, especially in triggers and events
  • Use DECLARE HANDLER with GET DIAGNOSTICS to catch and log errors
  • Use session variables (@var) to trace execution flow without extra result sets
  • MySQL Workbench offers a visual step debugger for interactive debugging
  • Always remove debug output before deploying to production
Related reading
Course
Beginner
27 lessons
10 hours
System Design Fundamentals

Build a strong foundation in designing scalable, reliable distributed systems.

View the course
Track what you have practised

A free account saves your progress, solutions and study plan across every problem on Codemia.

System Design practice on Codemia

Work through 120+ system design problems with detailed solutions, from rate limiters to multi-region storage.

Practice system design
Codemia
© 2026 Codemia
Resources
Legal
Social
All Rights Reserved.