Bug #18191 Server unable to start after a MYSQL config wizard (db corrupted)
Submitted: 13 Mar 2006 15:27 Modified: 25 Mar 2006 9:06
Reporter: [ name withheld ] Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.18 OS:Microsoft Windows (XP or 2000)
Assigned to: CPU Architecture:Any

[13 Mar 2006 15:27] [ name withheld ]
Description:
Ok, it's not a misuse of the DB. I post this pb some time ago, I had the answer "see the manual".
This occured randomly, and under 2000 or XP. When this occurs, you have to reinstall MYSQl and you lose your work. I hope it's a bug, not a feature.
-----
Server can't start after a re-configuration using the Configuration Wizard 

I Have the following trace if the ".err" file:

InnoDB: The first specified data file .\ibdata1 did not exist:
InnoDB: a new database to be created!
060313 15:42:17  InnoDB: Setting file .\ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Error: all log files must be created at the same time.
InnoDB: All log files must be created also in database creation.
InnoDB: If you want bigger or smaller log files, shut down the
InnoDB: database and make sure there were no errors in shutdown.
InnoDB: Then delete the existing log files. Edit the .cnf file
InnoDB: and start the database again.
060313 15:42:17 [ERROR] Default storage engine (InnoDB) is not available
060313 15:42:17 [ERROR] Aborting

060313 15:42:17 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: Shutdown complete

060313 15:42:33  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...
060313 15:42:33  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 99729.
InnoDB: Doing recovery: scanned up to log sequence number 0 99729
InnoDB: Page directory corruption: supremum not pointed to
060313 15:42:33  InnoDB: Page dump in ascii and hex (16384 bytes):
 len 16384; hex 00000 ..... [ A LOT OF ZERO LIKE THOSE];
asc     ... [A LOT OF SPACES LIKE THOSE]
;InnoDB: End of page dump
060313 15:42:33  InnoDB: Page checksum 1575996416, prior-to-4.0.14-form checksum 1371122432
InnoDB: stored checksum 0, prior-to-4.0.14-form stored checksum 0
InnoDB: Page lsn 0 0, low 4 bytes of lsn at page end 0
InnoDB: Page number (if stored to page already) 0,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
InnoDB: Page directory corruption: supremum not pointed to
060313 15:42:33  InnoDB: Page dump in ascii and hex (16384 bytes):
 len 16384; hex 00000 ..... [ A LOT OF ZERO LIKE THOSE]
asc                 ... [A LOT OF SPACES LIKE THOSE]
;InnoDB: End of page dump
060313 15:42:33  InnoDB: Page checksum 1575996416, prior-to-4.0.14-form checksum 1371122432
InnoDB: stored checksum 0, prior-to-4.0.14-form stored checksum 0
InnoDB: Page lsn 0 0, low 4 bytes of lsn at page end 0
InnoDB: Page number (if stored to page already) 0,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
060313 15:42:33 [ERROR] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: Got signal 11. Aborting!

060313 15:42:33 [ERROR] Aborting

060313 15:42:33 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: Shutdown complete

How to repeat:
Install a fresh MYSql server under Win XP. Use the Config wizard with the default values.
Create a simple table, populate it, let it live a certain amount of time, use it with some "select" ...
Restart the Config wizard and use the default. At the end, it will try to restart the MYSQL server, a red cross will be displayed, with an error code = 0 (!!!)
The dump of the log error is just above.

Suggested fix:
Reinstall MYSql and lose your old DB ...
[13 Mar 2006 15:36] Valeriy Kravchuk
Please, explain, why are you reconfiguring the working server? And, even more importantly: what exact steps do you perform upon reconfiguration? Describe all the clicks and parameters entered.
[13 Mar 2006 15:47] Heikki Tuuri
Hi!

What probably happened is that the new MySQL server had its datadir in a new location. I have noticed that the default datadir of 5.0.18 now seems to be something like:

C:\Program Files\MySQL\MySQL Server 5.0\data\

When the config wizard started mysqld, InnoDB saw that no ibdata1 file was in the datadir, and created a new one. But for some reason the mysqld server WAS able to see the old ib_logfiles. InnoDB complained about this, since it does not make sense to use old ib_logfiles with a new ibdata file!

The next time you started mysqld, InnoDB thought that it was recovering after a crash because now there was a new empty zero-filled ibdata1, and also ib_logfiles. But since ibdata1 is is zero-filled, InnoDB notices that it does not contain sensible information and prints the error about a corrupt ibdata file.

Your original InnoDB data was not lost. You have to find the old ibdata1 file, and use it along with the correct ib_logfiles. Then everything should be ok again.

The bug in this is why the config wizard allows you to reconfigure an existing InnoDB installation, or why it does not know about the old ibdata files.

From which MySQL version did you upgrade?

Can you find your old ibdata1 file and ib_logfiles?

Are you sure that you did not manually move or delete any InnoDB files?

If you need to add new ibdata files or change the size of ib_logfiles, you should do it manually according to the instructions at:

http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html

I would not trust any wizard in that operation.

Regards,

Heikki
[14 Mar 2006 8:15] [ name withheld ]
Hi,
According that I've reinstalled MYSql and deleted old dirs, I can't tell you where was the "ib_logfiles" files.
But before, I detected that it was the file ibdata1 that was the most sensible. When I reinstall MYSQl server, I replaced "ibdata1" with the old one, the same pb occured. But ib_logfile0 and ib_logfile1 replaced do not prevent the server to start.

With your explaination, I think this may occur when I select a dir (the first time I run the wizard), that is not the default one.
Now, I keep the default one and and can't repeat this problem.
But it doens't shows there's no pb now....

But this pb it's not due to an upgrade. I had this pb under 5.0.17 (I start with this version), also 5.0.18, independently, on different OS and machines.
The reason why I reconfigure was to change encoding (utf8) and change the root password.

Next time, I will use the manual configuration method.

Many thanks.
[16 Mar 2006 11:22] Valeriy Kravchuk
So, can I close this report as not a bug in server, really?
[24 Mar 2006 16:28] Sam
I think I'll just add my two cents to clarify... as I accidentally posted them in a closed bug report from mid July last year before. :P

I installed a fresh mysql and decided to reconfigure for whatever reason.
Version: 5.0.19-nt

This seems not to be a server bug, but a configure wizard bug. Luckily, on production servers, I've never used the GUI wizard!

FWIW:
-----
I have experienced the same problem and have managed to reproduce and fix the
problem.
This problem occurs when you, given the option to change the InnoDB storage
path, choose anything else than "Installation Path".
What happens if you don't is; the appropriate path is created and ibdata1 is
placed within.
Additionally, ibdata1, ib_logfile0 and ib_logfile1 is created under
InstallationPath\data
When reconfiguring (just clicking through all options and respecifying the
password), starting the service fails and leaves a message in the log
InstallationPath\computername.err:
060324 17:13:58  InnoDB: Setting file C:\Data\ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Error: all log files must be created at the same time.
InnoDB: All log files must be created also in database creation.
InnoDB: If you want bigger or smaller log files, shut down the
InnoDB: database and make sure there were no errors in shutdown.
InnoDB: Then delete the existing log files. Edit the .cnf file
InnoDB: and start the database again.
060324 17:13:59 [ERROR] Default storage engine (InnoDB) is not available
060324 17:13:59 [ERROR] Aborting

The windows event log system says mysqld-nt got a signal 11.

The issue was solved by manually removing InstallationPath\data\ib_data1,
ib_logfile0 and ib_logfile1 before reconfiguring the server.
[25 Mar 2006 9:06] [ name withheld ]
OK, that's cool. If it's just a wizzard bug, I will not use it again and use manual conf instead.
I've not got any pb now.
This subject can be closed (in my opinion)
May be a reliable procedure could be provided to recover the DB in this case. I've seen other despaired people having the same pb on the web.

Many thanks to those who take time to help on this pb.

Francillo