SHOW WARNINGS [LIMIT [offset,] row_count]SHOW COUNT(*) WARNINGS

SHOW WARNINGS shows information about the conditions (errors, warnings, and notes) that resulted from the last statement in the current session that generated messages. It shows nothing if the last statement used a table and generated no messages. (That is, a statement that uses a table but generates no messages clears the message list.) Statements that do not use tables and do not generate messages have no effect on the message list.

Warnings are generated for DML statements such as INSERT, UPDATE, and LOAD DATA INFILE as well as DDL statements such as CREATE TABLE and ALTER TABLE.

SHOW WARNINGS is also used following EXPLAIN EXTENDED, to display the extra information generated by EXPLAIN when the EXTENDED keyword is used. See Section 8.8.3, "EXPLAIN EXTENDED Output Format".

The LIMIT clause has the same syntax as for the SELECT statement. See Section 13.2.9, "SELECT Syntax".

A related statement, SHOW ERRORS, shows only the error conditions (it excludes warnings and notes). See Section 13.7.5.18, "SHOW ERRORS Syntax". GET DIAGNOSTICS can be used to examine information for individual conditions. See Section 13.6.7.3, "GET DIAGNOSTICS Syntax".

The SHOW COUNT(*) WARNINGS statement displays the total number of errors, warnings, and notes. You can also retrieve this number from the warning_count system variable:

SHOW COUNT(*) WARNINGS;SELECT @@warning_count;

Here is a simple example that shows a syntax warning for CREATE TABLE and conversion warnings for INSERT:

mysql> CREATE TABLE
        t1     >   (a TINYINT NOT NULL, b CHAR(4))     >   TYPE=MyISAM;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> SHOW WARNINGS\G*************************** 1. row ***************************  Level: Warning   Code: 1287Message: 'TYPE=storage_engine' is deprecated, use         'ENGINE=storage_engine' instead1 row in set (0.00 sec)mysql> INSERT INTO t1 VALUES(10,'mysql'),    -> (NULL,'test'), (300,'Open Source');Query OK, 3 rows affected, 4 warnings (0.01 sec)Records: 3  Duplicates: 0  Warnings: 4mysql> SHOW WARNINGS\G*************************** 1. row ***************************  Level: Warning   Code: 1265Message: Data truncated for column 'b' at row 1*************************** 2. row ***************************  Level: Warning   Code: 1263Message: Data truncated, NULL supplied to NOT NULL column 'a' at row 2*************************** 3. row ***************************  Level: Warning   Code: 1264Message: Data truncated, out of range for column 'a' at row 3*************************** 4. row ***************************  Level: Warning   Code: 1265Message: Data truncated for column 'b' at row 34 rows in set (0.00 sec)

The max_error_count system variable controls the maximum number of error, warning, and note messages for which the server stores information, and thus the number of messages that SHOW WARNINGS displays. By default, max_error_count is 64. To change the number of messages the server can store, change the value of max_error_count.

The value of warning_count is not limited by max_error_count if the number of messages generated exceeds max_error_count.

In the following example, the ALTER TABLE statement produces three warning messages (as shown by the value of warning_count), but only one is stored because max_error_count has been set to 1:

mysql> SHOW VARIABLES LIKE 'max_error_count';+-----------------+-------+| Variable_name   | Value |+-----------------+-------+| max_error_count | 64    |+-----------------+-------+1 row in set (0.00 sec)mysql> SET max_error_count=1;Query OK, 0 rows affected (0.00 sec)mysql> ALTER TABLE t1 MODIFY b CHAR;Query OK, 3 rows affected, 3 warnings (0.00 sec)Records: 3  Duplicates: 0  Warnings: 3mysql> SELECT @@warning_count;+-----------------+| @@warning_count |+-----------------+|               3 |+-----------------+1 row in set (0.01 sec)mysql> SHOW WARNINGS;+---------+------+----------------------------------------+| Level   | Code | Message                                |+---------+------+----------------------------------------+| Warning | 1263 | Data truncated for column 'b' at row 1 |+---------+------+----------------------------------------+1 row in set (0.00 sec)

To disable warnings, set max_error_count to 0. In this case, warning_count still indicates how many warnings have occurred, but none of the messages are stored.

The following DROP TABLE statement results in a note:

mysql> DROP TABLE IF EXISTS
        test.no_such_table;Query OK, 0 rows affected, 1 warning (0.01 sec)mysql> SHOW WARNINGS;+-------+------+------------------------------------+| Level | Code | Message                            |+-------+------+------------------------------------+| Note  | 1051 | Unknown table 'test.no_such_table' |+-------+------+------------------------------------+

If the sql_notes system variable is set to 0, notes do not increment warning_count and the server does not record them.

The MySQL server sends back a count indicating the total number of errors, warnings, and notes resulting from the last statement. From the C API, this value can be obtained by calling mysql_warning_count(). See Section 22.8.7.73, "mysql_warning_count()".