MySQL的常见错误的解决方法(英文)

Common Errors When Using MySQL(和PHP搭配之最佳组合) Programs

This section lists some errors that users frequently encounter when running MySQL(和PHP搭配之最佳组合) programs. Although the problems show up when you try to run client programs, the solutions to many of the problems involves changing the configuration of the MySQL(和PHP搭配之最佳组合) server.

1. Access denied

An Access denied error can have many causes. Often the problem is related to the MySQL(和PHP搭配之最佳组合) accounts that the server allows client programs to use when connecting. See Section 5.7.8, “Causes of Access denied Errors”. See Section 5.7.2, “How the Privilege System Works”.

2. Can't connect to [local] MySQL(和PHP搭配之最佳组合) server

A MySQL(和PHP搭配之最佳组合) client on Unix can connect to the MySQL(和PHP搭配之最佳组合)d server in two different ways: By using a Unix socket file to connect through a file in the filesystem (default /tmp/MySQL(和PHP搭配之最佳组合).sock), or by using TCP/IP, which connects through a port number. A Unix socket file connection is faster than TCP/IP, but can be used only when connecting to a server on the same computer. A Unix socket file is used if you don't specify a hostname or if you specify the special hostname localhost.

If the MySQL(和PHP搭配之最佳组合) server is running on Windows 9x or Me, you can connect only via TCP/IP. If the server is running on Windows NT, 2000, XP, or 2003 and is started with the --enable-named-pipe option, you can also connect with named pipes if you run the client on the host where the server is running. The name of the named pipe is MySQL(和PHP搭配之最佳组合) by default. If you don't give a hostname when connecting to MySQL(和PHP搭配之最佳组合)d, a MySQL(和PHP搭配之最佳组合) client first tries to connect to the named pipe. If that doesn't work, it connects to the TCP/IP port. You can force the use of named pipes on Windows by using . as the hostname.

The error (2002) Can't connect to ... normally means that there is no MySQL(和PHP搭配之最佳组合) server running on the system or that you are using an incorrect Unix socket filename or TCP/IP port number when trying to connect to the server.

Start by checking whether there is a process named MySQL(和PHP搭配之最佳组合)d running on your server host. (Use ps xa | grep MySQL(和PHP搭配之最佳组合)d on Unix or the Task Manager on Windows.) If there is no such process, you should start the server. See Section 2.9.2.3, “Starting and Troubleshooting the MySQL(和PHP搭配之最佳组合) Server”.

If a MySQL(和PHP搭配之最佳组合)d process is running, you can check it by trying the following commands. The port number or Unix socket filename might be different in your setup. host_ip represents the IP number of the machine where the server is running.

shell> MySQL(和PHP搭配之最佳组合)admin version

shell> MySQL(和PHP搭配之最佳组合)admin variables

shell> MySQL(和PHP搭配之最佳组合)admin -h `hostname` version variables

shell> MySQL(和PHP搭配之最佳组合)admin -h `hostname` --port=3306 version

shell> MySQL(和PHP搭配之最佳组合)admin -h host_ip version

shell> MySQL(和PHP搭配之最佳组合)admin --protocol=socket --socket=/tmp/MySQL(和PHP搭配之最佳组合).sock version

Note the use of backticks rather than forward quotes with the hostname command; these cause the output of hostname (that is, the current hostname) to be substituted into the MySQL(和PHP搭配之最佳组合)admin command. If you have no hostname command or are running on Windows, you can manually type the hostname of your machine (without backticks) following the -h option. You can also try -h 127.0.0.1 to connect with TCP/IP to the local host.

Here are some reasons the Can't connect to local MySQL(和PHP搭配之最佳组合) server error might occur:

MySQL(和PHP搭配之最佳组合)d is not running. Check your operating system's process list to ensure the MySQL(和PHP搭配之最佳组合)d process is present.

You're running a MySQL(和PHP搭配之最佳组合) server on Windows with many TCP/IP connections to it. If you're experiencing that quite often your clients get that error, you can find a workaround here: Section 2.1, “Connection to MySQL(和PHP搭配之最佳组合) Server Failing on Windows”.

You are running on a system that uses MIT-pthreads. If you are running on a system that doesn't have native threads, MySQL(和PHP搭配之最佳组合)d uses the MIT-pthreads package. See Section 2.1.1, “Operating Systems Supported by MySQL(和PHP搭配之最佳组合)”. However, not all MIT-pthreads versions support Unix socket files. On a system without socket file support, you must always specify the hostname explicitly when connecting to the server. Try using this command to check the connection to the server:

shell> MySQL(和PHP搭配之最佳组合)admin -h `hostname` version

Someone has removed the Unix socket file that MySQL(和PHP搭配之最佳组合)d uses (/tmp/MySQL(和PHP搭配之最佳组合).sock by default). For example, you might have a cron job that removes old files from the /tmp directory. You can always run MySQL(和PHP搭配之最佳组合)admin version to check whether the Unix socket file that MySQL(和PHP搭配之最佳组合)admin is trying to use really exists. The fix in this case is to change the cron job to not remove MySQL(和PHP搭配之最佳组合).sock or to place the socket file somewhere else. See Section A.4.5, “How to Protect or Change the MySQL(和PHP搭配之最佳组合) Socket File /tmp/MySQL(和PHP搭配之最佳组合).sock”.

You have started the MySQL(和PHP搭配之最佳组合)d server with the --socket=/path/to/socket option, but forgotten to tell client programs the new name of the socket file. If you change the socket pathname for the server, you must also notify the MySQL(和PHP搭配之最佳组合) clients. You can do this by providing the same --socket option when you run client programs. You also need to ensure that clients have permission to access the MySQL(和PHP搭配之最佳组合).sock file. To find out where the socket file is, you can do:

shell> netstat -ln | grep MySQL(和PHP搭配之最佳组合)

See Section A.4.5, “How to Protect or Change the MySQL(和PHP搭配之最佳组合) Socket File /tmp/MySQL(和PHP搭配之最佳组合).sock”.

You are using Linux and one server thread has died (dumped core). In this case, you must kill the other MySQL(和PHP搭配之最佳组合)d threads (for example, with kill or with the MySQL(和PHP搭配之最佳组合)_zap script) before you can restart the MySQL(和PHP搭配之最佳组合) server. See Section A.4.2, “What to Do If MySQL(和PHP搭配之最佳组合) Keeps Crashing”.

The server or client program might not have the proper access privileges for the directory that holds the Unix socket file or the socket file itself. In this case, you must either change the access privileges for the directory or socket file so that the server and clients can access them, or restart MySQL(和PHP搭配之最佳组合)d with a --socket option that specifies a socket filename in a directory where the server can create it and where client programs can access it.

If you get the error message Can't connect to MySQL(和PHP搭配之最佳组合) server on some_host, you can try the following things to find out what the problem is:

Check whether the server is running on that host by executing telnet some_host 3306 and pressing the Enter key a couple of times. (3306 is the default MySQL(和PHP搭配之最佳组合) port number. Change the value if your server is listening to a different port.) If there is a MySQL(和PHP搭配之最佳组合) server running and listening to the port, you should get a response that includes the server's version number. If you get an error such as telnet: Unable to connect to remote host: Connection refused, then there is no server running on the given port.

If the server is running on the local host, try using MySQL(和PHP搭配之最佳组合)admin -h localhost variables to connect using the Unix socket file. Verify the TCP/IP port number that the server is configured to listen to (it is the value of the port variable.)

Make sure that your MySQL(和PHP搭配之最佳组合)d server was not started with the --skip-networking option. If it was, you cannot connect to it using TCP/IP.

Check to make sure that there is no firewall blocking access to MySQL(和PHP搭配之最佳组合). Applications such as ZoneAlarm and the Windows XP personal firewall may need to be configured to allow external access to a MySQL(和PHP搭配之最佳组合) server.

2.1. Connection to MySQL(和PHP搭配之最佳组合) Server Failing on Windows

When you're running a MySQL(和PHP搭配之最佳组合) server on Windows with many TCP/IP connections to it, and you're experiencing that quite often your clients get a Can't connect to MySQL(和PHP搭配之最佳组合) server error, the reason might be that Windows doesn't allow for enough ephemeral (short-lived) ports to serve those connections.

By default, Windows allows 5000 ephemeral (short-lived) TCP ports to the user. After any port is closed it will remain in a TIME_WAIT status for 120 seconds. This status allows the connection to be reused at a muc