MySQL Error Handling in Stored Procedures

MySQL Error Handling in Stored Procedures

Error handling in MySQL stored procedures allows developers to anticipate, manage, and respond to runtime errors gracefully. MySQL uses handlers to manage errors that occur during the execution of a stored procedure.

Error Handling Basics

1. Handlers:
   A handler is a construct used to specify actions to be taken when certain conditions, such as errors or warnings, occur.
   Syntax:

   DECLARE handler_type HANDLER FOR condition_value action;
   

   • handler_type: Specifies the type of handler. Options are:
     • CONTINUE: The procedure continues execution after handling the error.
     • EXIT: The procedure terminates execution after handling the error.
   • condition_value: Specifies the condition that triggers the handler. Common values:
     • SQLSTATE [value]: Matches a specific SQLSTATE error code (e.g., '23000' for integrity constraint violations).
     • SQLWARNING: Matches any warning condition.
     • NOT FOUND: Matches when no rows are returned (e.g., from a FETCH statement in a cursor).
     • SQLEXCEPTION: Matches any exception.
   • action: Specifies the action, usually a block of code or a simple SET statement.

Types of Error Handlers

1. EXIT Handler:
   The procedure stops execution after the error is handled.

   DECLARE EXIT HANDLER FOR SQLEXCEPTION
   BEGIN
       -- Code to handle the error
   END;
   

2. CONTINUE Handler:
   The procedure continues execution after the error is handled.

   DECLARE CONTINUE HANDLER FOR SQLWARNING
   BEGIN
       -- Code to handle the warning
   END;
   

Example: Basic Error Handling

1. Handling General SQL Errors

DELIMITER $$

CREATE PROCEDURE example_error_handling()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- Handle the error (e.g., log or clean up)
        ROLLBACK;
        SELECT 'An error occurred. The transaction was rolled back.';
    END;

    START TRANSACTION;

    -- Intentionally cause an error (e.g., insert duplicate key)
    INSERT INTO employees (id, name) VALUES (1, 'John Doe');
    INSERT INTO employees (id, name) VALUES (1, 'Jane Doe'); -- Error here

    COMMIT;
END$$

DELIMITER ;

Explanation:

1. Transaction Management:
   • The START TRANSACTION block ensures that changes are either fully applied or rolled back in case of an error.
2. Error Handler:
   • The EXIT handler rolls back the transaction and provides feedback to the user.

2. Handling Specific SQLSTATE Errors

DELIMITER $$

CREATE PROCEDURE handle_specific_error()
BEGIN
    DECLARE EXIT HANDLER FOR SQLSTATE '23000'
    BEGIN
        -- Handle integrity constraint violation
        SELECT 'Duplicate key error occurred.';
    END;

    -- Attempt to insert a duplicate key
    INSERT INTO employees (id, name) VALUES (1, 'John Doe');
    INSERT INTO employees (id, name) VALUES (1, 'Jane Doe'); -- Triggers the handler
END$$

DELIMITER ;

Error Handling with NOT FOUND

The NOT FOUND condition is useful for cursors or queries that may not return rows.

Example: Using NOT FOUND with a Cursor

DELIMITER $$

CREATE PROCEDURE process_data_with_cursor()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE emp_id INT;
    DECLARE emp_name VARCHAR(100);

    -- Declare a cursor
    DECLARE emp_cursor CURSOR FOR SELECT id, name FROM employees;

    -- Declare NOT FOUND handler
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    -- Open the cursor
    OPEN emp_cursor;

    -- Loop through rows
    read_loop: LOOP
        FETCH emp_cursor INTO emp_id, emp_name;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Process each row
        INSERT INTO employee_log (employee_id, employee_name, action)
        VALUES (emp_id, emp_name, 'Processed');
    END LOOP;

    -- Close the cursor
    CLOSE emp_cursor;
END$$

DELIMITER ;

Explanation:

1. The NOT FOUND handler sets the done variable to 1 when no more rows are found.
2. The loop terminates when done = 1.

Example: Combining Multiple Handlers

You can declare multiple handlers to manage different error types.

DELIMITER $$

CREATE PROCEDURE combined_handlers()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- Handle any SQL exception
        SELECT 'A general SQL error occurred.';
    END;

    DECLARE CONTINUE HANDLER FOR SQLWARNING
    BEGIN
        -- Handle warnings
        SELECT 'A warning was issued, but execution continues.';
    END;

    DECLARE CONTINUE HANDLER FOR NOT FOUND
    BEGIN
        -- Handle no data found
        SELECT 'No rows found.';
    END;

    -- Example operations
    INSERT INTO employees (id, name) VALUES (1, 'John Doe'); -- Possible duplicate key
    SELECT * FROM non_existing_table; -- Causes an exception
END$$

DELIMITER ;

Best Practices for Error Handling

1. Use Handlers Appropriately:

   • Use EXIT handlers for critical errors where execution must stop.
   • Use CONTINUE handlers to skip errors and proceed with the execution.

2. Log Errors:

   • Record errors in a log table for debugging and auditing.
   • Example:

     INSERT INTO error_log (error_message, error_time)
     VALUES ('Duplicate key error', NOW());
     

3. Combine Handlers:

   • Define separate handlers for different error conditions (SQLSTATE, SQLEXCEPTION, NOT FOUND, etc.).

4. Test Procedures Thoroughly:

   • Simulate different error scenarios to ensure your handlers work as expected.

5. Transaction Management:

   • Always use START TRANSACTION, COMMIT, and ROLLBACK for operations involving multiple steps.

Conclusion

Error handling in MySQL stored procedures provides the tools to make your database operations more robust and fault-tolerant. By using DECLARE HANDLER, you can gracefully manage errors, log important events, and ensure data consistency. For complex scenarios, carefully combining handlers with transaction management ensures smooth execution.

Let me know if you'd like further details or more advanced examples!