MySQL - SHOW ERRORS Statement

---

---

MySQL SHOW ERRORS Statement

The MySQL SHOW ERRORS Statement is used to retrieve the information about the error occurred during the execution of the previous MySQL statement in the current session.

Syntax

Following is the syntax of the SHOW ERRORS Statement −

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

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

Following is the output of the above query −

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

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

Insert into MyPlayers values(2, 'Rohit', 'Sharma', DATE('1987-04-30'), 
'Nagpur', 'India');
ERROR 1062 (23000): Duplicate entry '2' for key 'myplayers.PRIMARY'

Following query retrieves the state and message of the above generated error −

SHOW ERRORS;

The above mysql query produces the following output −

Level	Code	Message
Error	1136	Column count doesn't match value count at row 1

Following are two more insert statements that generates errors −

Insert into WrongTable values(3, 'Kumara', 'Sangakkara', 
DATE('1977-10-27'), 'Matale', 'Srilanka');
ERROR 1146 (42S02): Table 'demo.wrongtable' doesn't exist

Following query retrieves the state and message of the above generated error −

SHOW ERRORS;

After executing the above query, it generates the output shown below −

Level	Code	Message
Error	1146	Table 'demo.wrongtable' doesn't exist

Insert into MyPlayers values('Virat', 'Kohli', DATE('1988-11-05'), 
'Delhi', 'India');
ERROR 1136 (21S01): Column count doesn't match value count at row 1

Following query retrieves the state and message of the above generated error −

SHOW ERRORS;

This query will generate the output shown below −

Level	Code	Message
Error	1136	Column count doesn't match value count at row 1

LIMIT

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

Insert into MyPlayers values(3, 'Kumara', 'Sangakkara', 
DATE('1977-10-27'),'Matale', 'Long Name For Country');
ERROR 1406 (22001): Data too long for column 'Country' at row 1

Let's limit the number of errors using the LIMIT clause:

SHOW ERRORS LIMIT 0;
Empty set (0.00 sec)

Now, Let's use the LIMIT clause to restrict the number of errors to 1 −

SHOW ERRORS LIMIT 1;

Following is the output of the above query −

Level	Code	Message
Error	1064	You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LINIT 0' at line 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 into MyPlayers values(3, 'Kumara', 'Sangakkara', 
DATE('1977-10-27'), 'Matale', 'Long Name For Country');
ERROR 1406 (22001): Data too long for column 'Country' at row 1

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

SHOW COUNT(*) ERRORS;

Output

After executing the above query, it generates the output shown below −

@@session.error_count
1

We can also use the error_count variable for the same purpose as −

SELECT @@error_count;

Output

Once the query is executed, it will produce an output shown below −

@@error_count
1