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. Section 4.9.1, “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. Section E.1.4, “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:
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 filesystem locks (normally handled by the lockd daemon ) or if you are running multiple servers with -skip-external-locking
You have a crashed index/datafile 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 storage engine 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 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. Chapter 4, Database Administration
.
<listitem>
Use 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. Section 4.9.2, “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!
Section 1.6.1.3, “How to Report Bugs or Problems
”.
Try to make a test case that we can use to reproduce the problem.
Section E.1.6, “Making a Test Case If You Experience Table Corruption
”.
Try running the included mysql-test test and the MySQL
benchmarks. Section 12.1.2, “MySQL Test Suite
”. They should test MySQL
rather well. You can also add code to the benchmarks 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.
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.
Section E.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-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.)
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).
Section E.1.3, “Debugging mysqld under gdb
”.
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:
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. Section 1.6.1.3, “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
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!
backtrace
info local
up
info local
up
info local
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. Section 4.2.2, “How to Make MySQL Secure Against Crackers ”.
If you have set a root password, but forgot what it was, you can set a new password with the following procedure:
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:
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.
Restart mysqld with the -skip-grant-tables option.
Set a new password with the mysqladmin password command:
shell mysqladmin -u root password 'mynewpassword'
Now you can either stop mysqld and restart it normally, or just load the privilege tables with:
shell mysqladmin -h hostname flush-privileges
After this, you should be able to connect using the new password.
Alternatively, you can set the new password using the mysql client:
Take down and restart mysqld with the -skip-grant-tables option as described above.
Connect to the mysqld server with:
shell mysql -u root mysql
Issue the following commands in the mysql client:
mysql UPDATE user SET Password=PASSWORD('mynewpassword')
- WHERE User='root';
mysql FLUSH PRIVILEGES;
After this, you should be able to connect using the new password.
You can now stop mysqld and restart it normally.
When a disk-full condition occurs, MySQL does the following:
It checks once every minute to see whether 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 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:
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 mysqld_safe 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. Section 2.3.3, “Typical configure Options ”.
You can test that the socket works with this command:
shell mysqladmin --socket=/path/to/socket version
If you have a problem with SELECT NOW() returning values in GMT and not your local time, you have to set the TZ environment variable to your current time zone. This should be done for the environment in which the server runs, for example, in mysqld_safe or mysql.server. Appendix F, Environment Variables .