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
. 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.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.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, you must 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
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 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:
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.
User Comments
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.
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.
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
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.
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
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.
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.
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`
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.
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
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.
(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
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.
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.
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.