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: | |
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
[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