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 with by setting the
wait_timeout variable when
you start mysqld.
You can check that the MySQL hasn't died by executing
mysqladmin version and examining the uptime.
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):
| The client couldn't send a question to the
| 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 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
mysqld with the
-O max_allowed_packet=# option
(default 1M). The extra memory is allocated on demand, so
use more memory only when you issue a big query or when
return a big result row!
A MySQL client 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
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
Start by check (using
ps) that there is a process running named
mysqld on your server! If there isn't any
mysqld process, you
should start one. 4.15.2 Problems starting the MySQL server.
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
command; these cause the output of
hostname (i.e., the current
hostname) to be substituted into the
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,
uses the MIT-pthreads package.
4.2 Operating systems supported by MySQL.
However, MIT-pthreads doesn't support Unix sockets, so on such a system 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 (e.g., a job that removes old files from the `/tmp'
directory). 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 remove
`mysqld.sock' or to place the socket somewhere else. You can specify
a different socket location at MySQL configuration time with this
shell> ./configure --with-unix-socket-path=/path/to/socket
You can also start
safe_mysqld with the
--socket=/path/to/socket option and set the environment variable
MYSQL_UNIX_PORT to the socket pathname before starting your
MySQL clients. @item You have started the
mysqld server with
--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 setting the environment variable
MYSQL_UNIX_PORT to the socket pathname or by providing the socket path
as an argument to the clients. You can test the socket with this command:
shell> mysqladmin --socket=/path/to/socket version
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. 18.1 What to do if MySQL keeps crashing
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 is the
Check if the server up by doing
tcp-ip-port-number and press
RETURN a couple of times. If there
is a MySQL server running on this port you should get a
responses 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, then there is no server running on the
Try connecting to the
mysqld daemon on the local machine and check
the TCP/IP port that mysqld it's configured to use (variable
Check that your
mysqld server is not started with the
If you get a 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 (
of connect requests from the host
'hostname' that have been interrupted
in the middle. After
max_connect_errors failed requests,
assumes that something is wrong (like a attack from a cracker), and
blocks the site from further connections until someone executes the command
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 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
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
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
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
max_allowed_packet bytes, it issues a
Packet too large
error and closes the connection.
If you are using the
mysql client, you may specify a bigger buffer by
starting the client with
If you are using other clients that do not allow you to specify the maximum
packet size (such as
DBI), you need to set the packet size when you
start the server. You cau use a command-line option to
mysqld to set
max_allowed_packet to a larger size. For example, if you are
expecting to store the full length of a
BLOB into a table, you'll need
to start the server with the
This error occurs 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
you issue the problematic query.
You can also start
mysqld with the
This is exactly the same as using
SQL_BIG_TABLES for all queries.
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
try to execute a new query before you have called
It can also happen if you try to execute two queries that return data without
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
Possible causes of and fixes for this problem:
If you get the error
Table 'xxx' doesn't exist or
find file: 'xxx' (errno: 2), this means that no table exists
in the current database with the name
Note that as MySQL uses directories and files to store databases and
tables, the database and table names are case sensitive!
(On Win32 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