Submitted by roadskater on December 14, 2007 - 5:40am.
I was working along on a MySQL project for a skater who has a software business, and things were going well. I went off to work on other projects and to clean some files up on my server, backup and development pc drives, all Windows XP gear, then knocked off for zzz time.
The next day I installed some MySQL-related software and after the installation finished, nothing was working...not just the new software, the website I was developing. I was so mad at that software! I knew I could get back to where I had been, had done an anti-virus scan on the install files, and knew I had restore points and backups and all that...none of it as recent as I would have liked, but OK....
I checked the roadskater.net/index.htm pages to see if the server software was working OK and it was (not the live but the dev version running on the devtop). But index.php wasn't letting me in and informed me that MySQL was likely not active.
...
Can't connect to MySQL server on 'localhost' (10061).
I visited Start/Administration Tools/Services to see that the service was not running, and tried to start it again. The reply was:
...
Could not start the MySQL service on Local Computer.
Error 1067: The process terminated unexpectedly.
Aargh! Next, I took a look in the "data" directory that is a default folder of the "MySQL Server x.x" directory (4.1 or 5.0 in my experience, and in this case 5.0) to find the ".err" file. On my XP box, the first part of the filename is the same as the "Computer Name" I gave my system.
The .err file had other stuff but this was the last part [dates here are massaged to date of writing]:
...
071214 2:50:43 InnoDB: Started; log sequence number 0 2025506856
071214 2:50:43 [ERROR] Fatal error: Can't open and lock privilege tables: File '.\mysql\host.MYD' not found (Errcode: 2)
Ok that's a great clue! So I looked for the host.MYD file and indeed it was not there. It also wasn't in other copies of "data" directories I had squirreled away on that machine. Yikes! It was too much to go to another room to look at another machine, of course. Better to bullheadedly go on.
Fear crept in as I thought, well that's a file the MySQL must create or at least maintain and I have no idea how to create that or put the right information in it. I thought well if there's no host.MYD here there won't be one on my other kits either.
I tried a "repair" installation. Forget about it. That was a waste of time. Not sure why not!
I downloaded the no-installer version of the files, and there was host.MYD in there, but it was just a zero-length file.
Then I decided to look at the dates on all the files in my "data/mysql" directory (inside the "mysql server x.x" directory...on your machine, whatever directory you picked as the install directory).
I found out that only three of my files had changed since installation:
This gave me hope that I might not need to do some complicated restore point or file restore or whatever organized and intelligent people do, and that I might get it going quickly anyway.
Not being overly smart, I didn't think to compare the number of files in that directory (duh!) but decided to copy ALL BUT these three changed-by-me-since-installation files over from the freshly downloaded installation files in the "data/mysql" folder. I thought there's no way this could hurt.
"Wallah," as I recently read on the net. Fixed! The .err file sagt:
...
071214 3:15:26 InnoDB: Started; log sequence number 0 2025506856
071214 3:15:26 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: ready for connections.
Version: '5.0.45-community-nt' socket: '' port: 3306 MySQL Community Edition (GPL)
Why? Well here's what I think happened to me, and maybe it'll help you.
I believe what happened had nothing to do with the MySQL utility program I had installed. I think it was my fault and it happed the night before, but I didn't know it until I was working with MySQL on the devel pc again.
While checking for duplicate files and folders on my three setups, somewhere along the way I vaguely recall a checkbox about zero-length files. I think I must have checked that box without thinking or knowing enough about it. Or maybe it was using CCleaner or some other nifty utility that should only be used in the cold light of afternoon with at least one good tall espresso Americano down the hatch.
It seems I could have simply used a text editor to create a zero-length data/mysql/host.MYD file! But there are other files that might be needed too, so it's quicker just to copy all the files you haven't touched from the distrib into the live devel copy (after backups lalala) to make sure you have all of those zero-length files from the distribution and that no other files are corrupt.
Good luck!
It turns out that in my download install package there are eleven zero-length files in the standard distribution (in the "data/mysql" subfolder):
...
07/04/2007 03:49 PM 0 time_zone_leap_second.MYD
07/04/2007 03:49 PM 0 time_zone.MYD
07/04/2007 03:49 PM 0 func.MYD
07/04/2007 03:49 PM 0 time_zone_name.MYD
07/04/2007 03:49 PM 0 tables_priv.MYD
07/04/2007 03:49 PM 0 procs_priv.MYD
07/04/2007 03:49 PM 0 time_zone_transition.MYD
07/04/2007 03:49 PM 0 host.MYD
07/04/2007 03:49 PM 0 columns_priv.MYD
07/04/2007 03:49 PM 0 time_zone_transition_type.MYD
07/04/2007 03:49 PM 0 proc.MYD
Another way InnoDB could be the reason MySQL won't start...
If you look in your .err file inside the "data" subfolder of your MySQL install directory and see this:
...
InnoDB: Error: log file .\ib_logfile0 is of different size 0 25165824 bytes
InnoDB: than specified in the .cnf file 0 29360128 bytes!
071120 18:36:07 [ERROR] Can't init databases
071120 18:36:07 [ERROR] Aborting
Maybe you were performance tweaking without really knowing all the ramifications and munged the config file to change the size of the InnoDB log files? You'd think that would be something the software would allow for and perhaps prompt you about on startup!?
Regardless, if you think this is your problem, give this a try: move the index files (not the data file ibdata1) in your "data" directory that look like the following somewhere safe:
Then try to start the MySQL service again.
Again, DO NOT delete ibdata1 unless you REALLY know what you're doing. For example, if your ibdata1 has blown up in size because of some access log or other data capture you let log for too long, you'll eventually realize you'll not get that space back when you delete the data in the tables, as InnoDB is an evergrowing data file. You can very carefully fix this by exporting your data to SQL statements, killing the data files and index files, and importing the data back. This is "beyond the scope of this article" and there are several resources for this which you may easily catch via search engine fishing, one of them being:
http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html
Regardless, after you move ib_logfile0 and ib_logfile1, the .err file will create new log files and will celebrate like this (sizes and other details will differ, and dates changed to day or this writing):
...
071214 20:31:11 InnoDB: Log file .\ib_logfile0 did not exist: new to be created
InnoDB: Setting log file .\ib_logfile0 size to 24 MB
InnoDB: Database physically writes the file full: wait...
071214 20:31:13 InnoDB: Log file .\ib_logfile1 did not exist: new to be created
InnoDB: Setting log file .\ib_logfile1 size to 24 MB
InnoDB: Database physically writes the file full: wait...
071214 20:31:15 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
071214 20:31:15 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 2018347532.
InnoDB: Doing recovery: scanned up to log sequence number 0 2018347532
071214 20:31:15 InnoDB: Started; log sequence number 0 2018347532
071214 20:31:15 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: ready for connections.
Version: '5.0.45-community-nt' socket: '' port: 3306 MySQL Community Edition (GPL)
I hope these two possible solutions will help you with your MySQL startup problems! It's great software but now and then the glitches can be a mystery! It does so much so well, then fails in these crazy simple ways without graceful assistance. Of course, it's free open source software and it is the engine behind a lot of great webstuff!
Bookmark/Search this post with:
Avoid oops.any in your my.ini
Ha! Another one crept in tonight as I was setting the my.ini to output in the Slow Query Log the SQL queries without proper indexes. I used Alt-FS to do a File Save but as often happens, I ended up typing "fs" into the beginning of one of the lines of my.ini. This was enough to make MySQL fail to restart so I lost some visitors in the time it took to find my snafu.
If you're interested in that slow query stuff, part of my my.ini appears thus:
This tells MySQL to output information on the slowest queries. This is useful for creating indexes (indices). In my case, just now, I'm interested in SQL queries that take 2 seconds or longer, except I have added the option to log queries that don't have indexes (indices). These are often ripe candidates for speed improvements as the system has to scan the full table for the desired info. I'll write more about this later after I'm better at it, heh.
The slow query file shows up in the data subfolder of your MySQL installation, and on an XP box it will be named OHSO-slow.log if OHSO is the name of your PC.
InnoDB Status Logging Is Another Helpful Tool
Hey while I'm at it, here's another nice line to add to the #*** INNODB Specific options *** section of your my.ini. Just add:
below the #*** INNODB Specific options *** line and stop and restart the server.
This generates a status file that on Windows XP looks like:
where the last number is a session ID of some sort, so that number is a bit annoying as the "file extension," but OK, it's nice to have this extra information. More on this another day, but get this one going if you use InnoDB. It gives you another window into how the InnoDB engine is working, including cacheing and more.