MySQL SHOW WARNINGS

 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, if errors, warnings, or notes occur, you can use the SHOW WARNINGS diagnostic statements to display detailed information.

Here is the basic syntax of the SHOW WARNINGS:

SHOW WARNINGS;

For example, the following query returns an error due to the table xyz does not exist:

SELECT * FROM xyz;

To show the error, you use the SHOW WARNINGS statement:

SHOW WARNINGS;

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

SHOW WARNINGS [LIMIT [offset,] row_count]

The LIMIT the clause has the same meaning as for the SELECT statement.

To show the total number of errors, warnings, and notes, you use the following form of the SHOW WARNINGS statement:

SHOW COUNT(*) WARNINGS;

You can also get the same result from the @@warning_count system variable:

SELECT @@warning_count;

Note that the SHOW WARNINGS is a diagnostic statement that does not clear the message list while the SELECT a statement, which is a nondiagnostic statement, clear all message list.

MySQL SHOW WARNINGS example

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

SELECT 
    DATE_SUB('2017-02-29', INTERVAL - 1 DAY),
    DATE_SUB('2017-12-32', INTERVAL + 2 DAY),
    DATE_SUB('2017-15-03', INTERVAL + 5 DAY);

The following example uses the SHOW WARNINGS statement to show all warnings:

SHOW WARNINGS;

This example uses the SHOW WARNING LIMIT to show the first two warnings:

SHOW WARNINGS LIMIT 2;

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

SHOW COUNT(*) WARNINGS;

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';

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;

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

In this tutorial, you have learned how to use MySQL SHOW WARNINGS to display information about errors, warnings, and notes.