 |
 |
|
|
| [ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top] | [Contents] | [Index] | [ ? ] |
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 The Error Log.
On some systems you can find in this file a stack trace of where mysqld
died that you can resolve with resolve_back_stack. See section Using a Stack Trace. Note that the variable values written in the .err
file may not always be 100 percent correct.
Many crashes of MySQL are caused by corrupted index files or datafiles.
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 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 The General Query Log.
If you can repeatedly 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 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 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 Debugging mysqld under gdb.
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.
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 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 mysqld_safe 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 use MySQL 4.1 or later you can spread load between
several physical disks by setting --tmpdir to a list of paths
separated by colon : (semicolon ; on Windows). They
will be used in round-robin fashion.
Note: These paths should end up on different physical disks,
not different partitions of the same disk.
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-file |
See section `my.cnf' Option Files.
mysqld_safe 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 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 mysqld_safe or mysql.server.
See section Environment Variables.
| [ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] |
Hosting by: Hurra Communications Ltd.
Generated: 2007-01-26 17:58:44