This section lists some errors that users frequently get. You will find descriptions of the errors and how to solve the problems here.
See Section 4.2.6, and especially, see Section 4.2.11.
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. By default, the server closes the connection after 8 hours if nothing has happened. You can change the time limit by setting the wait_timeout variable when you start mysqld.
Another common reason to receive the MySQL server has gone away error is because you have issued a “close” on your MySQL connection and then tried to run a query on the closed connection.
If you have a script, you just have to issue the query again for the client to do an automatic reconnection.
You normally can get the following error codes in this case (which one you get is OS-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. |
You will also get this error if someone has kills the running thread with kill #threadid#.
You can check that MySQL hasn’t died by executing mysqladmin version and examining the uptime. If the problem is that mysqld crashed you should concentrate one finding the reason for the crash. You should in this case start by checking if issuing the query again will kill MySQL again. See Section A.4.1.
You can also get these errors if you send a query to the server that is incorrect or too large. If mysqld gets 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 starting mysqld with the -O max_allowed_packet=# option (default 1M). The extra memory is allocated on demand, so mysqld will use more memory only when you issue a big query or when mysqld must return a big result row!
If you want to make a bug report regarding this problem, be sure that you include the following information:
Include information if MySQL died or not. (You can find this in the hostname.err file. See Section A.4.1.
If a specific query kills mysqld and the involved tables where checked with CHECK TABLE before you did the query, can you do a test case for this? Section D.1.6.
What is the value of the wait_timeout variable in the MySQL server ? mysqladmin variables gives you the value of this
Have you tried to run mysqld with --log and check if the issued query appears in the log ?
A MySQL client on Unix can connect to the mysqld server in two
different ways: Unix sockets, which connect through a file in the file
system (default /tmp/mysqld.sock) or TCP/IP, which connects
through a port number. Unix sockets are faster than TCP/IP but can only
be used when connecting to a server on the same computer. Unix sockets
are used if you don’t specify a hostname or if you specify the special
hostname localhost.
On Windows, if the mysqld server is running on 9x/Me, you can connect only via TCP/IP. If the server is running on NT/2000/XP and mysqld is started with --enable-named-pipe, you can also connect with named pipes. The name of the named pipe is MySQL. If you don’t give a hostname when connecting to mysqld, a MySQL client will first try to connect to the named pipe, and if this 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 isn’t a MySQL server running on the system or that you are using a wrong socket file or TCP/IP port when trying to connect to the mysqld server.
Start by checking (using ps or the task manager on Windows) that there is a process running named mysqld on your server! If there isn’t any mysqld process, you should start one. See Section 2.4.2.
If a mysqld process is running, you can check the server by trying these different connections (the port number and socket pathname might be different in your setup, of course):
shell> mysqladmin version shell> mysqladmin variables shell> mysqladmin -h `hostname` version variables shell> mysqladmin -h `hostname` --port=3306 version shell> mysqladmin -h 'ip for your host' version shell> mysqladmin --socket=/tmp/mysql.sock version
Note the use of backquotes 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.
Here are some reasons the Can't connect to local MySQL server error might occur:
mysqld is not running.
You are running on a system that uses MIT-pthreads. If you are running on a system that doesn’t have native threads, mysqld uses the MIT-pthreads package. See Section 2.2.2. However, not all MIT-pthreads versions support Unix sockets. On a system without sockets 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> mysqladmin -h `hostname` version
Someone has removed the Unix socket that mysqld uses (default
/tmp/mysqld.sock). You might have a cron job that removes the MySQL socket (for example, a job that removes old files from the/tmpdirectory). You can always run mysqladmin version and check that the socket mysqladmin is trying to use really exists. The fix in this case is to change the cron job to not removemysqld.sockor to place the socket somewhere else. See Section A.4.5.You have started the mysqld server with the --socket=/path/to/socket option. If you change the socket pathname for the server, you must also notify the MySQL clients about the new path. You can do this by providing the socket path as an argument to the client. See Section A.4.5.
You are using Linux and one thread has died (core-dumped). In this case you must kill the other mysqld threads (for example, with the mysql_zap script) before you can start a new MySQL server. See Section A.4.1.
You may not have read and write privilege to either the directory that holds the socket file or to the socket file itself. In this case you either change the privilege for the directory / file or restart mysqld so that it uses a directory that you can access.
If you get the error message Can't connect to MySQL server on some_hostname, you can try the following things to find out what the problem is:
Check if the server is up by doing telnet your-host-name tcp-ip-port-number and press Enter a couple of times. If there is a MySQL server running on this port you should get a response that includes the version number of the running MySQL server. If you get an error like telnet: Unable to connect to remote host: Connection refused, there is no server running on the given port.
Try connecting to the mysqld daemon on the local machine and check the TCP/IP port that mysqld is configured to use (variable port) with mysqladmin variables.
Check that your mysqld server is not started with the --skip-networking option.
If you get an error like this:
Host 'hostname' is blocked because of many connection errors. Unblock with 'mysqladmin flush-hosts'
this means that mysqld has gotten a lot (max_connect_errors) of connect requests from the host 'hostname' that have been interrupted in the middle. After max_connect_errors failed requests, mysqld assumes that something is wrong (like an attack from a cracker), and blocks the site from further connections until someone executes the command mysqladmin flush-hosts.
By default, mysqld blocks a host after 10 connection errors. You can easily adjust this by starting the server like this:
shell> safe_mysqld -O max_connect_errors=10000 &
Note that if you get this error message for a given host, you should first check that there isn’t anything wrong with the TCP/IP connections from that host. If your TCP/IP connections aren’t working, it won’t do you any good to increase the value of the max_connect_errors variable!
If you get the error Too many connections when you try to connect to MySQL, this means that there is already max_connections clients connected to the mysqld server.
If you need more connections than the default (100), you should restart mysqld with a bigger value for the max_connections variable.
Note that mysqld actually allows (max_connections+1) clients to connect. The last connection is reserved for a user with the process privilege. By not giving this privilege to normal users (they shouldn’t need this), an administrator with this privilege can log in and use SHOW PROCESSLIST to find out what could be wrong. See Section 4.5.6.
The maximum number of connects allowable to MySQL depends on how good the thread library is 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.
If you get the error/warning: Warning: Some non-transactional changed tables couldn't be rolled back when trying to do a ROLLBACK, this means that some of the tables you used in the transaction didn’t support transactions. These non-transactional tables will not be affected by the ROLLBACK statement.
Typically, this happens when you have tried to create a table of a type that is not supported by your mysqld binary. If mysqld doesn’t support a table type (or if the table type is disabled by a startup option), it will instead create the table type with the table type that most resembles the one you requested, probably MyISAM.
You can check the table type for a table by doing:
SHOW TABLE STATUS LIKE 'table_name'. See Section 4.5.6.2.
You can check the extensions your mysqld binary supports by doing:
show variables like 'have_%'. See Section 4.5.6.4.
If you issue a query and get something like the following error:
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
note that the error refers to the MySQL client mysql. The reason for this error is simply that the client does not have enough memory to store the whole result.
To remedy the problem, first check that your query is correct. Is it reasonable that it should return so many rows? If so, you can use mysql --quick, which uses mysql_use_result( ) to retrieve the result set. This places less of a load on the client (but more on the server).
When a MySQL client or the mysqld server gets a packet bigger than max_allowed_packet bytes, it issues a Packet too large error and closes the connection.
In MySQL 3.23 the biggest possible packet is 16M (due to limits in the client/server protocol). In MySQL 4.0.1 and up, this is only limited by the amount of memory you have on your server (up to a theoretical maximum of 2G).
A communication packet is a single SQL statement sent to the MySQL server or a single row that is sent to the client.
When a MySQL client or the mysqld server gets 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 Lost connection to MySQL server during query error if the communication packet is too big.
Note that both the client and the server have their own max_allowed_packet variable. If you want to handle big packets, you have to increase this variable both in the client and in the server.
It’s safe to increase this variable as memory is only allocated when needed; this variable is more of a precaution to catch wrong packets between the client/server and also to ensure that you don’t accidently use big packets so that you run out of memory.
If you are using the mysql client, you may specify a bigger buffer by starting the client with mysql --set-variable=max_allowed_packet=8M. Other clients have different methods to set this variable.
You can use the option file to set max_allowed_packet to a larger size in mysqld. For example, if you are expecting to store the full length of a MEDIUMBLOB into a table, you’ll need to start the server with the set-variable=max_allowed_packet=16M option.
You can also get strange problems with large packets if you are using big blobs, but you haven’t 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 safe_mysqld script and restart mysqld.
Starting with MySQL 3.23.40 you only get the Aborted connection error if you start mysqld with --warnings.
If you find errors like the following in your error log (see Section 4.9.1):
010301 14:38:23 Aborted connection 854 to db: 'users' user: 'josh'
this means that one of the following has happened:
The client program did not call mysql_close( ) before exit.
The client had been sleeping more than wait_timeout or interactive_timeout without doing any requests. See Section 4.5.6.4.
The client program ended abruptly in the middle of the transfer.
When this happens, the server variable Aborted_clients is incremented.
The server variable Aborted_connects is incremented:
When a connection packet doesn’t contain the right information
When the user didn’t have privileges to connect to a database
When a user uses a wrong password
When it takes more than connect_timeout seconds to get a connect package
Note that this could indicate that someone is trying to break into your database!
See Section 4.5.6.4.
Other reasons for problems with aborted clients/aborted connections.
Usage of duplex Ethernet protocol, both half and full with Linux. Many Linux Ethernet drivers have this bug. You should test for this bug by transferring a huge file via ftp between these two machines. If a transfer goes in burst-pause-burst-pause ... mode, you are experiencing a Linux duplex syndrome. The only solution to this problem is switching both half and full duplexing on hubs and switches.
Some problem with the thread library that causes interrupts on reads.
Badly configured TCP/IP.
Faulty Ethernets, hubs, switches, cables... This can be diagnosed properly only by replacing hardware.
max_allowed_packet is too small or queries require more memory than you have allocated for mysqld. See Section A.2.8.
This error occurs in older MySQL versions when an in-memory temporary table becomes larger than tmp_table_size bytes. To avoid this problem, you can use the -O tmp_table_size=# option to mysqld to increase the temporary table size or use the SQL option SQL_BIG_TABLES before you issue the problematic query. See Section 5.5.6.
You can also start mysqld with the --big-tables option. This is exactly the same as using SQL_BIG_TABLES for all queries.
In MySQL Version 3.23, in-memory temporary tables will automatically be converted to a disk-based MyISAM table after the table size gets bigger than tmp_table_size.
If you get an error for some queries of type:
Can't create/write to file '\\sqla3fe_0.ism'.
this means that MySQL can’t create a temporary file for the result set in the given temporary directory. (This error is a typical error message on Windows, and the Unix error message is similar.) The fix is to start mysqld with --tmpdir=path or to add to your option file:
[mysqld] tmpdir=C:/temp
assuming that the c:\\temp directory exists. See Section 4.1.2.
Check also the error code that you get with perror. One reason may also be a disk-full error:
shell> perror 28 Error code 28: No space left on device
If 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 a mysql_use_result( ) or mysql_store_result( ) in between.
If you get the following error:
Found wrong password for user: 'some_user@some_host'; ignoring user
this means that when mysqld was started or when it reloaded the permissions tables, it found an entry in the user table with an invalid password. As a result, the entry is simply ignored by the permission system.
Possible causes of and fixes for this problem:
You may be running a new version of mysqld with an old user table. You can check this by executing mysqlshow mysql user to see if the password field is shorter than 16 characters. If so, you can correct this condition by running the scripts/add_long_password script.
The user has an old password (8 characters long) and you didn’t start mysqld with the --old-protocol option. Update the user in the user table with a new password or restart mysqld with --old-protocol.
You have specified a password in the user table without using the PASSWORD( ) function. Use mysql to update the user in the user table with a new password. Make sure to use the PASSWORD( ) function:
mysql> UPDATE user SET password=PASSWORD('your password') -> WHERE user='XXX';
If you get the error Table 'xxx' doesn't exist or Can't find file: 'xxx' (errno: 2), this means that no table exists in the current database with the name xxx.
Note that as MySQL uses directories and files to store databases and tables, the database and table names are case-sensitive! (On Windows the databases and tables names are not case-sensitive, but all references to a given table within a query must use the same case!)
You can check which tables you have in the current database with SHOW TABLES. See Section 4.5.6.
MySQL Connection Failed: Can't initialize character set xxx
this means one of the following things:
The character set is a multi-byte character set and you have no support for the character set in the client.
In this case you need to recompile the client with --with-charset=xxx or with --with-extra-charsets=xxx. See Section 2.3.3.
All standard MySQL binaries are compiled with --with-extra-character-sets=complex, which will enable support for all multi-byte character sets. See Section 4.6.1.
The character set is a simple character set that is not compiled into mysqld and the character set definition files are not in the place where the client expects to find them.
In this case you need to:
Recompile the client with support for the character set. See Section 2.3.3.
Specify to the client where the character set definition files are. For many clients you can do this with the --character-sets-dir=path-to-charset-dir option.
Copy the character definition files to the path where the client expects them to be.
If 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 MySQL. 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 using the -O table_cache=32 option to safe_mysqld (the default value is 64). Reducing the value of max_connections will also reduce the number of open files (the default value is 90).
To change the number of file descriptors available to mysqld, you can use the option --open-files-limit=# to safe_mysqld or -O open-files-limit=# to mysqld. See Section 4.5.6.4. The easiest way to do that is to add the option to your option file. See Section 4.1.2. If you have an old mysqld version that doesn’t support this, you can edit the safe_mysqld 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 affect the number of file descriptors available to mysqld.
ulimit (and open-files-limit) 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 only be overrided if you start safe_mysqld or mysqld as root (just remember that you need to also use the --user=... option in this case). If you need to increase the OS limit on the number of file descriptors available to each process, consult the documentation for your operating system.
Note that 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 safe_mysqld with sh!
Get MySQL Reference Manual now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
