|
|
This chapter lists some common problems and error messages that users have run into. You will learn how to figure out what the problem is, and what to do to solve it. You will also find proper solutions to some common problems.
When you run into problems, the first thing you should do is to find out which program / piece of equipment is causing problems:
kbd_mode -a
on it.
top
, ps
, taskmanager
, or some similar program,
to check which program is taking all CPU or is locking the machine.
top
, df
, or a similar program if you are out of
memory, disk space, open files, or some other critical resource.
If after you have examined all other possibilities and you have concluded that it's the MySQL server or a MySQL client that is causing the problem, it's time to do a bug report for our mailing list or our support team. In the bug report, try to give a very detailed description of how the system is behaving and what you think is happening. You should also state why you think it's MySQL that is causing the problems. Take into consideration all the situations in this chapter. State any problems exactly how they appear when you examine your system. Use the 'cut and paste' method for any output and/or error messages from programs and/or log files!
Try to describe in detail which program is not working and all symptoms you see! We have in the past received many bug reports that just state "the system doesn't work". This doesn't provide us with any information about what could be the problem.
If a program fails, it's always useful to know:
top
. Let the
program run for a while, it may be evaluating something heavy.
mysqld
server that is causing problems, can you
do mysqladmin -u root ping
or mysqladmin -u root processlist
?
mysql
, for example)
when you try to connect to the MySQL server?
Does the client jam? Do you get any output from the program?
When sending a bug report, you should of follow the outlines described in this manual. See section 1.6.2.2 Asking Questions or Reporting Bugs.
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 4.2.6 How the Privilege System Works, and especially. See section 4.2.11 Causes of Access denied
Errors.
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 |
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. See section A.4.1 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
use more memory only when you issue a big query or when mysqld
must
return a big result row!
If you want to make a bug report regarding this problem, be sure that you include the following information:
hostname.err file
. See section A.4.1 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 E.1.6 Making a Test Case When 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 1.6.2.2 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 2.4.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
(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 2.2.2 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 A.4.5 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 A.4.5 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 A.4.1 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.
Host '...' is blocked
ErrorIf 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!
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 4.5.6 SHOW
Syntax.
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 4.5.6.2 SHOW TABLE STATUS
.
You can check the extensions your mysqld
binary supports by doing:
show variables like 'have_%'
. See section 4.5.6.4 SHOW VARIABLES
.
Out of memory
ErrorIf 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.
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 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'
See section 4.9.1 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 4.5.6.4 SHOW VARIABLES
.
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.
Note that the above could indicate that someone is trying to break into your database!
See section 4.5.6.4 SHOW VARIABLES
.
Other reasons for problems with Aborted clients / Aborted connections.
max_allowed_packet
is too small or queries require more memory
than you have alloacated for mysqld
. See section A.2.8 Packet too large
Error.
The table is full
ErrorThere is 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 5.5.6 SET
Syntax.
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 data
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
ErrorIf 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 4.1.2 `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
ErrorIf 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 4.5.6 SHOW
Syntax.
Can't initialize character set xxx
errorIf you get an error like:
MySQL Connection Failed: Can't initialize character set xxx
This means one of the following things:
--with-charset=utf-8
or with --with-extra-charsets=xxx
.
See section 2.3.3 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 4.6.1 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
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
. See section 4.5.6.4 SHOW VARIABLES
.
The easiest way to do that is to add the option to your option file.
See section 4.1.2 `my.cnf' Option Files. 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
!
If you are linking your program and you get errors for unreferenced
symbols that start with mysql_
, like the following:
/tmp/ccFKsdPa.o: In function `main': /tmp/ccFKsdPa.o(.text+0xb): undefined reference to `mysql_init' /tmp/ccFKsdPa.o(.text+0x31): undefined reference to `mysql_real_connect' /tmp/ccFKsdPa.o(.text+0x57): undefined reference to `mysql_real_connect' /tmp/ccFKsdPa.o(.text+0x69): undefined reference to `mysql_error' /tmp/ccFKsdPa.o(.text+0x9a): undefined reference to `mysql_close'
you should be able to solve this by adding -Lpath-to-the-mysql-library
-lmysqlclient
last on your link line.
If you get undefined reference
errors for the uncompress
or compress
function, add -lz
last on your link
line and try again!
If you get undefined reference
errors for functions that should
exist on your system, like connect
, check the man page for the
function in question, for which libraries you should add to the link
line!
If you get undefined reference
errors for functions that don't
exist on your system, like the following:
mf_format.o(.text+0x201): undefined reference to `__lxstat'
it usually means that your library is compiled on a system that is not 100% compatible with yours. In this case you should download the latest MySQL source distribution and compile this yourself. See section 2.3 Installing a MySQL Source Distribution.
If you are trying to run a program and you then get errors for
unreferenced symbols that start with mysql_
or that the
mysqlclient
library can't be found, this means that your system
can't find the share `libmysqlclient.so' library.
The fix for this is to tell your system to search after shared libraries where the library is located by one of the following methods:
LD_LIBRARY_PATH
environment variable.
LD_LIBRARY
environment variable.
ldconfig
.
Another way to solve this problem is to link your program statically, with
-static
, or by removing the dynamic MySQL libraries
before linking your code. In the second case you should be
sure that no other programs are using the dynamic libraries!
The MySQL server mysqld
can be started and run by any user.
In order to change mysqld
to run as a Unix user user_name
, you must
do the following:
mysqladmin shutdown
).
user_name
has
privileges to read and write files in them (you may need to do this as
the Unix root
user):
shell> chown -R user_name /path/to/mysql/datadirIf directories or files within the MySQL data directory are symlinks, you'll also need to follow those links and change the directories and files they point to.
chown -R
may not follow symlinks for
you.
user_name
, or, if you are using
MySQL Version 3.22 or later, start mysqld
as the Unix root
user and use the --user=user_name
option. mysqld
will switch
to run as the Unix user user_name
before accepting any connections.
user
line that specifies the user name to
the [mysqld]
group of the `/etc/my.cnf' option file or the
`my.cnf' option file in the server's data directory. For example:
[mysqld] user=user_name
At this point, your mysqld
process should be running fine and dandy as
the Unix user user_name
. One thing hasn't changed, though: the
contents of the permissions tables. By default (right after running the
permissions table install script mysql_install_db
), the MySQL
user root
is the only user with permission to access the mysql
database or to create or drop databases. Unless you have changed those
permissions, they still hold. This shouldn't stop you from accessing
MySQL as the MySQL root
user when you're logged in
as a Unix user other than root
; just specify the -u root
option
to the client program.
Note that accessing MySQL as root
, by supplying -u
root
on the command-line, has nothing to do with MySQL running
as the Unix root
user, or, indeed, as another Unix user. The access
permissions and user names of MySQL are completely separate from
Unix user names. The only connection with Unix user names is that if you
don't provide a -u
option when you invoke a client program, the client
will try to connect using your Unix login name as your MySQL user
name.
If your Unix box itself isn't secured, you should probably at least put a
password on the MySQL root
users in the access tables.
Otherwise, any user with an account on that machine can run mysql -u
root db_name
and do whatever he likes.
If you have problems with file permissions, for example, if mysql
issues the following error message when you create a table:
ERROR: Can't find file: 'path/with/filename.frm' (Errcode: 13)
then the environment variable UMASK
might be set incorrectly when
mysqld
starts up. The default umask value is 0660
. You can
change this behaviour by starting safe_mysqld
as follows:
shell> UMASK=384 # = 600 in octal shell> export UMASK shell> /path/to/safe_mysqld &
By default MySQL will create database and RAID
directories with permission type 0700. You can modify this behaviour by
setting the UMASK_DIR
variable. If you set this, new
directories are created with the combined UMASK
and
UMASK_DIR
. For example, if you want to give group access to
all new directories, you can do:
shell> UMASK_DIR=504 # = 770 in octal shell> export UMASK_DIR shell> /path/to/safe_mysqld &
In MySQL Version 3.23.25 and above, MySQL assumes that the
value for UMASK
and UMASK_DIR
is in octal if it starts
with a zero.
See section F Environment Variables.
All MySQL versions are tested on many platforms before they are released. This doesn't mean that there aren't any bugs in MySQL, but it means if there are bugs, they are very few and can be hard to find. If you have a problem, it will always help if you try to find out exactly what crashes your system, as you will have a much better chance of getting this fixed quickly.
First, you should try to find out whether the problem is that the
mysqld
daemon dies or whether your problem has to do with your
client. You can check how long your mysqld
server has been up by
executing mysqladmin version
. If mysqld
has died, you may
find the reason for this in the file
`mysql-data-directory/`hostname`.err'. See section 4.9.1 The Error Log.
Many crashes of MySQL are caused by corrupted index / data
files. MySQL will update the data on disk, with the
write()
system call, after every SQL statement and before the
client is notified about the result. (This is not true if you are running
with delay_key_write
, in which case only the data is written.)
This means that the data is safe even if mysqld
crashes, as the OS will
ensure that the not flushed data is written to disk. You can force
MySQL to sync everything to disk after every SQL command by
starting mysqld
with --flush
.
The above means that normally you shouldn't get corrupted tables unless:
mysqld
or the machine in the middle
of an update.
mysqld
that caused it to die in the
middle of an update.
mysqld
servers on the same data on a
system that doesn't support good filesystem locks (normally handled by
the lockd
daemon ) or if you are running
multiple servers with --skip-external-locking
mysqld
confused.
ALTER TABLE
on a
repaired copy of the table!
Because it is very difficult to know why something is crashing, first try to check whether things that work for others crash for you. Please try the following things:
mysqld
daemon with mysqladmin shutdown
, run
myisamchk --silent --force */*.MYI
on all tables, and restart the
mysqld
daemon. This will ensure that you are running from a clean
state. See section 4 Database Administration.
mysqld --log
and try to determine from the information in the log
whether some specific query kills the server. About 95% of all bugs are
related to a particular query! Normally this is one of the last queries in
the log file just before MySQL restarted. See section 4.9.2 The General Query Log.
If you can repeatadly kill MySQL with one of the queries, even
when you have checked all tables just before doing the query, then you
have been able to locate the bug and should do a bug report for this!
See section 1.6.2.3 How to Report Bugs or Problems.
fork_test.pl
and fork2_test.pl
.
--with-debug
option or
--with-debug=full
to configure
and then recompile.
See section E.1 Debugging a MySQL server.
--skip-external-locking
option to mysqld
. On some
systems, the lockd
lock manager does not work properly; the
--skip-external-locking
option tells mysqld
not to use external
locking. (This means that you cannot run 2 mysqld
servers on the same
data and that you must be careful if you use myisamchk
, but it may be
instructive to try the option as a test.)
mysqladmin -u root processlist
when mysqld
appears to be running but not responding? Sometimes mysqld
is not
comatose even though you might think so. The problem may be that all
connections are in use, or there may be some internal lock problem.
mysqladmin processlist
will usually be able to make a connection even
in these cases, and can provide useful information about the current number
of connections and their status.
mysqladmin -i 5 status
or mysqladmin -i 5
-r status
or in a separate window to produce statistics while you run
your other queries.
mysqld
from gdb
(or in another debugger).
See section E.1.3 Debugging mysqld under gdb.
mysqld
has crashed inside
gdb:
backtrace info local up info local up info localWith gdb you can also examine which threads exist with
info
threads
and switch to a specific thread with thread #
, where
#
is the thread id.
BLOB/TEXT
columns (but only VARCHAR
columns), you
can try to change all VARCHAR
to CHAR
with ALTER
TABLE
. This will force MySQL to use fixed-size rows.
Fixed-size rows take a little extra space, but are much more tolerant to
corruption!
The current dynamic row code has been in use at MySQL AB for at
least 3 years without any problems, but by nature dynamic-length rows are
more prone to errors, so it may be a good idea to try the above to see if
it helps!
If you never set a root
password for MySQL, then the server will
not require a password at all for connecting as root
. It is
recommended to always set a password for each user. See section 4.2.2 How to Make MySQL Secure Against Crackers.
If you have set a root
password, but forgot what it was, you can
set a new password with the following procedure:
mysqld
server by sending a kill
(not kill
-9
) to the mysqld
server. The pid is stored in a `.pid'
file, which is normally in the MySQL database directory:
shell> kill `cat /mysql-data-directory/hostname.pid`You must be either the Unix
root
user or the same user mysqld
runs as to do this.
mysqld
with the --skip-grant-tables
option.
mysqladmin password
command:
shell> mysqladmin -u root password 'mynewpassword'
mysqld
and restart it normally,
or just load the privilege tables with:
shell> mysqladmin -h hostname flush-privileges
Alternatively, you can set the new password using the mysql
client:
mysqld
with the --skip-grant-tables
option as described above.
mysqld
server with:
shell> mysql -u root mysql
mysql
client:
mysql> UPDATE user SET Password=PASSWORD('mynewpassword') -> WHERE User='root'; mysql> FLUSH PRIVILEGES;
mysqld
and restart it normally.
When a disk-full condition occurs, MySQL does the following:
To alleviate the problem, you can take the following actions:
mysqladmin kill
to the thread.
The thread will be aborted the next time it checks the disk (in 1 minute).
Exceptions to the above behaveour is when you use REPAIR
or
OPTIMIZE
or when the indexes are created in a batch after an
LOAD DATA INFILE
or after an ALTER TABLE
statement.
All of the above commands may use big temporary files that left to
themself would cause big problems for the rest of the system. If
MySQL gets disk full while doing any of the above operations,
it will remove the big temporary files and mark the table as crashed
(except for ALTER TABLE
, in which the old table will be left
unchanged).
MySQL uses the value of the TMPDIR
environment variable as
the pathname of the directory in which to store temporary files. If you don't
have TMPDIR
set, MySQL uses the system default, which is
normally `/tmp' or `/usr/tmp'. If the filesystem containing your
temporary file directory is too small, you should edit safe_mysqld
to
set TMPDIR
to point to a directory in a filesystem where you have
enough space! You can also set the temporary directory using the
--tmpdir
option to mysqld
.
MySQL creates all temporary files as hidden files. This ensures
that the temporary files will be removed if mysqld
is terminated. The
disadvantage of using hidden files is that you will not see a big temporary
file that fills up the filesystem in which the temporary file directory is
located.
When sorting (ORDER BY
or GROUP BY
), MySQL normally
uses one or two temporary files. The maximum disk-space needed is:
(length of what is sorted + sizeof(database pointer)) * number of matched rows * 2
sizeof(database pointer)
is usually 4, but may grow in the future for
really big tables.
For some SELECT
queries, MySQL also creates temporary SQL
tables. These are not hidden and have names of the form `SQL_*'.
ALTER TABLE
creates a temporary table in the same directory as
the original table.
If you have problems with the fact that anyone can delete the
MySQL communication socket `/tmp/mysql.sock', you can,
on most versions of Unix, protect your `/tmp' filesystem by setting
the sticky
bit on it. Log in as root
and do the following:
shell> chmod +t /tmp
This will protect your `/tmp' filesystem so that files can be deleted
only by their owners or the superuser (root
).
You can check if the sticky
bit is set by executing ls -ld /tmp
.
If the last permission bit is t
, the bit is set.
You can change the place where MySQL uses / puts the socket file the following ways:
/etc/my.cnf
:
[client] socket=path-for-socket-file [mysqld] socket=path-for-socket-fileSee section 4.1.2 `my.cnf' Option Files.
safe_mysqld
and most
clients with the --socket=path-for-socket-file
option.
MYSQL_UNIX_PORT
environment
variable.
configure
option
--with-unix-socket-path=path-for-socket-file
. See section 2.3.3 Typical configure
Options.
You can test that the socket works with this command:
shell> mysqladmin --socket=/path/to/socket version
If you have a problem with SELECT NOW()
returning values in GMT and
not your local time, you have to set the TZ
environment variable to
your current time zone. This should be done for the environment in which
the server runs, for example, in safe_mysqld
or mysql.server
.
See section F Environment Variables.
By default, MySQL searches are case-insensitive (although there are
some character sets that are never case-insensitive, such as czech
).
That means that if you search with col_name LIKE 'a%'
, you will get all
column values that start with A
or a
. If you want to make this
search case-sensitive, use something like INSTR(col_name, "A")=1
to
check a prefix. Or use STRCMP(col_name, "A") = 0
if the column value
must be exactly "A"
.
Simple comparison operations (>=, >, = , < , <=
, sorting and
grouping) are based on each character's ``sort value''. Characters with
the same sort value (like E, e and é) are treated as the same character!
In older MySQL versions LIKE
comparisons where done on
the uppercase value of each character (E == e but E <> é). In newer
MySQL versions LIKE
works just like the other comparison
operators.
If you want a column always to be treated in case-sensitive fashion,
declare it as BINARY
. See section 6.5.3 CREATE TABLE
Syntax.
If you are using Chinese data in the so-called big5 encoding, you want to
make all character columns BINARY
. This works because the sorting
order of big5 encoding characters is based on the order of ASCII codes.
DATE
Columns
The format of a DATE
value is 'YYYY-MM-DD'
. According to ANSI
SQL, no other format is allowed. You should use this format in UPDATE
expressions and in the WHERE clause of SELECT
statements. For
example:
mysql> SELECT * FROM tbl_name WHERE date >= '1997-05-05';
As a convenience, MySQL automatically converts a date to a number if
the date is used in a numeric context (and vice versa). It is also smart
enough to allow a ``relaxed'' string form when updating and in a WHERE
clause that compares a date to a TIMESTAMP
, DATE
, or a
DATETIME
column. (Relaxed form means that any punctuation character
may be used as the separator between parts. For example, '1998-08-15'
and '1998#08#15'
are equivalent.) MySQL can also convert a
string containing no separators (such as '19980815'
), provided it
makes sense as a date.
The special date '0000-00-00'
can be stored and retrieved as
'0000-00-00'.
When using a '0000-00-00'
date through
MyODBC
, it will automatically be converted to NULL
in
MyODBC
Version 2.50.12 and above, because ODBC can't handle this kind of
date.
Because MySQL performs the conversions described above, the following statements work:
mysql> INSERT INTO tbl_name (idate) VALUES (19970505); mysql> INSERT INTO tbl_name (idate) VALUES ('19970505'); mysql> INSERT INTO tbl_name (idate) VALUES ('97-05-05'); mysql> INSERT INTO tbl_name (idate) VALUES ('1997.05.05'); mysql> INSERT INTO tbl_name (idate) VALUES ('1997 05 05'); mysql> INSERT INTO tbl_name (idate) VALUES ('0000-00-00'); mysql> SELECT idate FROM tbl_name WHERE idate >= '1997-05-05'; mysql> SELECT idate FROM tbl_name WHERE idate >= 19970505; mysql> SELECT MOD(idate,100) FROM tbl_name WHERE idate >= 19970505; mysql> SELECT idate FROM tbl_name WHERE idate >= '19970505';
However, the following will not work:
mysql> SELECT idate FROM tbl_name WHERE STRCMP(idate,'19970505')=0;
STRCMP()
is a string function, so it converts idate
to
a string and performs a string comparison. It does not convert
'19970505'
to a date and perform a date comparison.
Note that MySQL does very limited checking whether the date is
correct. If you store an incorrect date, such as '1998-2-31'
, the
wrong date will be stored.
Because MySQL packs dates for storage, it can't store any given date as it would not fit onto the result buffer. The rules for accepting a date are:
DATE
and DATETIME
columns.
DATE
column and you only know part
of the date.
If the date cannot be converted to any reasonable value, a 0
is
stored in the DATE
field, which will be retrieved as
0000-00-00
. This is both a speed and convenience issue as we
believe that the database's responsiblity is to retrieve the same date
you stored (even if the data was not logically correct in all cases).
We think it is up to the application to check the dates, and not the server.
NULL
Values
The concept of the NULL
value is a common source of confusion for
newcomers to SQL, who often think that NULL
is the same thing as an
empty string ""
. This is not the case! For example, the following
statements are completely different:
mysql> INSERT INTO my_table (phone) VALUES (NULL); mysql> INSERT INTO my_table (phone) VALUES ("");
Both statements insert a value into the phone
column, but the first
inserts a NULL
value and the second inserts an empty string. The
meaning of the first can be regarded as ``phone number is not known'' and the
meaning of the second can be regarded as ``she has no phone''.
In SQL, the NULL
value is always false in comparison to any
other value, even NULL
. An expression that contains NULL
always produces a NULL
value unless otherwise indicated in
the documentation for the operators and functions involved in the
expression. All columns in the following example return NULL
:
mysql> SELECT NULL,1+NULL,CONCAT('Invisible',NULL);
If you want to search for column values that are NULL
, you
cannot use the =NULL
test. The following statement returns no
rows, because expr = NULL
is FALSE, for any expression:
mysql> SELECT * FROM my_table WHERE phone = NULL;
To look for NULL
values, you must use the IS NULL
test.
The following shows how to find the NULL
phone number and the
empty phone number:
mysql> SELECT * FROM my_table WHERE phone IS NULL; mysql> SELECT * FROM my_table WHERE phone = "";
Note that you can only add an index on a column that can have NULL
values if you are using MySQL Version 3.23.2 or newer and are using the
MyISAM
or InnoDB
table type.
In earlier versions and with other table types, you must declare such
columns NOT NULL
. This also means you cannot then insert
NULL
into an indexed column.
When reading data with LOAD DATA INFILE
, empty columns are updated
with ''
. If you want a NULL
value in a column, you should use
\N
in the text file. The literal word 'NULL'
may also be used
under some circumstances.
See section 6.4.9 LOAD DATA INFILE
Syntax.
When using ORDER BY
, NULL
values are presented first. If you
sort in descending order using DESC
, NULL
values are presented
last. When using GROUP BY
, all NULL
values are regarded as
equal.
To help with NULL
handling, you can use the IS NULL
and
IS NOT NULL
operators and the IFNULL()
function.
For some column types, NULL
values are handled specially. If you
insert NULL
into the first TIMESTAMP
column of a table, the
current date and time is inserted. If you insert NULL
into an
AUTO_INCREMENT
column, the next number in the sequence is inserted.
alias
You can use an alias to refer to a column in the GROUP BY
,
ORDER BY
, or in the HAVING
part. Aliases can also be used
to give columns better names:
SELECT SQRT(a*b) as rt FROM table_name GROUP BY rt HAVING rt > 0; SELECT id,COUNT(*) AS cnt FROM table_name GROUP BY id HAVING cnt > 0; SELECT id AS "Customer identity" FROM table_name;
Note that ANSI SQL doesn't allow you to refer to an alias in a
WHERE
clause. This is because when the WHERE
code is
executed the column value may not yet be determined. For example, the
following query is illegal:
SELECT id,COUNT(*) AS cnt FROM table_name WHERE cnt > 0 GROUP BY id;
The WHERE
statement is executed to determine which rows should
be included in the GROUP BY
part while HAVING
is used to
decide which rows from the result set should be used.
As MySQL doesn't yet support subselects, nor the use of more than one
table in the DELETE
statement (prior to Version 4.0), you should
use the following approach to delete rows from 2 related tables:
SELECT
the rows based on some WHERE
condition in the main table.
DELETE
the rows in the main table based on the same condition.
DELETE FROM related_table WHERE related_column IN (selected_rows)
.
If the total number of characters in the query with
related_column
is more than 1,048,576 (the default value of
max_allowed_packet
, you should split it into smaller parts and
execute multiple DELETE
statements. You will probably get the
fastest DELETE
by only deleting 100-1000 related_column
id's per query if the related_column
is an index. If the
related_column
isn't an index, the speed is independent of the
number of arguments in the IN
clause.
If you have a complicated query that has many tables and that doesn't return any rows, you should use the following procedure to find out what is wrong with your query:
EXPLAIN
and check if you can find something that is
obviously wrong. See section 5.2.1 EXPLAIN
Syntax (Get Information About a SELECT
).
WHERE
clause.
LIMIT 10
with the query.
SELECT
for the column that should have matched a row against
the table that was last removed from the query.
FLOAT
or DOUBLE
columns with numbers that
have decimals, you can't use '='
. This problem is common in most
computer languages because floating-point values are not exact values.
In most cases, changing the FLOAT
to a DOUBLE
will fix this.
See section A.5.7 Problems with Floating-Point Comparison.
mysql test < query.sql
that shows your problems.
You can create a test file with mysqldump --quick database tables > query.sql
. Open the file in an editor, remove some insert lines (if there are
too many of these), and add your select statement at the end of the file.
Test that you still have your problem by doing:
shell> mysqladmin create test2 shell> mysql test2 < query.sqlPost the test file using
mysqlbug
to [email protected].
floating-point numbers cause confusion sometimes, because these numbers are not stored as exact values inside computer architecture. What one can see on the screen usually is not the exact value of the number.
Field types FLOAT
, DOUBLE
and DECIMAL
are such.
CREATE TABLE t1 (i INT, d1 DECIMAL(9,2), d2 DECIMAL(9,2)); INSERT INTO t1 VALUES (1, 101.40, 21.40), (1, -80.00, 0.00), (2, 0.00, 0.00), (2, -13.20, 0.00), (2, 59.60, 46.40), (2, 30.40, 30.40), (3, 37.00, 7.40), (3, -29.60, 0.00), (4, 60.00, 15.40), (4, -10.60, 0.00), (4, -34.00, 0.00), (5, 33.00, 0.00), (5, -25.80, 0.00), (5, 0.00, 7.20), (6, 0.00, 0.00), (6, -51.40, 0.00); mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b -> FROM t1 GROUP BY i HAVING a <> b; +------+--------+-------+ | i | a | b | +------+--------+-------+ | 1 | 21.40 | 21.40 | | 2 | 76.80 | 76.80 | | 3 | 7.40 | 7.40 | | 4 | 15.40 | 15.40 | | 5 | 7.20 | 7.20 | | 6 | -51.40 | 0.00 | +------+--------+-------+
The result is correct. Although the first five records look like they shouldn't pass the comparison test, they may do so because the difference between the numbers show up around tenth decimal, or so depending on computer architecture.
The problem cannot be solved by using ROUND() (or similar function), because the result is still a floating-point number. Example:
mysql> SELECT i, ROUND(SUM(d1), 2) AS a, ROUND(SUM(d2), 2) AS b -> FROM t1 GROUP BY i HAVING a <> b; +------+--------+-------+ | i | a | b | +------+--------+-------+ | 1 | 21.40 | 21.40 | | 2 | 76.80 | 76.80 | | 3 | 7.40 | 7.40 | | 4 | 15.40 | 15.40 | | 5 | 7.20 | 7.20 | | 6 | -51.40 | 0.00 | +------+--------+-------+
This is what the numbers in row 'a' look like:
mysql> SELECT i, ROUND(SUM(d1), 2)*1.0000000000000000 AS a, -> ROUND(SUM(d2), 2) AS b FROM t1 GROUP BY i HAVING a <> b; +------+----------------------+-------+ | i | a | b | +------+----------------------+-------+ | 1 | 21.3999999999999986 | 21.40 | | 2 | 76.7999999999999972 | 76.80 | | 3 | 7.4000000000000004 | 7.40 | | 4 | 15.4000000000000004 | 15.40 | | 5 | 7.2000000000000002 | 7.20 | | 6 | -51.3999999999999986 | 0.00 | +------+----------------------+-------+
Depending on the computer architecture you may or may not see similar results. Each CPU may evaluate floating-point numbers differently. For example in some machines you may get 'right' results by multiplaying both arguments with 1, an example follows.
WARNING: NEVER TRUST THIS METHOD IN YOUR APPLICATION, THIS IS AN EXAMPLE OF A WRONG METHOD!!!
mysql> SELECT i, ROUND(SUM(d1), 2)*1 AS a, ROUND(SUM(d2), 2)*1 AS b -> FROM t1 GROUP BY i HAVING a <> b; +------+--------+------+ | i | a | b | +------+--------+------+ | 6 | -51.40 | 0.00 | +------+--------+------+
The reason why the above example seems to be working is that on the particular machine where the test was done, the CPU floating-point arithmetics happens to round the numbers to same, but there is no rule that any CPU should do so, so it cannot be trusted.
The correct way to do floating-point number comparison is to first decide on what is the wanted tolerance between the numbers and then do the comparsion against the tolerance number. For example, if we agree on that floating-point numbers should be regarded the same, if they are same with precision of one of ten thousand (0.0001), the comparsion should be done like this:
mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b FROM t1 -> GROUP BY i HAVING ABS(a - b) > 0.0001; +------+--------+------+ | i | a | b | +------+--------+------+ | 6 | -51.40 | 0.00 | +------+--------+------+ 1 row in set (0.00 sec)
And vice versa, if we wanted to get rows where the numbers are the same, the test would be:
mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b FROM t1 -> GROUP BY i HAVING ABS(a - b) < 0.0001; +------+-------+-------+ | i | a | b | +------+-------+-------+ | 1 | 21.40 | 21.40 | | 2 | 76.80 | 76.80 | | 3 | 7.40 | 7.40 | | 4 | 15.40 | 15.40 | | 5 | 7.20 | 7.20 | +------+-------+-------+
ALTER TABLE
.
ALTER TABLE
changes a table to the current character set.
If you during ALTER TABLE
get a duplicate key error, then the cause
is either that the new character sets maps to keys to the same value
or that the table is corrupted, in which case you should run
REPAIR TABLE
on the table.
If ALTER TABLE
dies with an error like this:
Error on rename of './database/name.frm' to './database/B-a.frm' (Errcode: 17)
the problem may be that MySQL has crashed in a previous ALTER
TABLE
and there is an old table named `A-something' or
`B-something' lying around. In this case, go to the MySQL data
directory and delete all files that have names starting with A-
or
B-
. (You may want to move them elsewhere instead of deleting them.)
ALTER TABLE
works the following way:
If something goes wrong with the renaming operation, MySQL tries to undo the changes. If something goes seriously wrong (this shouldn't happen, of course), MySQL may leave the old table as `B-xxx', but a simple rename on the system level should get your data back.
The whole point of SQL is to abstract the application from the data storage format. You should always specify the order in which you wish to retrieve your data. For example:
SELECT col_name1, col_name2, col_name3 FROM tbl_name;
will return columns in the order col_name1
, col_name2
, col_name3
, whereas:
SELECT col_name1, col_name3, col_name2 FROM tbl_name;
will return columns in the order col_name1
, col_name3
, col_name2
.
You should never, in an application, use SELECT *
and
retrieve the columns based on their position, because the order in which
columns are returned cannot be guaranteed over time. A simple
change to your database may cause your application to fail rather
dramatically.
If you want to change the order of columns anyway, you can do it as follows:
INSERT INTO new_table SELECT fields-in-new_table-order FROM old_table
.
old_table
.
ALTER TABLE new_table RENAME old_table
.
The following are a list of the limitations with TEMPORARY TABLES
.
HEAP
, ISAM
,
MyISAM
or InnoDB
.
mysql> SELECT * FROM temporary_table, temporary_table AS t2;We plan to fix the above in 4.0.
RENAME
on a TEMPORARY
table.
Note that ALTER TABLE org_name RENAME new_name
works!
We plan to fix the above in 4.0.
Go to the first, previous, next, last section, table of contents.