MySQL 에러코드와 에러메세지(ERROR Codes and Messages)

MySQL 에러코드와 에러메세지
(ERROR Codes and Messages)

Just a general rule with error messages: Usually they are not as good as they could be. But never the less read them carefully. Most of the time they tell you what you need to know...

And an other wisdom: Errors are really errors and they have to be solved. Warnings are warnings and you should investigate if they appear. Do not ignore both of them unless you have clearly understood what you are doing.

With MySQL:

mysql> SHOW WARNINGS

is often your friend! Further sources to find the problems are:

mysql> SHOW SLAVE STATUS\G shell> perror <error_number> shell> perror --ndb <error_number>

And the MySQL Error log.

MYSQL SERVER ERROR CODES AND MESSAGES

 

• Error: 1000 SQLSTATE: HY000 (ER_HASHCHK)
• Message: hashchk
• Error: 1001 SQLSTATE: HY000 (ER_NISAMCHK)
• Message: isamchk
• Error: 1002 SQLSTATE: HY000 (ER_NO)
• Message: NO
• Error: 1003 SQLSTATE: HY000 (ER_YES)
• Message: YES
• Error: 1004 SQLSTATE: HY000 (ER_CANT_CREATE_FILE)
• Message: Can't create file '%s' (errno: %d)
• Error: 1005 SQLSTATE: HY000 (ER_CANT_CREATE_TABLE)
• Message: Can't create table '%s' (errno: %d)

• ---

  Error: 1006 SQLSTATE: HY000 (ER_CANT_CREATE_DB) Message: Can't create database '%s' (errno: %d)How does the MySQL error message look like?mysql> CREATE DATABASE test; ERROR 1006 (HY000): Can't create database 'test' (errno: 13)What does the MySQL error message mean?Additional information you can get as follows:shell> perror 13 OS error code 13: Permission deniedWhen does this MySQL error message happen?How to fix this MySQL error?shell> sudo su - shell> cd /var/lib shell> chown -R mysql:mysql mysqlAlternatively you can start the mysqld process as follows:shell> bin/mysqld_safe --user=mysqlor set the user accordingly in your my.cnf file.# my.cnf [mysqld] user = mysql
• If the MySQL data directory does not belong to the user the mysqld process is running under, you can fix this for example as follows:
• This MySQL error usually happens when the MySQL data directory (datadir) does not belong to the user under which the mysqld process runs. For example the data directory belongs to the user root and the mysqld process runs as user mysql.
• The MySQL Server (mysqld process) can, for whatever reason, NOT create a schema/database.

• ---

  Error: 1007 SQLSTATE: HY000 (ER_DB_CREATE_EXISTS) Message: Can't create database '%s'; database existsHow does the MySQL error message look like?mysql> CREATE SCHEMA test; ERROR 1007 (HY000): Can't create database 'test'; database existsWhat does the MySQL error message mean?When does this MySQL error message happen?How to fix this MySQL error?
  1. Choose an other name for your new database / schema.
  2. Drop your old database / schema.
  3. Rename your old database / schema (we can provide you more information, how to do this).
• You have 2 possiblities:
• This MySQL error apears, as the error message states, when the database already exists.
• MySQL (the mysqld process) can, for whatever reason NOT create a schema/database.

• ---

  Error: 1008 SQLSTATE: HY000 (ER_DB_DROP_EXISTS)
• Message: Can't drop database '%s'; database doesn't exist
• Error: 1009 SQLSTATE: HY000 (ER_DB_DROP_DELETE)
• Message: Error dropping database (can't delete '%s', errno: %d)
• Error: 1010 SQLSTATE: HY000 (ER_DB_DROP_RMDIR)
• Message: Error dropping database (can't rmdir '%s', errno: %d)
• Error: 1011 SQLSTATE: HY000 (ER_CANT_DELETE_FILE)
• Message: Error on delete of '%s' (errno: %d)
• Error: 1012 SQLSTATE: HY000 (ER_CANT_FIND_SYSTEM_REC)
• Message: Can't read record in system table

• ---

  Error: 1013 SQLSTATE: HY000 (ER_CANT_GET_STAT) Message: Can't get status of '%s' (errno: %d)How does the MySQL error message look like?mysql> CREATE DATABASE test; ERROR 13 (HY000): Can't get stat of './test' (Errcode: 13)What does the MySQL error message mean?shell> perror 13 OS error code 13: Permission deniedthe mysqld process has not rights to write to the datadir (typically /var/lib/mysql/data) directory.This usually happens when you mix the usage of ther user root and the database user (typically mysql). Often a directory is created with a user (root) which does not allow an other user (mysql) to access its files or directories.Keep both roles (root and mysql) strictly separated.
  If you are already in this situation you have to change the directory permissions:shell> sudo su - shell> cd /var/lib/mysql shell> chown -R mysql:mysql data
• How to fix this MySQL error?
• When does this MySQL error message happen?
• As you can see with the perror command:

• ---

  Error: 1014 SQLSTATE: HY000 (ER_CANT_GET_WD)
• Message: Can't get working directory (errno: %d)
• Error: 1015 SQLSTATE: HY000 (ER_CANT_LOCK)
• Message: Can't lock file (errno: %d)
• Error: 1016 SQLSTATE: HY000 (ER_CANT_OPEN_FILE)
• Message: Can't open file: '%s' (errno: %d)
• Error: 1017 SQLSTATE: HY000 (ER_FILE_NOT_FOUND)
• Message: Can't find file: '%s' (errno: %d)
• Error: 1018 SQLSTATE: HY000 (ER_CANT_READ_DIR)
• Message: Can't read dir of '%s' (errno: %d)
• Error: 1019 SQLSTATE: HY000 (ER_CANT_SET_WD)
• Message: Can't change dir to '%s' (errno: %d)
• Error: 1020 SQLSTATE: HY000 (ER_CHECKREAD)
• Message: Record has changed since last read in table '%s'
• Error: 1021 SQLSTATE: HY000 (ER_DISK_FULL)
• Message: Disk full (%s); waiting for someone to free some space...
• Error: 1022 SQLSTATE: 23000 (ER_DUP_KEY)
• Message: Can't write; duplicate key in table '%s'
• Error: 1023 SQLSTATE: HY000 (ER_ERROR_ON_CLOSE)
• Message: Error on close of '%s' (errno: %d)
• Error: 1024 SQLSTATE: HY000 (ER_ERROR_ON_READ)
• Message: Error reading file '%s' (errno: %d)
• Error: 1025 SQLSTATE: HY000 (ER_ERROR_ON_RENAME)
• Message: Error on rename of '%s' to '%s' (errno: %d)
• Error: 1026 SQLSTATE: HY000 (ER_ERROR_ON_WRITE)
• Message: Error writing file '%s' (errno: %d)
• Error: 1027 SQLSTATE: HY000 (ER_FILE_USED)
• Message: '%s' is locked against change

• ---

  Error: 1028 SQLSTATE: HY000 (ER_FILSORT_ABORT) Message: Sort abortedHow does the MySQL error message look like?120216 14:08:17 [ERROR] /usr/sbin/mysqld: Sort abortedWhen does this MySQL error message happen?
  • Insufficient disk space in tmpdir prevented tmpfile from being created.
  • Insufficient memory for sort_buffer_size to be allocated.
  • Somebody ran KILL <id> in the middle of a filesort.
  • The server was shutdown while some queries were sorting.
  • A transaction got rolled back or aborted due to lock wait timeout or deadlock.
  • Unexpected errors, such as source table or even tmp table was corrupt.
  • Processing of a subquery failed which was also sorting.
  Shane Bester: [Bug 36022]
• In the MySQL error log you see:

• ---

  Error: 1029 SQLSTATE: HY000 (ER_FORM_NOT_FOUND)
• Message: View '%s' doesn't exist for '%s'
• Error: 1030 SQLSTATE: HY000 (ER_GET_ERRNO)
• Message: Got error %d from storage engine
• Error: 1031 SQLSTATE: HY000 (ER_ILLEGAL_HA)
• Message: Table storage engine for '%s' doesn't have this option
• Error: 1032 SQLSTATE: HY000 (ER_KEY_NOT_FOUND)
• Message: Can't find record in '%s'
• Error: 1033 SQLSTATE: HY000 (ER_NOT_FORM_FILE)
• Message: Incorrect information in file: '%s'
• Error: 1034 SQLSTATE: HY000 (ER_NOT_KEYFILE)
• Message: Incorrect key file for table '%s'; try to repair it
• Error: 1035 SQLSTATE: HY000 (ER_OLD_KEYFILE)
• Message: Old key file for table '%s'; repair it!
• Error: 1036 SQLSTATE: HY000 (ER_OPEN_AS_READONLY)
• Message: Table '%s' is read only
• Error: 1037 SQLSTATE: HY001 (ER_OUTOFMEMORY)
• Message: Out of memory; restart server and try again (needed %d bytes)
• Error: 1038 SQLSTATE: HY001 (ER_OUT_OF_SORTMEMORY)
• Message: Out of sort memory; increase server sort buffer size
• Error: 1039 SQLSTATE: HY000 (ER_UNEXPECTED_EOF)
• Message: Unexpected EOF found when reading file '%s' (errno: %d)
• Error: 1040 SQLSTATE: 08004 (ER_CON_COUNT_ERROR)
• Message: Too many connections
• Error: 1041 SQLSTATE: HY000 (ER_OUT_OF_RESOURCES)
• Message: Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
• Error: 1042 SQLSTATE: 08S01 (ER_BAD_HOST_ERROR)
• Message: Can't get hostname for your address
• Error: 1043 SQLSTATE: 08S01 (ER_HANDSHAKE_ERROR)
• Message: Bad handshake
• Error: 1044 SQLSTATE: 42000 (ER_DBACCESS_DENIED_ERROR)
• Message: Access denied for user '%s'@'%s' to database '%s'

• ---

  Error: 1045 SQLSTATE: 28000 (ER_ACCESS_DENIED_ERROR) Message: Access denied for user '%s'@'%s' (using password: %s)How does the MySQL error message look like?mysql --user=nogo ERROR 1045 (28000): Access denied for user 'nogo'@'localhost' (using password: NO)orshell> mysql --user=nogo --password=nogo ERROR 1045 (28000): Access denied for user 'nogo'@'localhost' (using password: YES)What does the MySQL error message mean?When does this MySQL error message happen?How to fix this MySQL error?mysql> SELECT user, host FROM mysql.user WHERE user = 'nogo'; mysql> SHOW GRANTS FOR 'nogo'@'localhost';
• Check the access privileges for this user like:
• This can happen when you connect with the wrong user to the database or when you use a password when no password is required or when you do not use a password when one is required or when you connect from the wrong host or over the socket/port when you are not allowed to.
• This MySQL Error Message either means, that this user does not exist or that the password is not correct.

• ---

  Error: 1046 SQLSTATE: 3D000 (ER_NO_DB_ERROR)
• Message: No database selected
• Error: 1047 SQLSTATE: 08S01 (ER_UNKNOWN_COM_ERROR)
• Message: Unknown command
• Error: 1048 SQLSTATE: 23000 (ER_BAD_NULL_ERROR)
• Message: Column '%s' cannot be null

• ---

  Error: 1049 SQLSTATE: 42000 (ER_BAD_DB_ERROR) Message: Unknown database '%s'How does the MySQL error message look like?shell> mysql -u root bla ERROR 1049 (42000): Unknown database 'bla'What does the MySQL error message mean?When does this MySQL error message happen?How to fix this MySQL error?mysql> CREATE SCHEMA bla;
• Either try to connect to an existing MySQL database (for example test or mysql). Or create a database with the according name:
• In the above example the database bla does not exist.
• This MySQL error message means that you try to connect to a database that does not exist.

---

1. MySQL Errors 1000 - 1049
2. MySQL Errors 1050 - 1099
3. MySQL Errors 1100 - 1149
4. MySQL Errors 1150 - 1199
5. MySQL Errors 1200 - 1249
6. MySQL Errors 1250 - 1299
7. MySQL Errors 1300 - 1349
8. MySQL Errors 1350 - 1399
9. MySQL Errors 1400 - 1449
10. MySQL Errors 1450 - 1499
11. MySQL Errors 1500 - 1549
12. MySQL Errors 1550 - 1599
13. MySQL Errors 1600 - 1649
14. MySQL Errors 1650 - 1699
15. MySQL Errors 1700 - 1749

MYSQL CLIENT ERROR CODES AND MESSAGES

1. MySQL Errors 2000 - 2049
2. MySQL Errors 2050 - 2099

LITERATURE

1. [ 1 ] Appendix C. Errors, Error Codes, and Common Problems