Appendix B Errors, Error Codes, and Common Problems

Table of Contents

B.1 Sources of Error Information
B.2 Types of Error Values
B.3 Server Error Codes and Messages
B.4 Client Error Codes and Messages
B.5 Problems and Common Errors
B.5.1 How to Determine What Is Causing a Problem
B.5.2 Common Errors When Using MySQL Programs
B.5.3 Administration-Related Issues
B.5.4 Query-Related Issues
B.5.5 Optimizer-Related Issues
B.5.6 Table Definition-Related Issues
B.5.7 Known Issues in MySQL

This appendix lists common problems and errors that may occur and potential resolutions, in addition to listing the errors that may appear when you call MySQL from any host language. The first section covers problems and resolutions. Detailed information on errors is provided: One list displays server error messages. Another list displays client program messages.

B.1 Sources of Error Information

There are several sources of error information in MySQL:

Descriptions of server and client errors are provided later in this Appendix. For information about errors related to InnoDB, see Section 15.20.4, “InnoDB Error Handling”.

B.2 Types of Error Values

When an error occurs in MySQL, the server returns two types of error values:

  • A MySQL-specific error code. This value is numeric. It is not portable to other database systems.

  • An SQLSTATE value. The value is a five-character string (for example, '42S02'). The values are taken from ANSI SQL and ODBC and are more standardized.

A message string that provides a textual description of the error is also available.

When an error occurs, the MySQL error code, SQLSTATE value, and message string are available using C API functions:

For prepared statements, the corresponding error functions are mysql_stmt_errno(), mysql_stmt_sqlstate(), and mysql_stmt_error(). All error functions are described in Section 27.7, “MySQL C API”.

The number of errors, warnings, and notes for the previous statement can be obtained by calling mysql_warning_count(). See Section 27.7.7.82, “mysql_warning_count()”.

The first two characters of an SQLSTATE value indicate the error class:

  • Class = '00' indicates success.

  • Class = '01' indicates a warning.

  • Class = '02' indicates not found. This is relevant within the context of cursors and is used to control what happens when a cursor reaches the end of a data set. This condition also occurs for SELECT ... INTO var_list statements that retrieve no rows.

  • Class > '02' indicates an exception.

B.3 Server Error Codes and Messages

MySQL programs have access to several types of error information when the server returns an error. For example, the mysql client program displays errors using the following format:

shell> SELECT * FROM no_such_table;
ERROR 1146 (42S02): Table 'test.no_such_table' doesn't exist

The message displayed contains three types of information:

  • A numeric error code (1146). This number is MySQL-specific and is not portable to other database systems.

  • A five-character SQLSTATE value ('42S02'). The values are taken from ANSI SQL and ODBC and are more standardized. Not all MySQL error numbers have corresponding SQLSTATE values. In these cases, 'HY000' (general error) is used.

  • A message string that provides a textual description of the error.

For error checking, use error codes, not error messages. Error messages do not change often, but it is possible. Also if the database administrator changes the language setting, that affects the language of error messages.

Error codes are stable across GA releases of a given MySQL series. Before a series reaches GA status, new codes may still be under development and subject to change.

Server error information comes from the following source files. For details about the way that error information is defined, see the MySQL Internals Manual.

  • Error message information is listed in the share/errmsg-utf8.txt file. %d and %s represent numbers and strings, respectively, that are substituted into the Message values when they are displayed.

  • The Error values listed in share/errmsg-utf8.txt are used to generate the definitions in the include/mysqld_error.h and include/mysqld_ername.h MySQL source files.

  • The SQLSTATE values listed in share/errmsg-utf8.txt are used to generate the definitions in the include/sql_state.h MySQL source file.

Because updates are frequent, it is possible that those files will contain additional error information not listed here.