MySQL - SHOW WARNINGS Statement

---

---

MySQL SHOW WARNINGS Statement

The MySQL SHOW WARNINGS Statement is used to retrieve the information about the error, warnings, and notes occurred during the execution of the previous MySQL statement in the current session.

Syntax

Following is the syntax of the SHOW ERRORS Statement −

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

Example

Let us create a table with name MyPlayers in MySQL database using CREATE statement as shown below −

CREATE TABLE MyPlayers(
   ID INT,
   First_Name VARCHAR(255) NOT NULL,
   Last_Name VARCHAR(255) NOT NULL,
   Date_Of_Birth date,
   Place_Of_Birth VARCHAR(15),
   Country VARCHAR(15),
   PRIMARY KEY (ID)
);

Now, we will insert some records in MyPlayers table using INSERT statements −

Insert into MyPlayers values
(1, 'Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India'),
(2, 'Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica');

If verify the contents of the MyPlayers table, you can observe the created records as −

select * from MyPlayers;

Output

Once the query is executed, it will display the following output −

ID	First_Name	Last_Name	Date_Of_Birth	Place_Of_Birth	Country
1	Shikhar	Dhawan	1981-12-05	Delhi	India
2	Jonathan	Trott	1981-04-22	CapeTown	SouthAfrica

NOTE − Make sure you disable the strict SQL mode or use the INSERT IGNORE statement to display the errors as warnings.

Now, let us try to insert more rows with repeated ID value, wrong table name and wrong number of values −

Insert ignore into MyPlayers values
(2, NULL, 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India');

Following query retrieves the warnings generated by the above statement −

SHOW WARNINGS;

Output

Following is the output of the above query −

Level	Code	Message
Warning	1048	Column 'First_Name' cannot be null
Warning	1062	Duplicate entry '2' for key 'myplayers.PRIMARY'

LIMIT

You can limit the number of errors to an offset using the LIMIT clause −

Example

Following INSERT statement tries to insert a record into the table MyPlayers −

Insert ignore into MyPlayers values
(1, NULL, 'Sangakkara', '1977-10-27', 'Long Name For Place Of Birth', 
'Long Name For Country');

Following statement displays the warnings generated by the above statement −

SHOW WARNINGS;

Output

The above mysql query displays the following output −

Level	Code	Message
Warning	1048	Column 'First_Name' cannot be null
Warning	1265	Data truncated for column 'Place_Of_Birth' at row 1
Warning	1265	Data truncated for column 'Country' at row 1
Warning	1062	Duplicate entry '1' for key 'myplayers.PRIMARY'

You can limit the warnings using the LIMIT statement as follows −

SHOW WARNINGS LIMIT 2;

Output

After executing the above query, it will generate the following output −

Level	Code	Message
Warning	1048	Column 'First_Name' cannot be null
Warning	1265	Data truncated for column 'Place_Of_Birth' at row 1

Counting the errors

You can count the number of errors you can use the COUNT(*) or the @@error_count; variable.

Example

Following query tries to insert a record in the above created MyPlayers table.

Insert ignore into MyPlayers values
(1, NULL, NULL, DATE('1977-10-27'), 'Matale', 'Long Name For Country');

You can get the number of errors generated by the above statement using the SHOW ERRORS query as follows −

SHOW COUNT(*) WARNINGS;

Output

The above query displays the following output −

@@session.warning_count
4