Bug #28913 Running MySQL data on a GPT drive?
Submitted: 6 Jun 2007 4:54 Modified: 7 Jan 2011 13:31
Reporter: Marcos Boyington Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.0.45, 5.1 OS:Microsoft Windows
Assigned to: Assigned Account
Tags: GPT large hard drive 2TB
Triage: Triaged: D1 (Critical)

[6 Jun 2007 4:54] Marcos Boyington
Description:
I am trying to get MySQL running/using data from a GPT drive under Windows Server 2003 x64 R2.  It seems that for some reason, the server fails whenever trying to start.
I've tried starting the server on the same machine on a regular MBR disk, and everything works great (with the exact same settings), so it definitely has something to do with GPT.

For info about GPT, see:

http://www.microsoft.com/whdc/device/storage/GPT-on-x64.mspx

How to repeat:
Format a drive using GPT (has to be a secondary partition that's not the boot partition, booting from GPT is not supported).
Install/setup MySQL on this drive.
[6 Jun 2007 17:47] Valerii Kravchuk
Thank you for a problem report. Pleasem send error log (*.err) with information from your failed start attempts.
[6 Jul 2007 23:00] 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".
[9 Jul 2007 23:26] Marcos Boyington
070709 16:25:55  InnoDB: Operating system error number 87 in a file operation.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/operating-system-error-codes.html
InnoDB: File name .\ib_logfile0
InnoDB: File operation call: 'aio read'.
InnoDB: Cannot continue operation.
[26 Jul 2007 8:14] Sveta Smirnova
Thank you for the feedback.

Please try with current version 5.0.45 and if you can repeat provide your configuration file.
[3 Aug 2007 7:19] Marcos Boyington
I am still able to reproduce with 5.0.45:

070802 23:44:33  InnoDB: Operating system error number 87 in a file operation.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/operating-system-error-codes.html
InnoDB: File name .\ib_logfile0
InnoDB: File operation call: 'aio read'.
InnoDB: Cannot continue operation.

The problem is not with my config file. Have you formatted a drive partition using GPT, and tried to install MySQL on that drive?  That is the cause of this issue, I am 100% certain.

As I said before, my config file works perfectly while I am running MySQL 64-bit from C:\Program Files\MySQL\MySQL Server 5.0, with my data located there as well.

My C: drive is an MBR-formatted drive. MySQL starts up and works perfectly.

When I move the data files to D:\Program Files\MySQL\MySQL Server 5.0, and change my.ini to to D:, I get the error above.

Additionaly, if I move my files to F:\Program Files\MySQL\MySQL Server 5.0, everything also works perfectly.

The only difference between these 3 configurations are:

Drives C: and F: (the working drives) are MBR partitions.
Drive D: is a GPT partition.
[3 Aug 2007 18:16] Marcos Boyington
NON-WORKING configuration file, running out of D: (GPT-formatted partition)

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

[25 Aug 2007 23:06] Marcos Boyington
I've narrowed down the problem.  It is actually caused by a large hard drive, *NOT* GPT (although to format a drive > 2TB, you'll need it to be GPT).

The problem is caused by sector size, in the call to ReadFile.  It appears that, by default, ib_logfile is read using FILE_FLAG_NO_BUFFERING, which requires calls to ReadFile to be sector-aligned.  My sectors seem to have a size of 4096, or 0x1000.  It seems it's trying to read a block at offset 0x200, which is of course not a legal offset as it is not block-aligned.
[25 Aug 2007 23:11] Marcos Boyington
More information - actually caused by OS_FILE_LOG_BLOCK_SIZE being defined as 512.
Any way to make this so it's specifiable in the settings, or at the very least, auto-detected based on sector size?

I've found that by setting the following in the config file:

innodb_flush_method=normal

(the default is innodb_flush_method=unbuffered) my server starts up correctly - is this safe?
[26 Aug 2007 1:15] Marcos Boyington
And actually the correct define is OS_FILE_SECTOR_SIZE.

Changing OS_FILE_SECTOR_SIZE to 4096 seems to have fixed the problem.

OS_FILE_SECTOR_SIZE should *really* be auto-detected to be the disk sector size.
[26 Aug 2007 22:56] Marcos Boyington
So there seems to be an additional problem.  It seems that OS_FILE_LOG_BLOCK_SIZE must be a multiple of OS_FILE_SECTOR_SIZE.  There is a comment in os0file.h, which is incorrect:

#define OS_FILE_SECTOR_SIZE		4096

/* The next value should be smaller or equal to the smallest sector size used
on any disk.... */

#define OS_FILE_LOG_BLOCK_SIZE		4096

This is not true, obviously, because ReadFile() with disk caching turned off requires a read to be a multiple of the sector size.

The problem occurs in log_group_read_checkpoint_info(), which uses OS_FILE_LOG_BLOCK_SIZE.  If OS_FILE_LOG_BLOCK_SIZE is not a multiple of the sector byte size, then this function fails.  Problem is, if your logfile was written out with a OS_FILE_LOG_BLOCK_SIZE of 512, then there is no way to change this value.
[12 Mar 2008 14:04] Susanne Ebrecht
I set bug #28120 as duplicate of this bug here. Please also read this bug to get more informations about the problem.
[17 Mar 2008 13:54] Heikki Tuuri
Marcos,

does GPT have a non-standard disk block size?

Regards,

Heikki
[17 Mar 2008 22:45] Marcos Boyington
The main reason to use GPT is that it allows for drives > 2TB.  Because of the drive's large size, the OS defaults to a larger block size than the default 4096 bytes.

This in turn breaks MySQL, as by default it does direct writes to the drive w/o caching (as it has its own internal caching).

Windows requires non-cached disk writes to be a multiple of the block size.
[23 Jun 2008 17:43] Heikki Tuuri
In the other bug report, someone suggested this my.cnf option:

innodb_flush_method=normal

Does that fix the problem?

Regards,

Heikki
[23 Jul 2008 23:00] 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".
[10 Mar 2009 18:59] Ivan Ivanov
Hi

I get the same error
System:
 - Windwos XP (32)
 - mySQL 5.1.32
 - hard drive - 2,9Tb NTFS (RAID)

Any solution?
[7 Jan 2011 13:32] Valerii Kravchuk
Looks like we have at least one case when setting

innodb_flush_method=normal

helped.
[23 Jun 2012 1:12] Jack Chen
We met same problem on Microsoft Azure cloud drive ( a virtual vhd file). After installed Mysql 5.5.25 on a Windows 2008 R2 Azure instance, the first time mysqld created the ib_logs and ibdata file, once it's shutdown, it won't start, error log shows this error number 87 error.

I am not sure how innodb_flush_method=normal could work, since this is a error only on Windows platform, and MySQL document http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_flush_method  state 

"This variable is relevant only for Unix. On Windows, the flush method is always async_unbuffered and cannot be changed."
[23 Jun 2012 2:03] Jack Chen
To my surprise, adding 

innodb_flush_method=normal

did solved the problem.

Seems the MySQL document is misleading.
[20 Nov 2014 22:43] Meiji Kimura
The same problem occurrs on Windows 8/8.1 Tablet (e.g. DELL Venus Pro 8, lenovo miix2 8)

I think it cause by differenct 'Bytes Per Physical Sector'. Normal Desktop Windows's value is 512, but tablet's value is 4096.

[DELL Venus Pro8]
C:\Windows\system32>fsutil fsinfo ntfsinfo c:
NTFS Volume Serial Number :       0x8a14504614503781
NTFS Version   :                  3.1
LFS Version    :                  2.0
Number Sectors :                  0x000000000682c7ff
Total Clusters :                  0x0000000000d058ff
Free Clusters  :                  0x000000000076c281
Total Reserved :                  0x00000000000013c0
Bytes Per Sector  :               512
Bytes Per Physical Sector :       4096 <- This value is 512 on normal desktop machine
Bytes Per Cluster :               4096
Bytes Per FileRecord Segment    : 1024
Clusters Per FileRecord Segment : 0
Mft Valid Data Length :           0x000000000c5c0000
Mft Start Lcn  :                  0x00000000000c0000
Mft2 Start Lcn :                  0x0000000000000002
Mft Zone Start :                  0x00000000003f0000
Mft Zone End   :                  0x00000000003fa7e0
Resource Manager Identifier :     16B4AFBA-754B-11E3-8281-00808E8A9FBF

[workaround]
set innodb_flush_method=normal
[21 Nov 2014 0:27] Meiji Kimura
[Normal Desktop]

C:\Windows\system32>fsutil fsinfo ntfsinfo c:
NTFS Volume Serial Number :       0x56e438d1e438b4d9
Version :                         3.1
Number Sectors :                  0x000000001c1e6fff
Total Clusters :                  0x000000000383cdff
Free Clusters  :                  0x0000000000619511
Total Reserved :                  0x0000000000003b90
Bytes Per Sector  :               512
Bytes Per Physical Sector :       512 <- This value is 512
Bytes Per Cluster :               4096
Bytes Per FileRecord Segment    : 1024
Clusters Per FileRecord Segment : 0
Mft Valid Data Length :           0x0000000015140000
Mft Start Lcn  :                  0x00000000000c0000
Mft2 Start Lcn :                  0x0000000000000002
Mft Zone Start :                  0x0000000002db7860
Mft Zone End   :                  0x0000000002dbc740
RM Identifier:        3B182F61-AEB1-11E0-9C63-90FBA69FEA4D
[21 Nov 2014 20:18] Meiji Kimura
Tablet use Win8.1, and Normal desktop machine use Win7.

Microsoft said this format 'Advanced Format or 512E (4K physical and 512-byte logical sector size)'

[English]
http://support.microsoft.com/kb/2510009/en-us

[Japanese]
http://support.microsoft.com/kb/2510009/ja
http://answers.microsoft.com/ja-jp/windows/forum/windows_8-hardware/4k%E3%82%BB%E3%82%AF%E...
[12 Apr 2015 4:54] Jim Thatcher
I encountered this problem on a Windows 8.1 system when I moved my datadir to a drive using Storage Spaces. Adding the innodb_flush_method=normal setting solved the problem.
[20 Mar 20:50] Liam Stewart
I have encountered this error on a late 2016 Macbook pro running windows 10 in bootcamp, unlike the other posts here however I am experiencing it when trying to install (which always fails, hanging on starting the service).

I think its fair to say that this is preventing MySQL Server from being installed on large sector drives on Windows :(

See https://bugs.mysql.com/bug.php?id=85020
[23 Aug 15:17] Karthik Kamath Koteshwar
Please be advised to use the workaround of setting innodb-flush-method= normal for running mysqld in 4K sector hard drives in windows platform.
[23 Aug 15:37] Liam Stewart
That does not help with installing it in the first place however. If you are trying to install on one of these drives you are screwed.
[11 Nov 13:32] Shane Bester
https://bugs.mysql.com/bug.php?id=85339
(Mysqld server not starting in win10)