Troubleshooting a Microsoft Windows MySQL Server Installation
When installing and running MySQL for the first time, you may encounter certain errors that prevent the MySQL server from starting. The purpose of this section is to help you diagnose and correct some of these errors.
Your first resource when troubleshooting server issues is the error log. The MySQL server uses the error log to record information relevant to the error that prevents the server from starting. The error log is located in the data directory specified in your my.ini file. The default data directory location is C:\Program Files\MySQL\MySQL Server 5.1\data, or C:\ProgramData\Mysql on Windows 7 and Windows Server 2008. The C:\ProgramData diectory is hidden by default. You need to change your folder options to see the directory and contents. For more information on the error log and understanding the content, see Section 5.2.2, “The Error Log”.
Another source of information regarding possible errors is the console messages displayed when the MySQL service is starting. Use the NET START MySQL command from the command line after installing mysqld as a service to see any error messages regarding the starting of the MySQL server as a service. See Section 2.3.5.7, “Starting MySQL Server as a Microsoft Windows Service”.
The following examples show other common error messages you may encounter when installing MySQL and starting the server for the first time:
• If the MySQL server cannot find the mysql privileges database or other critical files, you may see these messages:
• System error 1067 has occurred.
Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist
These messages often occur when the MySQL base or data directories are installed in different locations than the default locations (C:\Program Files\MySQL\MySQL Server 5.1 and C:\Program Files\MySQL\MySQL Server 5.1\data, respectively).
This situation may occur when MySQL is upgraded and installed to a new location, but the configuration file is not updated to reflect the new location. In addition, there may be old and new configuration files that conflict. Be sure to delete or rename any old configuration files when upgrading MySQL.
If you have installed MySQL to a directory other than C:\Program Files\MySQL\MySQL Server 5.1, you need to ensure that the MySQL server is aware of this through the use of a configuration (my.ini) file. The my.ini file needs to be located in your Windows directory, typically C:\WINDOWS. You can determine its exact location from the value of the WINDIR environment variable by issuing the following command from the command prompt:
C:\> echo %WINDIR%
An option file can be created and modified with any text editor, such as Notepad. For example, if MySQL is installed in E:\mysql and the data directory is D:\MySQLdata, you can create the option file and set up a [mysqld] section to specify values for the basedir and datadir options:
[mysqld]
# set basedir to your installation path
basedir=E:/mysql
# set datadir to the location of your data directory
datadir=D:/MySQLdata
Note that Windows path names are specified in option files using (forward) slashes rather than backslashes. If you do use backslashes, double them:
[mysqld]
# set basedir to your installation path
basedir=C:\\Program Files\\MySQL\\MySQL Server 5.1
# set datadir to the location of your data directory
datadir=D:\\MySQLdata
The rules for use of backslash in option file values are given in Section 4.2.3.3, “Using Option Files”.
If you change the datadir value in your MySQL configuration file, you must move the contents of the existing MySQL data directory before restarting the MySQL server.
• If you reinstall or upgrade MySQL without first stopping and removing the existing MySQL service and install MySQL using the MySQL Config Wizard, you may see this error:
Error: Cannot create Windows service for MySql. Error: 0
This occurs when the Config Wizard tries to install the service and finds an existing service with the same name.
One solution to this problem is to choose a service name other than mysql when using the configuration wizard. This enables the new service to be installed correctly, but leaves the outdated service in place. Although this is harmless, it is best to remove old services that are no longer in use.
To permanently remove the old mysql service, execute the following command as a user with administrative privileges, on the command-line:
C:\> sc delete mysql
[SC] DeleteService SUCCESS
If the sc utility is not available for your version of Windows, download the delsrv utility from http://www.microsoft.com/windows2000/techinfo/reskit/tools/existing/delsrv-o.asp and use the delsrv mysql syntax.
C.5.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, this is insecure. For instructions on assigning passwords, see Section 2.12.2, “Securing the Initial MySQL Accounts”.
If you know the root password, but want to change it, see Section 12.7.1.6, “SET PASSWORD Syntax”.
If you set a root password previously, but have forgotten it, you can set a new password. The following sections provide instructions for Windows and Unix systems, as well as generic instructions that apply to any system.
C.5.4.1.1. Resetting the Root Password: Windows Systems
On Windows, use the following procedure to reset the password for all MySQL root accounts:
1. Log on to your system as Administrator.
2. Stop the MySQL server if it is running. For a server that is running as a Windows service, go to the Services manager: From the Start menu, select Control Panel, then Administrative Tools, then Services. 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.
3. Create a text file containing the following statements. Replace the password with the password that you want to use.
4. UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;
Write the UPDATE and FLUSH statements each on a single line. The UPDATE statement resets the password for all root accounts, and the FLUSH statement tells the server to reload the grant tables into memory so that it notices the password change.
5. Save the file. For this example, the file will be named C:\mysql-init.txt.
6. Open a console window to get to the command prompt: From the Start menu, select Run, then enter cmd as the command to be run.
7. Start the MySQL server with the special --init-file option (notice that the backslash in the option value is doubled):
8. C:\> C:\mysql\bin\mysqld --init-file=C:\\mysql-init.txt
If you installed MySQL to a location other than C:\mysql, adjust the command accordingly.
The server executes the contents of the file named by the --init-file option at startup, changing each root account password.
You can also add the --console option to the command if you want server output to appear in the console window rather than in a log file.
If you installed MySQL using the MySQL Installation Wizard, you may need to specify a --defaults-file option:
C:\> "C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld.exe"
--defaults-file="C:\\Program Files\\MySQL\\MySQL Server 5.1\\my.ini"
--init-file=C:\\mysql-init.txt
The appropriate --defaults-file setting can be found using the Services Manager: From the Start menu, select Control Panel, then Administrative Tools, then Services. Find the MySQL service in the list, right-click it, and choose the Properties option. The Path to executable field contains the --defaults-file setting.
9. After the server has started successfully, delete C:\mysql-init.txt.
You should now be able to connect to the MySQL server as root using the new password. 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.
C.5.4.1.2. Resetting the Root Password: Unix Systems
On Unix, use the following procedure to reset the password for all MySQL root accounts. The instructions assume that you will start the server so that it runs using the Unix login account that you normally use for running the server. For example, if you run the server using the mysql login account, you should log in as mysql before using the instructions. Alternatively, you can log in as root, but in this case you must start mysqld with the --user=mysql option. If you start the server as root without using --user=mysql, the server may create root-owned files in the data directory, such as log files, and these may cause permission-related problems for future server startups. If that happens, you will need to either change the ownership of the files to mysql or remove them.
1. Log on to your system as the Unix user that the mysqld server runs as (for example, mysql).
2. Locate the .pid file that contains the server's process ID. The exact location and name of this file depend on your distribution, host name, and configuration. Common locations are /var/lib/mysql/, /var/run/mysqld/, and /usr/local/mysql/data/. Generally, the file name has an extension of .pid and begins with either mysqld or your system's host name.
You can stop the MySQL server by sending a normal kill (not kill -9) to the mysqld process, using the path name of the .pid file in the following command:
shell> kill `cat /mysql-data-directory/host_name.pid`
Use backticks (not forward quotation marks) with the cat command. These cause the output of cat to be substituted into the kill command.
3. Create a text file containing the following statements. Replace the password with the password that you want to use.
4. UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;
Write the UPDATE and FLUSH statements each on a single line. The UPDATE statement resets the password for all root accounts, and the FLUSH statement tells the server to reload the grant tables into memory so that it notices the password change.
5. Save the file. For this example, the file will be named /home/me/mysql-init. The file contains the password, so it should not be saved where it can be read by other users. If you are not logged in as mysql (the user the server runs as), make sure that the file has permissions that permit mysql to read it.
6. Start the MySQL server with the special --init-file option:
7. shell> mysqld_safe --init-file=/home/me/mysql-init &
The server executes the contents of the file named by the --init-file option at startup, changing each root account password.
8. After the server has started successfully, delete /home/me/mysql-init.
You should now be able to connect to the MySQL server as root using the new password. Stop the server and restart it normally.
C.5.4.1.3. Resetting the Root Password: Generic Instructions
The preceding sections provide password-resetting instructions for Windows and Unix systems. Alternatively, on any platform, you can set the new password using the mysql client (but this approach is less secure):
1. Stop mysqld and restart it with the --skip-grant-tables option. This enables anyone to connect without a password and with all privileges. Because this is insecure, you might want to use --skip-grant-tables in conjunction with --skip-networking to prevent remote clients from connecting.
2. Connect to the mysqld server with this command:
3. shell> mysql
4. Issue the following statements in the mysql client. Replace the password with the password that you want to use.
5. mysql> UPDATE mysql.user SET Password=PASSWORD('MyNewPass')
6. -> WHERE User='root';
7. mysql> FLUSH PRIVILEGES;
The FLUSH statement tells the server to reload the grant tables into memory so that it notices the password change.
You should now be able to connect to the MySQL server as root using the new password. Stop the server, then restart it normally (without the --skip-grant-tables and --skip-networking options).
C.5.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.2.2, “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 MySQL Internals: Porting. 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 file system 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 is 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, MySQL Server Administration.
• Start mysqld with the general query log enabled (see Section 5.2.3, “The General Query Log”). Then 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.2.3, “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.7, “How to Report Bugs or Problems”.
• Try to make a test case that we can use to repeat the problem. See MySQL Internals: Porting.
• Try running the tests in the mysql-test directory and the MySQL benchmarks. See Section 21.1.2, “The 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 MySQL Internals: Porting.
• 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:
a. Start mysqld from gdb (or another debugger). See MySQL Internals: Porting.
b. Run your test scripts.
c. 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:
d. backtrace
e. info local
f. up
g. info local
h. 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.7, “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 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 your memory and disk subsystems when troubleshooting hardware.
C.5.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 Section 2.14, “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 permissible values for --timezone or TZ are system dependent. Consult your operating system documentation to see what values are acceptable.
C.5.4.4. Where MySQL Stores Temporary Files
On Unix, MySQL uses the value of the TMPDIR environment variable as the path name of the directory in which to store temporary files. If TMPDIR is not set, MySQL uses the system default, which is usually /tmp, /var/tmp, or /usr/tmp.
On Windows, Netware and OS2, MySQL checks in order the values of the TMPDIR, TEMP, and TMP environment variables. For the first one found to be set, MySQL uses it and does not check those remaining. If none of TMPDIR, TEMP, or TMP are set, MySQL uses the Windows system default, which is usually C:\windows\temp\.
If the file system containing your temporary file directory is too small, you can use the --tmpdir option to mysqld to specify a directory in a file system where you have enough space.
In MySQL 5.1, 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 file system 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 file system 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.
C.5.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
• 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.11.4, “MySQL Source-Configuration 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
