| Bug #57168 | show table status + small myisam_sort_buffer_size in repair, table crashed error | ||
|---|---|---|---|
| Submitted: | 1 Oct 2010 13:08 | Modified: | 8 Oct 2010 21:03 |
| Reporter: | Shane Bester (Platinum Quality Contributor) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: MyISAM storage engine | Severity: | S2 (Serious) |
| Version: | 5.0.91,5.1.51, 5.1.52-bzr, 5.5-runtime, 5.6.99 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | myisam_sort_buffer_size | ||
[1 Oct 2010 13:18]
Valeriy Kravchuk
Verified just as described with current mysql-5.1 from bzr on Mac OS X. This is what I see in the error log: macbook-pro:5.1 openxs$ tail -100 var/macbook-pro.err 101001 15:50:37 [Note] Plugin 'FEDERATED' is disabled. 101001 15:50:37 [Note] Plugin 'ndbcluster' is disabled. 101001 15:50:37 InnoDB: Started; log sequence number 0 88273748 101001 15:50:37 [Note] Event Scheduler: Loaded 0 events 101001 15:50:37 [Note] /Users/openxs/dbs/5.1/libexec/mysqld: ready for connections. Version: '5.1.52-debug' socket: '/tmp/mysql.sock' port: 3306 Source distribution 101001 15:54:39 [Note] Got signal 15 to shutdown mysqld 101001 15:54:39 [Note] /Users/openxs/dbs/5.1/libexec/mysqld: Normal shutdown 101001 15:54:39 [Note] Event Scheduler: Purging the queue. 0 events 101001 15:54:39 InnoDB: Starting shutdown... 101001 15:54:42 InnoDB: Shutdown completed; log sequence number 0 88273748 101001 15:54:42 [Note] /Users/openxs/dbs/5.1/libexec/mysqld: Shutdown complete 101001 15:54:42 mysqld_safe mysqld from pid file /Users/openxs/dbs/5.1/var/macbook-pro.pid ended 101001 16:14:12 mysqld_safe Starting mysqld daemon with databases from /Users/openxs/dbs/5.1/var 101001 16:14:12 [Warning] Setting lower_case_table_names=2 because file system for /Users/openxs/dbs/5.1/var/ is case insensitive 101001 16:14:12 [Warning] One can only use the --user switch if running as root 101001 16:14:12 [Note] Plugin 'FEDERATED' is disabled. 101001 16:14:12 [Note] Plugin 'ndbcluster' is disabled. 101001 16:14:13 InnoDB: Started; log sequence number 0 88273748 101001 16:14:13 [Note] Event Scheduler: Loaded 0 events 101001 16:14:13 [Note] /Users/openxs/dbs/5.1/libexec/mysqld: ready for connections. Version: '5.1.52-debug' socket: '/tmp/mysql.sock' port: 3306 Source distribution 101001 16:15:25 [Warning] Warning: Enabling keys got errno 137 on test.t2, retrying 101001 16:15:25 [Warning] Warning: Enabling keys got errno 137 on test.t2, retrying 101001 16:15:25 [Warning] Warning: Enabling keys got errno 137 on test.t2, retrying 101001 16:15:26 [Warning] Warning: Enabling keys got errno 137 on test.t2, retrying 101001 16:15:26 [Warning] Warning: Enabling keys got errno 137 on test.t2, retrying 101001 16:15:26 [Warning] Warning: Enabling keys got errno 137 on test.t2, retrying 101001 16:15:26 [Warning] Warning: Enabling keys got errno 137 on test.t2, retrying 101001 16:15:26 [Warning] Warning: Enabling keys got errno 137 on test.t2, retrying 101001 16:15:26 [Warning] Warning: Enabling keys got errno 137 on test.t2, retrying 101001 16:15:26 [Warning] Warning: Enabling keys got errno 137 on test.t2, retrying 101001 16:15:26 [Warning] Warning: Enabling keys got errno 137 on test.t2, retrying 101001 16:15:26 [Warning] Warning: Enabling keys got errno 137 on test.t2, retrying 101001 16:15:26 [Warning] Warning: Enabling keys got errno 137 on test.t2, retrying 101001 16:15:27 [Warning] Warning: Enabling keys got errno 137 on test.t2, retrying 101001 16:15:27 [Warning] Warning: Enabling keys got errno 137 on test.t2, retrying 101001 16:15:27 [Warning] Warning: Enabling keys got errno 137 on test.t2, retrying 101001 16:15:27 [Warning] Warning: Enabling keys got errno 137 on test.t2, retrying 101001 16:15:27 [Warning] Warning: Enabling keys got errno 137 on test.t2, retrying 101001 16:15:28 [Warning] Warning: Enabling keys got errno 137 on test.t2, retrying 101001 16:15:28 [Warning] Warning: Enabling keys got errno 137 on test.t2, retrying 101001 16:15:28 [Warning] Warning: Enabling keys got errno 137 on test.t2, retrying 101001 16:15:28 [Warning] Warning: Enabling keys got errno 137 on test.t2, retrying 101001 16:15:29 [Warning] Warning: Enabling keys got errno 137 on test.t2, retrying 101001 16:15:29 [Warning] Warning: Enabling keys got errno 137 on test.t2, retrying 101001 16:15:29 [Warning] Warning: Enabling keys got errno 137 on test.t2, retrying 101001 16:15:30 [Warning] Warning: Enabling keys got errno 137 on test.t2, retrying 101001 16:15:30 [Warning] Warning: Enabling keys got errno 137 on test.t2, retrying 101001 16:15:30 [ERROR] /Users/openxs/dbs/5.1/libexec/mysqld: Table './test/t2' is marked as crashed and last (automatic?) repair failed 101001 16:15:31 [Warning] Warning: Enabling keys got errno 137 on test.t2, retrying 101001 16:15:31 [Warning] Warning: Enabling keys got errno 137 on test.t2, retrying 101001 16:15:31 [ERROR] /Users/openxs/dbs/5.1/libexec/mysqld: Table './test/t2' is marked as crashed and last (automatic?) repair failed 101001 16:15:32 [Warning] Warning: Enabling keys got errno 137 on test.t2, retrying ...
[6 Oct 2010 19:27]
Konstantin Osipov
Could you please verify in 5.5? If it's not there, I suggest to set to "Won't fix" for 5.1 (may be too risky).
[8 Oct 2010 21:03]
Sveta Smirnova
Problem exists in 5.5-runtime and next-mr
[11 Oct 2017 17:25]
MySQL Verification Team
Tested this on a debug build of 9.0.0 today (using latin1 chars). Ran check table in a second connection while stored procedure running asserted server. [NOTE] mysqld-debug.exe: Version: '9.0.0-dmr-debug-log' (Built on 2017/10/08) [Warning] Warning: Enabling keys got errno 137 on test.t2, retrying [Warning] Warning: Enabling keys got errno 137 on test.t2, retrying [Warning] Warning: Enabling keys got errno 137 on test.t2, retrying Assertion failed: cnt <= keycache->blocks_used, file mf_keycache.cc, line 3387 abort() has been called 17:20:38 UTC - mysqld got exception 0x80000003 ; mysqld-debug.exe!my_sigabrt_handler()[my_thr_init.cc:415] ucrtbased.dll!raise() ucrtbased.dll!abort() ucrtbased.dll!_get_wide_winmain_command_line() ucrtbased.dll!_get_wide_winmain_command_line() ucrtbased.dll!_get_wide_winmain_command_line() ucrtbased.dll!_wassert() mysqld-debug.exe!flush_key_blocks_int()[mf_keycache.cc:3387] mysqld-debug.exe!flush_key_blocks()[mf_keycache.cc:3773] mysqld-debug.exe!chk_size()[mi_check.cc:350] mysqld-debug.exe!ha_myisam::check()[ha_myisam.cc:979] mysqld-debug.exe!handler::ha_check()[handler.cc:4803] mysqld-debug.exe!mysql_admin_table()[sql_admin.cc:871] mysqld-debug.exe!Sql_cmd_check_table::execute()[sql_admin.cc:1488] mysqld-debug.exe!mysql_execute_command()[sql_parse.cc:4627] mysqld-debug.exe!mysql_parse()[sql_parse.cc:5420] mysqld-debug.exe!dispatch_command()[sql_parse.cc:1714] mysqld-debug.exe!do_command()[sql_parse.cc:1298] mysqld-debug.exe!handle_connection()[connection_handler_per_thread.cc:328] mysqld-debug.exe!pfs_spawn_thread()[pfs.cc:2989] mysqld-debug.exe!win_thread_start()[my_thread.cc:42]
Description: when repair by sort happens with small myisam_sort_buffer_size, and somebody runs 'show table status' or selects from information_schema tables referencing the table, errors appear in the logs: 101001 14:57:57 [ERROR] ./bin/mysqld: Table './test/t2' is marked as crashed and last (automatic?) repair failed The table doesn't appear corrupt when check table is run. This didn't affect affect 5.6.1 How to repeat: in one thread, run this SP. in another thread run a few times: "show table status like 't2';" delimiter ; set session myisam_sort_buffer_size=1; drop table if exists t1,t2; create table t2(a char(255),b char(255),key(a),key(b),key(b,a),key(a,b))engine=myisam pack_keys=0; create table t1(a char(255),b char(255))engine=myisam; drop procedure if exists p1; delimiter $ create procedure p1(cnt int) begin declare i int default 0; declare j int default 0; repeat set j=0; repeat insert into t1 values ('a','b'); set j=j+1; until j>i end repeat; alter table t2 disable keys; insert into t2 select * from t1; alter table t2 enable keys; delete from t2 order by a limit 99999999; set i=i+1; until i>cnt end repeat; end $ delimiter ; call p1(500); Suggested fix: don't access the table when it's in some inconsistent state.