Common Errors When Using MySQL
==============================
This section lists some errors that users frequently get. You will find
descriptions of the errors, and how to solve the problem here.
`Access denied' Error
---------------------
*Note Access denied::. *Note Privileges::.
`MySQL server has gone away' Error
----------------------------------
This section also covers the related `Lost connection to server during
query' error.
You
can change the time limit by setting the `wait_timeout' variable when
you start `mysqld'.
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 the 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. *Note Crashing::.
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
allocate more memory only when you issue a big query or when `mysqld'
must return a big result row!
You will also get a lost connection if you are sending a packet >= 16M
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 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'. *Note Crashing::.
* 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 ?
*Note Asking questions::.
`Can't connect to [local] MySQL server' Error
---------------------------------------------
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'.
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. *Note Starting
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 --protocol=socket --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. *Note Which OS::. 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
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. *Note
Problems with mysql.sock::.
* 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. *Note Problems with mysql.sock::.
* 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. *Note Crashing::.
* You may not have read and write privilege to either the directory
that holds the socket file or privilege to the socket file itself.
In this case you have to 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 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 given
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.
`Client does not support authentication protocol' error
-------------------------------------------------------
MySQL 4.1 uses an authentication protocal 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 a 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 do one of the following:
* Upgrade all client programs to use the 4.1.1 or newer client
library.
* Use a user account with an old password when connecting from a pre
4.1 client.
* Reset the user that needs a pre-4.1 client to use an old password:
mysql> UPDATE user SET Password = OLD_PASSWORD('mypass')
-> WHERE Host = 'some_host' AND User = 'some_user';
mysql> FLUSH PRIVILEGES;
* Tell the server to use the older password hashing algorithm:
1. Start mysqld with `--old-passwords'.
2. Set the password for all users that has a long password. You
can find these users with:
SELECT * FROM mysql.user WHERE LEN(password) > 16;
For background on password hashing and authentication, see *Note
Password hashing::.
`Host '...' is blocked' Error
-----------------------------
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> mysqld_safe -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!
`Too many connections' Error
----------------------------
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), then 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 `SUPER'
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. *Note SHOW
PROCESSLIST::.
The maximum number of connects MySQL is depending 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.
`Some non-transactional changed tables couldn't be rolled back' Error
---------------------------------------------------------------------
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.
The most typical case when this happens is 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 is most resembles to the one you requested, probably
`MyISAM'.
You can check the table type for a table by doing:
`SHOW TABLE STATUS LIKE 'table_name''. *Note SHOW TABLE STATUS::.
You can check the extensions your `mysqld' binary supports by doing:
`show variables like 'have_%''. *Note SHOW VARIABLES::.
`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.
result
set. This places less of a load on the client (but more on the server).
`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.
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.
connection to MySQL server during query' error if the communication
packet is too big.
Note that both the client and the server has it's 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 a precaution to catch wrong packets
between the client/server and also to ensure that you don't accidentally
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. Please note
that `--set-variable' is deprecated since MySQL 4.0, just use
`--max-allowed-packet=8M' instead.
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 `mysqld_safe' script and restart
`mysqld'.
Communication Errors / Aborted Connection
-----------------------------------------
Starting with `MySQL 3.23.40' you only get the `Aborted connection'
error of you start `mysqld' with `--warnings'.
If you find errors like the following in your error log.
010301 14:38:23 Aborted connection 854 to db: 'users' user: 'josh'
*Note Error log::.
This means that something of the following has happened:
* The client program did not call `mysql_close()' before exit.
VARIABLES.
* The client program ended abruptly in the middle of the transfer.
When the above happens, the server variable `Aborted_clients' is
incremented.
The server variable `Aborted_connects' is incremented when:
* 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 `connect_timeout': SHOW VARIABLES.
Note that the above could indicate that someone is trying to break into
your database!
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 then you are
experiencing a Linux duplex syndrome. The only solution is
switching duplex mode for both your network card and Hub/Switch to
either full duplex or to half duplex and testing the results to
decide on the best setting.
* Some problem with the thread library that causes interrupts on
reads.
* Badly configured TCP/IP.
* Faulty Ethernets or hubs or 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'. *Note Packet too large::.
`The table is full' Error
-------------------------
There are a couple of different cases when you can get this error:
option to make `mysqld' increase the temporary
table size or use the SQL option `BIG_TABLES' before you issue the
problematic query. *Note `SET': SET OPTION.
You can also start `mysqld' with the `--big-tables' option. This
is exactly the same as using `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'.
* You are using `InnoDB' tables and run out of room in the `InnoDB'
tablespace. In this case the solution is to extend the `InnoDB'
tablespace.
* You are using `ISAM' or `MyISAM' tables on an OS that only
supports files of 2G in size and you have hit this limit for the
datafile or index file.
* You are using `MyISAM' tables and the needed data or index size is
bigger than what MySQL has allocated pointers for. (If you don't
specify `MAX_ROWS' to `CREATE TABLE' MySQL will only allocate
pointers to hold 4G of data).
You can check the maximum data/index sizes by doing
SHOW TABLE STATUS FROM database LIKE 'table_name';
or using `myisamchk -dv database/table_name'.
`BLOB/TEXT' fields as in this case MySQL can't optimise the space
required based only on the number of rows.
`Can't create/write to file' Error
----------------------------------
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. (The above 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. *Note Option files::.
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
`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.
`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 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'.
function:
mysql> UPDATE user SET password=PASSWORD('your password')
-> WHERE user='XXX';
`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'.
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'. *Note `SHOW': SHOW.
`Can't initialize character set xxx' error
------------------------------------------
If you get an error like:
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'. *Note
configure options::.
All standard MySQL binaries are compiled with
`--with-extra-character-sets=complex' which will enable support for
all multi-byte character sets. *Note Character sets::.
* The character set is a simple character set which 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.
*Note configure options::.
* 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.
File Not Found
--------------
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
`mysqld_safe' (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 `mysqld_safe' or `-O
open-files-limit=#' to `mysqld'. *Note `open_files_limit': SHOW
VARIABLES. The easiest way to do that is to add the option to your
option file. *Note Option files::. If you have an old `mysqld'
version that doesn't support this, 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 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 overridden if you start
`mysqld_safe' 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 `mysqld_safe' with `sh'!
[Назад] [Содержание] [Вперед]
| Главная |