MySQL - Debugging

Messages typically have three components: a MySQL-specific error code, a SQLSTATE error code (defined by standard SQL and ODBC), a text message that describes the problem.

An informational string is returned by statements that affect multiple rows. This string provides a summary of the statement outcome.

For cases such as the preceding SELECT from a non-existent table, where all three error values are displayed, you can simply look at the information provided to see what the problem was. In other cases, all information might not be displayed. The information string for multiple-row statements is a summary, not a complete diagnostics. An operating system error includes an Errcode number that might have system-specific meaning.

The SHOW WARNINGS and SHOW ERRORS statements display warning and error information for statements that produce diagnostic information.

The perror command-line utility (part of MySQL) displays the meaning of operating system-related error codes.

There is a chapter in the MySQL Reference Manual that lists error codes and messages.

MySQL server generates warnings when it is not able to fully comply with a request or when an action has possibly unintended side effects. These warnings can be displayed with SHOW WARNINGS.

You can combine SHOW WARNINGS with LIMIT to scroll through the warnings one section at a time.


Warnings generated by one statement are available from the server only for a limited time (until you issue another statement that can generate warnings). If you need to see warnings, you should always fetch them as soon as you detect that they were generated.

To suppress generation of Note warnings, you can set the sql_notes system variable to zero:

SET sql_notes = 0;

SHOW WARNINGS displays notes, warnings, and errors. SHOW ERRORS is similar to SHOW WARNINGS but only displays messages for error conditions. It shows only messages having higher severity and tends to produce less output. Like SHOW WARNINGS, SHOW ERRORS supports the LIMIT clause. You can also use SHOW COUNT(*) ERRORS to obtain a count of error messages.

The purpose of the perror program is to show you information about the error codes used by MySQL when operating system-level errors occur.

mysql> CREATE TABLE CountryCopy SELECT * FROM Country;
ERROR 1 (HY000): Can't create/write to file './world/CountryCopy.frm'
(Errcode: 13)

This error message indicates that MySQL cannot write to the file CountryCopy.frm, but does not report the reason. It might be due to a full disk, a filesystem permission problem, or some other error. To find out, run the perror program with an argument of the number given following the Errcode. perror displays a message indicating that the source of the problem is that someone has incorrectly set the filesystem permissions for the current database.

shell> perror 13
Error code 13:  Permission denied.
mysql> SELECT * FROM no_such_table;
ERROR 1146 (42S02): Table 'test.no_such_table' doesn't exist

mysql> INSERT INTO integers VALUES ('abc'), (-5), (NULL);
Query OK, 3 rows affected, 3 warnings (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 3

mysql> CREATE TABLE CountryCopy SELECT * FROM Country;
ERROR 1 (HY000): Can't create/write to file './world/CountryCopy.frm'
(Errcode: 13)
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License