Bug #5845 Restart fails after reconfiguration with new Windows config wizard
Submitted: 1 Oct 2004 12:14 Modified: 5 May 2009 5:20
Reporter: David Powers Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Config Wizard Severity:S1 (Critical)
Version:5.0, 5.1, 5.4, 6.0 OS:Windows (Windows XP)
Assigned to: CPU Architecture:Any

[1 Oct 2004 12:14] David Powers
Description:
MySQL Server fails to start after using the MySQL Server Instance Configuration Wizard 1.0.0 to change any settings.

How to repeat:
Installation of 4.1.5-gamma using Windows Essentials and MySQL Server Instance Configuration Wizard 1.0.0 works fine. If the Configuration Wizard is used, however, to reconfigure any of the settings, the final dialog box reports "The service could not be started. Error: 0".

Opening a Windows Command Prompt and running "net start mysql" reports that the service is started successfully, but mysqld fails to run. I suspected a corruption of the my.cnf file, but removing it makes no difference. Also removing the mysql service from Windows and attempting to start mysqld manually results in failure.

Only solution appears to be a complete reinstall. I have tested this about half a dozen times, and get the same problem each time.

I am testing this for a forthcoming book to be published by Apress/friends of ED, so would like to get to the bottom of this ASAP.
[1 Oct 2004 13:48] MySQL Verification Team
Please send us the image path of the service using the SCM -> properties
something like the below example:

"E:\MySQL\MySQL Server 4.1\bin\mysqld-nt" --defaults-file="E:\MySQL\MySQL Server 4.1\my.cnf" MySQLNew

and the output of:

mysqld-nt --defaults-file="E:\MySQL\MySQL Server 4.1\my.cnf" --standalone --console

when performed from the \bin directory on DOS prompt screen.

Thanks
[1 Oct 2004 15:06] David Powers
I performed the requested tasks with a fresh install of 4.1.5-gamma-win.msi. The first task produced this:
--
C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-nt: Too many arguments (first extra is 'MySQLNew')
--
The second task produced this:
--
041001 15:37:36 InnoDB: Started log sequence number 0 1477132
mysqld-nt: ready for connections.
Version: '4.1.5-gamma-nt'  socket: '' port 3306  Source distribution
--
I did a clean shutdown using mysqladmin shutdown. I then ran the Configuration wizard, and as on all previous occasions, it reported not being able to start the server. I subsently ran the second task again. The output this time was a screen dump (mainly 0), followed by:
--
InnoDB: End of page dump
041001 15:47:13 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
041001 15:47:13  [ERROR] mysqld-nt: Got signal 11. Aborting!

041001 15:47:13  [ERROR] Aborting

041001 15:47:13  [NOTE] mysqld-nt: Shutdown complete
--
The content of the .err file was as follows:
--
041001 12:41:58  InnoDB: Log file .\ib_logfile0 did not exist: new to be created
InnoDB: Setting log file .\ib_logfile0 size to 10 MB
InnoDB: Database physically writes the file full: wait...
041001 12:41:58  InnoDB: Log file .\ib_logfile1 did not exist: new to be created
InnoDB: Setting log file .\ib_logfile1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
041001 12:41:59  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...
041001 12:41:59  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 1477132.
InnoDB: Doing recovery: scanned up to log sequence number 0 1477132
041001 12:41:59  InnoDB: Flushing modified pages from the buffer pool...
041001 12:41:59  InnoDB: Started; log sequence number 0 1477132
InnoDB: The first specified data file .\ibdata1 did not exist:
InnoDB: a new database to be created!
041001 15:46:44  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.
041001 15:46:45  [ERROR] Can't init databases
041001 15:46:45  [ERROR] Aborting

041001 15:46:45  [NOTE] C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-nt: Shutdown complete
--
[1 Oct 2004 17:40] MySQL Verification Team
Hi,

Sorry the first task I asked you was just to see the  executable path 
using the Service Control Manager (SCM) and to post here for to analyze,
I showed you just the executable and service name I used for to install
the MySQL stuff on my machine, so:

C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-nt: Too many arguments (first
extra is 'MySQLNew')

isn't a valid test.

However the second task inform us that the reason of the service is not
able to be started is an issue with the InnoDB engine:

041001 15:46:45  [ERROR] Can't init databases
041001 15:46:45  [ERROR] Aborting

So I have more questions:

1- That was a fresh install ?.
2- If yes.. then you have 2 options:
   a- Perform an un-install procedure using the Windows Panel and after
      this to delete all the directories created before and try again a fresh
      install.
   b- You just delete the InnoDB ibdata and iblogs files and try to start
       the service again like InnoDB output showed:

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.

It isn't clear to me is an Installer fails for to happened this, so I
need more information from you for.

Thanks
[1 Oct 2004 18:08] David Powers
Further testing with the config wizard: After removing all contents of the data folder, I performed a completely fresh installation of Windows Essentials, and selected a Non-Transactional Database Only configuration. I then re-ran the configuration wizard, this time selecting Multifunctional Database. However, the InnoDB Tablespace Settings dialog box was greyed out, preventing selection of an alternative location for InnoDB data files. When completing the Wizard, everything started fine.

Another reconfiguration test - this time setting it to Transactional Database Only, produced a failure. Looking in the ERR log, the problem lay in the log files ib_logfile0 and ib_logfile1. Deleting them allowed MySQL to start up successfully.

I hope that helps narrow down the problem and its possible causes.
[1 Oct 2004 18:12] David Powers
Your message timed 10.40am crossed with my latest submission. In answer to your question: yes, it was a completely fresh install. I have tested both with a completely fresh installation, and with just removing the innodb log files.
[4 Oct 2004 18:20] Michael G. Zinner
Template file

Attachment: my-template.ini (application/octet-stream, text), 11.32 KiB.

[4 Oct 2004 18:23] Michael G. Zinner
David, thanks again for reporting this issue and giving such detailed bug information. This bug seems to be related to bug #5816. This bug was caused by a innodb_buffer_pool_size that was too low. We now set it to min. 8M which solved the problem.

It would be excellent if you could verify the fix. I have uploaded the modified my-template.ini. After the file installation there is a my-template.cnf in the installation directory. Replace this one with the one attached to this bug. Please note that you have to rename the file back to .cnf (we now changed to .ini to make editing easier)

Thanks a lot.
[5 Oct 2004 10:43] David Powers
Hi Mike, 

Glad to have been of help. I've tested the replacement version of my-template.cnf, and the configuration wizard is now able to restart the MySQL service successfully. In that sense, this bug can now be closed.

Just a few comments, though...

As I have already indicated elsewhere, I still think it would be a good idea to allow users to choose multifunctional database with MyISAM as the default. Also, the configuration wizard grays out the option to set the location of the InnoDB tablespace when being used to reconfigure an existing installation.

To make sure everything was working, I ran the configuration wizard about half-a-dozen times in quick succession. When I selected non-transactional database only, the InnoDB tablespace option didn't appear (expected behaviour). However, the next time I ran it, and chose Multifunctional database, the tablespace option appeared, but was inactive. I suspect this is because I did not remove ibdata1, ib_logfile0 and ib_logfile1 from the data directory before running the wizard again. This may confuse users, although cramming more instructions in the dialog boxes may confuse them even more.

In spite of my reservations, it's a very useful addition to MySQL, which I'm sure many people will appreciate.
[21 Dec 2004 17:02] Charlie Graham
Just an FYI that I tried installing my sql 4.1.8 tried to run the reconfigure application multiple times and had the same problem as was reported here.  My logs were identical to his, and like the bug reported, I only got it working again once  I deleted my entire mysql install directory (after running the "uninstall" program).  Interestingly, just deleting the database data files and running the "uninstall" program did not fix it.  I had to physically also remove the program install directory. (My database is on a separate drive than the rest of the application)

Hopefully that helps!
[8 Feb 2005 20:50] Gili
Actually, I have found the following problems with the configuration tool:

- Removes "set-variable = max_allowed_packet=25M" if it is set. The configuration tool shouldn't be touching these things...

- Removes "innodb_data_home_dir=<some path>"

The latter might cause startup problems. The former might cause problems down the road.

Also, I found that I had to delete:

ib_logfile0
ib_logfile1
ibdata1

in order for the server to start up properly again...

Gili
[14 Feb 2005 22:54] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[12 Apr 2007 22:26] Manoj Sharma
Hi.  Just downloaded the latest version of MySql 5.0.37 for Windows (mysql-5.0.37-win32.zip).  On running the SETUP.EXE, the installation program fails with the same Error:0 (MySQL service could not be started).

Tried following the steps as documented over here and in other places, but it still does not work.  

But, when I tried running the following commands from the command prompt (as explained in the my.ini file), there was absolutely no problem:

1. mysqld --install MySQLXY --defaults-file="C:\Program Files\MySQL\MySQL Server 5.0\my.ini"

2. mysqld --defaults-file="C:\Program Files\MySQL\MySQL Server 5.0\my.ini"

3. net start MySQLXY

However, when I went to the Services section on My Computer, I found that MySQL and MySQLXY services, both not running at all.  Tried starting them too, but this too did not solve the problem.

Can anyone help me please?

Thanks in advance,

MANOJ KUMAR SHARMA.
[5 May 2009 5:17] Susanne Ebrecht
Verified as described. Starting mysqld not possible with default innodb settings.
[5 May 2009 5:20] Susanne Ebrecht
I will set this as duplicate of bug #42820 because there are more detailed informations.