MySQL RESIGNAL

Master MySQL RESIGNAL for re-raising caught errors from inside handlers. Covers bare RESIGNAL (preserve original), RESIGNAL with SET (modify diagnostic items), RESIGNAL with new SQLSTATE (full translation), the canonical EXIT HANDLER FOR SQLEXCEPTION + ROLLBACK + RESIGNAL transactional pattern, and SHOW ERRORS / SHOW WARNINGS for inspecting the last statement's conditions.

The RESIGNAL statement, used inside a handler, re-raises the current condition — passing it up to the caller as if your handler hadn't caught it. You can re-raise it unchanged, or modify the SQLSTATE, error number, and message before it propagates.

This is the missing piece in clean error handling. Without RESIGNAL, your handler either has to fully recover from the error or invent some way to tell the caller it failed. With RESIGNAL, you can do cleanup and then let the error propagate — exactly like try { ... } catch (e) { cleanup(); throw; } in modern languages.

1. Bare RESIGNAL — Re-Raise Unchanged

Re-raises the current condition exactly as caught. The SQLSTATE, error number, and message are all preserved. This is the most common form — used in the standard ROLLBACK; RESIGNAL; pattern.

2. RESIGNAL with SET — Modify the Diagnostic

Re-raises the current condition with one or more pieces of diagnostic information replaced. The unchanged items are preserved. Useful when you want to translate a low-level error into a more meaningful one.

3. RESIGNAL with New Condition — Replace SQLSTATE

Replaces the SQLSTATE entirely (and optionally other items) — turns the original condition into a different one before propagating. Most aggressive form.

The info_item values you can set on RESIGNAL (and SIGNAL) match what GET DIAGNOSTICS can read:

MYSQL_ERRNO and MESSAGE_TEXT are by far the most commonly used.

The simplest pattern: catch any SQL exception, optionally do cleanup, then bare-RESIGNAL to let the original error propagate:

DROP PROCEDURE IF EXISTS DropTableXYZ;

DELIMITER //

CREATE PROCEDURE DropTableXYZ()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- (cleanup would go here)
        RESIGNAL;     -- pass the same error up to the caller
    END;

    -- This will fail because XYZ doesn't exist
    DROP TABLE XYZ;
END //

DELIMITER ;

CALL DropTableXYZ();

The original error (1051, "Unknown table") flows through unchanged. Without the RESIGNAL inside the handler, the caller would have seen the procedure complete normally — silently masking the failure.

Catch the error, then re-raise it with a different error number — for example, mapping internal codes to your application's error scheme:

DROP PROCEDURE IF EXISTS DropTableXYZ;

DELIMITER //

CREATE PROCEDURE DropTableXYZ()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        RESIGNAL SET MYSQL_ERRNO = 10;
    END;

    DROP TABLE XYZ;
END //

DELIMITER ;

CALL DropTableXYZ();

The error number is now 10, but the SQLSTATE (42S02) and message (Unknown table 'XYZ') are preserved from the original. Only what you SET changes; everything else flows through.

Aggressive translation — turn an internal error into something more meaningful for the calling application:

DROP PROCEDURE IF EXISTS DropTableXYZ;

DELIMITER //

CREATE PROCEDURE DropTableXYZ()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        RESIGNAL SQLSTATE '45000'
            SET MYSQL_ERRNO  = 9999,
                MESSAGE_TEXT = 'Setup failed — table missing';
    END;

    DROP TABLE XYZ;
END //

DELIMITER ;

CALL DropTableXYZ();

Now the caller sees an application-level error (SQLSTATE 45000 is the convention for user-defined exceptions) with a meaningful message, instead of having to interpret raw MySQL error 1051.

The most important RESIGNAL use case: combine with ROLLBACK to make stored procedures transaction-safe:

DROP PROCEDURE IF EXISTS transfer;

DELIMITER $$

CREATE PROCEDURE transfer(IN sender_id   INT,
                          IN receiver_id INT,
                          IN amount      DECIMAL(10, 2))
    MODIFIES SQL DATA
BEGIN
    -- One handler, catches everything, cleans up, re-raises
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    UPDATE accounts SET balance = balance - amount WHERE account_id = sender_id;
    UPDATE accounts SET balance = balance + amount WHERE account_id = receiver_id;

    COMMIT;
END$$

DELIMITER ;

If any SQL exception occurs — sender doesn't exist, balance constraint violated, deadlock, network blip — the handler runs ROLLBACK (so the database is left consistent), then RESIGNAL (so the caller learns what happened). The original error code, SQLSTATE, and message are all preserved, which is exactly what the application needs to react properly.

Two interactive commands let you inspect the error/warning state of the most recent statement:

Run a query that fails, then inspect the error:

SELECT id FROM employees;     -- column 'id' doesn't exist (it's employee_id)

SHOW ERRORS;

DROP TABLE IF EXISTS doesn't error when the table is missing — it raises a note, which SHOW WARNINGS reveals:

DROP TABLE IF EXISTS abc_does_not_exist;

SHOW WARNINGS;

The output has three columns:

1. Use bare RESIGNAL; by default — preserves the original diagnostic information, which is what the caller usually wants.
2. Pair RESIGNAL with cleanup work — the standard pattern is BEGIN cleanup_steps; RESIGNAL; END; inside the handler.
3. Translate errors only when it helps the caller. Replacing a specific error with a generic one ("something went wrong") loses information; do it only when you genuinely need to abstract internals.
4. Use SQLSTATE '45000' for user-defined errors — that's the conventional class for "unhandled user-defined exception".
5. Always set both MYSQL_ERRNO and MESSAGE_TEXT when translating — without a custom errno, callers can't distinguish your translated error from the underlying one.
6. Don't RESIGNAL outside a handler. It's only valid inside an active handler context; using it elsewhere is a syntax error.
7. Use SHOW WARNINGS to debug — many MySQL operations produce warnings/notes that aren't visible by default. SHOW WARNINGS reveals them.

• RESIGNAL re-raises the current condition from inside a handler — like throw; in an except block.
• Three forms: bare (preserve everything), SET-only (modify diagnostic info, keep SQLSTATE), full (replace SQLSTATE and optionally other items).
• The most useful info-items to set: MYSQL_ERRNO and MESSAGE_TEXT.
• The canonical transactional pattern: DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END; — catches any error, rolls back the transaction, and lets the caller see what went wrong.
• SHOW ERRORS displays errors from the last statement; SHOW WARNINGS additionally shows warnings and notes.
• Output of those commands has three columns: Level, Code, Message.