MySQL Bugs: #42820: MySQL not starting due to InnoDB failure

Bug #42820	MySQL not starting due to InnoDB failure
Submitted:	13 Feb 10:38	Modified:	24 Feb 12:32
Reporter:	Sören Zimmermann
Status:	Verified
Category:	Server: Installing	Severity:	S1 (Critical)
Version:	4.1, 5.0, 5.1, 5.4, 6.0	OS:	Microsoft Windows (WinXP Prof SP3)
Assigned to:	Iggy Galarza	Target Version:	5.0+
Triage:	Triaged: D2 (Serious)

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.

I have seen the same, the mysql_instance_config.exe should not allow to configure the
mysqld with innodb unless it's supported.

Thank you for the bug report. The install process which failed it was a fresh install or
an upgrade install?. Thanks in advance.

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.

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>

Requested ini-file

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

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".

Service started but the wizard says no

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

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.

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.

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.

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

Yes, this works.

Thank you very much

Soeren

Bug #42820 was marked as duplicate of this one.

Bug #5845 is a duplicate of this bug here.

Bug #35890 is a duplicate of this bug here.

Bug #43374 is a duplicate of this bug here

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.

Bug: http://bugs.mysql.com/bug.php?id=45157 marked as duplicate of this one.

Bug: http://bugs.mysql.com/bug.php?id=45401 marked as duplicate of this one.

Bug: http://bugs.mysql.com/bug.php?id=32120 marked as duplicate of this one.

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.

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.