How safe/stable is MySQL

How stable is MySQL?

At TcX, MySQL has worked without any problems in our projects since mid-1996. When released to a wider public we noticed that there were some pieces of 'untested code' in MySQL that were quickly found by the new user group who made queries in a different manner. Each new release has had fewer portability problems than the previous one, even though they have all had a lot of new features, and we hope that it will be possible to label one of the next releases 'stable'.

Each release of MySQL has been usable and there have only been problems when users start to use code from 'the gray zones'. Naturally, outside users can't know what the gray zones are and I hope this section will clarify those currently known.

We will here try to answer some of the more important questions that seems to concern a lot of people and clarify some issues. This section has been put together from the information that has come forward in the mailing list which is very active in reporting bugs.

How stable is MySQL? Can I depend on MySQL in this project?

This is about the 3.21.x version of MySQL. All known and reported bugs are fixed in the latest version with the exception of the bugs listed in the BUGS file which are things that are 'design' related.

MySQL is written in multiple layers and different independent modules. Here is a list of the different modules and how tested each of them are.

The ISAM table handler. Stable

This is how all the data is stored. In all MySQL releases there hasn't been a single (reported) bug in this code. The only known way to get a corrupted table is to kill the server in the middle of a update and because all data is flushed to disk between each query even this is unlikely to destroy any data beyond rescue. There hasn't been a single bug report about lost data because of bugs in MySQL either.

The parser and lexical analyser. Stable

There hasn't been a single reported bug in this system for a couple of months.

The C client code. Stable

No known problems. In early 3.20 releases there were some limitations in the send/receive buffer size. In 3.21.x the send/receive buffer is now dynamic up to a default of 512K.

mysql, mysqladmin and mysqlshow. Stable

The command line clients have had very few bugs.

mysqldump and mysqlimport. Beta

Rewritten for 3.21.

Basic SQL. Stable

The basic SQL function system and string classes and dynamic memory handling. Not a single reported bug on this system.

Query optimiser. Gamma

Some changes in 3.21.

Range optimiser. Alpha

Totally rewritten for 3.21.x

Join optimiser. Gamma

Small changes for 3.21.

GROUP BY, ORDER BY and related function COUNT(). Beta

Rewritten for 3.21 and throughly tested.

Locking. Gamma

This is very system dependent. One some systems there are big problems using standard OS locking (fcntl). In these cases, you should run the MySQL daemon with the --skip-locking flag. Known problems are some Linux systems and SunOS when using NFS-mounted file systems.

Linux threads. Gamma

The only found problem is with the fcntl() call, which is fixed by using --skip-locking. Some people have reported lockup problems with the 0.5 release.

Solaris 2.5+ pthreads. Stable

We use this for all our production work.

MIT threads (Other systems). Beta

No reported bugs since 3.20.15 and no known bugs since 3.20.16. On some system there is 'misfeature' where some operations are quote slow (a 1/20 second sleep is done between each query). Of course MIT threads may slow down everything a bit, but for index based selects a select is usually done in one time frame so there shouldn't be a mutex locking/thread juggling.

Other thread implementions. Alpha

The ports to other systems is very new and may still have bugs, sometimes in MySQL but most often in the thread implementation itself.

LOAD DATA..., INSERT ... SELECT. Stable

Some people have thought they have found bugs in this but have turned up being misunderstandings. So check the manual before reporting bugs!

ALTER TABLE. Gamma

Partly rewritten for 3.21.

mysqlperl. Stable

No bugs reported except a lot of compiling and linking problems.

DBD. Beta

Now maintained by Jochen Wiedmann.

mysqlaccess. Beta

Written and maintained by

The Technical Documentation. Beta

It is improving.

MyODBC (uses ODBC SDK 2.5). Beta

It seems to work well with some programs.

TcX provides email support for paying customers, but the MySQL mailing list usually provides answers to all common questions. Bugs are usually fixed right away with a patch that usually works and for serious bugs there is almost always a new release.

Why are there is so many releases of MySQL?

Well, MySQL is evolving quite rapidly here at TcX and we want to share this with other MySQL users. We try to make a release when we have a very useful feature that others seem to have a need for.

We also try to help out users who request features that are easy to implement. We also take note of what our licensed users want and we especially take note of what our extended email supported customers want and try to help them out.

No one has to download a new release. The News section will tell you if the new release has something you really want. See section MySQL change history.

If there is, by any chance, a fatal bug in a release we will make a new release as soon as possible. We would like other companies to do this too. :)

The 3.21.x version incorporates major portability changes for many different systems. When the 3.21 release is stable we will remove the alpha/beta suffix and move active development to 3.22. Bugs will still be fixed in the stable version. We don't believe in a complete freeze, as this also leaves out bug fixes and things that 'must be done'. 'Somewhat freezed' means that we will maybe add small things that 'almost surely will not affect anything thats already working'.

If you are running and old system and want to upgrade, but you don't want to take chances with 3.21 you should upgrade to 3.20.32. I have tried to only fix fatal bugs and make small, relatively safe changes in this version.

If you are trying MySQL for the first time or have a little time to test out that your current system, you should use 3.21.

Checking a table for errors.

If MySQL crashed (for example if the computer is turned off) when all data is not written to disk the tables may have become corrupted. To check a table use:

isamchk table_name

This finds 99.99 % of all errors. What it can't find is when only the data file has been corrupted.

isamchk -e table_name

This goes through all data and does a complete check.

isamchk -ei table_name

As the above but it also prints some statistics.

We at TcX run a cron job on all our important tables once a week.

35 0 * * 0 /path/to/isamchk -s /path/to/dbs/*/*.ISM

This prints out any crashed tables so we can go and examine and repair them when needed.

As we haven't had any unexpected crashed (without hardware trouble) tables for a couple of years now (this is really true), once a week is more than enough for us.

Of course, whenever the machine has done a reboot in the middle of a update one usually has to check all the tables that could have been affected. (This is a 'expected crashed table'.)

We recommend that to start with one should do a isamchk -s on all updated tables each night until one comes to trust MySQL as much as we do.

Naturally, one could add a check to safe_mysql that, if there is an old pid file left after a reboot, it should check all tables that have been modified the last 24 hours.

How to repair tables.

The file format that MySQL uses to store data has been extensively tested, but there are always instances (like a hard kill on the mysqld process in the middle of a write, a hardware error or a unexpected shutdown of the computer) when some tables may be corrupted.

The sign of a corrupted table is usually when queries abort unexpectedly and one gets errors like:

• table.frm is locked against change.
• Can't find file 'table.ISM' (Errcode: ###)
• Got error ### from table handler. (Error 135 is an exception in this case)
• Unexpected end of file.
• Record file is crashed.

In these cases you have to repair your tables. The isamchk external utility can usually detect and fix most things that go wrong. See section The MySQL table check, optimise and repair program.

If you are going to use isamchk on very large files, you should first decide how much memory you want to give to isamchk. More memory gives more speed. For example, if you have more than 32M ram, try:

isamchk -O sortbuffer=16M -O keybuffer=16M -O readbuffer=1M
        -O writebuffer=1M ....

• Part 1: Checking
  • Check the permissions of the table files. Make them readable for the user running mysqld.
  • cd to the database directory.
  • Run 'isamchk *.ISM' or ('isamchk -e *.ISM' if you have more time).
  • You only have to repair those tables for which isamchk gives an error. Use option -s to avoid unnecessary information.
• Part 2: Easy safe repair.
  • If you get weird errors when checking or repairing, such as out of memory errors or if isamchk crashes, go to part 3.
  • Try first 'isamchk -rq table'. This will try to repair the .ISM file without touching the important data table (.ISD). If the data file (.ISD) contains everything and the delete links point at the right places in the data file, this should work and the table is fixed. Start repairing next table.
  • Make a backup of the data file (table.ISD) before continuing.
  • Use 'isamchk -r table'. This will remove wrong records and deleted records from the data file and reconstruct the index (.ISM) file.
  • If the above fails, use 'isamchk -ro table'. This is a little slower but a crash-safer version of the above.
• Part 3:; Hard repair.
  • This should only happen if the first 16K block in the .ISM file is destroyed, contains wrong information or if the .ISM is missing.
  • In this case we have to create a new .ISM file. Do as follows:
  • Move the data file .ISD file to some safe place.
  • Recreate the .ISM file from the .frm file:

    shell> mysql database
    mysql> delete from table_name;
    mysql> quit
    

  • Copy (don't move) the data file (.ISD) back on the newly created .ISD file
  • Go back to Part 2. (This shouldn't be a endless loop). isamchk -rq should now work.
• Part 4: Very hard repair.
  This can only happen if the descriptor file (.frm) also has crashed. This should never happen, because the .frm file isn't written after the table is created.
  • Restore the .frm file from a backup and go back to Part 3. You can also restore the .ISM file and go back to Part 2. In the latter case you should start with 'isamchk -r'.
  • If you don't have a backup but know exactly how the table was created, create a copy of the table in another database and copy the .frm and .ISM file from there to your crashed database and go back to Part 2.

Is there anything special to do when upgrading/downgrading MySQL?

One can always move the MySQL form and data files between different versions on the same architecture as long as one has the same base version of MySQL. The current base version is of course 3. If one changes the character set (sort order) by recompiling MySQL one has to do a isamchk -rq on all tables.

If you are paranoid and/or afraid of new versions you can always rename your old mysqld to something like mysqld-'old-version-number'. If your new mysqld then does something unexpected you can simple shut it down and restart with your old mysqld!

When you do a upgrade you should of course also take a backup of your old databases. Sometimes its good to be a little paranoid!

Upgrading to 3.21 from a 3.20 version

If you already have a version older than 3.20.28 running and want to switch to 3.21.# you need to do the following:

You can start the mysqld 3.21 server with safe_mysqld --old-protocol to use it with the original 3.20 data. In this case the new client function, mysql_errno(), will not return any server error, only CR_UNKNOWN_ERROR, (but it works for client errors) and the server uses the old password() checking instead of the new one.

If you are NOT using --old-protocol:

• All client code must be recompiled. If you are using ODBC you must get the new myodbc 2.# driver
• The script scripts/add_long_password must be run to convert the password field in the 'mysql/user' table to char(16).
• All passwords must be reassigned in the mysql/user table (to get 62-bit instead of 31-bit passwords).
• The table format hasn't changed so you don't have to convert any tables.

MySQL 3.20.28 and above can handle the new user table format without affecting clients. If you have a MySQL version below 3.20.28, passwords will not work on it anymore if you convert the user table. So to be safe, you should first upgrade to at least 3.20.28 and then upgrade to 3.21.#.

The new client code works with a 3.20.# version, so you can use the old 3.20.# server if you experience problems with 3.21.# without having to recompile the clients again.

If you are not using the option --old-protocol to mysqld, old clients will issue the error message:

ERROR: Protocol mismatch. Server Version = 10 Client Version = 9

Go to the first, previous, next, last section, table of contents.