Common Errors

This section lists some of the most common error messages you will encounter and the steps necessary to work out what is wrong.

Can't Connect to MySQL Server

ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) ERROR 2003 (HY000): Can't connect to MySQL server on '123.45.67.89' (113) 

Usually these errors mean that there is no mysqld process running on the server machine.

If you are unable to connect to a local MySQL server, check your system's process list using ps on Unix or Task Manager on Windows and start the MySQL server if it is not running.

It's possible that the local socket file has been removed. The default location is /tmp/mysql.sock, and some systems periodically clean out the contents to /tmp. It could even be that another user has deleted this fileinadvertently or otherwisebecause the /tmp directory is often writable by all users. If you experience this issue, change the location of the socket file to another directory owned by the mysql user by adding these lines in my.cnf:

[mysqld] socket=/path/to/mysql.sock [client] socket=/path/to/mysql.sock 

To check that mysqld is listening on the port you are expecting, use the netstat command to check that it is indeed accepting network connections. On Unix/Linux use this command:

shell> netstat l 

On Windows, the command is slightly different:

> netstat a | find "LISTENING" 

If you are unable to connect to a remote MySQL server and you are sure mysqld is running, check your firewall settings. You must allow TCP/IP traffic on port 3306or whatever other port the server runs onto allow remote connections.

Note that this error message only indicates an incapability to communicate with a MySQL server, not an authentication problem. If you are able to connect to mysqld but use an invalid password, you see one of the errors from the next section.

Access Denied

ERROR 1045 (28000): Access denied for user 'user'@'host' (using password: YES) 

You might have supplied an incorrect username or password, or a database name that the given username does not have access to when connecting from the current host. Check the privilege tables in the mysql database to make sure the connection arguments are correct.

If you use the short p switch instead of password, remember that the password must follow immediately with no space. Otherwise the argument following p is treated as the database name.

Too Many Connections

ERROR 1040: Too many connections 

The maximum number of simultaneous connections allowed is defined in the max_connections system variable, and the default value is 100. A new value can be set in my.cnf using the following lines:

[mysqld] max_connections=200 

Increasing the max_connections value is usually not the answer. MySQL has exceeded the maximum number of connections for a reason, and you should try to find out why it ran out. You should only increase this value if you are sure your system can support more connections.

In fact, mysqld allows one more connection than the specified number, so a superuser can connect even when the maximum connections have been exceeded. To diagnose problems, run the mysqladmin processlist command and try to work out why there are so many connections.

MySQL Server Has Gone Away

ERROR 2006: MySQL server has gone away 

This error usually indicates a timeout where mysqld has closed the connection with the client. The default timeout interval is eight hours, but this value can be changed using the wait_timeout system variableits value is in seconds. When this error is the result of a timeout, the mysql program attempts to reconnect automatically.

You also see this error if the mysqld process stops running while you are connected to a MySQL servereven if it is restarted instantly. If mysql is unable to reconnect, you should check whether mysqld is still running.

Got Error from Table Handler

Error 1030: Got error 141 from table handler 

This error indicates an internal problem with the database table storage file. It can usually be fixed using the myisamchk utility. The error number reported in this message indicates the nature of the problem. Use the perror command from the shell to find the corresponding error message.

shell> perror 141 141 = Duplicate unique key or constraint on write       or update 

When you encounter this kind of error, you should stop mysqld and run myisamchk --recover on the corresponding .MYI file.

Sometimes the errors reported are symptomatic of other system issues, as in the following case:

$ perror 28 Error code  28: No space left on device