Bug #69839 windows only: Impossible to REPAIR or OPTIMIZE datafiles (.MYD) files > 4GB
Submitted: 25 Jul 2013 16:32 Modified: 25 Jul 2013 18:18
Reporter: dan d Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S1 (Critical)
Version:5.6.12 OS:Windows (x64)
Assigned to: CPU Architecture:Any
Tags: 4GB, corrupted, limitation, myd, myisam, Optimize, Repair, windows

[25 Jul 2013 16:32] dan d
Description:
Impossible to neither REPAIR nor OPTIMIZE .MYD files bigger than 4GB.
Only the Windows versions are affected.
The table doesn't need to have any keys.
After deleting some rows, execute the OPTIMIZE command or REPAIR any table with a MYD data file bigger than 4GB results in an error.

The table is marked as crashed and any attempt to repair it fails (again).

Additionally, the repaired/optimized temporary data file (.TMD) isn't deleted.

The culprit is a function call to stat() inside the mysys\my_redel.c file:
----
struct stat statbuf;
if (stat(from, &statbuf))
{
----
It should be (if executed on Windows):

struct _stat64	statbuf;
if(_stat64(from, &statbuf))
{
------

Follows the output from the error:

mysql> status
--------------
bin\mysql  Ver 14.14 Distrib 5.6.12, for Win64 (x86_64)

Connection id:          35
Current database:       test
Current user:           dan@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.6.12-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    cp850
Conn.  characterset:    cp850
TCP port:               3307
Uptime:                 2 days 5 hours 26 min 57 sec

Threads: 5  Questions: 2311  Slow queries: 8  Opens: 743  Flush tables: 2  Open tables: 7  Queries per second avg: 0.012
--------------
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE t1(a CHAR(255), b CHAR(60)) ENGINE=MYISAM;
Query OK, 0 rows affected (0.11 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 52.78 sec)
Records: 14348907  Duplicates: 0  Warnings: 0

mysql> DELETE FROM t1 LIMIT 1;
Query OK, 1 row affected (0.06 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\data\test\t1.MYD' (Errcode: 0 - No error) |
| test.t1 | optimize | status   | Operation failed                                                          |
+---------+----------+----------+---------------------------------------------------------------------------+
3 rows in set (1 min 29.53 sec)

mysql> SHOW WARNINGS;
Empty set (0.00 sec)

mysql> exit
Bye

E:\mysql-5.6>cd data\test

E:\mysql-5.6\data\test>dir
 Volume in drive E is EData
 Volume Serial Number is 6268-258E

 Directory of E:\mysql-5.6\data\test

25/07/2013  17:36    <DIR>          .
25/07/2013  17:36    <DIR>          ..
05/04/2013  14:27                65 db.opt
25/07/2013  17:32             8 578 t1.frm
25/07/2013  17:37     4 534 255 560 t1.MYD
25/07/2013  17:37             1 024 t1.MYI
25/07/2013  17:37     4 534 255 244 t1.TMD
               5 File(s)  9 068 520 471 bytes
               2 Dir(s)  49 166 381 056 bytes free

How to repeat:
Create a MyISAM table without any keys (to speedup OPTIMZE) with a MYD data file bigger than 4GB. And then, do a REPAIR on it:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1(a CHAR(255), b CHAR(60)) ENGINE=MYISAM;

INSERT t1(a,b) VALUES (RAND(), RAND()),(RAND(), RAND()),(RAND(), RAND());

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;

REPAIR TABLE t1;

See the error:
"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\\data\\test\\t1.MYD' (Errcode: 0 - No error)"
"test.t1"	"repair"	"status"	"Operation failed"

Suggested fix:
Use the _stat64() function when compiling for Windows.
See the following .patch file for a suggested source code fix.
[25 Jul 2013 16:34] dan d
path file for mysys/my_redel.c

Attachment: my_redel.patch (application/octet-stream, text), 518 bytes.

[25 Jul 2013 17:17] dan d
I forgot to mention: 
After applying my patch, the REPAIR and OPTIMIZE on >4GB .MYD files are working perfectly.
Also the myisamchk.exe command line tool, which before the patch weren't able the repair the >4GB table.
[25 Jul 2013 17:44] MySQL Verification Team
Thank you for the bug report. Please check for duplicate with http://bugs.mysql.com/bug.php?id=69683 . Thanks.
[25 Jul 2013 17:52] dan d
Yes, the bug:
http://bugs.mysql.com/bug.php?id=69683
is the same.
[25 Jul 2013 18:18] MySQL Verification Team
Thank you for the feedback.
[6 Jan 2014 11:39] Thomas Mayerhofer
I regret, that this bug has not been solved for half a year, despite it´s severity. Is this because Oracle wants to dump MyISAM?

Nevertheless I found a workaround for me: The myisamchk.exe out of an old server version (e.g. 5.1) works without problems.
[19 Aug 2015 21:44] John Curtusan
This is quite a severe bug to continue and release in all these releases :/ 

What is going on here? 

My workaround is partitioning the table into 100+ chunks.