MySQL Error Handling

Error handling is a critical aspect of writing robust and reliable stored procedures in MySQL. When your stored procedures run into unexpected issues, proper error handling ensures your database remains consistent and provides meaningful feedback to applications or users.

Introduction to Error Handling

In any programming environment, things can go wrong. Database connections might fail, queries might encounter duplicate keys, or data might violate constraints. Without proper error handling, your stored procedures would simply abort execution when encountering an error, potentially leaving databases in inconsistent states or failing silently without any useful feedback.

MySQL provides mechanisms to:

1. Detect errors
2. Handle errors gracefully
3. Provide meaningful error messages
4. Take alternative actions when errors occur

Error Handling Fundamentals

Understanding MySQL Error Codes

MySQL uses numeric error codes to identify specific error conditions. Each error code is associated with an "SQLSTATE" value (a five-character string) and an error message.

For example:

• Error Code 1062: Duplicate entry (SQLSTATE: '23000')
• Error Code 1216: Foreign key constraint fails (SQLSTATE: '23000')

The DECLARE...HANDLER Statement

The core of MySQL error handling is the DECLARE...HANDLER statement, which lets you specify how to respond when specific errors occur.

Syntax:

sql

DECLARE handler_type HANDLER FOR condition_value [, condition_value] ... handler_statements

Where:

• handler_type can be CONTINUE or EXIT
• condition_value can be:
  • A specific MySQL error code
  • An SQLSTATE value
  • A named condition
  • General conditions like SQLEXCEPTION, SQLWARNING, or NOT FOUND
• handler_statements is the SQL statement to execute when the handler is triggered

Types of Handlers

CONTINUE Handler

When a CONTINUE handler catches an error, it executes its handler code and then continues execution from the statement following the one that caused the error.

sql

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @error_occurred = TRUE;

EXIT Handler

An EXIT handler executes its handler code and then exits the current BEGIN...END compound statement.

sql

DECLARE EXIT HANDLER FOR SQLEXCEPTION 
BEGIN
  ROLLBACK;
  SELECT 'An error occurred, transaction rolled back' AS message;
END;

Basic Error Handling Example

Let's create a simple stored procedure with error handling:

sql

DELIMITER //

CREATE PROCEDURE InsertUser(IN p_username VARCHAR(50), IN p_email VARCHAR(100))
BEGIN
  -- Declare variables to track errors
  DECLARE duplicate_entry CONDITION FOR 1062;
  DECLARE error_message VARCHAR(255);
  
  -- Declare an EXIT handler for duplicate key errors
  DECLARE EXIT HANDLER FOR duplicate_entry
  BEGIN
    SET error_message = CONCAT('Error: Username or email already exists: ', p_username);
    SELECT error_message AS 'Error';
  END;

  -- Declare a generic error handler for other errors
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    GET DIAGNOSTICS CONDITION 1
      @sqlstate = RETURNED_SQLSTATE,
      @errno = MYSQL_ERRNO,
      @text = MESSAGE_TEXT;
    
    SET error_message = CONCAT('Error: ', @errno, ' (', @sqlstate, '): ', @text);
    SELECT error_message AS 'Error';
  END;
  
  -- Attempt to insert the new user
  INSERT INTO users (username, email) VALUES (p_username, p_email);
  
  SELECT CONCAT('User ', p_username, ' successfully created') AS 'Success';
END //

DELIMITER ;

How This Works:

1. We declare two handlers:

   • A specific handler for error 1062 (duplicate entry)
   • A generic handler for all other exceptions

2. When a duplicate key error occurs:

   • The duplicate_entry handler executes
   • A friendly error message is displayed
   • The procedure exits

3. When any other error occurs:

   • The generic handler executes
   • We use GET DIAGNOSTICS to retrieve detailed error information
   • A descriptive error message is displayed
   • The procedure exits

Input and Output Example

When calling the procedure with a new user:

sql

CALL InsertUser('john_doe', '[email protected]');

Output:

+----------------------------------+
| Success                          |
+----------------------------------+
| User john_doe successfully created |
+----------------------------------+

When calling it with a duplicate username:

sql

CALL InsertUser('john_doe', '[email protected]');

Output:

+--------------------------------------------+
| Error                                      |
+--------------------------------------------+
| Error: Username or email already exists: john_doe |
+--------------------------------------------+

Advanced Error Handling Techniques

Using GET DIAGNOSTICS

For more detailed error information, MySQL provides the GET DIAGNOSTICS statement:

sql

GET DIAGNOSTICS CONDITION 1
  @sqlstate = RETURNED_SQLSTATE,
  @errno = MYSQL_ERRNO, 
  @text = MESSAGE_TEXT;

This populates variables with:

• The SQLSTATE value
• The MySQL error code
• The error message text

Handling Multiple Error Conditions

You can handle multiple specific errors with different strategies:

sql

DELIMITER //

CREATE PROCEDURE TransferFunds(
  IN from_account INT, 
  IN to_account INT,
  IN amount DECIMAL(10,2)
)
BEGIN
  -- Declare error handlers
  DECLARE insufficient_funds CONDITION FOR 1690; -- Custom error code for insufficient funds
  DECLARE invalid_account CONDITION FOR 1048; -- NULL value in non-nullable column
  
  -- Handler for insufficient funds
  DECLARE EXIT HANDLER FOR insufficient_funds
  BEGIN
    SELECT 'Error: Insufficient funds for transfer' AS 'Error';
  END;
  
  -- Handler for invalid account
  DECLARE EXIT HANDLER FOR invalid_account 
  BEGIN
    SELECT 'Error: Invalid account number' AS 'Error';
  END;
  
  -- Generic error handler
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    GET DIAGNOSTICS CONDITION 1
      @sqlstate = RETURNED_SQLSTATE,
      @errno = MYSQL_ERRNO,
      @text = MESSAGE_TEXT;
    
    SELECT CONCAT('Error ', @errno, ': ', @text) AS 'Error';
    ROLLBACK;
  END;
  
  -- Start transaction
  START TRANSACTION;
  
  -- Check if accounts exist and have sufficient funds
  IF NOT EXISTS (SELECT 1 FROM accounts WHERE account_id = from_account) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Source account does not exist';
  END IF;
  
  IF NOT EXISTS (SELECT 1 FROM accounts WHERE account_id = to_account) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Destination account does not exist';
  END IF;
  
  IF (SELECT balance FROM accounts WHERE account_id = from_account) < amount THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds';
  END IF;
  
  -- Perform the transfer
  UPDATE accounts SET balance = balance - amount WHERE account_id = from_account;
  UPDATE accounts SET balance = balance + amount WHERE account_id = to_account;
  
  -- If we got here, commit the transaction
  COMMIT;
  
  SELECT 'Transfer completed successfully' AS 'Success';
END //

DELIMITER ;

Using SIGNAL to Raise Errors

MySQL's SIGNAL statement allows you to generate errors from within stored procedures:

sql

SIGNAL SQLSTATE '45000' 
SET MESSAGE_TEXT = 'Custom error message here';

The SQLSTATE '45000' indicates a custom error. Here's an example of using SIGNAL:

sql

DELIMITER //

CREATE PROCEDURE ValidateAge(IN p_age INT)
BEGIN
  IF p_age < 0 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Age cannot be negative';
  ELSEIF p_age > 120 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Invalid age: Age cannot be greater than 120';
  ELSE
    SELECT CONCAT('Age ', p_age, ' is valid') AS 'Result';
  END IF;
END //

DELIMITER ;

Example calls:

sql

CALL ValidateAge(30);  -- Success
CALL ValidateAge(-5);  -- Error: Age cannot be negative
CALL ValidateAge(130); -- Error: Invalid age: Age cannot be greater than 120

Implementing Transactions with Error Handling

Transactions and error handling work together to maintain data integrity:

sql

DELIMITER //

CREATE PROCEDURE CreateOrderWithItems(
  IN p_customer_id INT,
  IN p_product_id INT,
  IN p_quantity INT
)
BEGIN
  DECLARE v_order_id INT;
  
  -- Declare error handler for transaction
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    GET DIAGNOSTICS CONDITION 1
      @sqlstate = RETURNED_SQLSTATE,
      @errno = MYSQL_ERRNO,
      @text = MESSAGE_TEXT;
    
    SELECT CONCAT('Error ', @errno, ': ', @text) AS 'Error';
  END;
  
  START TRANSACTION;
  
  -- Insert the order header
  INSERT INTO orders (customer_id, order_date, status)
  VALUES (p_customer_id, NOW(), 'PENDING');
  
  -- Get the new order ID
  SET v_order_id = LAST_INSERT_ID();
  
  -- Insert the order item
  INSERT INTO order_items (order_id, product_id, quantity)
  VALUES (v_order_id, p_product_id, p_quantity);
  
  -- Update inventory (might trigger an error if not enough stock)
  UPDATE products 
  SET stock_quantity = stock_quantity - p_quantity
  WHERE product_id = p_product_id;
  
  -- Check if we have negative inventory after update
  IF (SELECT stock_quantity FROM products WHERE product_id = p_product_id) < 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient inventory';
  END IF;
  
  -- Everything went well, commit the transaction
  COMMIT;
  
  SELECT CONCAT('Order created successfully with ID: ', v_order_id) AS 'Success';
END //

DELIMITER ;

In this example:

1. We start a transaction
2. Insert an order and order items
3. Update inventory
4. If an error occurs at any stage, everything is rolled back
5. If all operations succeed, we commit the transaction

Real-World Application: Audit Logging with Error Handling

This example demonstrates how to create a stored procedure that logs user actions and properly handles errors:

sql

DELIMITER //

CREATE PROCEDURE UpdateUserProfile(
  IN p_user_id INT,
  IN p_email VARCHAR(100),
  IN p_full_name VARCHAR(150),
  IN p_modified_by INT
)
BEGIN
  DECLARE v_current_time DATETIME;
  DECLARE v_original_email VARCHAR(100);
  
  -- Error handling
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    
    GET DIAGNOSTICS CONDITION 1
      @sqlstate = RETURNED_SQLSTATE,
      @errno = MYSQL_ERRNO,
      @text = MESSAGE_TEXT;
      
    -- Log the error to an error_log table
    INSERT INTO error_log (error_code, error_message, procedure_name, created_at)
    VALUES (@errno, @text, 'UpdateUserProfile', NOW());
    
    -- Re-throw the error to the caller
    SIGNAL SQLSTATE @sqlstate
    SET MESSAGE_TEXT = @text;
  END;
  
  -- Start transaction
  START TRANSACTION;
  
  -- Get current time for consistent timestamps
  SET v_current_time = NOW();
  
  -- Get original email for audit log
  SELECT email INTO v_original_email 
  FROM users 
  WHERE user_id = p_user_id;
  
  -- Update the user profile
  UPDATE users
  SET 
    email = p_email,
    full_name = p_full_name,
    updated_at = v_current_time
  WHERE user_id = p_user_id;
  
  -- If no rows were affected, the user doesn't exist
  IF ROW_COUNT() = 0 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'User not found';
  END IF;
  
  -- Log the change to audit_log table
  INSERT INTO audit_log 
    (user_id, action, field_name, old_value, new_value, modified_by, created_at)
  VALUES 
    (p_user_id, 'UPDATE', 'email', v_original_email, p_email, p_modified_by, v_current_time);
  
  -- Commit transaction if everything succeeded
  COMMIT;
  
  SELECT 'Profile updated successfully' AS 'Result';
END //

DELIMITER ;

This robust procedure:

1. Wraps operations in a transaction
2. Captures error details
3. Logs errors to a dedicated table
4. Creates audit trail entries
5. Re-throws errors to inform the calling application

Best Practices for Error Handling

1. Always use transactions with error handling for operations that modify data
2. Be specific with error handlers - catch specific errors where you need special handling
3. Provide meaningful error messages to help with troubleshooting
4. Log errors to a table for auditing and monitoring
5. Use SIGNAL for validation errors to catch problems early
6. Organize error handling code for readability
7. Consider using custom error codes for application-specific errors
8. Test error conditions thoroughly to ensure handlers work as expected

Summary

Error handling is an essential aspect of building reliable MySQL stored procedures. Through DECLARE...HANDLER statements, you can catch and manage specific error conditions, making your database code more resilient and user-friendly.

In this guide, we've covered:

• The basics of MySQL error handling
• Different types of handlers (CONTINUE and EXIT)
• Handling specific error codes and generic exceptions
• Using GET DIAGNOSTICS for detailed error information
• Generating custom errors with the SIGNAL statement
• Combining transactions with error handling
• Real-world error handling patterns

By implementing proper error handling in your stored procedures, you can:

• Prevent partial operations that leave data inconsistent
• Provide meaningful feedback to applications and users
• Create more maintainable and professional database code
• Log issues for troubleshooting and monitoring

Additional Resources and Exercises

Exercises

1. Modify the InsertUser procedure to include logging of errors to an error_log table.
2. Create a stored procedure for updating a product's inventory that includes validation and error handling.
3. Implement a stored procedure that transfers money between accounts with comprehensive error handling for all possible error conditions.
4. Create a procedure that implements retries on deadlock errors.

Further Learning

• MySQL Error Codes: Review the comprehensive list of MySQL error codes in the official documentation
• MySQL Diagnostic Statements: Learn more about GET DIAGNOSTICS and other diagnostic capabilities
• Transaction Isolation Levels: Understand how different isolation levels interact with error handling
• Performance Considerations: Study how error handling affects the performance of your stored procedures

By mastering error handling techniques, you'll create more professional, reliable, and user-friendly database applications.