Bug #49030 optimize table breaks myisam table when using myisam_use_mmap on windows
Submitted: 24 Nov 2009 11:40 Modified: 21 Oct 2011 15:45
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S1 (Critical)
Version:5.1.41 OS:Windows (XP64)
Assigned to: CPU Architecture:Any
Tags: myisam_use_mmap

[24 Nov 2009 11:40] Shane Bester
Description:
on windows, optimize table renders certain myisam tables useless when using myisam_use_mmap=1 option.  Testcase outputs this:

mysql> optimize table `t1`;
+---------+----------+----------+-----------------------------------------
| Table   | Op       | Msg_type | Msg_text
+---------+----------+----------+-----------------------------------------
| test.t1 | optimize | error    | 5 for record at pos 0
| test.t1 | optimize | Error    | Error on delete of 'E:\downloads\mysql\5
| test.t1 | optimize | status   | OK
+---------+----------+----------+-----------------------------------------
3 rows in set (0.00 sec)

And in the database directory is a file: t1.MYD.ADF635FE.deleted

How to repeat:
startup windows server with --myisam-use-mmap=1

drop table if exists `t1`;
create table `t1` (`col7` year) engine=myisam;
insert ignore into `t1` set `col7`=1;
optimize table `t1`;
delete from `t1` where `col7` = 1;
optimize table `t1`;
check table `t1`;
[24 Nov 2009 11:44] MySQL Verification Team
and in the error log of mysqld we see this message:
091124 13:39:16 [Warning] Warning: Optimize table got errno 5 on test.t1, retrying
[24 Nov 2009 11:59] MySQL Verification Team
Thank you for the bug report.

c:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.42-Win X64-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.1 >use test
Database changed
mysql 5.1 >drop table if exists `t1`;
Query OK, 0 rows affected (0.00 sec)

mysql 5.1 >create table `t1` (`col7` year) engine=myisam;
Query OK, 0 rows affected (0.12 sec)

mysql 5.1 >insert ignore into `t1` set `col7`=1;
Query OK, 1 row affected (0.00 sec)

mysql 5.1 >optimize table `t1`;
+---------+----------+----------+----------+
| Table   | Op       | Msg_type | Msg_text |
+---------+----------+----------+----------+
| test.t1 | optimize | status   | OK       |
+---------+----------+----------+----------+
1 row in set (0.00 sec)

mysql 5.1 >delete from `t1` where `col7` = 1;
Query OK, 1 row affected (0.00 sec)

mysql 5.1 >optimize table `t1`;
+---------+----------+----------+---------------------------------------------------------------+
| Table   | Op       | Msg_type | Msg_text                                                      |
+---------+----------+----------+---------------------------------------------------------------+
| test.t1 | optimize | error    | 5 for record at pos 0                                         |
| test.t1 | optimize | Error    | Error on delete of 'c:\dbs\5.1\data\test\t1.MYD' (Errcode: 5) |
| test.t1 | optimize | status   | OK                                                            |
+---------+----------+----------+---------------------------------------------------------------+
3 rows in set (0.01 sec)

mysql 5.1 >check table `t1`;
+---------+-------+----------+---------------------------------------------+
| Table   | Op    | Msg_type | Msg_text                                    |
+---------+-------+----------+---------------------------------------------+
| test.t1 | check | Error    | File '.\test\t1.MYD' not found (Errcode: 2) |
| test.t1 | check | Error    | Can't find file: 't1' (errno: 2)            |
| test.t1 | check | error    | Corrupt                                     |
+---------+-------+----------+---------------------------------------------+
3 rows in set (0.00 sec)

mysql 5.1 >
[24 Nov 2009 12:00] Valeriy Kravchuk
Same on 32-bit XP:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.41-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table if exists `t1`;
Query OK, 0 rows affected (0.20 sec)

mysql> create table `t1` (`col7` year) engine=myisam;
Query OK, 0 rows affected (0.13 sec)

mysql> insert ignore into `t1` set `col7`=1;
Query OK, 1 row affected (0.06 sec)

mysql> optimize table `t1`;
+---------+----------+----------+----------+
| Table   | Op       | Msg_type | Msg_text |
+---------+----------+----------+----------+
| test.t1 | optimize | status   | OK       |
+---------+----------+----------+----------+
1 row in set (0.00 sec)

mysql> delete from `t1` where `col7` = 1;
Query OK, 1 row affected (0.06 sec)

mysql> optimize table `t1`;
+---------+----------+----------+-----------------------------------------------
--------------------------------------------------------------------------------
-+
| Table   | Op       | Msg_type | Msg_text

 |
+---------+----------+----------+-----------------------------------------------
--------------------------------------------------------------------------------
-+
| test.t1 | optimize | error    | 5 for record at pos 0

 |
| test.t1 | optimize | Error    | Error on delete of 'C:\Documents and Settings\
All Users\Application Data\MySQL\MySQL Server 5.1\Data\test\t1.MYD' (Errcode: 5)
 |
| test.t1 | optimize | status   | OK

 |
+---------+----------+----------+-----------------------------------------------
--------------------------------------------------------------------------------
-+
3 rows in set (0.01 sec)

mysql> check table `t1`;
+---------+-------+----------+---------------------------------------------+
| Table   | Op    | Msg_type | Msg_text                                    |
+---------+-------+----------+---------------------------------------------+
| test.t1 | check | Error    | File '.\test\t1.MYD' not found (Errcode: 2) |
| test.t1 | check | Error    | Can't find file: 't1' (errno: 2)            |
| test.t1 | check | error    | Corrupt                                     |
+---------+-------+----------+---------------------------------------------+
3 rows in set (0.00 sec)
[24 Jul 2010 5:49] MySQL Verification Team
bug #55504 is a duplicate of this.
[24 Jul 2010 5:56] MySQL Verification Team
If this won't be fixed anytime soon, I believe myisam_use_mmap option should be disabled completely on affected platforms (windows only?)

Having an option that causes these problems is worse than not having the option!
Many folks script OPTIMIZE/REPAIR table and don't check the outputs manually, which might lead to unseen data loss.
[17 Jun 2011 7:12] vincenzo antolini
5.5.11 Windows Version
is also affected by this bug.
This is a DESTRUCTIVE bug.
I wonder why so destructive.
[17 Jun 2011 7:49] MySQL Verification Team
5.1.57 breaks with the testcase. 5.5.13 doesn't...
[17 Jun 2011 7:51] MySQL Verification Team
Maybe we need a bigger/better testcase for 5.5 ?
[21 Oct 2011 15:45] Paul DuBois
Noted in 5.1.60, 5.5.18, 5.6.4 changelogs.

OPTIMIZE TABLE could corrupt MyISAM tables if myisam_use_mmap was
enabled.
[6 Dec 2011 8:52] Dominik P
I just ran into the same problem with mmap disabled:

show variables like 'myisam_%';
myisam_data_pointer_size	6
myisam_max_sort_file_size	107374182400
myisam_mmap_size	4294967295
myisam_recover_options	OFF
myisam_repair_threads	1
myisam_sort_buffer_size	36700160
myisam_stats_method	nulls_unequal
myisam_use_mmap	OFF