MySQL SHOW WARNINGS

Summary: in this tutorial, you will learn how to use the MySQL SHOW WARNINGS to display information about errors, warnings, and notes.

Introduction to MySQL SHOW WARNINGS statement

When you execute a statement and encounter an error or warning, you can use the SHOW WARNINGS statement to display detailed information.

Here is the basic syntax of the SHOW WARNINGS statement:

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

For example, the following statement attempts to drop the table abc that doesn’t exist:

DROP TABLE IF EXISTS abc;Code language: SQL (Structured Query Language) (sql)

MySQL returns a message indicating that it encountered a warning:

Query OK, 0 rows affected, 1 warning (0.01 sec)Code language: CSS (css)

To display the warning, you use the SHOW WARNINGS statement:

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

Output:

+-------+------+-----------------------------------+
| Level | Code | Message                           |
+-------+------+-----------------------------------+
| Note  | 1051 | Unknown table 'classicmodels.abc' |
+-------+------+-----------------------------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

The output has three columns:

Level: It can be a Note or Error.
Code: This is an integer that represents a MySQL error code.
Message: This stores the detailed warning or error message.

In this example, the warning was that the table abc doesn’t exist in the classicmodels sample database.

If you want to limit the number of conditions (errors, warnings, and notes), you can use the LIMIT clause:

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

The LIMIT clause carries the same meaning as it does for the SELECT statement.

To show the total number of errors and warnings, you use the COUNT(*) function with the of the SHOW WARNINGS statement like this:

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

Alternatively, you can also get the same result from the @@warning_count system variable:

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

MySQL SHOW WARNINGS statement example

The following statement uses the DATE_SUB() function to add/subtract intervals from dates:

SELECT 
    DATE_SUB('2017-02-29', INTERVAL - 1 DAY) d1,
    DATE_SUB('2017-12-32', INTERVAL + 2 DAY) d2,
    DATE_SUB('2017-15-03', INTERVAL + 5 DAY) d3;Code language: SQL (Structured Query Language) (sql)

Output:

+------+------+------+
| d1   | d2   | d3   |
+------+------+------+
| NULL | NULL | NULL |
+------+------+------+
1 row in set, 3 warnings (0.00 sec)Code language: PHP (php)

The following SHOW WARNINGS statement to show all three warnings:

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

Output:

+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2017-02-29' |
| Warning | 1292 | Incorrect datetime value: '2017-12-32' |
| Warning | 1292 | Incorrect datetime value: '2017-15-03' |
+---------+------+----------------------------------------+
3 rows in set (0.00 sec)Code language: JavaScript (javascript)

This example uses the SHOW WARNINGS LIMIT statement to display the first two warnings:

SHOW WARNINGS LIMIT 2;Code language: SQL (Structured Query Language) (sql)

Output:

+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2017-02-29' |
| Warning | 1292 | Incorrect datetime value: '2017-12-32' |
+---------+------+----------------------------------------+
2 rows in set (0.00 sec)Code language: JavaScript (javascript)

The following statement uses the SHOW COUNT(*) WARNINGS to show the total number of warnings:

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

Output:

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

MySQL max_error_count system variable

MySQL uses the max_error_count system variable to control the maximum number of warnings, errors, and notes that the server can store.

To view the value of the max_error_count system variable, you use the SHOW VARIABLES statement:

SHOW VARIABLES LIKE 'max_error_count';Code language: SQL (Structured Query Language) (sql)

Output:

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_error_count | 1024  |
+-----------------+-------+
1 row in set (0.02 sec)Code language: JavaScript (javascript)

To change the value of the max_error_count variable, you use the SET statement. For example, this statement sets the max_error_count to 2048:

SET max_error_count=2048;Code language: SQL (Structured Query Language) (sql)

Setting the value of the max_error_count variable to zero will disable the message storage. However, the warning_count still shows the number of errors and warnings that occurred, but the server does not store these messages.

Summary

Use MySQL SHOW WARNINGS statement to display errors and warnings from the most recent execution of a statement.

Was this tutorial helpful?