Error handling in procedures in MySQL - Deep Dive

Overview - Error handling in procedures

What is it?

Error handling in procedures means managing problems that happen when running stored procedures in a database. Stored procedures are sets of SQL commands saved to run repeatedly. When something goes wrong, like a wrong input or a missing record, error handling helps catch and respond to these issues so the procedure doesn't just stop unexpectedly. This makes database operations more reliable and predictable.

Why it matters

Without error handling, a procedure might stop suddenly and leave data incomplete or inconsistent. This can cause bigger problems like wrong reports, lost data, or system crashes. Proper error handling ensures the database stays accurate and users get clear feedback when something goes wrong, improving trust and system stability.

Where it fits

Before learning error handling, you should understand basic SQL and how to write stored procedures. After mastering error handling, you can learn advanced topics like transaction control, performance tuning, and debugging complex procedures.

Mental Model

Core Idea

Error handling in procedures is like having a safety net that catches problems during execution and decides how to fix or report them without crashing the whole process.

Think of it like...

Imagine cooking with a recipe (procedure). If you run out of an ingredient (error), instead of stopping and wasting everything, you check the problem and decide to substitute or pause and fix it. This keeps the cooking going smoothly.

┌─────────────────────────────┐
│ Start Procedure             │
├─────────────────────────────┤
│ Execute SQL Statements      │
│ ┌───────────────────────┐ │
│ │ Error Occurs?         │─┤
│ └─────────┬─────────────┘ │
│           │ Yes           │
│           ▼               │
│   ┌───────────────────┐   │
│   │ Handle Error       │   │
│   └─────────┬─────────┘   │
│             │             │
│             ▼             │
│ Continue or Exit Procedure │
└─────────────────────────────┘

Build-Up - 7 Steps

1

Foundation

What are Stored Procedures

Concept: Introduces stored procedures as reusable SQL code blocks.

A stored procedure is a saved set of SQL commands that you can run anytime. It helps automate tasks like inserting or updating data. For example, a procedure can add a new user to a database with one command.

Result

You can run complex SQL tasks easily and repeatedly by calling the procedure name.

Understanding stored procedures is essential because error handling works inside these blocks to keep them safe and reliable.

2

Foundation

Basic Errors in SQL Procedures

Concept: Shows common errors that can happen during procedure execution.

Errors can be syntax mistakes, missing data, or constraint violations. For example, trying to insert a duplicate key causes an error. Without handling, the procedure stops and returns an error to the user.

Result

Procedures can fail unexpectedly if errors are not managed.

Knowing typical errors helps you prepare to catch and handle them properly.

3

Intermediate

Using DECLARE HANDLER for Errors

🤔Before reading on: do you think DECLARE HANDLER stops the procedure or lets it continue after an error? Commit to your answer.

Concept: Introduces DECLARE HANDLER to catch errors and decide what to do next.

In MySQL, you can declare a handler inside a procedure to catch specific errors or warnings. For example, DECLARE CONTINUE HANDLER FOR SQLEXCEPTION lets the procedure continue after an error and run custom code to handle it.

Result

The procedure can catch errors and respond without stopping abruptly.

Understanding handlers lets you control error flow and improve procedure robustness.

4

Intermediate

Types of Handlers: CONTINUE vs EXIT

🤔Before reading on: do you think CONTINUE handler stops the procedure or lets it keep running? Commit to your answer.

Concept: Explains the difference between CONTINUE and EXIT handlers.

CONTINUE handler runs error code but continues the procedure after. EXIT handler runs error code and then stops the procedure immediately. Choosing the right type depends on whether you want to recover or stop on error.

Result

You can decide if the procedure should keep going or stop when an error happens.

Knowing handler types helps you design procedures that behave correctly under different error conditions.

5

Intermediate

Using GET DIAGNOSTICS for Error Details

🤔Before reading on: do you think GET DIAGNOSTICS can give you error messages inside a procedure? Commit to your answer.

Concept: Shows how to retrieve detailed error info after an error occurs.

GET DIAGNOSTICS lets you get error codes and messages inside a handler. For example, you can store the error message in a variable and log it or return it to the caller for better debugging.

Result

You get detailed error information to understand and fix problems.

Accessing error details inside procedures improves troubleshooting and user feedback.

6

Advanced

Combining Transactions with Error Handling

🤔Before reading on: do you think error handling automatically rolls back transactions? Commit to your answer.

Concept: Explains how to use error handling with transactions to keep data consistent.

When using transactions (BEGIN, COMMIT, ROLLBACK), error handlers can decide to roll back changes if something goes wrong. You must explicitly call ROLLBACK inside the handler to undo partial changes and keep data clean.

Result

Data stays consistent even if errors happen during complex operations.

Knowing how error handling interacts with transactions prevents data corruption.

7

Expert

Limitations and Surprises in MySQL Error Handling

🤔Before reading on: do you think MySQL handlers catch all errors including compile-time errors? Commit to your answer.

Concept: Reveals MySQL error handling limitations and unexpected behaviors.

MySQL handlers catch runtime errors but not compile-time errors like syntax mistakes. Also, some errors cause the procedure to stop immediately despite handlers. Nested handlers and multiple errors can behave unpredictably, so testing is crucial.

Result

You understand when error handling might fail or behave unexpectedly.

Recognizing these limits helps avoid false confidence and design safer procedures.

Under the Hood

When a stored procedure runs, MySQL executes each SQL statement in order. If an error occurs, MySQL checks if a handler is declared for that error type. If yes, it runs the handler code. CONTINUE handlers let the procedure keep running after handling, while EXIT handlers stop execution. Without handlers, the error bubbles up and aborts the procedure. Internally, MySQL uses an error stack and diagnostics area to store error info accessible by GET DIAGNOSTICS.

Why designed this way?

MySQL error handling was designed to give developers control over error flow inside procedures, improving robustness. The separation of CONTINUE and EXIT handlers allows flexible recovery or fail-fast strategies. However, some limitations exist due to MySQL's architecture focusing on performance and simplicity over full exception handling like in programming languages.

┌───────────────────────────────┐
│ Procedure Execution            │
├───────────────┬───────────────┤
│ SQL Statement │ Error Occurs? │
│               ├───────┬───────┤
│               │ Yes   │ No    │
│               ▼       │       │
│        ┌────────────┐ │       │
│        │ Check for  │ │       │
│        │ Handler   │ │       │
│        └─────┬──────┘ │       │
│              │ Yes    │       │
│              ▼        │       │
│       ┌─────────────┐ │       │
│       │ Run Handler │ │       │
│       └─────┬───────┘ │       │
│             │ CONTINUE│       │
│             │ or EXIT │       │
│             ▼        │       │
│    Continue or Stop  │       │
└──────────────────────┴───────┘

Myth Busters - 4 Common Misconceptions

Quick: Do MySQL error handlers catch syntax errors inside procedures? Commit yes or no.

Common Belief: Error handlers catch all errors including syntax mistakes inside procedures.

Reality: Handlers only catch runtime errors; syntax errors cause the procedure to fail before running.

Why it matters: Assuming handlers catch syntax errors leads to missing compile-time mistakes and unexpected procedure failures.

Quick: Does DECLARE CONTINUE HANDLER stop the procedure after handling an error? Commit yes or no.

Common Belief: CONTINUE handlers stop the procedure after handling an error.

Reality: CONTINUE handlers run error code but let the procedure keep running.

Why it matters: Misunderstanding this causes incorrect flow control and unexpected procedure behavior.

Quick: Does MySQL automatically roll back transactions on error without explicit commands? Commit yes or no.

Common Belief: MySQL automatically rolls back transactions when an error occurs in a procedure.

Reality: You must explicitly call ROLLBACK in error handlers; otherwise, partial changes remain.

Why it matters: Assuming automatic rollback can cause data corruption and inconsistent states.

Quick: Can multiple error handlers run for a single error in MySQL procedures? Commit yes or no.

Common Belief: Multiple handlers can run for the same error in a procedure.

Reality: Only the first matching handler runs; others are ignored for that error.

Why it matters: Expecting multiple handlers can lead to missed error handling logic.

Expert Zone

1

Handlers do not catch warnings or notices unless explicitly declared, so some minor issues may pass silently.

2

Nested procedures with their own handlers can cause complex error propagation that is hard to predict without careful design.

3

GET DIAGNOSTICS can retrieve multiple pieces of error info, but some fields are only available in newer MySQL versions (8.0+).

When NOT to use

Avoid relying solely on error handlers for complex transaction management; use explicit transaction control and application-level error handling instead. For very complex logic, consider using application code or other database systems with richer exception handling.

Production Patterns

In production, error handling is combined with logging errors to tables, returning standardized error codes to applications, and rolling back transactions to maintain data integrity. Handlers often clean up resources or set flags to signal failure without crashing the whole system.

Connections

Exception Handling in Programming Languages

Error handling in procedures is a database-specific form of exception handling found in programming languages like Java or Python.

Understanding how exceptions work in programming helps grasp database error handlers as a similar concept adapted for SQL execution.

Transaction Management

Error handling works closely with transactions to ensure data consistency during failures.

Knowing transaction control deepens understanding of why error handlers often include rollback commands.

Fault Tolerance in Engineering

Error handling in procedures is a fault tolerance mechanism that prevents system failure by managing errors gracefully.

Seeing error handling as fault tolerance connects database concepts to broader system reliability engineering.

Common Pitfalls

#1 Ignoring to declare handlers and expecting procedures to continue on error.

Wrong approach:

CREATE PROCEDURE add_user() BEGIN
INSERT INTO users(id, name) VALUES(1, 'Alice');
INSERT INTO users(id, name) VALUES(1, 'Bob'); -- duplicate key error
END;

Correct approach:

CREATE PROCEDURE add_user() BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN
-- handle error, e.g., log or set flag
END;
INSERT INTO users(id, name) VALUES(1, 'Alice');
INSERT INTO users(id, name) VALUES(1, 'Bob'); -- duplicate key error caught
END;

Root cause:

Not understanding that without handlers, errors abort the procedure immediately.

#2 Using CONTINUE handler but expecting procedure to stop on error.

Wrong approach:

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET error_flag = 1; -- expecting stop

Correct approach:

DECLARE EXIT HANDLER FOR SQLEXCEPTION SET error_flag = 1; -- stops procedure after handling

Root cause:

Confusing CONTINUE and EXIT handler behavior.

#3 Assuming transactions rollback automatically on error without explicit ROLLBACK.

Wrong approach:

BEGIN;
INSERT INTO accounts VALUES(1, 100);
-- error occurs
COMMIT;

Correct approach:

BEGIN;
DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN
ROLLBACK;
END;
INSERT INTO accounts VALUES(1, 100);
COMMIT;

Root cause:

Misunderstanding that MySQL does not auto-rollback transactions on errors.

Key Takeaways

Error handling in MySQL procedures lets you catch and manage runtime errors to keep procedures running smoothly or stop them gracefully.

DECLARE HANDLER statements define how to respond to errors, with CONTINUE handlers allowing recovery and EXIT handlers stopping execution.

GET DIAGNOSTICS provides detailed error information inside handlers, improving debugging and user feedback.

Error handling must be combined with explicit transaction control to maintain data consistency during failures.

MySQL error handling has limitations, such as not catching syntax errors and some unexpected behaviors, so thorough testing is essential.

Practice

1. What is the main purpose of using DECLARE HANDLER in a MySQL stored procedure?

easy

A. To declare variables inside the procedure

B. To catch and handle errors during procedure execution

C. To create temporary tables for data processing

D. To optimize query performance automatically

Solution

1. Step 1: Understand the role of DECLARE HANDLER

   DECLARE HANDLER is used to define actions when certain conditions or errors occur during procedure execution.

2. Step 2: Identify the purpose in error handling

   It helps catch errors and decide whether to continue or exit, preventing crashes and allowing custom responses.

3. Final Answer:

   To catch and handle errors during procedure execution -> Option B

4. Quick Check:

   Error handling = DECLARE HANDLER usage [OK]

Hint: DECLARE HANDLER is for managing errors inside procedures [OK]

Common Mistakes:

• Confusing DECLARE HANDLER with variable declaration
• Thinking it creates tables or indexes
• Assuming it optimizes queries automatically

2. Which of the following is the correct syntax to declare a handler for SQL exceptions that continues execution in MySQL?

easy

A. DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN ... END;

B. DECLARE HANDLER CONTINUE FOR SQLEXCEPTION;

C. DECLARE HANDLER FOR CONTINUE SQLEXCEPTION;

D. DECLARE HANDLER FOR SQLEXCEPTION CONTINUE;

Solution

1. Step 1: Recall correct DECLARE HANDLER syntax

   The syntax is: DECLARE [CONTINUE|EXIT] HANDLER FOR condition statement;

2. Step 2: Match syntax with options

   DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN ... END; correctly uses DECLARE CONTINUE HANDLER FOR SQLEXCEPTION followed by a statement block.

3. Final Answer:

   DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN ... END; -> Option A

4. Quick Check:

   Correct syntax = DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN ... END; [OK]

Hint: Syntax: DECLARE CONTINUE HANDLER FOR error BEGIN...END [OK]

Common Mistakes:

• Placing CONTINUE after HANDLER incorrectly
• Omitting BEGIN...END block for multiple statements
• Wrong order of keywords

3. Consider this procedure snippet:

DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @error_flag = 1;
SET @error_flag = 0;
INSERT INTO users(id, name) VALUES(1, 'Alice');
INSERT INTO users(id, name) VALUES(1, 'Bob');

Assuming id is a primary key, what will be the value of @error_flag after execution?

medium

A. 1

B. 0

C. NULL

D. Procedure will crash without setting @error_flag

Solution

1. Step 1: Analyze the inserts and handler

   The first insert succeeds. The second insert violates primary key (duplicate id), causing an SQLEXCEPTION.

2. Step 2: Understand EXIT HANDLER effect

   The EXIT HANDLER sets @error_flag = 1 and stops further execution.

3. Final Answer:

   1 -> Option A

4. Quick Check:

   Duplicate key error triggers EXIT HANDLER = 1 [OK]

Hint: EXIT HANDLER sets flag on error and stops execution [OK]

Common Mistakes:

• Assuming @error_flag stays 0 despite error
• Thinking procedure continues after EXIT HANDLER
• Confusing CONTINUE and EXIT handlers

4. Identify the error in this MySQL procedure snippet:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
DECLARE done BOOLEAN DEFAULT FALSE;
WHILE done = FALSE DO
  -- some code
END WHILE;

medium

A. NOT FOUND is not a valid condition for handler

B. Missing semicolon after DECLARE statements

C. WHILE loop condition syntax is incorrect

D. Handler declared before variable 'done' is declared

Solution

1. Step 1: Check declaration order rules

   In MySQL procedures, variables must be declared before handlers.

2. Step 2: Identify declaration order in snippet

   The handler is declared before the variable 'done', which is invalid.

3. Final Answer:

   Handler declared before variable 'done' is declared -> Option D

4. Quick Check:

   Variables before handlers = correct order [OK]

Hint: Declare variables before handlers in procedures [OK]

Common Mistakes:

• Declaring handlers before variables
• Confusing NOT FOUND condition usage
• Ignoring declaration order rules

5. You want to write a procedure that tries to insert a new user but if a duplicate key error occurs, it should update the user's name instead. Which error handling approach is best?

hard

A. Use DECLARE CONTINUE HANDLER for NOT FOUND to insert new user

B. Use DECLARE EXIT HANDLER for any error to rollback transaction

C. Use DECLARE CONTINUE HANDLER for duplicate key error to run UPDATE statement

D. Use no handler and rely on client application to handle errors

Solution

1. Step 1: Understand the goal

   Insert user; if duplicate key error occurs, update existing user's name instead.

2. Step 2: Choose handler type

   CONTINUE HANDLER allows procedure to continue after catching duplicate key error and run UPDATE.

3. Step 3: Evaluate other options

   EXIT HANDLER stops execution, NOT FOUND is unrelated, and no handler defers error handling outside procedure.

4. Final Answer:

   Use DECLARE CONTINUE HANDLER for duplicate key error to run UPDATE statement -> Option C

5. Quick Check:

   CONTINUE HANDLER for duplicate key = update fallback [OK]

Hint: Use CONTINUE HANDLER to catch duplicate key and update [OK]

Common Mistakes:

• Using EXIT HANDLER which stops procedure
• Using NOT FOUND for duplicate key errors
• Ignoring error handling inside procedure