A.4. Administration-Related Issues

A.4.1. How to Reset the Root Password

If you have never set a root password for MySQL, the server does not require a password at all for connecting as root. However, it is recommended to set a password for each account. See Section 5.7.1, “General Security Guidelines”.

If you set a root password previously, but have forgotten what it was, you can set a new password. The following procedure is for Windows systems. The procedure for Unix systems is given later in this section.

The procedure under Windows:

Log on to your system as Administrator.
Stop the MySQL server if it is running. For a server that is running as a Windows service, go to the Services manager:
```
Start Menu -> Control Panel -> Administrative Tools -> Services
```
Then find the MySQL service in the list, and stop it.
If your server is not running as a service, you may need to use the Task Manager to force it to stop.
Create a text file and place the following command within it on a single line:
```
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPassword');
```
Save the file with any name. For this example the file will be C:\mysql-init.txt.
Open a console window to get to the DOS command prompt:
```
Start Menu -> Run -> cmd
```
We are assuming that you installed MySQL to C:\mysql. If you installed MySQL to another location, adjust the following commands accordingly.
At the DOS command prompt, execute this command:
```
C:\> C:\mysql\bin\mysqld-nt --init-file=C:\mysql-init.txt
```
The contents of the file named by the --init-file option are executed at server startup, changing the root password. After the server has started successfully, you should delete C:\mysql-init.txt.
If you install MySQL using the MySQL Installation Wizard, you may need to specify a --defaults-file option:
```
C:\> "C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt.exe"
         --defaults-file="C:\Program Files\MySQL\MySQL Server 5.0\my.ini"
         --init-file=C:\mysql-init.txt
```
The appropriate --defaults-file setting can be found using the Services Manager:
```
Start Menu -> Control Panel -> Administrative Tools -> Services
```
Find the MySQL service in the list, right-click on it, and choose the Properties option. The Path to executable field contains the --defaults-file setting.
Stop the MySQL server, then restart it in normal mode again. If you run the server as a service, start it from the Windows Services window. If you start the server manually, use whatever command you normally use.
You should be able to connect using the new password.

In a Unix environment, the procedure for resetting the root password is as follows:

Log on to your system as either the Unix root user or as the same user that the mysqld server runs as.
Locate the .pid file that contains the server's process ID. The exact location and name of this file depend on your distribution, hostname, and configuration. Common locations are /var/lib/mysql/, /var/run/mysqld/, and /usr/local/mysql/data/. Generally, the filename has the extension of .pid and begins with either mysqld or your system's hostname.
You can stop the MySQL server by sending a normal kill (not kill -9) to the mysqld process, using the pathname of the .pid file in the following command:
```
shell> kill `cat /mysql-data-directory/host_name.pid`
```
Note the use of backticks rather than forward quotes with the cat command; these cause the output of cat to be substituted into the kill command.
Create a text file and place the following command within it on a single line:
```
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPassword');
```
Save the file with any name. For this example the file will be ~/mysql-init.
Restart the MySQL server with the special --init-file=~/mysql-init option:
```
shell> mysqld_safe --init-file=~/mysql-init &
```
The contents of the init-file are executed at server startup, changing the root password. After the server has started successfully you should delete ~/mysql-init.
You should be able to connect using the new password.

Alternatively, on any platform, you can set the new password using the mysql client(but this approach is less secure):

Stop mysqld and restart it with the --skip-grant-tables --user=root options (Windows users omit the --user=root portion).
Connect to the mysqld server with this command:
```
shell> mysql -u root
```

Issue the following statements in the mysql client:

mysql> UPDATE mysql.user SET Password=PASSWORD('newpwd')
    ->                   WHERE User='root';
mysql> FLUSH PRIVILEGES;

Replace “newpwd” with the actual root password that you want to use.

You should be able to connect using the new password.

A.4.2. What to Do If MySQL Keeps Crashing

Each MySQL version is tested on many platforms before it is released. This doesn't mean that there are no bugs in MySQL, but if there are bugs, they should be very few and can be hard to find. If you have a problem, it always helps if you try to find out exactly what crashes your system, because you have a much better chance of getting the problem fixed quickly.

First, you should try to find out whether the problem is that the mysqld server 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 and restarted, you may find the reason by looking in the server's error log. See Section 5.12.1, “The Error Log”.

On some systems, you can find in the error log a stack trace of where mysqld died that you can resolve with the resolve_stack_dump program. See Section E.1.4, “Using a Stack Trace”. Note that the variable values written in the error log may not always be 100% correct.

Many server crashes are caused by corrupted data files or index files. MySQL updates the files 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 data files are written but not index files.) This means that data file contents are safe even if mysqld crashes, because the operating system ensures that the unflushed data is written to disk. You can force MySQL to flush everything to disk after every SQL statement by starting mysqld with the --flush option.

The preceding means that normally you should not get corrupted tables unless one of the following happens:

The MySQL server or the server host was killed in the middle of an update.
You have found a bug in mysqld that caused it to die in the middle of an update.
Some external program is manipulating data files or index files at the same time as mysqld without locking the table properly.
You are running many mysqld servers using the same data directory on a system that doesn't support good filesystem locks (normally handled by the lockd lock manager), or you are running multiple servers with external locking disabled.
You have a crashed data file or index file that contains very corrupt data that confused mysqld.
You have found a bug in the data storage code. This isn't likely, but it's at least possible. In this case, you can try to change the storage engine to another 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:

Stop the mysqld server with mysqladmin shutdown, run myisamchk --silent --force */*.MYI from the data directory to check all MyISAM tables, and restart mysqld. This ensures that you are running from a clean state. See Chapter 5, Database Administration.
Start mysqld with the --log option and try to determine from the information written to 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 the server restarts. See Section 5.12.2, “The General Query Log”. If you can repeatedly kill MySQL with a specific query, even when you have checked all tables just before issuing it, then you have been able to locate the bug and should submit a bug report for it. See Section 1.8, “How to Report Bugs or Problems”.
Try to make a test case that we can use to repeat the problem. See Section E.1.6, “Making a Test Case If You Experience Table Corruption”.
Try running the tests in the mysql-test directory and the MySQL benchmarks. See Section 24.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 sql-bench directory in a source distribution or, for a binary distribution, in the sql-bench directory under your MySQL installation directory.
Try the fork_big.pl script. (It is located in the tests directory of source distributions.)
If you configure MySQL for debugging, it is much easier to gather information about possible errors if something goes wrong. 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. Reconfigure MySQL with the --with-debug or --with-debug=full option to configure and then recompile. See Section E.1, “Debugging a MySQL Server”.
Make sure that you have 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 two mysqld servers on the same data directory and that you must be careful if you use myisamchk. Nevertheless, 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 -u root processlist usually is 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 in a separate window to produce statistics while you run your other queries.
Try the following:
1. Start mysqld from gdb (or another debugger). See Section E.1.3, “Debugging mysqld under gdb”.
2. Run your test scripts.
3. Print the backtrace and the local variables at the three 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 N, where N 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.8, “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 containing dynamic-length rows and you are using only VARCHAR columns (not BLOB or TEXT columns), you can try to change all VARCHAR to CHAR with ALTER TABLE. This forces 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 several years with very few problems, but dynamic-length rows are by nature more prone to errors, so it may be a good idea to try this strategy to see whether it helps.
Do not rule out your server hardware when diagnosing problems. Defective hardware can be the cause of data corruption. Particular attention should be paid to both RAMS and hard-drives when troubleshooting hardware.

A.4.3. How MySQL Handles a Full Disk

This section describes how MySQL responds to disk-full errors (such as “no space left on device”), and to quota-exceeded errors (such as “write failed” or “user block limit reached”).

This section is relevant for writes to MyISAM tables. It also applies for writes to binary log files and binary log index file, except that references to “row” and “record” should be understood to mean “event.”

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 10 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 use mysqladmin kill. The thread is aborted the next time it checks the disk (in one minute).
Other threads might 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 allows the other threads to continue.

Exceptions to the preceding behavior are when you use REPAIR TABLE or OPTIMIZE TABLE or when the indexes are created in a batch after LOAD DATA INFILE or after an ALTER TABLE statement. All of these statements may create large temporary files that, if left to themselves, would cause big problems for the rest of the system. If the disk becomes full while MySQL is doing any of these operations, it removes the big temporary files and mark the table as crashed. The exception is that for ALTER TABLE, the old table is left unchanged.

A.4.4. Where MySQL Stores Temporary Files

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, /var/tmp, or /usr/tmp. If the filesystem containing your temporary file directory is too small, you can use the --tmpdir option to mysqld to specify a directory in a filesystem where you have enough space.

In MySQL 5.0, the --tmpdir option can be set to a list of several paths that are used in round-robin fashion. Paths should be separated by colon characters (‘:’) on Unix and semicolon characters (‘;’) on Windows, NetWare, and OS/2. Note: To spread the load effectively, these paths should be located on different physical disks, not different partitions of the same disk.

If the MySQL server is acting as a replication slave, you should not set --tmpdir to point to a directory on a memory-based filesystem or to a directory that is cleared when the server host restarts. A replication slave needs some of its temporary files to survive a machine restart so that it can replicate temporary tables or LOAD DATA INFILE operations. If files in the temporary file directory are lost when the server restarts, replication fails.

MySQL creates all temporary files as hidden files. This ensures that the temporary files are removed if mysqld is terminated. The disadvantage of using hidden files is that you do 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 required is determined by the following expression:

(length of what is sorted + sizeof(row pointer))
* number of matched rows
* 2

The row pointer size is usually four bytes, 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.

A.4.5. How to Protect or Change the MySQL Unix Socket File

The default location for the Unix socket file that the server uses for communication with local clients is /tmp/mysql.sock. (For some distribution formats, the directory might be different, such as /var/lib/mysql for RPMs.)

On some versions of Unix, anyone can delete files in the /tmp directory or other similar directories used for temporary files. If the socket file is located in such a directory on your system, this might cause problems.

On most versions of Unix, you can protect your /tmp directory so that files can be deleted only by their owners or the superuser (root). To do this, set the sticky bit on the /tmp directory by logging in as root and using the following command:

shell> chmod +t /tmp

You can check whether the sticky bit is set by executing ls -ld /tmp. If the last permission character is t, the bit is set.

Another approach is to change the place where the server creates the Unix socket file. If you do this, you should also let client programs know the new location of the file. You can specify the file location in several ways:

Specify the path in a global or local option file. For example, put the following lines in /etc/my.cnf:
```
[mysqld]
socket=/path/to/socket

[client]
socket=/path/to/socket
```
See Section 4.3.2, “Using Option Files”.
Specify a --socket option on the command line to mysqld_safe and when you run client programs.
Set the MYSQL_UNIX_PORT environment variable to the path of the Unix socket file.
Recompile MySQL from source to use a different default Unix socket file location. Define the path to the file with the --with-unix-socket-path option when you run configure. See Section 2.9.2, “Typical configure Options”.

You can test whether the new socket location works by attempting to connect to the server with this command:

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

A.4.6. Time Zone Problems

If you have a problem with SELECT NOW() returning values in UTC and not your local time, you have to tell the server your current time zone. The same applies if UNIX_TIMESTAMP() returns the wrong value. This should be done for the environment in which the server runs; for example, in mysqld_safe or mysql.server. See Appendix F, Environment Variables.

You can set the time zone for the server with the --timezone=timezone_name option to mysqld_safe. You can also set it by setting the TZ environment variable before you start mysqld.

The allowable values for --timezone or TZ are system-dependent. Consult your operating system documentation to see what values are acceptable.