How to handle errors in MySQL stored procedures

Use DECLARE CONTINUE or DECLARE EXIT HANDLER to specify error handling behavior, where CONTINUE allows execution to proceed after handling the error, and EXIT stops execution of the current block; 2. Handle errors using SQLSTATE values (e.g., '23000' for constraint violations), MySQL error codes (e.g., 1062 for duplicate entries), or named conditions for better readability by declaring a condition first and referencing it in the handler; 3. In practical applications, such as a user insertion procedure, implement a CONTINUE HANDLER for specific errors like duplicate keys to log messages and allow further execution, while using an EXIT HANDLER for SQLEXCEPTION to rollback transactions and re-throw errors with RESIGNAL; 4. Enhance logging and debugging by capturing error details via GET DIAGNOSTICS to retrieve SQLSTATE, error number, and message text, and store them in variables or log tables for analysis; 5. Follow best practices including wrapping data modifications in transactions, avoiding silent failures by logging or signaling errors, using RESIGNAL to propagate handled errors, preferring named conditions for maintainability, and testing error scenarios to ensure handlers function correctly, thereby building robust MySQL stored procedures despite limited native error handling capabilities.

Handling errors in MySQL stored procedures is essential for building robust database applications. Without proper error handling, unexpected issues like constraint violations or invalid data can cause procedures to fail silently or produce incorrect results. Here’s how to effectively manage errors using MySQL’s DECLARE HANDLER syntax and related features.

1. Use DECLARE CONTINUE or DECLARE EXIT HANDLER

MySQL allows you to define error handlers using the DECLARE HANDLER statement. These handlers specify what should happen when a specific condition (like an error or warning) occurs during procedure execution.

There are two main types:

• CONTINUE: Execution continues after handling the error.
• EXIT: Execution stops and exits the current block (typically used in stored procedures or functions).

Syntax:

DECLARE handler_type HANDLER FOR condition_value statement;

Common handler_type: CONTINUE, EXIT, or UNDO (not supported in MySQL as of now).

2. Handle Errors by SQLSTATE, MySQL Error Code, or Named Conditions

You can define handlers based on:

a) SQLSTATE values

These are 5-character standardized error codes.

DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' 
BEGIN
    -- Handle duplicate key or constraint violation
    SET @error_message = 'Integrity constraint violated';
END;

b) MySQL-specific error numbers

Use 1062 for duplicate entry, for example.

DECLARE CONTINUE HANDLER FOR 1062 
BEGIN
    SET @error_message = 'Duplicate entry found';
    SET @sql_error = 1;
END;

c) Named conditions (improves readability)

First define a named condition, then use it in the handler.

DECLARE duplicate_entry CONDITION FOR 1062;

DECLARE CONTINUE HANDLER FOR duplicate_entry
BEGIN
    SET @error_message = 'Duplicate entry occurred';
END;

3. Practical Example: Insert with Error Handling

Here’s a stored procedure that attempts to insert a record and gracefully handles a duplicate key error:

DELIMITER $$

CREATE PROCEDURE InsertUser(
    IN p_username VARCHAR(50),
    IN p_email VARCHAR(100)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL; -- Re-throw the error after rollback
    END;

    DECLARE CONTINUE HANDLER FOR 1062
    BEGIN
        SET @msg = CONCAT('User with username=', p_username, ' already exists');
        INSERT INTO error_log(message, created_at) 
        VALUES (@msg, NOW());
    END;

    START TRANSACTION;

    INSERT INTO users(username, email) VALUES (p_username, p_email);

    COMMIT;
END$$

DELIMITER ;

In this example:

• A CONTINUE handler catches duplicate key errors (1062), logs them, and allows the procedure to continue.
• An EXIT handler for general SQL exceptions performs a rollback and re-signals the error using RESIGNAL to inform the caller.
• Transactions ensure data consistency.

4. Logging and Debugging Tips

When handling errors, consider:

• Setting user-defined variables (e.g., @error_flag = 1) to track status.
• Logging errors into an audit or error table.
• Using GET DIAGNOSTICS to retrieve detailed error info (available in MySQL 5.6 ):

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

SELECT @sqlstate, @errno, @text; -- For debugging

Add this inside a handler to inspect the exact error.

5. Best Practices

• Always use transactions when modifying data and pair them with EXIT HANDLER ROLLBACK.
• Avoid silent failures — log or signal errors appropriately.
• Use RESIGNAL if you want to handle an error locally but still notify the caller.
• Prefer named conditions for better code readability and maintenance.
• Test error scenarios manually (e.g., insert duplicates) to verify handlers work.

Handling errors in MySQL stored procedures isn’t as advanced as in other databases, but with DECLARE HANDLER, SIGNAL, and GET DIAGNOSTICS, you can build reliable logic. The key is anticipating common issues and responding gracefully.

Basically, define handlers early in the procedure, wrap writes in transactions, and always clean up or report when things go wrong.

The above is the detailed content of How to handle errors in MySQL stored procedures. For more information, please follow other related articles on the PHP Chinese website!