MySQL Bugs: #28913: Running MySQL data on a GPT drive?

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:	Windows
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	GPT large hard drive 2TB

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.

Thank you for a problem report. Pleasem send error log (*.err) with information from your failed start attempts.

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

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.

Thank you for the feedback.

Please try with current version 5.0.45 and if you can repeat provide your configuration file.

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.

NON-WORKING configuration file, running out of D: (GPT-formatted partition)

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

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.

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?

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.

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.

I set bug #28120 as duplicate of this bug here. Please also read this bug to get more informations about the problem.

Marcos,

does GPT have a non-standard disk block size?

Regards,

Heikki

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.

In the other bug report, someone suggested this my.cnf option:

innodb_flush_method=normal

Does that fix the problem?

Regards,

Heikki

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

Hi

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

Any solution?

Looks like we have at least one case when setting

innodb_flush_method=normal

helped.

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

To my surprise, adding 

innodb_flush_method=normal

did solved the problem.

Seems the MySQL document is misleading.

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

[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

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

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.

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

Please be advised to use the workaround of setting innodb-flush-method= normal for running mysqld in 4K sector hard drives in windows platform.

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.

https://bugs.mysql.com/bug.php?id=85339
(Mysqld server not starting in win10)

Hi,

innodb-flush-method= normal did not solve the problem at the first install on Windows 7 and 10, 32 or 64 bit, even with MBR disk, even if the disk is 1TB
If fsutil fsinfo ntfsinfo c: said
Bytes Per Sector  :               4096
Bytes Per Physical Sector :       4096
it always impossible to create data folder by the command
mysqld.exe --initialize-insecure
errors are:
[ERROR] InnoDB: Operating system error number 87 in a file operation
[ERROR] InnoDB: File .\ib_logfile101: 'aio write' return OS error 187.
[ERROR] InnoDB: Cannot continue operation

https://bugs.mysql.com/bug.php?id=89940

Its almost 2020, this bug still exists. i hv been trying run mysql 5.6, 5.7 on 32 and 64bit windows and all with same bug. 
All machines all when i apply the above fix in the my.ini conf.

Its almost 2020, this bug still exists. i hv been trying run mysql 5.6, 5.7 on 32 and 64bit windows and all with same bug. 
The bug is fixed when i apply the above fix. Tnx