Administration Related Issues
=============================

What To Do If MySQL Keeps Crashing
----------------------------------

 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'.
*Note 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'.  *Note
Using 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.

   
     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.  *Note MySQL Database Administration::.

   * 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.
     *Note 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!  *Note Bug reports::.

   * Try to make a test case that we can use to reproduce the problem.
     *Note Reproduceable test case::.

   * Try running the included mysql-test test and the MySQL benchmarks.
     *Note 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.  *Note
     Debugging 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:
       1. Start `mysqld' from `gdb' (or in another debugger).  *Note
          Using gdb on mysqld::.

       2. Run your test scripts.

       3. 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.

    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
     above to see if it helps!

How to Reset a Forgotten Root Password
--------------------------------------



If you have set a `root' password, but forgot what it was, you can set
a new password with the following procedure:

  1. 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.

  2. Restart `mysqld' with the `--skip-grant-tables' option.

  3. Set a new password with the `mysqladmin password' command:
          shell> mysqladmin -u root password 'mynewpassword'

  4. Now you can either stop `mysqld' and restart it normally, or just
     load the privilege tables with:
          shell> mysqladmin -h hostname flush-privileges

  5. After this, you should be able to connect using the new password.

Alternatively, you can set the new password using the `mysql' client:
  1. Take down and restart `mysqld' with the `--skip-grant-tables'
     option as described above.

  2. Connect to the `mysqld' server with:
          shell> mysql -u root mysql

  3. Issue the following commands in the `mysql' client:
          mysql> UPDATE user SET Password=PASSWORD('mynewpassword')
              ->             WHERE User='root';
          mysql> FLUSH PRIVILEGES;

  4. After this, you should be able to connect using the new password.

  5. You can now stop `mysqld' and restart it normally.

How MySQL Handles a Full Disk
-----------------------------

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).

   
     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.

 remove
the big temporary files and mark the table as crashed (except for
`ALTER TABLE', in which the old table will be left unchanged).

Where MySQL Stores Temporary Files
----------------------------------


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.

It is possible to set `tmpdir' to point to a memory-based filesystem,
except if the MySQL server is a slave. If it is a slave, it needs some
of its temporary files (for replication of temporary tables or of `LOAD
DATA INFILE') to survive a machine's reboot, so a memory-based `tmpdir'
which is cleared when the machine reboots is not suitable; a disk-based
`tmpdir' is necessary.

How to Protect or Change the MySQL Socket File `/tmp/mysql.sock'
----------------------------------------------------------------

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
     *Note Option files::.

   * 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'. *Note configure
     options::.

You can test that the socket works with this command:

     shell> mysqladmin --socket=/path/to/socket version

Time Zone Problems
------------------

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'.
*Note Environment variables::.

[Назад] [Содержание] [Вперед]