MySQL SHOW ERRORS

Summary: in this tutorial, you will learn how to use the MySQL SHOW ERRORS statement to display error information generated by a query.

Introduction to MySQL SHOW ERRORS statement

The SHOW ERRORS statement is used to display error information about the most recent execution of a statement or a stored procedure.

The SHOW ERRORS statement works like the SHOW WARNINGS statement but it shows only errors, not warnings, and notes.

Here’s the basic syntax of the SHOW ERRORS statement:

SHOW ERRORS;Code language: SQL (Structured Query Language) (sql)

To limit the number of errors, you use the SHOW ERRORS LIMIT statement:

SHOW ERRORS [LIMIT [offset,] row_count];Code language: SQL (Structured Query Language) (sql)

The LIMIT clause has the same meaning as it does in the SELECT statement.

To get the total number of errors, you use the following form of the SHOW ERRORS statement:

SHOW COUNT(*) ERRORS;Code language: SQL (Structured Query Language) (sql)

Alternatively, you can select it from the system variable @@error_count:

SELECT @@error_count;Code language: SQL (Structured Query Language) (sql)

MySQL SHOW ERRORS statement example

We’ll use the products table from the sample database for the demonstration:

The following statement attempts to retrieve the values from the id column of the products table:

SELECT id FROM products;Code language: SQL (Structured Query Language) (sql)

MySQL issues an error because the products table does not have the id column:

ERROR 1054 (42S22): Unknown column 'id' in 'field list'Code language: JavaScript (javascript)

In the message:

• ERROR indicates that the message is an error message.
• 1054 is an integer that represents the MySQL error code.
• 42S22 is a five-character alphanumeric code that represents the condition of the most recently executed SQL statement.
• "Unknown column 'id' in 'field list'" represents the detailed error message.

To show the errors, you use the SHOW ERRORS statement:

SHOW ERRORS;Code language: SQL (Structured Query Language) (sql)

Output:

+-------+------+-------------------------------------+
| Level | Code | Message                             |
+-------+------+-------------------------------------+
| Error | 1054 | Unknown column 'id' in 'field list' |
+-------+------+-------------------------------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

To get the total number of errors, you use the error_count variable:

SELECT @@error_count;Code language: SQL (Structured Query Language) (sql)

Output:

+-----------------------+
| @@session.error_count |
+-----------------------+
|                     0 |
+-----------------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

Summary

• Use the MySQL SHOW ERRORS statement to display errors of the most recently executed statement.