Skip navigation links
MySQL 5.0 Reference Manual :: 2 Installing and Upgrading MySQL :: 2.9 Installing MySQL on Windows :: 2.9.13 Troubleshooting a MySQL Installation Under Windows
2.9.14 Upgrading MySQL on Windows »
Section Navigation      [Toggle]

2.9.13. Troubleshooting a MySQL Installation Under Windows

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.0\data. See Section 5.2.1, “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.9.11, “Starting MySQL as a 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.0 and C:\Program Files\MySQL\MySQL Server 5.0\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.0, 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: 

    shell> 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.0
# 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.

    See Section 2.9.7, “Creating an Option File”.

  • If you reinstall or upgrade MySQL without first stopping and removing the existing MySQL service and install MySQL using the MySQL Configuration Wizard, you may see this error: 

    Error: Cannot create Windows service for MySql. Error: 0

    This occurs when the Configuration 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 allows 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: 

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


User Comments

Posted by Keoghan Litchfield on June 29 2004 1:47pm[Delete] [Edit]

when using a different data directory e.g. d:/mysql/data I initially had problems.

I checked everything suggested on this site and everywhere else. Eventually I found the problem was linked to the permissions on the local machine. I had to change the user and password for the service to mine instead of the system for it to work.

Posted by Alex Frase on August 8 2004 1:52pm[Delete] [Edit]

I also had problems when installing 4.0.20 as a service under Windows 2000 Professional SP4. When I started 'mysqld-opt' from the command-line (leaving its console window open until I was done), it ran fine. But when I used 'mysqld-opt --install' to install and run MySQL as a service, it would not start correctly; about 10 seconds after starting the service, it would crash with a memory read error ("The instruction at 0x77------ referenced memory at 0x000000--, etc").

Although the SYSTEM user account has full privileges to the MySQL install directory and all subdirectories, I found that by changing the MySQL service to run as my 'administrator' account instead of 'local system', the service ran fine. I still do not know why it crashed when running as SYSTEM.

Posted by Chris Escalante on September 15 2004 12:47pm[Delete] [Edit]

We changed the service to admin, and now we have a data folder that has grown from 3megs to 34 gigs. The files are all #sql-...frm #sql-...MYD #sql-...MYI, Users might want to beware

Posted by William Zandvliet on September 17 2004 8:55pm[Delete] [Edit]

To run MySQL (4.0.20a) as a service on Win XP pro (SP2) using normal user privileges:

-Create a new user (e.g. MySQLservice)
-In Services, open the [Log on] tab of the MySQL properties and set Logon user to MySQLservice.
-If not automatically done so, using Local Security Policy or Group Policy console assign MySQLservice the right to Log on as a Service
-Make sure MySQLservice has appropriate NTFS settings for its Data dir
-In my case, it was also necessary to assign MySQLservice user the NTFS create files/write data privilege for C:\ (using "apply to this folder only" option). MySQL seems to create two zero byte temporary files at that location once it is up and running.

Posted by Mike Rabbitt on November 20 2004 10:49am[Delete] [Edit]

I have just upgraded form 3.23 to 4.1 - liked the new MSI GUI ,very nice - had a problem when I copied the old users across tho - I could only connect (localhost only) with root -

After setting the old_passwords variable to YES in my.ini it was all ok ....

cheers

Rab

Posted by Gerdo Lansing on November 3 2005 10:38am[Delete] [Edit]

We have version 4.1.13 on Windows XP SP2, but still received the message that the service could not be started: mysql error:0.

The upgrade procedure describes to remove the service manually before starting the installation if you have MySQL version 4.1.5 or below, but I suggest you always remove de service manually.

Posted by P M on November 17 2005 7:04am[Delete] [Edit]

I was having a problem trying to start the mysqld-nt server (version 5.0.15) on win2k pro. The error log kept saying: Can't open privilege tables, table 'mysql.host' doesn't exist.

After reading all the tips regarding a) moving the data files, and b) creating a dedicated user (MySQLservice) to log on to the MySQL service (which I did with no results), I found my problem was not renaming the [mysqld] to [mysqld-nt] in my "my.ini" file. This solved it for me.

Posted by richard lee on November 21 2005 12:59pm[Delete] [Edit]

If your running the test commands and getting the "Access denied for user 'ODBC'@localhost' " error it is because you are trying to connect to the MySQL database server as an anonymous user who doesnt have access to the specified database. You must use the root user:

c:\mysql\bin\mysqlshow -u root `database_name`

Posted by Normann Aa. Nielsen on November 23 2005 12:54pm[Delete] [Edit]

I had a problem while upgrading MySQL from ver. 4.1.10a-nt to ver. 5.0.16 on a Win2K-system. Reason was, that the server did not read the my.ini-file even though it was present in the WINNT-directory, but instead it used the my.ini file from the mysql-directory.

I also had to rename the [mysqld] to [mysqld-nt] in my "my.ini" file, as well as delete the ib_logfile0 and ib_logfile1 in my data-area to get things running.

Posted by James Moyle on March 3 2006 10:45am[Delete] [Edit]

If you think you have succesfully installed MySQL 5.x, create 2 Databases, create 2+ tables in one database and try to copy both to the second database, if there is an error copying the first database but u can copy the second one alone, you must uninstall MySQL and re-install, the corrupted databases cannot be repaired even when imported into a fresh installation.

A tip when re-installing is to backup any error free databases, un-install all instances and stop & delete all services, un-install MySQL completely, move your old databases to a temporary folder and delete all previous traces, including subdirectories and any remainng databases. Now you can do a clean install, this will minimize on any issues during installation, including the annoying "cannot start service" err msg.

I personally install to a directory path that contains no spaces, if your running an Apache server and PHP this is more critical since sometimes spaces can cause issues. I still use only up to 8 standard characters per sub-directory (just for backwards compatability with older (much older) systems).

Hope this helps

Posted by [name withheld] on April 19 2006 10:12pm[Delete] [Edit]

If you are just trying to run the service on your local machine (XP pro - for me) make sure you download the full installation package NOT just the windows essentials. I was trying to get this to work for a long time, following everyone else's suggestions until I finally uninstalled and deleted everything and tried with the full ~35mb file. Make sure you stop the old service too.

Posted by Bob Stein on May 23 2006 11:43am[Delete] [Edit]

(This was solved thanks to the tip above about checking "console messages" but I didn't see the exact error message covered anywhere so I thought I'd put it here so it'd be searchable.)

After upgrading to 5.1 in Windows this mystifying error...

ERROR 1017 (HY000): Can't find file: 't' (errno: 2)

(about a table 't' that should be there) ...turned out to have the same cause as this other SEEMINGLY unrelated error message from the console (mysqld --console):

InnoDB: Error: log file .\ib_logfile0 is of different size 0 10485760 bytes
InnoDB: than specified in the .cnf file 0 5242880 bytes!

And the solution to both (for me) was a line in my.ini:

innodb_log_file_size=10M

Posted by Héctor Hugo Huergo on February 21 2007 12:56am[Delete] [Edit]

THIS WORK -> THANKS!

Posted by Normann Aa. Nielsen on November 23 2005 12:54pm [Delete] [Edit]

I had a problem while upgrading MySQL from ver. 4.1.10a-nt to ver. 5.0.16 on a Win2K-system. Reason was, that the server did not read the my.ini-file even though it was present in the WINNT-directory, but instead it used the my.ini file from the mysql-directory.

I also had to rename the [mysqld] to [mysqld-nt] in my "my.ini" file, as well as delete the ib_logfile0 and ib_logfile1 in my data-area to get things running.

Posted by Ronald Trask on May 7 2007 3:06am[Delete] [Edit]

I installed from the mysql-noinstall-5.0.37-win32.zip, and ran into problems where the daemon would not run as a windows service, but would run fine under my user account. It first complained about not being able to find or read the ./ibdata1

I then deleted the directory, and re-extracted the zip file. I did not attempt to start it as my user, but immediately attempted to start it as a service. This got farther, but it complained about not being able to find or open the ./mysql/host.frm file. I noticed that the ibdata1 file was now owned by ????????? with a group of SYSTEM. This gave me the solution, I changed the group ownership of the mysql directory and all of its contents to SYSTEM, and everything seems to work fine now.

Posted by Peter MacKenize on March 15 2010 2:22am[Delete] [Edit]

Using Win 7, the path C:\ProgramData is hidden by default. Change folder options to show hidden files and folders to see this directory.
(organize -> folder and search options -> show hidden folders)

Add your own comment.