 |
 |
|
|
| [ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top] | [Contents] | [Index] | [ ? ] |
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 See section Causes of Access denied Errors.
See section How the Privilege System Works.
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 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 |
| The client couldn't send a question to the server. |
| 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. See section 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 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:
hostname.err file. See section What To Do If MySQL Keeps Crashing.
mysqld and the involved tables where
checked with CHECK TABLE before you did the query, can you do
a test case for this? See section Making a Test Case If You Experience Table Corruption.
wait_timeout variable in the MySQL server ?
mysqladmin variables gives you the value of this
mysqld with --log and check if the
issued query appears in the log ?
See section Asking Questions or Reporting Bugs.
Can't connect to [local] MySQL server Error 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 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 --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.
mysqld uses the MIT-pthreads package. See section Operating Systems Supported by MySQL. 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 |
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 `/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.
See section How to Protect or Change the MySQL Socket File `/tmp/mysql.sock'.
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 How to Protect or Change the MySQL Socket File `/tmp/mysql.sock'.
mysqld threads (for example, with the
mysql_zap script before you can start a new MySQL
server. See section What To Do If MySQL Keeps Crashing.
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 :
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.
mysqld daemon on the local machine and check
the TCP/IP port that mysqld it's configured to use (variable port) with
mysqladmin variables.
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 need to either tell the server to use the older password hashing algorithm, or upgrade the client programs to use the 4.1 client library. For background on password hashing and authentication, see Password Hashing in MySQL 4.1.
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. See section 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'. See section SHOW TABLE STATUS.
You can check the extensions your mysqld binary supports by doing:
show variables like 'have_%'. See section 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.
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).
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.
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 on 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 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.
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' |
See section The Error Log.
This means that something of the following has happened:
mysql_close() before exit.
wait_timeout or
interactive_timeout without doing any requests.
See section wait_timeout.
See section interactive_timeout.
When the above happens, the server variable Aborted_clients is
incremented.
The server variable Aborted_connects is incremented when:
connect_timeout seconds to get
a connect package.
See section connect_timeout.
Note that the above could indicate that someone is trying to break into your database!
Other reasons for problems with Aborted clients / Aborted connections.
max_allowed_packet is too small or queries require more memory
than you have allocated for mysqld. See section Packet too large Error.
The table is full Error There are a couple of different cases when you can get this error:
tmp_table_size bytes.
To avoid this problem, you can use the -O tmp_table_size=# option
to make mysqld increase the temporary table size or use the SQL
option BIG_TABLES before you issue the problematic query.
See section SET.
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.
InnoDB tables and run out of room in the
InnoDB tablespace. In this case the solution is to extend the
InnoDB tablespace.
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.
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.
If this is the problem, you can fix it by doing something like:
ALTER TABLE table_name MAX_ROWS=1000000000 AVG_ROW_LENGTH=nnn; |
You only have to specify AVG_ROW_LENGTH for tables with
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. See section `my.cnf' 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:
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.
mysqld with the --old-protocol option.
Update the user in the user table with a new password or
restart mysqld with --old-protocol.
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';
|
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 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 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:
In this case you need to recompile the client with
--with-charset=xxx or with --with-extra-charsets=xxx.
See section Typical configure Options.
All standard MySQL binaries are compiled with
--with-extra-character-sets=complex which will enable support for
all multi-byte character sets. See section The Character Set Used for Data and Sorting.
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:
configure Options.
--character-sets-dir=path-to-charset-dir option.
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.
See section open_files_limit.
The easiest way to do that is to add the option to your option file.
See section `my.cnf' 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!
| [ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] |
Hosting by: Hurra Communications Ltd.
Generated: 2007-01-26 17:58:44