| Bug #69683 | Optimize after a delete returns error 0 can't get stat of MYD file | ||
|---|---|---|---|
| Submitted: | 6 Jul 2013 5:17 | Modified: | 19 Aug 2015 21:37 |
| Reporter: | John Curtusan | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: MyISAM storage engine | Severity: | S1 (Critical) |
| Version: | 5.6.12 | OS: | Windows (7 latest updates) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | can't get stat, error 0, Optimize | ||
[6 Jul 2013 5:17]
John Curtusan
[6 Jul 2013 5:27]
John Curtusan
I forgot to mention. This is on a 2 TB Raid 10 setup and only 25% used. There is over 1.5 TB of free space so it cannot be space related. It is also not permissions related as MySQL has full system permissions and this only happens with key files larger than 1 GB.
[7 Jul 2013 12:40]
MySQL Verification Team
Hi John! Can you confirm that there is no antivirus scanning the table files? Does the following testcase repeat the problem? Anything in mysql error log? ------ drop table if exists t1; create table t1(id serial,a varchar(255),key(a,id),key(id,a))engine=myisam; alter table t1 disable keys; insert t1(a) values (rand()),(rand()),(rand()); insert t1(a) select rand() from t1 t1,t1 t2,t1 t3,t1 t4,t1 t5,t1 t6,t1 t7, t1 t8,t1 t9,t1 ta,t1 tb,t1 tc,t1 td,t1 te,t1 tf; alter table t1 enable keys; delete from t1 limit 10000; optimize table t1; show warnings; ------
[7 Jul 2013 17:55]
John Curtusan
Correct. No antivirus software running. The test case is successful only due to key file (MYI) being 924,684 KB which is under the 1 GB needed to get error. The table I create with index has a MYI file size of 1,590,865 KB. When deleting only a few records and issuing an optimize statement returns the following 3 rows. test_db.test_table optimize error 0 when fixing table test_db.test_table optimize Error Can't get stat of 'E:\MYSQL 5.6.1 Datafiles\test_db\test_table.MYD' (Errcode: 0 - No error) test_db.test_table optimize status Operation failed Table is now corrupted and cannot even do a repair. There are no errors written to the log file and no warnings or errors given other than the 3 returned rows above.
[20 Jul 2013 17:20]
John Curtusan
Any update on this? Currently I am getting around this by creating a new table and copying records into the table from table a delete/update was performed on which works fine even when it gets to the last stage where it updates by optimizing. Then I drop table and rename new one to old one. This is very time consuming as I have to make sure all our procedures and functions are not using the optimize command. I have tried numerous tests to find out what exactly is causing the problem and I can't seem to find out why. I do see the server create the temp file and then attempt to rebuild key file which is where it fails. Again, key file needs to be over 1gb in size for it to fail using the optimize command. If under 1gb it's fine. If copying records into a new table with key's created which finishes by optimizing key file it's still fine. No errors on server or in the error file except those 3 returned rows. I am lost at this point and can't seem to find out why it's doing this. Can I do the following? Is it possible to uninstall MySQL server and re-install it without losing the databases or do I need to do a dump? I am wondering if something in the MySQL core is corrupted and could be fixed with a re-installation.
[25 Jul 2013 15:06]
dan d
Hi John, I get the same error. And I'm using a fresh install. So I don't think your MySQL core files are corrupted. My config: OS: Win Srv 2008 R2 with 16 GB RAM MySQL: 5.6.12-log MySQL Community Server (GPL)
[25 Jul 2013 16:37]
dan d
In fact, I've debugged this problem and it's quite bigger than I thought: In the windows version MySQL can not neither optimize nor repair any MyISAM table where the datafile (.MYD) is bigger than 4GB. ( It doesn't matter if the index file (.MYI) is bigger than 1 GB, only the datafile size over 4GB triggers the bug ) I've filed a separated bug report as this concerns also REPAIR tables. http://bugs.mysql.com/bug.php?id=69839
[25 Jul 2013 18:01]
John Curtusan
Correct, I picked up on this last night when a 5gb table got same error and it has no keys on the table. Then I moved table to InnoDB and it worked fine. Though I am not familiar with the code, what you point out makes sense. Hope they release an update soon as this is affecting a few of our servers.
[25 Jul 2013 18:19]
MySQL Verification Team
http://bugs.mysql.com/bug.php?id=69839&thanks=1¬ify=67 marked as duplicate of this one.
[26 Jul 2013 10:00]
dan d
Hi,
I suggest to raise the severity of this bug to S1. As there is not way (on Windows platforms, with the bug present) to use the crashed (>4GB) MyISAM tables again.
This bug affects also the ongoing 5.7.1-m11-winx64
but surprisingly not 5.5.32-winx64.
(As my own bug report is marked as a duplicate, I report my comment here: )
I traced the bug to the function my_copystat() inside mysys/my_redel.c:
------
struct stat statbuf;
if (stat(from, &statbuf))
{
------
It should be (if compiled for Windows):
------
struct _stat64 statbuf;
if(_stat64(from, &statbuf))
{
------
See the following attached file for a source code patch proposal.
[26 Jul 2013 10:01]
dan d
Patch file for mysys/my_redel.c
Attachment: my_redel.patch (application/octet-stream, text), 529 bytes.
[26 Jul 2013 15:09]
John Curtusan
Changed bug to S1 as this is now a very serious matter.
[26 Jul 2013 19:55]
MySQL Verification Team
Thank you for the feedback.
mysql> INSERT t1(a,b) VALUES (RAND(), RAND()),(RAND(), RAND()),(RAND(), RAND());
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>
mysql> INSERT t1(a,b) SELECT RAND(), RAND() FROM
-> t1 t1,t1 t2,t1 t3,t1 t4,t1 t5,t1 t6,t1 t7,
-> t1 t8,t1 t9,t1 ta,t1 tb,t1 tc,t1 td,t1 te,t1 tf;
Query OK, 14348907 rows affected (7 min 55.89 sec)
Records: 14348907 Duplicates: 0 Warnings: 0
mysql> INSERT t1(a,b) SELECT a, b FROM
-> t1;
Query OK, 14348910 rows affected (9 min 26.04 sec)
Records: 14348910 Duplicates: 0 Warnings: 0
mysql> repair table t1;
+-------+--------+----------+---------------------------------------------------
-----------------------------------------------+
| Table | Op | Msg_type | Msg_text
|
+-------+--------+----------+---------------------------------------------------
-----------------------------------------------+
| wd.t1 | repair | error | 0 for record at pos 27148136774
|
| wd.t1 | repair | Error | Can't get stat of 'C:\ProgramData\MySQL\MySQL Serv
er 5.6\data\wd\t1.MYD' (Errcode: 0 - No error) |
| wd.t1 | repair | status | Operation failed
|
+-------+--------+----------+---------------------------------------------------
-----------------------------------------------+
3 rows in set (17 min 20.90 sec)
mysql> show variables like "%version%";
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.6.12 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.12 |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | Win64 |
+-------------------------+------------------------------+
7 rows in set (0.01 sec)
mysql>
[6 Aug 2013 8:34]
dan d
Hi, Any progress made? This bug seriously impedes our normal work-flow. Without surprise, it also affects GA version 5.6.13-winx64: mysql> use test; Database changed mysql> DROP TABLE IF EXISTS t1; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE t1(a CHAR(255), b CHAR(60)) ENGINE=MYISAM; Query OK, 0 rows affected (0.08 sec) mysql> INSERT t1(a,b) VALUES (RAND(), RAND()),(RAND(), RAND()),(RAND(), RAND()); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> INSERT t1(a,b) SELECT RAND(), RAND() FROM t1 t1,t1 t2,t1 t3,t1 t4,t1 t5,t1 t6,t1 t7, t1 t8,t1 t9,t1 ta,t1 tb,t1 tc,t1 td,t1 te,t1 tf; Query OK, 14348907 rows affected (2 min 42.50 sec) Records: 14348907 Duplicates: 0 Warnings: 0 mysql> DELETE FROM t1 LIMIT 1; Query OK, 1 row affected (0.11 sec) mysql> OPTIMIZE TABLE t1; +---------+----------+----------+-------------------------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------+----------+----------+-------------------------------------------------------------------------------------+ | test.t1 | optimize | error | 0 for record at pos 4534255244 | | test.t1 | optimize | Error | Can't get stat of 'E:\mysql-5.6.13-winx64\data\test\t1.MYD' (Errcode: 0 - No error) | | test.t1 | optimize | status | Operation failed | +---------+----------+----------+-------------------------------------------------------------------------------------+ 3 rows in set (1 min 15.19 sec) mysql> REPAIR TABLE t1; +---------+--------+----------+-------------------------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------+--------+----------+-------------------------------------------------------------------------------------+ | test.t1 | repair | error | 0 for record at pos 4534255244 | | test.t1 | repair | Error | Can't get stat of 'E:\mysql-5.6.13-winx64\data\test\t1.MYD' (Errcode: 0 - No error) | | test.t1 | repair | status | Operation failed | +---------+--------+----------+-------------------------------------------------------------------------------------+ 3 rows in set (1 min 18.77 sec) mysql> SHOW VARIABLES LIKE "%version%"; +-------------------------+------------------------------+ | Variable_name | Value | +-------------------------+------------------------------+ | innodb_version | 5.6.13 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.6.13-log | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | Win64 | +-------------------------+------------------------------+ 7 rows in set (0.00 sec)
[30 Oct 2013 14:48]
Rich Thorne
Have there been any updates provided on this issue?
[30 Oct 2013 15:17]
John Curtusan
Is MySQL bug team on permanent vacation? I mean seriously, this is a major issue on 64 bit operating systems. WAKE UP MYSQL No wonder Google is scrapping MySQL.
[30 Oct 2013 16:19]
dan d
Hi, I've just tested latest 5.6.14-log version and still the bug is present... :-( It's a pity, as this bug basically render >4Gb MyISAM tables unusable in windows.
[14 Jan 2014 16:33]
John Curtusan
January 14, 2014 and now version 5.6.15 is out but STILL NO BUG FIX FOR THIS.
[21 Jan 2014 4:05]
Jason OB
This issue affects me also, I have many >4GB tables that are marked as crashed because of this bug which I have no way to repair now. Hoping this fix makes it into the next release...
[11 Mar 2014 16:54]
MySQL Verification Team
I have verified this bug still exists in the source code. There are no pre-compiler macros for WIN32 code to do 64bit stat. This means any MyISAM tables over 4GB are impossible on WIN32, even though our docs say: Win32 w/ FAT/FAT32 2GB/4GB Win32 w/ NTFS 2TB (possibly larger) One of the comments suggested: struct _stat64 statbuf; if(_stat64(from, &statbuf)) however, that wouldn't work either: stat: 32-bit timestamp, 32-bit filesize stat64 : 64-bit timestamp, 32-bit filesize stati64: 64-bit timestamp, 64-bit filesize According to: http://msdn.microsoft.com/en-us/library/aa273365%28v=vs.60%29.aspx __int64 _stati64( const char *path, struct _stat *buffer ); So, the correct stat function is _stati64()
[11 Mar 2014 18:02]
MySQL Verification Team
All other instances of this error trace down to my_stat, which implements stati64 on WIN32. The *correct* solution for this code bug is to call my_stat instead of stat(). There is a lead as to the cause of result == -1 and errno == 0: http://connect.microsoft.com/VisualStudio/feedback/details/595553/stat-function-returns-er... MySQL 5.1 is compiled with VS 2005, 5.5 with VS 2008, and 5.6 with VS 2010. A test case compiled with mingw32 did not produce a similar result, which may point to a compiler bug. The bug seems to be when st_size would produce an overflow, result == -1 and errno == 0. Producing a test case to verify against 5.1, 5.5, and 5.6.
[11 Mar 2014 21:29]
MySQL Verification Team
followed test procedure from linked bug and got this result:
+---------+----------+----------+-----------------------------------------------
-----------------------------------------------------+
| Table | Op | Msg_type | Msg_text
|
+---------+----------+----------+-----------------------------------------------
-----------------------------------------------------+
| test.t1 | optimize | error | 0 for record at pos 13574067914
|
| test.t1 | optimize | Error | Can't get stat of 'C:\ProgramData\MySQL\MySQL
Server 5.6\data\test\t1.MYD' (Errcode: 0 - No error) |
| test.t1 | optimize | status | Operation failed
|
+---------+----------+----------+-----------------------------------------------
-----------------------------------------------------+
[11 Mar 2014 22:21]
MySQL Verification Team
Result from 5.6.16CE compiled with VS 2009: mysql> OPTIMIZE TABLE t1; +---------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------+----------+----------+----------+ | test.t1 | optimize | status | OK | +---------+----------+----------+----------+ 1 row in set (20.47 sec) I speculate that the bug referenced by connect.microsoft.com/VisualStudio/feedback/details/595553/stat-function-returns-error-for-directory-symbolic-links actually affects MySQL too. The problem is clear, the stat() call uses a 32 bit st_size and the MYD file size causes an overflow. It seems the VS 2010 runtime returns an error on overflow, but 2009 does not. Changing the reference of stat() to my_stat() should clear up the underlying problem.
[12 Mar 2014 0:27]
MySQL Verification Team
RCA: VS 2010 runtime has a broken stat() function, the documented behavior does not match actual. VS 2009 behavior matches the documented behavior.
Here is a sample test case that fails in the exact same manner as 5.6 when compiled with VS 2010, but works when compiled with VS 2009, which 5.5 is compiled with.
/* STAT.C: This program uses the _stat function to
* report information about the file named STAT.C.
*/
#include <time.h>
#include <sys/types.h>
#include <sys/stat.h>
#include <stdio.h>
#define PRId64 "I64d"
void main(int argc, char *argv[] )
{
struct stat buf;
struct _stati64 buf1;
int result,result1;
char buffer[] = "A line to output";
/* Get data associated with "stat.c": */
result = stat( argc ? argv[1] : "foo", &buf );
if (result) perror("Error in stat");
result1 = _stati64( argc ? argv[1] : "foo", &buf1 );
if (result1) perror("Error in stati64");
/* Check if statistics are valid: */
/* Output some of the statistics: */
printf( "File size : %lu\n", buf.st_size );
printf( "Result : %d\n", result);
printf( "File size 64 : %10" PRId64 "\n", buf1.st_size );
printf( "Result : %d\n", result1);
}
[21 Mar 2014 12:48]
Eric Joniec
Our company has run into this problem too with 5.6.16 on Windows and we are awaiting a fix. It does not seem to be a priority with Oracle/MySQL though. We might have to get our sales rep involved.
[6 May 2014 14:16]
Paul DuBois
Noted in 5.6.19, 5.7.5 changelogs. On Windows, REPAIR TABLE and OPTIMIZE TABLE failed for MyISAM tables with .MYD files larger than 4GB.
[2 Jun 2014 14:18]
Laurynas Biveinis
$ bzr log -r 5895
------------------------------------------------------------
revno: 5895
committer: Venkata Sidagam <venkata.sidagam@oracle.com>
branch nick: 5.6
timestamp: Fri 2014-04-18 16:22:06 +0530
message:
Bug #17235179 OPTIMIZE AFTER A DELETE RETURNS ERROR 0 CAN'T
GET STAT OF MYD FILE
Description: Impossible to neither REPAIR nor OPTIMIZE .MYD files bigger
than 4GB. Only the Windows versions are affected.
Analysis: In my_copystat() function while calling stat() system call for
Windows it is unable to get the file information for files greater than
4GB. Hence stat() fails with error and OPTIMIZE table on 4GB ".MYD" files
fails. And the corresponding .TMD file(temporary .MYD file) is not getting
deleted.
Fix: Now we are calling windows specific stat() system call i.e _stati64()
instead of stat() for windows and stat() call for other OS's.
[19 Aug 2015 21:37]
John Curtusan
we are on version 5.6.26 and still no fix? Seriously?
[20 Aug 2015 5:23]
MySQL Verification Team
"Noted in 5.6.19, 5.7.5 changelogs." This was fixed already. If you're still seeing the problem on 5.6.26, please file a new bug...
