< Day Day Up > |
This section lists some errors that users frequently encounter when running MySQL 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 server. A.2.1 Access deniedAn Access denied error can have many causes. Often the problem is related to the MySQL accounts that the server allows client programs to use when connecting. See Section 4.4.8, "Causes of Access denied Errors." See Section 4.4.2, "How the Privilege System Works." A.2.2 Can't connect to [local] MySQL serverA MySQL client on Unix can connect to the mysqld server in two different ways: By using a Unix socket file to connect through a file in the filesystem (default /tmp/mysql.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 server is running on Windows 9x or Me, you can connect only via TCP/IP. If the server is running on Windows NT, 2000, or XP 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 by default. If you don't give a hostname when connecting to mysqld , a MySQL client first will try to connect to the named pipe. If that doesn't work, it will connect 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 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 mysqld running on your server host. (Use ps on Unix or the Task Manager on Windows.) If there is no such process, you should start the server. See Section 2.4.4, "Starting and Troubleshooting the MySQL Server." If a mysqld 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> mysqladmin version shell> mysqladmin variables shell> mysqladmin -h `hostname` version variables shell> mysqladmin -h `hostname` --port=3306 version shell> mysqladmin -h host_ip version shell> mysqladmin --protocol=socket --socket=/tmp/mysql.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 mysqladmin 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 server error might occur:
If you get the error message Can't connect to MySQL server on some_host , you can try the following things to find out what the problem is:
A.2.3 Client does not support authentication protocolMySQL 4.1 and up uses an authentication protocol based on a password hashing algorithm that is incompatible with that used by older clients. If you upgrade the server to 4.1, attempts to connect to it with an older client may fail with the following message: shell> mysql Client does not support authentication protocol requested by server; consider upgrading MySQL client To solve this problem, you should use one of the following approaches:
For additional background on password hashing and authentication, see Section 4.4.9, "Password Hashing in MySQL 4.1." A.2.4 Password Fails When Entered InteractivelyMySQL 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. A.2.5 Host 'host_name' is blockedIf 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 Section 4.2.3, "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 will do you no good to increase the value of the max_connect_errors variable. A.2.6 Too many connectionsIf you get a Too many connections error when you try to connect to the mysqld server, this means that all available connections already are used 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 already are connected. 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. A.2.7 Out of memoryIf 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). A.2.8 MySQL server has gone awayThis 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):
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 Section 4.2.3, "Server System Variables." If you have a script, you just have to issue the query again for the client to do an automatic reconnection. You will also get an error if someone has killed the running thread with a KILL statement or a mysqladmin kill command. Another common reason the MySQL server has gone away error occurs within an application program is that 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 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 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 A.2.9, "Packet too large." You will 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. If you want to create a bug report regarding this problem, be sure that you include the following information:
See Section 1.7.1.2, "Asking Questions or Reporting Bugs." A.2.9 Packet too largeA communication packet is a single SQL statement sent to the MySQL server or a single row that is sent to the client. In MySQL 3.23, the largest possible packet is 16MB, due to limits in the client/server protocol. In MySQL 4.0.1 and up, the limit 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. That is also the maximum value before MySQL 4.0. To set a larger value from 4.0 on, 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 Before MySQL 4.0, use this syntax instead: mysql> mysqld --set-variable=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 Before MySQL 4.0, use this syntax instead: [mysqld] set-variable = max_allowed_packet=16M It's 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 don't 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 . A.2.10 Communication Errors and Aborted ConnectionsThe server error log can be a useful source of information about connection problems. See Section 4.8.1, "The Error Log." Starting with MySQL 3.23.40, if you start the server with the --warnings option (or --log-warnings from MySQL 4.0.3 on), 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:
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:
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:
A.2.11 The table is fullThere are several ways a full-table error can occur:
A.2.12 Can't create/write to fileIf you get an error of the following type for some queries, it means that MySQL cannot create a temporary file for the result set in the temporary directory: Can't create/write to file '\sqla3fe_0.ism'. The preceding error is a typical message for Windows; the Unix message is similar. The fix is to start mysqld with the --tmpdir option or to add the option to the [mysqld] section of your option file. For example, to specify a directory of C:\temp , use these lines: [mysqld] tmpdir=C:/temp The C:\temp directory must already exist. See Section 3.3.2, "Using Option Files." Check also the error code that you get with perror . One reason the server cannot write to a table is that the filesystem is full: shell> perror 28 Error code 28: No space left on device A.2.13 Commands out of syncIf you get Commands out of sync; you can't run this command now in your client code, you are calling client functions in the wrong order. This can happen, for example, if you are using mysql_use_result() and try to execute a new query before you have called mysql_free_result() . It can also happen if you try to execute two queries that return data without calling mysql_use_result() or mysql_store_result() in between. A.2.14 Ignoring userIf you get the following error, it means that when mysqld was started or when it reloaded the grant tables, it found an account in the user table that had an invalid password. Found wrong password for user: ' some_user '@' some_host '; ignoring user As a result, the account is simply ignored by the permission system. The following list indicates possible causes of and fixes for this problem:
A.2.15 Table 'tbl_name' doesn't existIf you get either of the following errors, it usually means that no table exists in the current database with the given name: Table ' tbl_name ' doesn't exist Can't find file: ' tbl_name ' (errno: 2) In some cases, it may be that the table does exist but that you are referring to it incorrectly:
You can check which tables are in the current database with SHOW TABLES . A.2.16 Can't initialize character setYou might see an error like this if you have character set problems: MySQL Connection Failed: Can't initialize character set charset_name This error can have any of the following causes:
A.2.17 File Not FoundIf you get ERROR '...' not found (errno: 23) , Can't open file: ... (errno: 24) , or any other error with errno 23 or errno 24 from MySQL, it means that you haven't allocated enough file descriptors for the MySQL server. You can use the perror utility to get a description of what the error number means: shell> perror 23 File table overflow shell> perror 24 Too many open files shell> perror 11 Resource temporarily unavailable The problem here is that mysqld is trying to keep open too many files simultaneously . You can either tell mysqld not to open so many files at once or increase the number of file descriptors available to mysqld . To tell mysqld to keep open fewer files at a time, you can make the table cache smaller by reducing the value of the table_cache system variable (the default value is 64). Reducing the value of max_connections also will reduce the number of open files (the default value is 100). To change the number of file descriptors available to mysqld , you can use the --open-files-limit option to mysqld_safe or (as of MySQL 3.23.30) set the open_files_limit system variable. See Section 4.2.3, "Server System Variables." The easiest way to set these values is to add an option to your option file. See Section 3.3.2, "Using Option Files." If you have an old version of mysqld that doesn't support setting the open files limit, you can edit the mysqld_safe script. There is a commented-out line ulimit -n 256 in the script. You can remove the ' # ' character to uncomment this line, and change the number 256 to set the number of file descriptors to be made available to mysqld . --open-files-limit and ulimit can increase the number of file descriptors, but only up to the limit imposed by the operating system. There is also a "hard" limit that can be overridden only if you start mysqld_safe or mysqld as root (just remember that you also need to start the server with the --user option in this case so that it does not continue to run as root after it starts up). If you need to increase the operating system limit on the number of file descriptors available to each process, consult the documentation for your system. Note: If you run the tcsh shell, ulimit will not work! tcsh will also report incorrect values when you ask for the current limits. In this case, you should start mysqld_safe using sh . |
< Day Day Up > |