MySQL - Resignal

MySQL: The RESIGNAL Statement

In MySQL stored procedures, robust error handling is essential for creating reliable and predictable application logic. While simple handlers can catch standard MySQL errors, the SIGNAL and RESIGNAL statements give you the power to create and manage your own custom error conditions.

This allows you to enforce complex business rules directly within the database, providing clear, meaningful error messages back to the client application.

The SIGNAL and RESIGNAL Statements

SIGNAL is used to raise a custom error or warning. You define the SQLSTATE and can provide a custom MESSAGE_TEXT.

RESIGNAL is used inside an error handler. It allows you to re-raise the current error, or to raise a different error, possibly with a more user-friendly message, after performing some logic like logging the original error.

Syntax

-- Raise a new custom error
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Your custom error message';

-- Inside a handler, re-raise an error with a new message
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    RESIGNAL SET MESSAGE_TEXT = 'An internal error occurred.';
END;

The SQLSTATE ‘45000’ is a generic state reserved for user-defined exceptions, making it a safe choice for custom application errors.

A Practical Example: Validating an Order

Let’s create a stored procedure to place an order. It will check if the product’s inventory is sufficient. If not, it will SIGNAL a custom error. We will also use RESIGNAL inside a generic handler to provide a standard message for any unexpected database errors.

Setup: Tables

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(100),
    stock_level INT NOT NULL
);

INSERT INTO products VALUES (101, 'Super Widget', 20);

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    quantity INT
);

Setup: Stored Procedure

DELIMITER //

CREATE PROCEDURE place_order(IN p_product_id INT, IN p_quantity INT)
BEGIN
    DECLARE current_stock INT;
    -- Custom condition for insufficient stock
    DECLARE insufficient_stock CONDITION FOR SQLSTATE '45000';

    -- Generic handler to catch any other SQL error and resignal it
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- Here you could log the original error before resignaling
        RESIGNAL SET MESSAGE_TEXT = 'An unexpected error occurred while placing the order.';
    END;

    -- Start transaction
    START TRANSACTION;

    -- Check current stock level
    SELECT stock_level INTO current_stock FROM products
    WHERE product_id = p_product_id FOR UPDATE;

    IF current_stock < p_quantity THEN
        -- Rollback and signal a specific, user-friendly error
        ROLLBACK;
        SIGNAL insufficient_stock
            SET MESSAGE_TEXT = 'Insufficient stock for this product.';
    ELSE
        -- Update stock and insert the order
        UPDATE products SET stock_level = stock_level - p_quantity
        WHERE product_id = p_product_id;

        INSERT INTO orders (product_id, quantity) VALUES (p_product_id, p_quantity);
        COMMIT;
    END IF;

END //

DELIMITER ;

Calling the Procedure

First, a successful call:

CALL place_order(101, 5);

This works, and the stock level for product 101 becomes 15. Now, let’s try to order more than is available:

CALL place_order(101, 25);

This call will fail and return our custom error:

ERROR 1644 (45000): Insufficient stock for this product.

Handling Custom Errors in Application Code

The client application must be able to catch these custom errors and display appropriate messages to the user. Here’s how to do it in various languages.

Node.js (async/await)
Python (mysql-connector)
Java (JDBC with try-with-resources)
PHP (PDO)



#### Node.js with `mysql2/promise`
Catch the error and inspect its properties.

```javascript
// Assumes `place_order` procedure exists
const mysql = require('mysql2/promise');

async function attemptToPlaceOrder(productId, quantity) {
  let connection;
  try {
    connection = await mysql.createConnection({ /* connection details */ });
    await connection.query('CALL place_order(?, ?)', [productId, quantity]);
    console.log(`Order placed successfully for ${quantity} of product ${productId}.`);
  } catch (error) {
    // The custom error is in `error.message`
    console.error(`Failed to place order: ${error.message}`);
    // You can also check for specific error codes or SQLSTATEs
    if (error.sqlState === '45000') {
      console.log('This was a business rule violation (e.g., out of stock).');
    }
  } finally {
    if (connection) await connection.end();
  }
}

// This call will fail with our custom error
attemptToPlaceOrder(101, 25);

Python with mysql-connector-python

Use a try...except block to catch mysql.connector.Error.

# Assumes `place_order` procedure exists
import mysql.connector

def attempt_to_place_order(product_id, quantity):
    try:
        with mysql.connector.connect(database='TUTORIALS', user='root', password='password') as cnx:
            with cnx.cursor() as cursor:
                cursor.callproc('place_order', (product_id, quantity))
                print(f"Order placed for {quantity} of product {product_id}.")
                cnx.commit()
    except mysql.connector.Error as err:
        # The custom error message is in err.msg
        print(f"Failed to place order: {err.msg}")
        if err.sqlstate == '45000':
            print("Business rule violation: Insufficient Stock.")

# This call will fail
attempt_to_place_order(101, 25)

Java with modern JDBC

Catch the SQLException and check its SQLState.

// Assumes `place_order` procedure exists
import java.sql.*;

public class ResignalExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String user = "root";
        String password = "password";

        // This call will fail
        String sql = "{CALL place_order(?, ?)}";

        try (Connection conn = DriverManager.getConnection(url, user, password);
             CallableStatement stmt = conn.prepareCall(sql)) {

            stmt.setInt(1, 101); // product_id
            stmt.setInt(2, 25);  // quantity

            stmt.execute();
            System.out.println("Order placed successfully.");

        } catch (SQLException e) {
            System.err.println("Error placing order: " + e.getMessage());
            // Check for our custom SQLSTATE '45000'
            if ("45000".equals(e.getSQLState())) {
                System.err.println("This is a custom business logic error: Insufficient Stock.");
            }
        }
    }
}

PHP with PDO

Wrap the call in a try...catch block for PDOException.

<?php
// Assumes `place_order` procedure exists
$dsn = "mysql:host=127.0.0.1;dbname=TUTORIALS;charset=utf8mb4";
$options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION];

try {
    $pdo = new PDO($dsn, 'root', 'password', $options);

    // This call will fail
    $stmt = $pdo->prepare('CALL place_order(?, ?)');
    $stmt->execute([101, 25]); // product_id, quantity

    echo "Order placed successfully.\n";

} catch (PDOException $e) {
    // The custom message is in $e->getMessage()
    // The errorInfo array contains SQLSTATE
    $errorInfo = $e->errorInfo;
    echo "Error placing order: " . $errorInfo[2] . "\n";

    // Check for our custom SQLSTATE '45000'
    if ($errorInfo[1] == 1644) { // MySQL error code for SIGNAL/RESIGNAL
        echo "This was a business rule violation.\n";
    }
}
?>