Go to the first, previous, next, last section, table of contents.
All MySQL versions are tested on many platforms before they are
released. This doesn't mean that there isn't any bugs in
MySQL, but that 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
mysqladmin version. If
mysqld has died, you may
find the reason for this in the file
Since 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 13 Maintaining a MySQL installation.
mysqld --log and try to determine from the information in the log
whether or not some specific query kills the server. 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.
You may be able to verify this using the following procedure:
Take down the MySQL daemon (with
Make a backup of files in the MySQL database directory.
Check the tables with
myisamchk -s */*.MYI to verify that all tables
are correct. If any table is corrupted, repair it with
myisamchk -r path-to-table.MYI.
Remove (or move away) any old log files from the MySQL data directory.
Start the server with
mysqld now dies, you can test if the problem is a specific
query by restoring the backup and executing
mysql-log-file. You can of course do the last test in some other
directory than the standard MySQL database directory by
starting another MySQL server with
Have you tried the benchmarks? They should test MySQL rather well.
You can also add code that 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 directory.
Check the file `mysql-data-directory/'hostname'.err' for any errors.
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 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?
--skip-locking option to
mysqld. On some systems, the
lockd lock manager does not work properly; the
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
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
in a separate window to produce statistics while you run your other queries.
Try the following:
gdb (or another debugger).
Run your test scripts.
back (or the backtrace command in your debugger) when
mysqld core dumps.
Try to simulate your application with a Perl script to force
MySQL to crash or misbehave.
Or send a normal bug report. See section 2.3 How to report bugs or problems. But be even more detailed
than usual. Since 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 table with dynamic length rows and you are
BLOB/TEXT columns (but only
VARCHAR columns) you
can try to change all
TABLE. This will force MySQL to use fixed size rows. Fixed
size rows take a little extra place, but are much more tolerant for
The current dynamic row code has been in use at TCX 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 if the above helps!
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 with by setting the
wait_timeout variable when
you start mysqld.
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
mysqld with the
-O max_allowed_packet=# option
(default 1M). The extra memory is allocated on demand, so
use more memory only when you issue a big query or when
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
is running on Win95/Win98. If it's running on NT, you can also connect
with named pipes. The name of the named pipe is
MySQL. If you
don't give a hostname when connecting to
mysqld, a MySQL client
will first try to connect to the named pipe and if this doesn't work it
will connect to the TCP/IP port. You can force the use of named pipes
on Windows by using
. as the hostname.
The error (2002)
Can't connect to ... normally means that there
isn't a MySQL server running on the system or that you are
using a wrong socket file or TCP/IP port when trying to connect to the
Start by checking (using
ps or the task manager on windows) that
there is a process running named
mysqld on your server! If there
mysqld process, you should start one. See section 4.15.2 Problems starting the MySQL server.
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
command; these cause the output of
hostname (i.e., the current
hostname) to be substituted into the
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,
uses the MIT-pthreads package.
See section 4.2 Operating systems supported by MySQL.
However, MIT-pthreads doesn't support Unix sockets, so on such a system 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 (e.g., a job that removes old files from the `/tmp'
directory). You can always run
mysqladmin version and
check that the socket
mysqladmin is trying to use really exists.
The fix in this case is to change the
cron job to not remove
`mysqld.sock' or to place the socket somewhere else. You can specify
a different socket location at MySQL configuration time with this
shell> ./configure --with-unix-socket-path=/path/to/socket
You can also start
safe_mysqld with the
--socket=/path/to/socket option and set the environment variable
MYSQL_UNIX_PORT to the socket pathname before starting your
MySQL clients. @item You have started the
mysqld server with
--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 setting the environment variable
MYSQL_UNIX_PORT to the socket pathname or by providing the socket path
as an argument to the clients. You can test the socket with this command:
shell> mysqladmin --socket=/path/to/socket version
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 18.1 What to do if MySQL keeps crashing.
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 is the
Check if the server up by doing
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
Check that your
mysqld server is not started with the
If you get a 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 (
of connect requests from the host
'hostname' that have been interrupted
in the middle. After
max_connect_errors failed requests,
assumes that something is wrong (like a attack from a cracker), and
blocks the site from further connections until someone executes the command
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
If you get the error
Too many connections when you try to connect
to MySQL, this means that there is already
clients connected to the
If you need more connections than the default (100), then you should restart
mysqld with a bigger value for the
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 login and use
to find out what could be wrong. See section 7.21
SHOW syntax (Get information about tables, columns,...).
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
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
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
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
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
This error occurs 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
you issue the problematic query. See section 7.25
SET OPTION syntax.
You can also start
mysqld with the
This is exactly the same as using
SQL_BIG_TABLES for all queries.
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
try to execute a new query before you have called
It can also happen if you try to execute two queries that return data without
mysql_store_result() in between.
If you get the following error:
Found wrong password for user: 'some_user@some_host'; Ignoring user
This means that when
mysqld was started or when it reloaded the
permissions tables, it found an entry in the
user table with
an invalid password. As a result, the entry is simply ignored by the
Possible causes of and fixes for this problem:
If you get the error
Table 'xxx' doesn't exist or
find file: 'xxx' (errno: 2), this means that no table exists
in the current database with the name
Note that as MySQL uses directories and files to store databases and
tables, the database and table names are case sensitive!
(On Win32 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 7.21
SHOW syntax (Get information about tables, columns,...).
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.
mysql client typically is used interactively, like this:
shell> mysql database
However, it's also possible to put your SQL commands in a file and tell
mysql to read its input from that file. To do so, create a text
file `text_file' that contains the commands you wish to execute.
mysql as shown below:
shell> mysql database < text_file
You can also start your text file with a
USE db_name statement. In
this case, it is unnecessary to specify the database name on the command
shell> mysql < text_file
See section 12.1 Overview of the different MySQL programs.
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
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
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
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.
SELECT queries, MySQL also creates temporary SQL
tables. These are not hidden and have names of the form `SQL_*'.
ALTER TABLE and
OPTIMIZE TABLE create 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
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 (
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.
See section 6.8 How the privilege system works. And especially see section 6.15 Causes of
Access denied errors.
The MySQL server
mysqld can be started and run by any user.
In order to change
mysqld to run as Unix user
user_name, you must
do the following:
Stop the server if it's running (use
Change the database directories and files so that
privileges to read and write files in them (you may need to do this as
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 3.22 or later, start
mysqld as the Unix
user and use the
mysqld will switch
to run as Unix user
user_name before accepting any connections.
If you are using the
mysql.server script to start
the system is rebooted, you should edit
mysql.server to use
mysqld as user
user_name, or to invoke
--user option. (No changes to
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
root is the only user with permission to access the
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
root on the command line, has nothing to do with MySQL running
as the Unix
root user, or, indeed, as other 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
root db_name and do whatever he likes.
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
-9) to the
mysqld server. The pid is stored in a
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.
mysqld with the
Connect to the mysqld server with
mysql -h hostname mysql and change
the password with a
GRANT command. See section 7.26
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
If you have problems with file permissions, for example, if
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 behavior by starting
safe_mysqld as follows:
shell> UMASK=384 # = 600 in octal
shell> export UMASK
shell> /path/to/safe_mysqld &
If you get
ERROR '...' not found (errno: 23),
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
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
mysqld to keep open fewer files at a time, you can make the
table cache smaller by using the
-O table_cache=32 option
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
safe_mysqld script. There is a commented-out line
ulimit -n 256 in the script. You can remove the
to uncomment this line, and change the number 256 to change the number of
file descriptors available to
ulimit can increase the number of file descriptors, but only up to the
limit imposed by the operating system. 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
ulimit will not work!
tcsh will also report incorrect values when you ask for the current
limits! In this case you should start
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
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
clause that compares a date to a
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' 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
MyODBC 2.50.12 and above, because ODBC can't handle this kind of
Since 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
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
wrong date will be stored. If the date cannot be converted to any reasonable
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
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 timezone. This should be done for the environment in which
the server runs, for example in
By default, MySQL searches are case-insensitive (although there are
some character sets that are never case insensitive, such as
That means that if you search with
col_name LIKE 'a%', you will get all
column values that start with
a. If you want to make this
search case-sensitive, use something like
INDEX(col_name, "A")=0 to
check a prefix. Or use
STRCMP(col_name, "A") = 0 if the column value
must be exactly
Simple comparison operations (
>=, >, = , < , <=, sorting and
grouping) are based on each character's ``sort value''. Characters with
the same sort value (like E, e and 'e) are treated as the same character!
LIKE comparisons are done on the uppercase value of each character
(E == e but E <> 'e)
If you want a column always to be treated in case-sensitive fashion,
declare it as
BINARY. See section 7.7
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 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
''. 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
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
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
mysql> SELECT NULL,1+NULL,CONCAT('Invisible',NULL);
If you want to search for column values that are
cannot use the
=NULL test. The following statement returns no
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
''. 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 7.16
LOAD DATA INFILE syntax.
NULL values are presented first. If you
sort in descending order using
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
For some column types,
NULL values are handled specially. If you
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 alias to refer to a column in the
ORDER BY or in the
HAVING part. Aliases can also be used
to give columns more 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 you ANSI SQL doesn't allow you to refer to an alias in a
WHERE clause. This is because that when the
WHERE code is
executed the column value may not yet be determinated. For example the
following query is illegal:
SELECT id,COUNT(*) AS cnt FROM table_name WHERE cnt > 0 GROUP BY id;
WHERE statement is executed to determinate 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
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
DELETE statements. You will probably get the
DELETE by only deleting 100-1000
id's per time 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
If you have a complicated query with many tables that doesn't return any
rows, you should use the following procedure to find out what is wrong
with your query:
Test the query with
EXPLAIN and check if you can find something that is
obviously wrong. See section 7.22
EXPLAIN syntax (Get information about a
Select only those fields that are used in the
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.
SELECT for the column that should have matched a row,
against the table that was last removed from the query.
If you are comparing
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 find 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. Take the file up in a editor, remove some insert lines (if there are
too many of these) and add your select statement last in the file.
Test that you still have your problem by doing:
shell> mysqladmin create test2
shell> mysql test2 < query.sql
Post the test file using
mysqlbug to firstname.lastname@example.org.
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
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
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 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 wish you wish to
retrieve your data. For example:
SELECT col_name1, col_name2, col_name3 FROM tbl_name;
will return columns in the order
SELECT col_name1, col_name3, col_name2 FROM tbl_name;
will return columns in the order
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:
Create a new table with the columns in the right order.
INSERT INTO new_table SELECT fields-in-new_table-order FROM old_table.
Drop or rename
ALTER TABLE new_table RENAME old_table
Go to the first, previous, next, last section, table of contents.