MySQL Bugs: #14578: innodb format disabled

Bug #14578	innodb format disabled
Submitted:	2 Nov 2005 15:36	Modified:	8 Nov 2005 12:02
Reporter:	Robert Mottram	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Installing	Severity:	S3 (Non-critical)
Version:	5.0	OS:	Windows (Windows 2003 SP1)
Assigned to:		CPU Architecture:	Any

Description:
I have installed MYSQL 5 onto a windows 2003 SP1 server and wanted to use innodb database. 
When running the through the Configuration Wizard check the Transactional Database Only option to defualt the databases to innodb.
MYSQL would start and create a new blank database.
When I do SHOW ENGINES innodb is disabled.
Once I restart the MYSQL service it will not come back up (error 1067)
In the MYSQL log I get :

102 15:31:14  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
051102 15:31:14  InnoDB: Page dump in ascii and hex (16384 bytes):
 len 16384; hex
Followed by about 3 pages of 0's then 

;InnoDB: End of page dump
051102 15:31:14  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
051102 15:31:14 [ERROR] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: Got signal 11. Aborting!

051102 15:31:14 [ERROR] Aborting

051102 15:31:14 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: Shutdown complete

How to repeat:
Install MYSQL 5 on a windows 2003 SP1 machine and select Transactional Databases only during the configuration
Allow MSSQL to start and then restart the sevice.

Suggested fix:
I have tryed this on 2 servers now (Both windows 2003 SP1) 
Ran through the same install on a Windows XP SP2 machine and appears to have worked without any issues.

Thank you for a problem report. Please, check what distributions you have used on those machines. We already know for sure that *-essential-* (mysql-essential-5.0.15-win32.msi e.g.) does not include InnoDB support by default.

I download mysql-5.0.15-win32.zip and ran the only file in the zip file (setup.exe)

I used the same package, and InnoDB works for me on XP:

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.15-nt |
+-----------+
1 row in set (0.19 sec)

mysql> create table tinno(c1 int);
Query OK, 0 rows affected (0.61 sec)

mysql> show create table tinno;
+-------+-----------------------------------------------------------------------------------------+
| Table | Create Table                  |
+-------+-----------------------------------------------------------------------------------------+
| tinno | CREATE TABLE `tinno` (
  `c1` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------+
1 row in set (0.18 sec)

mysql> show engines;
+------------+----------+------------------------------------------------------------------------+
| Engine     | Support  | Comment                 |
+------------+----------+------------------------------------------------------------------------+
| MyISAM     | YES      | Default engine as of MySQL 3.23 with great performance                 |
| MEMORY     | YES      | Hash based, stored in memory, useful for temporary tables              |
| InnoDB     | DEFAULT  | Supports transactions, row-level locking, and foreign
keys             |
| BerkeleyDB | NO       | Supports transactions and page-level locking                 |
| BLACKHOLE  | NO       | /dev/null storage engine (anything you write to it disappears)         |
| EXAMPLE    | NO       | Example storage engine                 |
| ARCHIVE    | YES      | Archive storage engine                 |
| CSV        | NO       | CSV storage engine                 |
| ndbcluster | NO       | Clustered, fault-tolerant, memory-based tables                 |
| FEDERATED  | NO       | Federated MySQL storage engine                 |
| MRG_MYISAM | YES      | Collection of identical MyISAM tables                 |
| binlog     | DISABLED | This is a meta storage engine to represent the binlog
in a transaction |
| ISAM       | NO       | Obsolete storage engine                 |
+------------+----------+------------------------------------------------------------------------+
13 rows in set (0.01 sec)

Strange. It may be either a 2003 SP1 problem (somebody have to check...), or some of your choices during install. 

Please, describe all the selections you made during the installation.

I was going through the MYSQL 5 install step by step to let you know what I did. This time I set the database location to the default settings and the databases appears to be working without any issues. I normaly was setting the database location to e:\MYSQL Databases. 

I am going to reinstall again with the data set to e:\mysql databases and let you know in a bit what happens

I have reinstalled MYSQL again and the only difference was I set the Data Directory to e:\MYSQL databases. The only thing I noticed was that in my.ini file the datadir is set to C:/Program Files/MySQL/MySQL Server 5.0/data and not e:/MYSQL databases as I would expect if I had changed the database location.

Thank you for the additional information. Let me check... (I installed to the defaulty directory). It really expains something and may a small installer bug.

I've just tried to install 5.0.15 from the same .zip as yours on a fresh 2003 Server Standard Edition. I used non-default, c:\MySQL, directory when asked about data directory. 

Yes, indeed, datadir was set to default "C:/Program Files/MySQL/MySQL Server 5.0/data", and we may call it a bug of Configuration Wizard, BUT, innodb_data_home_dir was really set to "C:\MySQL", and InnoDB was the default and functional database engine.

So, I can't repeat the original behaviour you described, in any case. I believe it was you who choosed non-transactional engine only during installation... 

I would like to change the synopsis according to the real problem we discovered and mark this report as verified. What do you think about these?

Sorry for not getting back before, I have been out of the office.
 
I have found a work around by installing MYSQL to the default locations and then changing the datapath after the install. 

Thankyou very much for the very prompt help.