21.2. The SHOW WARNINGS Statement

21.2. The SHOW WARNINGS Statement

MySQL Server generates warnings when it is not able to fully comply with a request or when an action has possibly unintended side effects. These warnings can be displayed with the SHOW WARNINGS statement.

The following example shows how warnings are generated for attempts to insert a character string, a negative integer, and NULL into a column that is defined as INT UNSIGNED NOT NULL:

 mysql> CREATE TABLE integers (i INT UNSIGNED NOT NULL); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO integers VALUES ('abc'), (-5), (NULL); Query OK, 3 rows affected, 3 warnings (0.00 sec) Records: 3  Duplicates: 0  Warnings: 3 

The information returned by the server indicates that there were three instances in which it had to truncate or otherwise change the input to accept the data values that were passed in the INSERT statement. When a statement cannot be executed without some sort of problem occurring, the SHOW WARNINGS statement provides information to help you understand what went wrong. The following example shows the warnings generated by the preceding INSERT statement:

 mysql> SHOW WARNINGS\G *************************** 1. row ***************************   Level: Warning    Code: 1264 Message: Out of range value adjusted for column 'i' at row 1 *************************** 2. row ***************************   Level: Warning    Code: 1264 Message: Out of range value adjusted for column 'i' at row 2 *************************** 3. row ***************************   Level: Warning    Code: 1263 Message: Column set to default value; NULL supplied to NOT NULL          column 'i' at row 3 3 rows in set (0.00 sec) 

You can combine SHOW WARNINGS with LIMIT, just as you're used to doing with SELECT statements, to "scroll" through the warnings a section at a time:

 mysql> SHOW WARNINGS LIMIT 1,2\G *************************** 1. row ***************************   Level: Warning    Code: 1264 Message: Out of range value adjusted for column 'i' at row 2 *************************** 2. row ***************************   Level: Warning    Code: 1263 Message: Column set to default value; NULL supplied to NOT NULL          column 'i' at row 3 2 rows in set (0.00 sec) 

If you want to know only how many warnings there were, use SHOW COUNT(*) WARNINGS.

 mysql> SHOW COUNT(*) WARNINGS; +-------------------------+ | @@session.warning_count | +-------------------------+ |                       3 | +-------------------------+ 

Warnings generated by one statement are available from the server only for a limited time (until you issue another statement that can generate warnings). If you need to see warnings, you should always fetch them as soon as you detect that they were generated.

"Warnings" actually can occur at several levels of severity:

• Error messages indicate serious problems that prevent the server from completing a request.

• Warning messages indicate problems for which the server can continue processing the request.

• Note messages are informational only.

The following example shows messages that are generated at different levels. An error occurs for the SELECT statement, which cannot be executed successfully because the table does not exist. For the DELETE statement, the message is only a note. The purpose of the statement is to ensure that the table does not exist. That is certainly true when the statement finishes, even though the statement did nothing.

 mysql> SELECT * FROM no_such_table; ERROR 1146 (42S02): Table 'test.no_such_table' doesn't exist mysql> SHOW WARNINGS; +-------+------+------------------------------------------+ | Level | Code | Message                                  | +-------+------+------------------------------------------+ | Error | 1146 | Table 'test.no_such_table' doesn't exist | +-------+------+------------------------------------------+ 1 row in set (0.01 sec) mysql> DROP TABLE IF EXISTS no_such_table; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> SHOW WARNINGS; +-------+------+-------------------------------+ | Level | Code | Message                       | +-------+------+-------------------------------+ | Note  | 1051 | Unknown table 'no_such_table' | +-------+------+-------------------------------+ 1 row in set (0.00 sec) 

To suppress generation of Note warnings, you can set the sql_notes system variable to zero:

 mysql> SET sql_notes = 0;