MySQL Errors and Warnings

Overview

In this tutorial, we will learn how to detect and resolve MySQL Errors and Warnings. MySQL provides various methods for detecting errors and for avoiding them as well.

This troubleshooting guide will assist you to understand and solve any issues you may experience with the MySQL database.

MySQL Errors

The first step when encountering an error is to recognize the error and identify the cause and effect.

A typical error message you might get when issuing a flawed SQL query would look like this:

mysql> SELECT FROM Country WHERE Name=’North America’;
ERROR 1064 (42000): 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 ‘FROM Country WHERE Name=’North America” at line 1

mysql>
mysql> SHOW ERRORS \G
*************************** 1. row ***************************
Level: Error
Code: 1064
Message: 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 ‘FROM Country WHERE Name=’North America” at line 1
1 row in set (0.00 sec)

MySQL Errors and Warnings

In the above example, the error message indicates that there is a syntax error in your statement. It recommends that you check the manual.

MySQL Error message components are as follows:

Level -: Error Level.
Code – : An Error code. These codes are defined by standard SQL.
Message- : A text message that describes the problem.

In some cases, the warning/error will not display all three message components as shown in the above example. Many MySQL statements will generate warnings and errors that may not be output for the user to view automatically. In those cases there are two statements that can be issued in order to view the messages:

SHOW WARNINGS
SHOW ERRORS

Syntax

mysql>SHOW WARNINGS [LIMIT [offset,] row_count]

SHOW WARNING

The SHOW WARNINGS shows any error, warning, and note messages that resulted from the last statement that generated messages, or nothing if the last statement that used a table generated no messages.

SHOW ERRORS

The SHOW ERRORS statement, shows only the errors. The list of messages is reset for each new statement that uses a table.

MySQL Reference Manual

The MySQL reference manual can be used to find the error codes and the definitions.

Error Codes and Messages:

http://dev.mysql.com/doc/refman/8.0/en/error-handling.html

MySQL Common Errors:

http://dev.mysql.com/doc/refman/8.0/en/problems.html

MySQL Common Problems

Once you have identify and understood the cause of the error or the warning,the next step is to make the necessary modifications to the SQL query. Make the specified change and try to execute your SQL statement again.

—