MySQL SHOW ERRORS [Easy Tutorial]

By /
Mysql Show Errors Statement

In this tutorial, we will learn about the MySQL SHOW ERRORS statement. It is a diagnostic statement offered by MySQL which is identical to the MySQL SHOW WARNINGS statement. We will see the syntax of the SHOW ERRORS statement and learn different aspects of it through examples. This is going to be a quick guide so stay tuned with us till the end!

Introduction to MySQL SHOW ERRORS

As mentioned above, the SHOW ERRORS statement is used for a diagnostic purpose in MySQL. It displays the only errors in the queries rather than showing warnings and notes.

It is a simple statement that displays all the error statements raised in the query. However, we can control the number and offset of errors to display on the screen by using optional parameters offset and row count.

Along with it, if we want to display only the total number of errors, we can use the COUNT function. We will learn this below through examples.

Syntax of MySQL SHOW ERRORS Statement

To display all errors, the following syntax is used-

SHOW ERRORS;Code language: SQL (Structured Query Language) (sql)

The above statement will show all errors without any limit and offset. To limit the errors count, we can use the LIMIT option. Following is the correct syntax to use the LIMIT –

SHOW ERRORS [LIMIT [offset,] row_count];Code language: SQL (Structured Query Language) (sql)

Where,

  • offset – It indicates the starting index of errors. For example, offset 5 denotes starting from the 5th error.
  • row_count – It indicates the total number of errors to display.

To get the total number of errors, we can use the COUNT function with the SHOW ERRORS statement as shown below-

SHOW COUNT(*) ERRORS;Code language: SQL (Structured Query Language) (sql)

Alternatively, we can use the system variable @@error_count to display the total number of errors as shown in the below syntax-

SELECT @@error_count;Code language: SQL (Structured Query Language) (sql)

Now let’s take some examples of each syntax stated above to check what type of results we get.

Examples of MySQL SHOW ERRORS Statement

To demonstrate the use of the SHOW ERRORS statement, we will use the table of the following schema.

Accounts Table Schema
Accounts Table Schema

Now, let’s display the values of the column amount which does not exist.

SELECT amount FROM accounts;Code language: SQL (Structured Query Language) (sql)
Display Amount Column Values
Display Amount Column Values

Let’s display the error now using the SHOW ERROR statement.

SHOW ERRORS;Code language: SQL (Structured Query Language) (sql)
Show Errors
Show Errors

As you can see, we have received a table of errors which consists of three columns. The level column indicates what type the message is such as warning, error or note. The code column denotes the error code whereas the message column displays the complete message of error.

Let’s display the count of errors now using the COUNT function.

SHOW COUNT(*) ERRORS;Code language: SQL (Structured Query Language) (sql)
Show Error Count
Show Error Count

As you can see, the query shows the output as 1 because only one error is generated from the previous query. The same output can be generated using the @@error_count system variable as shown below.

SELECT @@error_count;Code language: SQL (Structured Query Language) (sql)
Select Error Count
Select Error Count

We have received exactly the same output as the SHOW COUNT(*) ERRORS statement.

Conclusion

In this tutorial, we learned about the SHOW ERRORS statement in MySQL. It is the simplest topic that you can understand without practising a lot of examples. It is very similar to the SHOW WARNINGS statement; so even if you study any one topic, you can understand both effectively.