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
When you run into problems, the first thing you should do is to find out
which program / piece of equipment is causing problems:
If you have one of the following symptoms, then it is probably a hardware
(like memory, motherboard, CPU, or hard disk) or kernel problem:
The keyboard doesn't work. This can normally be checked by pressing
Caps Lock. If the Caps Lock light doesn't change you have to replace
your keyboard. (Before doing this, you should try to reboot
your computer and check all cables to the keyboard.)
The mouse pointer doesn't move.
The machine doesn't answer to a remote machine's pings.
If your system rebooted unexpectedly (a faulty user level program should
NEVER be able to take down your system).
In this case you should start by checking all your cables and run some
diagnostic tool to check your hardware!
You should also check if there are any patches, updates, or service
packs for your operating system that could likely solve your problems.
Check also that all your libraries (like glibc) are up to date.
It's always good to use a machine with ECC memory to discover
memory problems early!
If your keyboard is locked up, you may be able to fix this by
logging into your machine from another machine and execute
kbd_mode -a on it.
Please examine your system log file (/var/log/messages or similar) for
reasons for your problems. If you think the problem is in MySQL
then you should also examine MySQL's log files. See section 4.9.3 The Update Log.
If you don't think you have hardware problems, you should try to find
out which program is causing problems.
Try using top, ps, taskmanager, or some similar program,
to check which program is taking all CPU or is locking the machine.
Check with top, df, or a similar program if you are out of
memory, disk space, open files, or some other critical resource.
If the problem is some runaway process, you can always try to kill it. If it
doesn't want to die, there is probably a bug in the operating system.
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:
Has the program in question made a segmentation fault (core dumped)?
Is the program taking the whole CPU? Check with top. Let the
program run for a while, it may be evaluating something heavy.
If it's the mysqld server that is causing problems, can you
do mysqladmin -u root ping or mysqladmin -u root processlist?
What does a client program say (try with mysql, for example)
when you try to connect to the MySQL server?
Does the client jam? Do you get any output from the program?
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):
The client couldn't send a question to the
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
On Windows you can connect only with TCP/IP if the mysqld server
is running on Win95/Win98. If mysqld is running on NT and 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
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.
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. See section 2.2.2 Operating Systems Supported by MySQL. 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.
See section A.4.5 How to Protect or change the MySQL socket file `/tmp/mysql.sock'.
You are using Linux and one thread has died (core dumped). In this case
you must kill the other mysqld threads (for example, with the
mysql_zap script before you can start a new MySQL
server. See section A.4.1 What To Do If MySQL Keeps Crashing.
You may not have read and write privilege to either the directory that holds
the socket file or privilege to the socket file itself. In this case you
have to either change the privilege for the directory / file or restart
mysqld so that it uses a directory that you can access.
If you get the error message Can't connect to MySQL server on
some_hostname, you can try the following things to find out what the
problem is :
Check if the server is up by doing telnet your-host-name
tcp-ip-port-number and press 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
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
Check that your mysqld server is not started with the
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
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. See section 4.5.5 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.
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,
You can check the table type for a table by doing:
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
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.
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
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. See section A.2.8 Packet too large Error.
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
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 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.
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
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.
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
Possible causes of and fixes for this problem:
You may be running a new version of mysqld with an old
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()
mysql> update user set password=PASSWORD('your password')
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.5 SHOW Syntax.
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.
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.
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:
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
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 126.96.36.199 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
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
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
-lmysqlclientLAST on your link line.
If you get undefined reference errors for the uncompress
or compress function, add -lzLAST 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
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:
Add the path to the directory where you have libmysqlclient.so the
LD_LIBRARY_PATH environment variable.
Add the path to the directory where you have libmysqlclient.so the
LD_LIBRARY environment variable.
Copy libmysqlclient.so to some place that is searched by your system,
like `/lib', and update the shared library information by executing
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:
Stop the server if it's running (use mysqladmin shutdown).
Change the database directories and files so that 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/datadir
If 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
Start the server as user 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.
To start the server as the given user name automatically at system
startup time, add a 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:
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
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.
By default MySQL will create database and RAID
directories with permission type 0700. You can modify this behavior 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:
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 delayed_key_writes, 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:
Someone/something killed mysqld or the machine in the middle
of an update.
You have found a bug in mysqld that caused it to die in the
middle of an update.
Someone is manipulating the data/index files outside of mysqld
without locking the table properly.
If you are running many mysqld servers on the same data on a
system that doesn't support good file system locks (normally handled by
the lockd daemon ) or if you are running
multiple servers with --skip-locking
You have a crashed index/data file that contains very wrong data that
got mysqld confused.
You have found a bug in the data storage code. This isn't that likely,
but it's at least possible. In this case you can try to change the file
type to another database handler by using 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 or not things that work for others crash for you. Please try
the following things:
Take down the 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 MySQL Database Administration.
Use mysqld --log and try to determine from the information in the log
whether or not 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 188.8.131.52 How to Report Bugs or Problems.
Try running the included mysql-test test and the MySQL
benchmarks. See section 9.3.2 MySQL Test Suite. They should test MySQL
rather well. You can also add code that to the benchmarks to simulates
your application! The benchmarks can be found in the `bench'
directory in the source distribution or, for a binary distribution, in
the `sql-bench' directory under your MySQL installation
Try fork_test.pl and fork2_test.pl.
If you configure MySQL for debugging, it will be much easier to
gather information about possible errors if something goes wrong.
Reconfigure MySQL with the --with-debug option or
--with-debug=full to configure and then recompile.
See section G.1 Debugging a MySQL server.
Configuring MySQL for debugging causes a safe memory allocator to be
included that can find some errors. It also provides a lot of output about
what is happening.
Have you applied the latest patches for your operating system?
Use the --skip-locking option to mysqld. On some systems, the
lockd lock manager does not work properly; the --skip-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.)
Have you tried 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.
Run the command mysqladmin -i 5 status or mysqladmin -i 5
-r status or in a separate window to produce statistics while you run
your other queries.
Print the backtrace and the local variables at the 3 lowest levels. In gdb you
can do this with the following commands when mysqld has crashed inside
With gdb you can also examine which threads exist with info
threads and switch to a specific thread with thread #, where
# is the thread id.
Try to simulate your application with a Perl script to force
MySQL to crash or misbehave.
Send a normal bug report. See section 184.108.40.206 How to Report Bugs or Problems. Be even more detailed
than usual. Because MySQL works for many people, it may be that the
crash results from something that exists only on your computer (for example,
an error that is related to your particular system libraries).
If you have a problem with tables with dynamic-length rows and you are
not using 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
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
If you have forgotten the root user password for MySQL, you
can restore it with the following procedure:
Take down the 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:
kill `cat /mysql-data-directory/hostname.pid`
You must be either the Unix root user or the same user the server
runs as to do this.
Restart mysqld with the --skip-grant-tables option.
Connect to the mysqld server with mysql -h hostname mysql and change
the password with a GRANT command. See section 4.3.1 GRANT and REVOKE Syntax.
You can also do this with
mysqladmin -h hostname -u user password 'new password'
Load the privilege tables with: mysqladmin -h hostname
flush-privileges or with the SQL command FLUSH PRIVILEGES.
Note that after you started mysqld with --skip-grant-tables,
any usage of GRANT commands will give you an Unknown command
error until you have executed FLUSH PRIVILEGES.
When a disk-full condition occurs, MySQL does the following:
It checks once every minute to see whether or not there is enough space to
write the current row. If there is enough space, it continues as if nothing had
Every 6 minutes it writes an entry to the log file warning about the disk
To alleviate the problem, you can take the following actions:
To continue, you only have to free enough disk space to insert all records.
To abort the thread, you must send a mysqladmin kill to the thread.
The thread will be aborted the next time it checks the disk (in 1 minute).
Note that other threads may be waiting for the table that caused the disk
full condition. If you have several ``locked'' threads, killing the one
thread that is waiting on the disk-full condition will allow the other
threads to continue.
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
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 file system containing your
temporary file directory is too small, you should edit safe_mysqld to
set TMPDIR to point to a directory in a file system 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 file system in which the temporary file directory is
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
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' file system by setting
the sticky bit on it. Log in as root and do the following:
shell> chmod +t /tmp
This will protect your `/tmp' file system 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
Specify the path in a global or local option file.
For example, put in /etc/my.cnf:
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 H 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
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.
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
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
Because MySQL performs the conversions described above, the following
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 no checking whether or not the date is
correct. If you store an incorrect date, such as '1998-2-31', the
wrong date will be stored. If the date cannot be converted to any reasonable
value, a 0 is stored in the DATE field. This is mainly a speed
issue and we think it is up to the application to check the dates, and not
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:
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 = "";
In MySQL, as in many other SQL servers, you can't index
columns that can have NULL values. You must declare such columns
NOT NULL. Conversely, you cannot insert NULL into an indexed
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.8 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
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.
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 support sub-selects or use of more than one table
in the DELETE statement, 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.
Select only those fields that are used in the WHERE clause.
Remove one table at a time from the query until it returns some rows.
If the tables are big, it's a good idea to use LIMIT 10 with the query.
Do a SELECT for the column that should have matched a row against
the table that was last removed from the query.
If you are comparing 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:
mysql> SELECT * FROM table_name WHERE float_column=3.5;
mysql> SELECT * FROM table_name WHERE float_column between 3.45 and 3.55;
In most cases, changing the FLOAT to a DOUBLE will fix this!
If you still can't figure out what's wrong, create a minimal test that can
be run with 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.sql
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:
Create a new table named `A-xxx' with the requested changes.
All rows from the old table are copied to `A-xxx'.
The old table is renamed `B-xxx'.
`A-xxx' is renamed to your old table name.
`B-xxx' is deleted.
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
If you want to change the order of columns anyway, you can do it as follows:
Create a new table with the columns in the right order.
INSERT INTO new_table SELECT fields-in-new_table-order FROM old_table.