Bug #42820 MySQL not starting due to InnoDB failure
Submitted: 13 Feb 2009 9:38 Modified: 4 Oct 2012 6:53
Reporter: Sören Zimmermann Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Installing Severity:S1 (Critical)
Version:4.1, 5.0, 5.1, 5.4, 6.0 OS:Microsoft Windows (WinXP Prof SP3)
Assigned to: Assigned Account
Triage: Triaged: D2 (Serious)

[13 Feb 2009 9:38] Sören Zimmermann
Description:
I downloaded and installed the MySQL-Server (mysql-5.1.31-win32.msi).
Then I started the Config wizard and selected the following configuration:
Server machine, multifunctional database, default InnoDB tablespace (C:, Installation path), decision support, TCP enabled (port 3306), strict mode enabled, standard character set.
After finishing the wizard, MySQL didn't start and showed the message: "The service could not be started. Error: 0".

First I deinstalled and installed the server again, but the error returned.

Searching the bug area I found the hint to start manually. Here's the result:
C:\Programme\MySQL\MySQL Server 5.1\bin>mysqld --console
InnoDB: Error: log file .\ib_logfile0 is of different size 0 178257920 bytes
InnoDB: than specified in the .cnf file 0 25165824 bytes!
090212 16:23:29 [ERROR] Plugin 'InnoDB' init function returned error.
090212 16:23:29 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

090212 16:23:29 [ERROR] Unknown/unsupported table type: INNODB
090212 16:23:29 [ERROR] Aborting

090212 16:23:29 [Warning] Forcing shutdown of 1 plugins
090212 16:23:29 [Note] mysqld: Shutdown complete

After I found out that InnoDB deals with the transaction handling, I reconfigured MySQL as "Non-Transactional database Only" and now ist starts.

How to repeat:
Changing the configuration between "Multifunctional Database" and "Non-Transactional database Only" will cause or resolve the error.
[13 Feb 2009 9:52] Magnus Blåudd
I have seen the same, the mysql_instance_config.exe should not allow to configure the mysqld with innodb unless it's supported.
[13 Feb 2009 13:58] Miguel Solorzano
Thank you for the bug report. The install process which failed it was a fresh install or an upgrade install?. Thanks in advance.
[13 Feb 2009 14:19] Sören Zimmermann
It was a fresh install. The PC it was installed on is a new installed Win XP system. The only software installed before MySQL was OOo 3.0.1, Apache  2.2.11 and PHP 5.2.8.
[13 Feb 2009 17:23] Miguel Solorzano
Thank you for the feedback. Could you please go to your install directory and issue the below 2 commands (type and dir) as showed. Please print here the output and attach your my.ini file. Thanks in advance.

c:\Program Files\MySQL\MySQL Server 5.1>type my.ini | findstr datadir
datadir="C:/ProgramData/MySQL/MySQL Server 5.1/Data/"

c:\Program Files\MySQL\MySQL Server 5.1>dir "C:/ProgramData/MySQL/MySQL Server 5.1/Data/"
 O volume na unidade C não tem nome.
 O Número de Série do Volume é 2E43-6159

 Pasta de C:\ProgramData\MySQL\MySQL Server 5.1\Data

13/02/2009  08:08    <DIR>          .
13/02/2009  08:08    <DIR>          ..
13/02/2009  08:08            29.676 hegel.err
13/02/2009  08:08                 5 hegel.pid
12/02/2009  22:29        10.485.760 ibdata1
13/02/2009  08:08        25.165.824 ib_logfile0
28/01/2009  19:58        25.165.824 ib_logfile1
28/01/2009  19:57    <DIR>          mysql
28/01/2009  19:56    <DIR>          test
               5 arquivo(s)     60.847.089 bytes
               4 pasta(s)   41.307.054.080 bytes disponíveis

c:\Program Files\MySQL\MySQL Server 5.1>
[18 Feb 2009 13:59] Sören Zimmermann
Requested ini-file

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

[18 Feb 2009 14:01] Sören Zimmermann
Sorry that I answer that late. I did what you requested me to do. The data directory is datadir="C:/Dokumente und Einstellungen/All Users/Anwendungsdaten/MySQL/MySQL Server 5.1/Data/".

The content of this directory is:
 Datentr„ger in Laufwerk C: ist System
 Volumeseriennummer: 745A-94E6

 Verzeichnis von c:\Dokumente und Einstellungen\All Users\Anwendungsdaten\MySQL\MySQL Server 5.1\data

18.02.2009  14:50    <DIR>          .
18.02.2009  14:50    <DIR>          ..
13.02.2009  16:11    <DIR>          bitweaver
18.02.2009  14:50                 0 data.txt
12.02.2009  15:59        10.485.760 ibdata1
12.02.2009  15:59       178.257.920 ib_logfile0
12.02.2009  15:56       178.257.920 ib_logfile1
13.02.2009  11:07    <DIR>          mysql
18.02.2009  14:19            17.337 nb-26.err
18.02.2009  14:19                 5 nb-26.pid
               6 Datei(en)    367.018.942 Bytes
               4 Verzeichnis(se), 309.555.818.496 Bytes frei

Note that the file data.txt was created by the command "dir > data.txt".
[18 Feb 2009 16:43] Miguel Solorzano
Service started but  the wizard says no

Attachment: can't_start_service.png (image/png, text), 69.33 KiB.

[18 Feb 2009 17:24] Miguel Solorzano
Thank you for the bug report. I was able to repeat following your initial instruction see attached picture. However on my side it was a false error menssage that the service can't start when actually it was started and I noticed when the firewall application asked to allow to run.
[19 Feb 2009 14:48] Mikhail Izioumtchenko
InnoDB failing to start when tablespace 0 or log file sizes don't much
those specified in my.ini is not a bug, it's a sanity check addressing possibility
of serious data corruption after the last shutdown. So if you want to reuse existing data/log files, you should enter their sizes in the Config Wizard,
Calvin should be able to explain where exactly, or it must be in the manual.
If you don't care about old files, you should remove them manually (only the first time you start the service!) before you start the service.
This is described in the relevant section of the 5.1 reference manual as well.
As for the false alarm message I doubt it's InnoDB problem but I'll still assign it to Calvin to investigate.
[19 Feb 2009 16:08] Miguel Solorzano
Michael,

Sorry I didn't noticed the category InnoDB, it wasn't InnoDB fails but the installer wizard which created InnoDB files and the service was really started, so a false message error.
[22 Feb 2009 10:13] Calvin Sun
Sören,

The problem is the mismatch of the innodb_log_file_size in your option file (my.ini) and the real size of the log files. Please change it from 24M to 170M, then restart the server.

Regards,
Calvin
[24 Feb 2009 11:32] Sören Zimmermann
Yes, this works.

Thank you very much

Soeren
[11 Mar 2009 7:58] Sveta Smirnova
Bug #42820 was marked as duplicate of this one.
[5 May 2009 5:21] Susanne Ebrecht
Bug #5845 is a duplicate of this bug here.
[5 May 2009 5:22] Susanne Ebrecht
Bug #35890 is a duplicate of this bug here.
[5 May 2009 5:23] Susanne Ebrecht
Bug #43374 is a duplicate of this bug here
[10 May 2009 2:29] Tq S
I have experienced the same thing on Server 2003 Enterprise, I can toggle between the non-working multifunctional database and working non-transactional database types.  I tried modifying the innodb_log_file_size to 170MB and that didn't fix anything.  I am unable to use MySQL Server whatsoever on this platform.
[28 May 2009 13:20] Miguel Solorzano
Bug: http://bugs.mysql.com/bug.php?id=45157 marked as duplicate of this one.
[9 Jun 2009 15:37] Miguel Solorzano
Bug: http://bugs.mysql.com/bug.php?id=45401 marked as duplicate of this one.
[17 Jun 2009 22:00] Iggy Galarza
Bug: http://bugs.mysql.com/bug.php?id=32120 marked as duplicate of this one.
[17 Jul 2009 10:51] Fitz Gerald
My solution to the bug was the following:

1. Completely uninstall MySQL 
2. Delete all relevant files 
3. Reinstall MySQL with the custom settings
4. Configure MySQL as a non-transactional database only
5. Specify a password and start the service (this will work 100%)
6. Reconfigure MySQL, but this time specifiy the option "multifunctional database"
7. Start the service.

There was no error or something. I think the clue was not to specify multifunctional database in the first instance, but in the second.
[11 Aug 2009 20:22] Mike Hoeffner
I ran into a similar issue with MySQL 5.1.37 on Vista where trying to configure the instance as anything other than "Non-Transactional Database Only" would fail. After using that option for the initial setup I was later able to activate InnoDB and make it the default storage engine in MySQL Administrator. That seemed to work until I actually tried to create a table. I didn't record the errors so I went back again to try to reproduce and document the issue accurately but I couldn't.

Now it's working completely as expected. I uninstalled all versions of MySQL and deleted any remaining MySQL directories. I then ran mysql-essential-5.1.37-winx64.msi and used all default selections for the install and all default selections for the instance configuration except for including the bin directory in my path. It worked without any problems so unfortunately I don't have much to share other than that I recommend starting completely clean.
[9 Aug 2010 21:25] Sveta Smirnova
Bug #54604 was marked as duplicate of this one.
[20 Sep 2010 17:07] Sveta Smirnova
See also bug #56851 and bug #56821
[22 Jan 2011 11:47] salik ahmed
Thanks its work for me
[4 Oct 2012 6:53] Marko Mäkelä
This is a duplicate of Bug#13494 and will be fixed in MySQL 5.6.8.