Introduction
In this lab, you will learn fundamental MySQL error handling and logging techniques. Effective error management is crucial for building robust and maintainable database applications, as it helps you diagnose issues, understand query execution, and ensure data integrity.
You will start by enabling the general query log to capture all SQL statements sent to the server, a powerful tool for debugging and auditing. Next, you will implement error handling within a stored procedure using a DECLARE HANDLER to gracefully manage unexpected errors. You will also learn to create and trigger custom error conditions using the SIGNAL statement to enforce business rules. Finally, you will inspect the MySQL error log, which contains vital information about server operations and critical issues.
By the end of this lab, you will have a solid foundation in MySQL error handling and logging, enabling you to build more reliable database solutions.
Enable and Review the General Query Log
The general query log records every SQL statement received from clients. It is an invaluable tool for debugging and auditing, but it should be used temporarily as it can impact performance and consume significant disk space. In this step, you will enable the log, generate some activity, and review the log file.
First, open the terminal from your desktop.
Connect to the MySQL server as the root user. In this lab environment, you can use sudo to connect without a password.
sudo mysql -u root
Once you see the mysql> prompt, enable the general query log globally.
SET GLOBAL general_log = 'ON';
By default, the log file is stored in the MySQL data directory. For easier access, let's change its location to the /tmp directory.
SET GLOBAL general_log_file = '/tmp/mysql_general.log';
You can verify the new location by running:
SHOW VARIABLES LIKE 'general_log_file';
The output should confirm the path you just set.
+------------------+-------------------------+
| Variable_name | Value |
+------------------+-------------------------+
| general_log_file | /tmp/mysql_general.log |
+------------------+-------------------------+
1 row in set (0.01 sec)
Now, execute a few commands to generate log entries.
CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;
SELECT 'Logging this query' AS message;
After running these commands, exit the MySQL shell.
exit
Back in your terminal, view the contents of the log file.
sudo cat /tmp/mysql_general.log
You will see the commands you executed, along with connection information and timestamps. This confirms that the general query log is working correctly.
/usr/sbin/mariadbd, Version: 10.6.18-MariaDB-0ubuntu0.22.04.1 (Ubuntu 22.04). started with:
Tcp port: 3306 Unix socket: /run/mysqld/mysqld.sock
Time Id Command Argument
250728 14:12:46 33 Query SHOW VARIABLES LIKE 'general_log_file'
250728 14:12:50 33 Query CREATE DATABASE IF NOT EXISTS testdb
33 Query SELECT DATABASE()
33 Init DB testdb
33 Query show databases
33 Query show tables
33 Query SELECT 'Logging this query' AS message
250728 14:12:56 33 Quit
Finally, it is good practice to disable the log when you are done. You can do this directly from the terminal.
sudo mysql -u root -e "SET GLOBAL general_log = 'OFF';"
This ensures the log does not continue to grow and affect server performance.
Handle Errors in a Stored Procedure
Stored procedures can fail for many reasons, such as attempting to insert duplicate data into a primary key column. Using an error handler allows you to catch these errors and respond gracefully instead of letting the procedure crash. In this step, you will create a stored procedure with an error handler for duplicate key errors.
First, connect to the MySQL server.
sudo mysql -u root
Create the testdb database if it doesn't already exist and switch to it. Then, create a products table.
CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(255),
quantity INT
);
Now, create a stored procedure to insert a new product. This version includes a DECLARE HANDLER that catches duplicate key errors (SQLSTATE 23000) and returns a custom message.
The DELIMITER command changes the statement terminator from ; to //, allowing the semicolon within the procedure body to be processed correctly.
DELIMITER //
CREATE PROCEDURE insert_product(IN p_id INT, IN p_name VARCHAR(255))
BEGIN
-- Declare an EXIT handler for duplicate key errors
DECLARE EXIT HANDLER FOR SQLSTATE '23000'
BEGIN
SELECT 'Error: Product with this ID already exists.' AS message;
END;
-- Attempt to insert the product
INSERT INTO products (id, name, quantity) VALUES (p_id, p_name, 0);
SELECT 'Product inserted successfully.' AS message;
END //
DELIMITER ;
Let's test the procedure. First, insert a new product.
CALL insert_product(1, 'Laptop');
This should succeed and return a success message.
+--------------------------------+
| message |
+--------------------------------+
| Product inserted successfully. |
+--------------------------------+
1 row in set (0.00 sec)
Now, try to insert a product with the same id.
CALL insert_product(1, 'Desktop');
This time, the error handler is triggered, and you receive the custom error message instead of a generic MySQL error.
+-----------------------------------------------+
| message |
+-----------------------------------------------+
| Error: Product with this ID already exists. |
+-----------------------------------------------+
1 row in set (0.00 sec)
This demonstrates how error handlers can make your stored procedures more user-friendly and robust.
Raise Custom Errors with SIGNAL
While handlers catch errors, the SIGNAL statement lets you raise them. This is useful for enforcing business rules that are not covered by standard database constraints. In this step, you will create a procedure that uses SIGNAL to prevent negative values from being entered for a product's quantity.
You should still be in the MySQL shell. If not, connect again.
sudo mysql -u root
Ensure you are using the testdb database.
USE testdb;
Now, create a stored procedure to update a product's quantity. The procedure will check if the new quantity is negative. If it is, it will SIGNAL a custom error.
DELIMITER //
CREATE PROCEDURE update_quantity(IN p_id INT, IN p_quantity INT)
BEGIN
-- Check if the quantity is negative
IF p_quantity < 0 THEN
-- Raise a custom error
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Error: Quantity cannot be negative.';
END IF;
-- Update the quantity if the check passes
UPDATE products SET quantity = p_quantity WHERE id = p_id;
SELECT 'Quantity updated successfully.' AS message;
END //
DELIMITER ;
Here, SQLSTATE '45000' is a generic state code for user-defined errors. MESSAGE_TEXT sets the error message that the client will see.
Let's test the procedure. First, try a valid update on the 'Laptop' product you created in the previous step.
CALL update_quantity(1, 50);
This should execute successfully.
+--------------------------------+
| message |
+--------------------------------+
| Quantity updated successfully. |
+--------------------------------+
1 row in set (0.00 sec)
Now, attempt to update the quantity with a negative number.
CALL update_quantity(1, -10);
This call will trigger the SIGNAL statement, and the procedure will terminate with your custom error.
ERROR 1644 (45000): Error: Quantity cannot be negative.
This confirms that you can successfully enforce custom business logic within your database using SIGNAL.
Review the MySQL Error Log
The MySQL error log is the primary resource for diagnosing server-level problems. It records server startup and shutdown events, critical errors, and warnings. Knowing how to find and read this log is an essential skill for any database administrator.
You should still be in the MySQL shell. First, find the location of the error log file by querying the log_error variable.
SHOW VARIABLES LIKE 'log_error';
In this LabEx VM environment (Docker container), you might see an empty value for the error log path:
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_error | |
+---------------+-------+
1 row in set (0.001 sec)
Note: In containerized environments like this LabEx VM, MySQL/MariaDB error logging is often configured to output to the container's standard error stream rather than a traditional log file. This is a common practice in Docker containers to follow the "12-factor app" methodology.
Let's demonstrate error handling by attempting to access a non-existent database:
USE non_existent_database;
This command will fail in the client, as expected.
ERROR 1049 (42000): Unknown database 'non_existent_database'
Now, exit the MySQL shell to return to your terminal.
exit
In a production environment with a traditional MySQL installation, you would typically find the error log at /var/log/mysql/error.log on Ubuntu systems. You can check if the traditional log file exists:
sudo ls -la /var/log/mysql/ 2> /dev/null || echo "MySQL log directory not found (normal in containerized environments)"
Understanding Error Logging in Different Environments:
- Traditional installations: Error logs are written to files like
/var/log/mysql/error.log - Containerized environments: Errors are often sent to stdout/stderr and captured by the container runtime
- Cloud databases: Error logs are typically accessed through the cloud provider's management interface
In production environments, you would regularly review error logs using commands like:
sudo tail -f /var/log/mysql/error.log(to follow logs in real-time)sudo grep -i error /var/log/mysql/error.log(to search for specific errors)
This practice is essential for monitoring server health and troubleshooting issues like failed startups, corrupted tables, or permission problems.
Summary
In this lab, you have learned fundamental techniques for error handling and logging in MySQL. You started by enabling and configuring the general query log to trace SQL statements, a key skill for debugging. You then implemented robust error handling in a stored procedure using DECLARE HANDLER, allowing it to manage specific errors gracefully.
Furthermore, you learned to enforce business rules by raising custom errors with the SIGNAL statement, providing clear and specific feedback. Finally, you explored MySQL error logging concepts, including how error logging differs between traditional installations and containerized environments like Docker containers.
You learned that while traditional MySQL installations write error logs to files (like /var/log/mysql/error.log), containerized environments often redirect error output to stdout/stderr for better integration with container orchestration platforms. This understanding is crucial when working with modern deployment environments.
By mastering these techniques, you are now better equipped to build reliable database applications, troubleshoot problems effectively, and ensure the integrity of your MySQL databases across different deployment scenarios.
