MySQL Show Warnings

MySQL Show Warnings displays the errors, warnings, and note messages that resulted from executing the last statement in the current session. The warnings are generated by DML statements like INSERT and UPDATE and DDL statements like CREATE TABLE and ALTER TABLE.

The warnings are shown only for the last executed statement which used a table. If a statement used a table and generated no message then it will clear the message list and the SHOW WARNINGS diagnostic statement will return nothing. On the other hand, if a statement does not use a table, then it does not effect on the warning message list. 

The Syntax for MySQL Show warnings is as follows:

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

Query-1: Show Warnings Syntax

Here, the first syntax is for showing the actual messages whereas the second syntax is for showing the number of warning counts. 

MySQL Show Warnings

The query below shows how MySQL SHOW WARNINGS can be used to get the warning messages generated by the last executed statement.

mysql> create table users(user_id INT NOT NULL, user_name VARCHAR(5) NOT NULL,salary DECIMAL(10,2) NOT NULL); Query OK, 0 rows affected (0.45 sec) mysql> insert into users values(1,'userA',123.456),(2,'user12',NULL),(3,NULL,100.12); Query OK, 3 rows affected, 4 warnings (0.23 sec) Records: 3  Duplicates: 0  Warnings: 4 mysql> show warnings; +---------+------+------------------------------------------------+ | Level   | Code | Message                                        | +---------+------+------------------------------------------------+ | Note    | 1265 | Data truncated for column 'salary' at row 1    | | Warning | 1265 | Data truncated for column 'user_name' at row 2 | | Warning | 1048 | Column 'salary' cannot be null                 | | Warning | 1048 | Column 'user_name' cannot be null              | +---------+------+------------------------------------------------+ 4 rows in set (0.00 sec) mysql> show count(*) warnings; +-------------------------+ | @@session.warning_count | +-------------------------+ |                       4 | +-------------------------+ 1 row in set (0.00 sec)

Example-1: Show Warnings

Here, we created a users table with user_id as INT, user_name as VARCHAR(5), and salary as DECIMAL(10,2) which are all required. 

Then, we inserted some rows that do not adhere to the table. The first data row has the salary in 3 significant digits, the second row has the username of length 5 and does not have salary data, and the last row does not have a username. On the execution of the statement, the server returned 4 warnings count. 

The warning message generated by the query can be viewed using the MySQL show warnings as shown in the above example.

Show Warnings mysqlimport 

We see how we can show warnings for the queries but what about when we import data using mysqlimport command? The answer is we mysqlimport and show warnings do not work together. So, to show warnings while importing data we have to use other methods if we want to show the warning messages. 

We can achieve this using the MySQL LOAD DATA INFILE command followed by the show warning command. 

The data to be imported is kept in import.txt file as follows:

1,userA,123.456 2,user12,NULL 3,NULL,100.12

Import.txt

Now, the data can be imported with show warnings as follows:

LOAD DATA INFILE 'D:/import.txt' INTO TABLE users FIELDS TERMINATED BY ','; show warnings; Query OK, 3 rows affected, 3 warnings (0.14 sec) Records: 3  Deleted: 0  Skipped: 0  Warnings: 3 +---------+------+---------------------------------------------------------------+ | Level   | Code | Message                                                       | +---------+------+---------------------------------------------------------------+ | Note    | 1265 | Data truncated for column 'salary' at row 1                   | | Warning | 1265 | Data truncated for column 'user_name' at row 2                | | Warning | 1366 | Incorrect decimal value: 'NULL' for column 'salary' at row 2 | +---------+------+---------------------------------------------------------------+ 3 rows in set (0.00 sec)

Example-2: Show Warnings on data import