MySQL Bugs: #16533: InnoDB is not very informative about the problem reason upon startup

Bug #16533	InnoDB is not very informative about the problem reason upon startup
Submitted:	16 Jan 2006 12:03	Modified:	11 Nov 2010 22:23
Reporter:	Valeriy Kravchuk	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	4.1.16	OS:	Windows (Windows 64-bit (XP, 2003))
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
MySQL 4.1.16 (mysql-4.1.16-win32.zip) can be installed on 64-bit Windows, but when you ask to create a new instance with many (40) InnoDB data files larger than 4GB each:

innodb_data_file_path=c:/mysql/ibdata1:4500M;c:/mysql/ibdata2:4500M;... ;c:/mysql/ibdata39:4500M;c:/mysql/ibdata40:4500M:autoextend

it will then fail to start. See the my.ini and error log files attached.

Verified on Windows XP 64-bit.

How to repeat:
1. Dowload mysql-4.1.16-win32.zip and install it on 64-bit Windows

2. Prepare the my.ini file similar to attached (maybe, InnoDB data file size >= 4GB is enough to show the problem, but I verified with 40, that is 40*4.5=180GB of total data files size)

3. Start server for the first time with something like:

C:\Program Files (x86)\MySQL\MySQL Server 4.1>"C:\Program Files (x86)\MySQL\MySQL Server 4.1\bin\mysqld-max" --defaults-file="C:\Program Files (x86)\MySQL\MySQL Server 4.1\my.ini" --console

This will evetually fail, after successfully creating all 40 files...

4. You may install it as service also:

C:\Program Files (x86)\MySQL\MySQL Server 4.1>"C:\Program Files (x86)\MySQL\MySQL Server 4.1\bin\mysqld" --install MySQL41 --defaults-file="C:\Program Files (x86)\MySQL\MySQL Server 4.1\my.ini"

This will work.

5. Start service (net start MySQL41, for example).

6. Read in the error log something similar to the attached. That is:

060116 13:28:45  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...
060116 13:28:45  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 43634.
InnoDB: Doing recovery: scanned up to log sequence number 0 43634
InnoDB: Page directory corruption: supremum not pointed to
...
      ;InnoDB: End of page dump
060116 13:28:45  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
...

Suggested fix:
I think, InnoDB data files larger that 4GB are not really supported by 32-bit MySQL servers (we used 32-bit binary on 64-bit platform, because we have no 64-bit Windows binaries yet). May be, this just has to be documented in the prominent place.

my.ini file used to repeat

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

Compressed error log from 64-bit XP running on Dell Precision 380 (1 Xeon CPU, HT on)

Attachment: ERRORLOG.ZIP (application/zip, text), 2.95 KiB.

Valeriy,

InnoDB does support > 4 GB on a 32-bit Windows. I have personally tested it with 15 GB files.

The .err log that you posted is incomplete. It does not show what mysqld printed when it created the big ibdata files. Please create the database again running mysqld at an MS-DOS prompt:

mysqld --console

so that we certainly get the complete mysqld printout.

Regards,

Heikki

By the way, several users have requested 64-bit builds of mysqld on Windows. Then they could use a buffer pool bigger than 2 GB. When can we have these binaries?

Regards,

Heikki

Log of successfull installation

Attachment: console.txt (text/plain), 19.59 KiB.

Heikki,

Yes, you were right. With the clean installation, all in one step, everything works OK. See the log attached (http://bugs.mysql.com/file.php?id=2733). But do you know about any possible reason of the following messages upon startup:

060116 13:28:45  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
...

Valeriy,

if ib_logfiles are not the right ones for the ibdata files, the database will appear corrupt. That may explain the page dumps. Remember that InnoDB log files are as important as InnoDB data files. They must always live together.

Regards,

Heikki

Heikki,

OK, so, it is not a bug. But as you can see from the resulting error log with page dumps, it is not obvious what is the reason and what to do next in such a cases. Reinstall, remove logs and start once more, or something else? May be, InnoDB should print some recommendations/additional information to the error log? The real reason may be not included in it, as happened in my first test case. What do you think?

Valeriy,

we have in the TODO list to keep better track of which ib_logfiles match with which ibdata files.

The general rule is that you should never touch ibdata files or ib_logfiles unless you have read the manual section.

Regards,

Heikki

Changed synopsis to a more appropriate. The original problem was a result of incorrect configuration.

dup of bug#13494.

This error will be fixed in MySQL 5.6.8.
In the case of a size mismatch, we would apply the old logs and then rewrite new ones. Also, with this change, the log file creation and database startup should be more crash-safe. Previously, if mysqld was killed while it was creating the files, it would not recover, and you would have to delete all files before restarting.