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