MySQL SIGNAL

Master MySQL SIGNAL for raising user-defined errors from stored programs. Covers the SIGNAL vs RESIGNAL distinction (throw vs re-throw), SQLSTATE 45000 convention for user-defined exceptions, default MYSQL_ERRNO 1644, MESSAGE_TEXT and other diagnostic items, validation patterns, and how SIGNAL'd errors interact with handlers and the transactional rollback pattern.

The SIGNAL statement raises an error from inside a stored program — like throw new Error(...) in JavaScript or raise ValueError(...) in Python. You use it to enforce business rules: "salary can't be negative", "order quantity must be positive", "customer not found". Without SIGNAL, your stored programs would have to invent their own error-reporting conventions; with it, errors propagate exactly the way the caller's application expects.

In short: SIGNAL throws; RESIGNAL re-throws. If you're inside a handler that caught something and want to pass it on, use RESIGNAL. If you're in normal code and detect an invalid state, use SIGNAL.

Where:

• condition_value — required. Either an SQLSTATE [VALUE] '...' or a named condition (declared with DECLARE ... CONDITION). MySQL error codes (just an integer) are not valid here — use SQLSTATE.
• SET clause — optional. Provides diagnostic information: MESSAGE_TEXT, MYSQL_ERRNO, etc.

The SQLSTATE you pass to SIGNAL can be any 5-character value except ones starting with '00' (success), '01' (warning), or '02' (no data). For arbitrary user-defined errors, the convention is:

Using '45000' is the most common choice. It clearly signals "this is application logic, not a MySQL internal error", and any reasonable application error handler will recognize the 45... class as user-defined.

The same items as RESIGNAL (and the same as GET DIAGNOSTICS can read):

For most use cases, MESSAGE_TEXT is the only item you'll set — sometimes paired with MYSQL_ERRNO for finer error-type information.

CREATE DATABASE IF NOT EXISTS hr;
USE hr;

DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
    id      INT PRIMARY KEY,
    name    VARCHAR(100),
    salary  DECIMAL(10, 2)
);

INSERT INTO employees (id, name, salary) VALUES
    (1, 'John Doe',    5000),
    (2, 'Jane Smith',  7500),
    (3, 'Bob Johnson', 9000);

A procedure that updates an employee's salary. Before the UPDATE, we validate two things — does the employee exist, and is the salary non-negative? On either failure, raise an application error with SIGNAL:

DROP PROCEDURE IF EXISTS update_salary;

DELIMITER //

CREATE PROCEDURE update_salary(IN p_employee_id INT,
                               IN p_salary      DECIMAL(10, 2))
    MODIFIES SQL DATA
BEGIN
    DECLARE employee_count INT;

    -- Check existence
    SELECT COUNT(*) INTO employee_count
    FROM   employees
    WHERE  id = p_employee_id;

    IF employee_count = 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Employee not found';
    END IF;

    -- Validate salary
    IF p_salary < 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Salary cannot be negative';
    END IF;

    -- All good — update
    UPDATE employees
    SET    salary = p_salary
    WHERE  id     = p_employee_id;
END //

DELIMITER ;

Try a successful update first:

CALL update_salary(1, 6000);

Now try a negative salary — SIGNAL fires:

CALL update_salary(1, -6000);

And a non-existent employee:

CALL update_salary(999, 6000);

SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Salary cannot be negative',
        MYSQL_ERRNO  = 50001;

An error raised with SIGNAL is just a SQL exception like any other — it can be caught by a DECLARE HANDLER in an enclosing block or in the calling procedure. Here's a wrapper that catches a SIGNAL from a child procedure:

DROP PROCEDURE IF EXISTS safe_update_salary;

DELIMITER //

CREATE PROCEDURE safe_update_salary(IN p_employee_id INT,
                                    IN p_salary      DECIMAL(10, 2))
    MODIFIES SQL DATA
BEGIN
    -- Catch any error, log it, and continue
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        DECLARE v_message TEXT;
        GET DIAGNOSTICS CONDITION 1 v_message = MESSAGE_TEXT;

        SELECT CONCAT('Update skipped: ', v_message) AS notice;
    END;

    CALL update_salary(p_employee_id, p_salary);
    SELECT 'Salary updated' AS result;
END //

DELIMITER ;

-- This will trigger the inner SIGNAL, get caught by the outer handler
CALL safe_update_salary(999, 6000);

The inner SIGNAL raised the error; the outer procedure's handler caught it via SQLEXCEPTION, retrieved the message via GET DIAGNOSTICS, and turned it into a friendly notice. The "Salary updated" message didn't run because the EXIT handler ended the block.

If your application has its own error-categorization scheme, set a custom error number for easier app-side handling:

DROP PROCEDURE IF EXISTS process_order;

DELIMITER $$

CREATE PROCEDURE process_order(IN p_order_id  INT,
                               IN p_quantity  INT)
BEGIN
    IF p_quantity <= 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Quantity must be positive',
                MYSQL_ERRNO  = 50100;
    END IF;

    IF p_quantity > 1000 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Quantity exceeds bulk limit',
                MYSQL_ERRNO  = 50101;
    END IF;

    -- ... order processing ...
END$$

DELIMITER ;

CALL process_order(101, 0);

Now your application code can catch error 50100 specifically and handle it differently from 50101 — both are SQLSTATE 45000 (user-defined), but the numeric code disambiguates them.

Pair SIGNAL with DECLARE ... CONDITION for self-documenting code:

DROP PROCEDURE IF EXISTS withdraw;

DELIMITER $$

CREATE PROCEDURE withdraw(IN p_account_id INT,
                          IN p_amount     DECIMAL(10, 2))
    MODIFIES SQL DATA
BEGIN
    DECLARE insufficient_funds CONDITION FOR SQLSTATE '45100';
    DECLARE v_balance          DECIMAL(10, 2);

    SELECT balance INTO v_balance
    FROM   accounts
    WHERE  account_id = p_account_id;

    IF v_balance < p_amount THEN
        SIGNAL insufficient_funds
            SET MESSAGE_TEXT = 'Withdrawal exceeds available balance';
    END IF;

    UPDATE accounts
    SET    balance = balance - p_amount
    WHERE  account_id = p_account_id;
END$$

DELIMITER ;

Reads naturally: "if v_balance < p_amount, signal insufficient_funds". The named condition documents intent clearly.

1. Use SIGNAL for business-rule violations — anything the application's code shouldn't have asked for. Validation failures, invalid states, missing data.
2. Use SQLSTATE '45000' as the catch-all for user-defined errors. Pick custom '451xx'-style values only when you need fine-grained categorization on the application side.
3. Always set MESSAGE_TEXT — the caller will see it. Make it descriptive and actionable.
4. Set a custom MYSQL_ERRNO when applications need to programmatically distinguish error types. The default 1644 is fine for human-only errors.
5. Validate early. SIGNAL before doing any work — fail fast, don't roll back partial work.
6. Don't SIGNAL warnings. If something is just a heads-up, log it or return a status code. SIGNAL is for failures.
7. Pair SIGNAL with named conditions when the same error appears in multiple procedures — easier to maintain a single condition definition than scattered SQLSTATE strings.
8. Inside a transaction, SIGNAL works with the EXIT HANDLER + ROLLBACK + RESIGNAL pattern seamlessly — your validation SIGNAL will be caught by the outer handler, transaction rolled back, error re-raised.

• SIGNAL SQLSTATE '...' SET MESSAGE_TEXT = '...'; raises an error from inside a stored program — the SQL equivalent of throw.
• Use SIGNAL to throw new errors (validation, business rules); use RESIGNAL to re-throw caught errors from inside a handler.
• Required: a SQLSTATE that doesn't start with '00', '01', or '02'. Convention: '45000' for generic user-defined errors.
• Always set MESSAGE_TEXT; optionally set MYSQL_ERRNO for app-side categorization.
• Without an explicit MYSQL_ERRNO, MySQL defaults to 1644 for user-defined SQLSTATE 45000 errors.
• SIGNAL'd errors are catchable by DECLARE HANDLER in enclosing blocks or in calling procedures — interacts cleanly with the transaction-rollback pattern.
• Pair with DECLARE ... CONDITION for self-documenting validation code: SIGNAL insufficient_funds SET MESSAGE_TEXT = '...';