|
Some 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.
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.
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):
|
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 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!
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 you can connect only with TCP/IP if the
mysqld server is running
on Win95/Win98. If it's running on NT, 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.
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. However, all MIT-pthreads versions doesn't 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 ´/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 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.
- 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 serverYou 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
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 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.
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 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), 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
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. 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.
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'.
You can check the extensions your
mysqld binary supports
by doing:
show variables like
'have_%'.
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.
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=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
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'
This means that something 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.
- 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 that the above could indicate that
someone is trying to break into your database!
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 then you are experiencing a Linux duplex
syndrome. The only solution to this problem is switching of 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 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 alloacated for
mysqld.
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 queryYou 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. (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.
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
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 not 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.
The character set is a simple character set which is not compiled into
mysqld and the character
set definition files is not in the place where the client expect to find them.
In this case you need to:
- .
- Specify to the client where the character set definition files are. For many
client 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 expect 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 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!
|
