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

Note: In older versions of PHP, the mysql extension does not support the authentication protocol in MySQL 4.1.1 and higher. This is true regardless of the PHP version being used. If you wish to use the mysql extension with MySQL 4.1 or newer, you may need to follow one of the options discussed above for configuring MySQL to work with old clients. The mysqli extension (stands for "MySQL, Improved"; added in PHP 5) is compatible with the improved password hashing employed in MySQL 4.1 and higher, and no special configuration of MySQL need be done in order to use this MySQL client library. For more information about the mysqli extension, see http://php.net/mysqli.

For additional background on password hashing and authentication, see Section 5.7.9, “Password Hashing in MySQL 4.1”.

4. Password Fails When Entered Interactively
MySQL client programs prompt for a password when invoked with a --password or -p option that has no following password value:

shell> mysql -u user_name -p
Enter password:

On some systems, you may find that your password works when specified in an option file or on the command line, but not when you enter it interactively at the Enter password: prompt. This occurs when the library provided by the system to read passwords limits password values to a small number of characters (typically eight). That is a problem with the system library, not with MySQL. To work around it, change your MySQL password to a value that is eight or fewer characters long, or put your password in an option file.

5. Host 'host_name' is blocked
If you get the following error, it means that mysqld has received many connect requests from the host 'host_name' that have been interrupted in the middle:

Host 'host_name' is blocked because of many connection errors.
Unblock with 'mysqladmin flush-hosts'

The number of interrupted connect requests allowed is determined by the value of the max_connect_errors system variable. After max_connect_errors failed requests, mysqld assumes that something is wrong (for example, that someone is trying to break in), and blocks the host from further connections until you execute a mysqladmin flush-hosts command or issue a FLUSH HOSTS statement. See the section called “Server System Variables”.

By default, mysqld blocks a host after 10 connection errors. You can adjust the value by starting the server like this:

shell> mysqld_safe --max_connect_errors=10000 &

If you get this error message for a given host, you should first verify that there isn't anything wrong with TCP/IP connections from that host. If you are having network problems, it does you no good to increase the value of the max_connect_errors variable.

6. Too many connections
If you get a Too many connections error when you try to connect to the mysqld server, this means that all available connections are in use by other clients.

The number of connections allowed is controlled by the max_connections system variable. Its default value is 100. If you need to support more connections, you should restart mysqld with a larger value for this variable.

mysqld actually allows max_connections+1 clients to connect. The extra connection is reserved for use by accounts that have the SUPER privilege. By granting the SUPER privilege to administrators and not to normal users (who should not need it), an administrator can connect to the server and use SHOW PROCESSLIST to diagnose problems even if the maximum number of unprivileged clients are connected. See Section 13.5.4.16, “SHOW PROCESSLIST Syntax”.

The maximum number of connections MySQL can support depends on the quality of the thread library on a given platform. Linux or Solaris should be able to support 500-1000 simultaneous connections, depending on how much RAM you have and what your clients are doing. Static Linux binaries provided by MySQL AB can support up to 4000 connections.

7. Out of memory
If you issue a query using the mysql client program and receive an error like the following one, it means that mysql does not have enough memory to store the entire query result:

mysql: Out of memory at line 42, 'malloc.c'
mysql: needed 8136 byte (8k), memory in use: 12481367 bytes (12189k)
ERROR 2008: MySQL client ran out of memory

To remedy the problem, first check whether your query is correct. Is it reasonable that it should return so many rows? If not, correct the query and try again. Otherwise, you can invoke mysql with the --quick option. This causes it to use the mysql_use_result() C API function to retrieve the result set, which places less of a load on the client (but more on the server).

8. MySQL server has gone away
This section also covers the related Lost connection to server during query error.

The most common reason for the MySQL server has gone away error is that the server timed out and closed the connection. In this case, you normally get one of the following error codes (which one you get is operating system-dependent):

Error Code Description
CR_SERVER_GONE_ERROR The client couldn't send a question to the server.
CR_SERVER_LOST The client didn't get an error when writing to the server, but it didn't get a full answer (or any answer) to the question.

By default, the server closes the connection after eight hours if nothing has happened. You can change the time limit by setting the wait_timeout variable when you start mysqld. See the section called “Server System Variables”.

If you have a script, you just have to issue the query again for the client to do an automatic reconnection. This assumes that you have automatic reconnection in the client enabled (which is the default for the mysql command-line client).

Some other common reasons for the MySQL server has gone away error are:

You (or the db administrator) has killed the running thread with a KILL statement or a mysqladmin kill command.

You tried to run a query after closing the connection to the server. This indicates a logic error in the application that should be corrected.

You got a timeout from the TCP/IP connection on the client side. This may happens if you have been using the commands: mysql_options(..., MYSQL_OPT_READ_TIMEOUT,...) or mysql_options(..., MYSQL_OPT_WRITE_TIMEOUT,...). In this case increasing the timeout may help solve the problem.

You have encountered a timeout on the server side and the automatic reconnection in the client is disabled (the reconnect flag in the MYSQL structure is equal to 0).

You are using a windows client and the server had dropped the connection (probably because wait_timeout expired) before the command was issued.

The problem on windows is that in some cases MySQL doesn't get an error from the OS when writing to the TCP/IP connection to the server, but instead gets the error when trying to read the answer from connection.

In this case, even if the reconnect flag in the MYSQL structure is equal to 1, MySQL does not automatically reconnect and re-issue the query as it doesn't know if the server did get the original query or not.

The solution to this is to either do a mysql_ping on the connection if there has been a long time since the last query (this is what MyODBC does) or set wait_timeout on the mysqld server so high that it in practice never times out.

You can also get these errors if you send a query to the server that is incorrect or too large. If mysqld receives a packet that is too large or out of order, it assumes that something has gone wrong with the client and closes the connection. If you need big queries (for example, if you are working with big BLOB columns), you can increase the query limit by setting the server's max_allowed_packet variable, which has a default value of 1MB. You may also need to increase the maximum packet size on the client end. More information on setting the packet size is given in Section 9, “Packet too large”.

You also get a lost connection if you are sending a packet 16MB or larger if your client is older than 4.0.8 and your server is 4.0.8 and above, or the other way around.

You may also see the MySQL server has gone away error if MySQL is started with the --skip-networking option.

You have encountered a bug where the server died while executing the query.

You can check whether the MySQL server died and restarted by executing mysqladmin version and examining the server's uptime. If the client connection was broken because mysqld crashed and restarted, you should concentrate on finding the reason for the crash. Start by checking whether issuing the query again kills the server again. See Section A.4.2, “What to Do If MySQL Keeps Crashing”.

You can get more information about the lost connections by starting mysqld with the --log-warnings=2 option. This logs some of the disconnected errors in the hostname.err file. See Section 5.11.1, “The Error Log”.

If you want to create a bug report regarding this problem, be sure that you include the following information:

Indicate whether or not the MySQL server died. You can find information about this in the server error log. See Section A.4.2, “What to Do If MySQL Keeps Crashing”.

If a specific query kills mysqld and the tables involved were checked with CHECK TABLE before you ran the query, can you provide a reproducible test case? See Section E.1.6, “Making a Test Case If You Experience Table Corruption”.

What is the value of the wait_timeout system variable in the MySQL server? (mysqladmin variables gives you the value of this variable.)

Have you tried to run mysqld with the --log option to determine whether the problem query appears in the log?

See also See Section 10, “Communication Errors and Aborted Connections”.

See Section 1.7.1.2, “Asking Questions or Reporting Bugs”.

9. Packet too large
A communication packet is a single SQL statement sent to the MySQL server or a single row that is sent to the client.

In MySQL 5.0, the largest possible packet is 1GB.

When a MySQL client or the mysqld server receives a packet bigger than max_allowed_packet bytes, it issues a Packet too large error and closes the connection. With some clients, you may also get a Lost connection to MySQL server during query error if the communication packet is too large.

Both the client and the server have their own max_allowed_packet variable, so if you want to handle big packets, you must increase this variable both in the client and in the server.

If you are using the mysql client program, its default max_allowed_packet variable is 16MB. To set a larger value, start mysql like this:

mysql> mysql --max_allowed_packet=32M

That sets the packet size to 32MB.

The server's default max_allowed_packet value is 1MB. You can increase this if the server needs to handle big queries (for example, if you are working with big BLOB columns). For example, to set the variable to 16MB, start the server like this:

mysql> mysqld --max_allowed_packet=16M

You can also use an option file to set max_allowed_packet. For example, to set the size for the server to 16MB, add the following lines in an option file:

[mysqld]
max_allowed_packet=16M

It is safe to increase the value of this variable because the extra memory is allocated only when needed. For example, mysqld allocates more memory only when you issue a long query or when mysqld must return a large result row. The small default value of the variable is a precaution to catch incorrect packets between the client and server and also to ensure that you do not run out of memory by using large packets accidentally.

You can also get strange problems with large packets if you are using large BLOB values but have not given mysqld access to enough memory to handle the query. If you suspect this is the case, try adding ulimit -d 256000 to the beginning of the mysqld_safe script and restarting mysqld.

10. Communication Errors and Aborted Connections
The server error log can be a useful source of information about connection problems. See Section 5.11.1, “The Error Log”. If you start the server with the --log-warnings option, you might find messages like this in your error log:

010301 14:38:23  Aborted connection 854 to db: 'users' user: 'josh'

If Aborted connections messages appear in the error log, the cause can be any of the following:

The client program did not call mysql_close() before exiting.

The client had been sleeping more than wait_timeout or interactive_timeout seconds without issuing any requests to the server. See the section called “Server System Variables”.

The client program ended abruptly in the middle of a data transfer.

When any of these things happen, the server increments the Aborted_clients status variable.

The server increments the Aborted_connects status variable when the following things happen:

A client doesn't have privileges to connect to a database.

A client uses an incorrect password.

A connection packet doesn't contain the right information.

It takes more than connect_timeout seconds to get a connect packet. See the section called “Server System Variables”.

If these kinds of things happen, it might indicate that someone is trying to break into your server!

Other reasons for problems with aborted clients or aborted connections:

Use of Ethernet protocol with Linux, both half and full duplex. Many Linux Ethernet drivers have this bug. You should test for this bug by transferring a huge file via FTP between the client and server machines. If a transfer goes in burst-pause-burst-pause mode, you are experiencing a Linux duplex syndrome. The only solution is switching the duplex mode for both your network card and hub/switch to either full duplex or to half duplex and testing the results to determine the best setting.

Some problem with the thread library that causes interrupts on reads.