MySQL Bugs: #86190: .err log is filling with "[ERROR] InnoDB: file write at offset

Bug #86190	.err log is filling with "[ERROR] InnoDB: file write at offset > 4 GB."
Submitted:	4 May 2017 20:42	Modified:	9 May 2017 16:34
Reporter:	Mark Kimmerly	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.7.10	OS:	Windows (Server 2016 x64)
Assigned to:		CPU Architecture:	Any
Tags:	innodb

Description:
We are running MySql 5.7.10 on MS Windows server 2016 x64.  Inspection of the MySql .err log shows a lot (over 300 per second) of row with the error in the Synopsis "file write at offset > 4 GB".

According to https://bugs.mysql.com/bug.php?id=77128, this is a bug that should have been fixed in 5.7.10

Can someone explain why this is happening and if it is a cause for alarm?

How to repeat:
I cannot provide explicit steps on reproduction, but the system in question is a large scale (for our product) system with 64GB of ram, 12 cores and high availability SSD storage. The DB is close to 900GB in size.

Hi!

That bug that you are mentioning is caused by this operation:

" Altering the letter case of a column introduced an inconsistency between the frm file and data dictionary resulting in a failed CREATE INDEX operation on the altered column."

Have you been doing the same when you got that error ???

No, there are no column or other table alterations occurring during the time these errors occur.

Apologies - the referenced bug is not associated with my issue, it only shows up as the 2nd top link in a search for the error on Google.  (The first is an unanswered question on StackOverflow.)

However, I still cannot find any help on this error.

I suspect you're using a 32-bit version of mysqld.exe. Can you confirm?

SHOW VARIABLES LIKE '%version%';

But anyway,  you need to upgrade to recent version. 5.7.10 is very old.
Take a look at :

http://bugs.mysql.com/bug.php?id=79150

Please see Shane1s comments. Thanks.

We are using the 64 bit version.

Here is the output of show variables like '%version%':

innodb_version	5.7.10
protocol_version	10
slave_type_conversions	
tls_version	TLSv1,TLSv1.1,TLSv1.2
version	5.7.10-enterprise-commercial-advanced
version_comment	MySQL Enterprise Server - Advanced Edition (Commercial)
version_compile_machine	x86_64
version_compile_os	Win64

Here is the output of mysqld.exe --version:

C:\Program Files\NetMotion Wireless Locality Server\MySQL\bin>mysqld.exe --version
mysqld.exe  Ver 5.7.10-enterprise-commercial-advanced for Win64 on x86_64 (MySQL Enterprise Server - Advanced Edition (Commercial))

Hi!

First of all, you are using 64-bit version of our server, on 64-bit OS.

However, it does not guarantee that this error can not pop up. First thing you have to do is upgrade to the latest release of the 5.7 version and see if you get that error again.

If you do get the error, do note that the bug you quoted is reproducible only if you are  altering the case of a character-based column which results in the failed CREATE INDEX operation on the altered column.

If that is not your case, you have to provide us with a fully repeatable test case that leads to the error reported. You also have to supply your entire MySQL and OS configuration, including the filesystem used on the partition where error occurred. 

Thank you in advance.

Once again, please ignore the bug I referenced in the initial post - it is not related to my issue, it only appeared when I searched the net for the bug text.

Also, it is not possible for us to simply upgrade to the latest version.  We include MySql in a shrink-wrapped software solution that is shipped to customers and installed on site.  Upgrading to a newer version of MySql involves an entire release cycle with a full suite of regression tests that takes months.

What I need to know is if this error is critical in any way, if it involves data loss, and if there is anything we can do using version 5.7.10 to address it.

Hi!

I will try to provide simple answers to your questions.

First of all, upgrading to latest 5.7 release does not guarantee that you would be safe from the problem that you experience. This is due to the fact that you have supplied us nothing regarding the operations and conditions under which you have got that error. Without that info, we can not provide an authoritative answer, nor can anybody else. 

The only thing that you can do with version 5.7.10 is to wait that it repeats again and to check whether it is an error in the OS configuration. You can check later by writing and running a simple program that would write files of 6 Gb into all partitions that hold any MySQL data. You should use various API's , like Posix and various WinApi calls in order to create that file. Watch if any error occurs.

Until you supply us with a repeatable test case, we are unable to help you any further.

Hi!

First of all, this is not a bug that is fixed in 5.7.10. Because, you can have a bug with the same effect  in many different places in the code, id est,  in many different statements. Which statement ??? You should tell us that ..... you can turn on the log and catch the problematic statement by using the timestamp.

Regarding the effect, this could be a temporary table or temporary file, created by some query or a file from some DML.

You are still not providing anything resembling, even vaguely, a test case, so nobody can provide you with the answers that you are looking for.

On my 5.7.10 I can get this message when innodb_flush_method=normal or innodb_flush_method=unbuffered in my.ini.

The default value for innodb_flush_method didn't cause any messages.
As far as I can tell the message is just annoying but no corruption is actually present.

Thank you Shane, that is the information I was looking for.