|
Administration Related Issues
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'.
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..
- 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.. 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 thisTry to make a test case
that we can use to reproduce the problemTry running the included mysql-test test
and the MySQL benchmarks. 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 directory.
- 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.
- 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.
- Try the following:
- Start mysqld from
gdb (or in another
debugger). Run your test scripts.
- 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 gdb:
- backtrace
- info local
- up
- info local
- up
- info
local
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. 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 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 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. 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
happened.
- Every 6 minutes it writes an entry to the log file warning about the disk
full condition.
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 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
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
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' 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
following ways:
- Specify the path in a global or local option file. For example, put in
/etc/my.cnf:
- [client]
- socket=path-for-socket-file
- [mysqld]
- socket=path-for-socket-file
- Specifying this on the command line to
safe_mysqld and most
clients with the
--socket=path-for-socket-file
option.
- Specify the path to the socket in the
MYSQL_UNIX_PORT
environment variable.
- Defining the path with the
configure option
--with-unix-socket-path=path-for-socket-file.
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.
|
