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, there are very few and they 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.
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 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.
This 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.
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 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 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 Chapter 4.
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. See Section 4.9.2. If you can repeatedly kill MySQL with one of the queries, even when you have checked all tables just before doing the query, you have been able to locate the bug and should do a bug report for this! See Section 1.6.2.3.
Try to make a test case that we can use to reproduce the problem. See Section D.1.6.
Try running the included mysql-test test and the MySQL benchmarks. See Section 9.1.2. They should test MySQL rather well. You can also add code to the benchmarks to simulate your application! The benchmarks can be found in the
benchdirectory in the source distribution or, for a binary distribution, in thesql-benchdirectory 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. See Section D.1.
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). See Section D.1.3.
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. See Section 1.6.2.3. 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 preceding tests to see if they help!
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
.pidfile, 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. 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 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 this behaviour is when you use REPAIR or OPTIMIZE, or when the indexes are created in a batch after a LOAD DATA INFILE or after an ALTER TABLE statement.
All of these commands may use big temporary files, which left to themselves would cause big problems for the rest of the system. If MySQL gets a disk-full condition while doing any of the preceding operations, it will remove the big temporary files and mark the table as crashed (except for ALTER TABLE, in which case 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 safe_mysqld 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 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 in 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
See Section 4.1.2.
Specify the path 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.
Define the path with the configure option --with-unix-socket-path=path-for-socket-file. See Section 2.3.3.
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. See Appendix E.
Get MySQL Reference Manual now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
