18.2 Some common errors when using MySQL

18.2.1 MySQL server has gone away error

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):

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!

18.2.2 Can't connect to [local] MySQL server error

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 hostname localhost.

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 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.

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 (i.e., 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. 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 command:

  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 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 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 problem:

• Check if the server up by doing telnet your-host-name 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 used port.
• Try connecting to the mysqld daemon on the local machine and check the TCP/IP port that mysqld it's configured to use (variable port) with mysqladmin variables.
• Check that your mysqld server is not started with the --skip-networking option.

18.2.3 Host '...' is blocked error

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 (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 a 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 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!

18.2.4 Out of memory error

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).

18.2.5 Packet too large error

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.

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.

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 --set-variable=max_allowed_packet=24M option.

18.2.6 The table is full error

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 SQL_BIG_TABLES before you issue the problematic query. SET OPTION.

You can also start mysqld with the --big-tables option. This is exactly the same as using SQL_BIG_TABLES for all queries.

18.2.7 Commands out of sync error in client

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.

18.2.8 Ignoring user error

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 chararacters 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';
  

18.2.9 Table 'xxx' doesn't exist error

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 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 SHOW TABLES. SHOW.