%20%E3%81%AF%E5%A4%A7%E3%81%8D%E3%81%AA%20ORDER%20BY%20%E3%82%B9%E3%83%86%E3%83%BC%E3%83%88%E3%83%A1%E3%83%B3%E3%83%88%E3%81%A7%E5%B8%B8%E3%81%AB%E3%82%AF%E3%83%A9%E3%83%83%E3%82%B7%E3%83%A5%E3%81%97%E3%81%BE%E3%81%99.png)
私は Debian 安定版 (通常は安定版パッケージからインストール) で MySQL を実行しています。
大きな ORDER BY を実行するたびに、mysql がクラッシュします。クエリの例 (innodb テーブル):
SELECT *
FROM `table`
INNER JOIN (
SELECT `key1`,
`key2`,
MAX(`date`) AS `date`
FROM `table`
WHERE `key2` = 1 # <-- Fancy variable to get different amount of datasets
AND `someOtherIndex` >= 5
GROUP BY `key1`, `key2`
) `temp` ON `table`.`key1` = `temp`.`key1` AND `table`.`key2` = `temp`.`key2` AND `table`.`date` = `temp`.`date`
INNER JOIN `key1Info` ON (`key1Info`.`id` = `table`.`key1`)
ORDER BY `score` DESC # <-- The deadly ORDER BY
LIMIT 500
約 6000 個のデータセットがある場合WHERE key2 = 1、すべて正常に動作します。WHERE key2 = 2約 18000 個のデータセットがある場合、mysql がクラッシュし (mysql エラー 2013、クエリ中に接続が失われました)、後続のすべてのクエリで「mysql gone away」エラーが発生します。ORDER BY 行がない場合、18000 個のデータセットを選択しても、mysql デーモンがクラッシュすることはありません。
このようなクラッシュ後の MySQL ログ:
[lots of cryptic hex-ascii stuff called "page dump"]
Sep 8 16:47:22 kvm01 mysqld: InnoDB: End of page dump
Sep 8 16:47:22 kvm01 mysqld: 130908 16:47:22 InnoDB: Page checksum 942713336, prior-to-4.0.14-form checksum 548706977
Sep 8 16:47:22 kvm01 mysqld: InnoDB: stored checksum 3862697688, prior-to-4.0.14-form stored checksum 548706977
Sep 8 16:47:22 kvm01 mysqld: InnoDB: Page lsn 6 2842419796, low 4 bytes of lsn at page end 2842419796
Sep 8 16:47:22 kvm01 mysqld: InnoDB: Page number (if stored to page already) 16182,
Sep 8 16:47:22 kvm01 mysqld: InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
Sep 8 16:47:22 kvm01 mysqld: InnoDB: Page may be an index page where index id is 174
Sep 8 16:47:22 kvm01 mysqld: InnoDB: (index "summoner_champion_date" of table "lolskill_net"."skillscore")
Sep 8 16:47:22 kvm01 mysqld: InnoDB: Database page corruption on disk or a failed
Sep 8 16:47:22 kvm01 mysqld: InnoDB: file read of page 16182.
Sep 8 16:47:22 kvm01 mysqld: InnoDB: You may have to recover from a backup.
Sep 8 16:47:22 kvm01 mysqld: InnoDB: It is also possible that your operating
Sep 8 16:47:22 kvm01 mysqld: InnoDB: system has corrupted its own file cache
Sep 8 16:47:22 kvm01 mysqld: InnoDB: and rebooting your computer removes the
Sep 8 16:47:22 kvm01 mysqld: InnoDB: error.
Sep 8 16:47:22 kvm01 mysqld: InnoDB: If the corrupt page is an index page
Sep 8 16:47:22 kvm01 mysqld: InnoDB: you can also try to fix the corruption
Sep 8 16:47:22 kvm01 mysqld: InnoDB: by dumping, dropping, and reimporting
Sep 8 16:47:22 kvm01 mysqld: InnoDB: the corrupt table. You can use CHECK
Sep 8 16:47:22 kvm01 mysqld: InnoDB: TABLE to scan your table for corruption.
Sep 8 16:47:22 kvm01 mysqld: InnoDB: See also http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
Sep 8 16:47:22 kvm01 mysqld: InnoDB: about forcing recovery.
Sep 8 16:47:22 kvm01 mysqld: InnoDB: Ending processing because of a corrupt database page.
Sep 8 16:47:22 kvm01 mysqld: 130908 16:47:22 InnoDB: Assertion failure in thread 139673058604800 in file buf0buf.c line 3629
Sep 8 16:47:22 kvm01 mysqld: InnoDB: We intentionally generate a memory trap.
Sep 8 16:47:22 kvm01 mysqld: InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
Sep 8 16:47:22 kvm01 mysqld: InnoDB: If you get repeated assertion failures or crashes, even
Sep 8 16:47:22 kvm01 mysqld: InnoDB: immediately after the mysqld startup, there may be
Sep 8 16:47:22 kvm01 mysqld: InnoDB: corruption in the InnoDB tablespace. Please refer to
Sep 8 16:47:22 kvm01 mysqld: InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
Sep 8 16:47:22 kvm01 mysqld: InnoDB: about forcing recovery.
Sep 8 16:47:22 kvm01 mysqld: 14:47:22 UTC - mysqld got signal 6 ;
Sep 8 16:47:22 kvm01 mysqld: This could be because you hit a bug. It is also possible that this binary
Sep 8 16:47:22 kvm01 mysqld: or one of the libraries it was linked against is corrupt, improperly built,
Sep 8 16:47:22 kvm01 mysqld: or misconfigured. This error can also be caused by malfunctioning hardware.
Sep 8 16:47:22 kvm01 mysqld: We will try our best to scrape up some info that will hopefully help
Sep 8 16:47:22 kvm01 mysqld: diagnose the problem, but since we have already crashed,
Sep 8 16:47:22 kvm01 mysqld: something is definitely wrong and this may fail.
Sep 8 16:47:22 kvm01 mysqld:
Sep 8 16:47:22 kvm01 mysqld: key_buffer_size=16777216
Sep 8 16:47:22 kvm01 mysqld: read_buffer_size=131072
Sep 8 16:47:22 kvm01 mysqld: max_used_connections=17
Sep 8 16:47:22 kvm01 mysqld: max_threads=151
Sep 8 16:47:22 kvm01 mysqld: thread_count=14
Sep 8 16:47:22 kvm01 mysqld: connection_count=14
Sep 8 16:47:22 kvm01 mysqld: It is possible that mysqld could use up to
Sep 8 16:47:22 kvm01 mysqld: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 192058 K bytes of memory
Sep 8 16:47:22 kvm01 mysqld: Hope that's ok; if not, decrease some variables in the equation.
Sep 8 16:47:22 kvm01 mysqld:
Sep 8 16:47:22 kvm01 mysqld: Thread pointer: 0x7f08240433c0
Sep 8 16:47:22 kvm01 mysqld: Attempting backtrace. You can use the following information to find out
Sep 8 16:47:22 kvm01 mysqld: where mysqld died. If you see no messages after this, something went
Sep 8 16:47:22 kvm01 mysqld: terribly wrong...
Sep 8 16:47:22 kvm01 mysqld: stack_bottom = 7f082b0aee80 thread_stack 0x30000
Sep 8 16:47:22 kvm01 mysqld: /usr/sbin/mysqld(my_print_stacktrace+0x29)[0x7f0849403569]
Sep 8 16:47:22 kvm01 mysqld: /usr/sbin/mysqld(handle_fatal_signal+0x3d8)[0x7f08492eb748]
Sep 8 16:47:22 kvm01 mysqld: /lib/x86_64-linux-gnu/libpthread.so.0(+0xf030)[0x7f0848a9d030]
Sep 8 16:47:22 kvm01 mysqld: /lib/x86_64-linux-gnu/libc.so.6(gsignal+0x35)[0x7f0847330475]
Sep 8 16:47:22 kvm01 mysqld: /lib/x86_64-linux-gnu/libc.so.6(abort+0x180)[0x7f08473336f0]
Sep 8 16:47:22 kvm01 mysqld: /usr/sbin/mysqld(+0x5ba4d9)[0x7f08494864d9]
Sep 8 16:47:22 kvm01 mysqld: /usr/sbin/mysqld(+0x5c50ac)[0x7f08494910ac]
Sep 8 16:47:22 kvm01 mysqld: /usr/sbin/mysqld(+0x5c5a1c)[0x7f0849491a1c]
Sep 8 16:47:22 kvm01 mysqld: /usr/sbin/mysqld(+0x5b6355)[0x7f0849482355]
Sep 8 16:47:22 kvm01 mysqld: /usr/sbin/mysqld(+0x5a28df)[0x7f084946e8df]
Sep 8 16:47:22 kvm01 mysqld: /usr/sbin/mysqld(+0x56ad4b)[0x7f0849436d4b]
Sep 8 16:47:22 kvm01 mysqld: /usr/sbin/mysqld(+0x545dba)[0x7f0849411dba]
Sep 8 16:47:22 kvm01 mysqld: /usr/sbin/mysqld(+0x34a4a7)[0x7f08492164a7]
Sep 8 16:47:22 kvm01 mysqld: /usr/sbin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x5b)[0x7f084921454b]
Sep 8 16:47:22 kvm01 mysqld: /usr/sbin/mysqld(+0x34837a)[0x7f084921437a]
Sep 8 16:47:22 kvm01 mysqld: /usr/sbin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x76)[0x7f0849214566]
Sep 8 16:47:22 kvm01 mysqld: /usr/sbin/mysqld(+0x34837a)[0x7f084921437a]
Sep 8 16:47:22 kvm01 mysqld: /usr/sbin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x76)[0x7f0849214566]
Sep 8 16:47:22 kvm01 mysqld: /usr/sbin/mysqld(+0x3578b5)[0x7f08492238b5]
Sep 8 16:47:22 kvm01 mysqld: /usr/sbin/mysqld(_ZN4JOIN4execEv+0x4b2)[0x7f0849231812]
Sep 8 16:47:22 kvm01 mysqld: /usr/sbin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x11d)[0x7f084922d60d]
Sep 8 16:47:22 kvm01 mysqld: /usr/sbin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x174)[0x7f08492336d4]
Sep 8 16:47:22 kvm01 mysqld: /usr/sbin/mysqld(+0x320464)[0x7f08491ec464]
Sep 8 16:47:22 kvm01 mysqld: /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x1309)[0x7f08491f3189]
Sep 8 16:47:22 kvm01 mysqld: /usr/sbin/mysqld(+0x32a64e)[0x7f08491f664e]
Sep 8 16:47:22 kvm01 mysqld: /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x193b)[0x7f08491f87cb]
Sep 8 16:47:22 kvm01 mysqld: /usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x105)[0x7f08492929b5]
Sep 8 16:47:22 kvm01 mysqld: /usr/sbin/mysqld(handle_one_connection+0x50)[0x7f0849292ad0]
Sep 8 16:47:22 kvm01 mysqld: /lib/x86_64-linux-gnu/libpthread.so.0(+0x6b50)[0x7f0848a94b50]
Sep 8 16:47:22 kvm01 mysqld: /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f08473d8a7d]
Sep 8 16:47:22 kvm01 mysqld:
Sep 8 16:47:22 kvm01 mysqld: Trying to get some variables.
Sep 8 16:47:22 kvm01 mysqld: Some pointers may be invalid and cause the dump to abort.
Sep 8 16:47:22 kvm01 mysqld: Query (7f084b6abd20): SELECT `skillscore`.`id`,
Sep 8 16:47:22 kvm01 mysqld: #011#011`skillscore`.`summoner`,
Sep 8 16:47:22 kvm01 mysqld: #011#011`summoners`.`realm`,
Sep 8 16:47:22 kvm01 mysqld: #011#011`skillscore`.`champion`,
Sep 8 16:47:22 kvm01 mysqld: #011#011`skillscore`.`score`
Sep 8 16:47:22 kvm01 mysqld: #011FROM `skillscore`
Sep 8 16:47:22 kvm01 mysqld: #011INNER JOIN (
Sep 8 16:47:22 kvm01 mysqld: #011#011SELECT `summoner`,
Sep 8 16:47:22 kvm01 mysqld: #011#011#011`champion`,
Sep 8 16:47:22 kvm01 mysqld: #011#011#011MAX(`date`) AS `date`
Sep 8 16:47:22 kvm01 mysqld: #011#011FROM `skillscore`
Sep 8 16:47:22 kvm01 mysqld: #011#011WHERE `champion` = 103
Sep 8 16:47:22 kvm01 mysqld: #011#011AND `games` >= 5
Sep 8 16:47:22 kvm01 mysqld: #011#011GROUP BY `summoner`, `champion`
Sep 8 16:47:22 kvm01 mysqld: #011#011ORDER BY `score` DESC
Sep 8 16:47:22 kvm01 mysqld: #011) `temp` ON `skillscore`.`summoner` = `temp`.`summoner` AND `skillscore`.`champion` = `temp`.`champion` AND `skillscore`.`date` = `temp`.`date`
Sep 8 16:47:22 kvm01 mysqld: #011INNER JOIN `summoners` ON (`summoners`.`id` = `skillscore`.`summoner`)
Sep 8 16:47:22 kvm01 mysqld: #011ORDER BY `score` DESC
Sep 8 16:47:22 kvm01 mysqld: #011LIMIT 500
Sep 8 16:47:22 kvm01 mysqld: Connection ID (thread ID): 314
Sep 8 16:47:22 kvm01 mysqld: Status: NOT_KILLED
Sep 8 16:47:22 kvm01 mysqld:
Sep 8 16:47:22 kvm01 mysqld: The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
Sep 8 16:47:22 kvm01 mysqld: information that should help you find out what is causing the crash.
Sep 8 16:47:22 kvm01 mysqld_safe: Number of processes running now: 0
Sep 8 16:47:22 kvm01 mysqld_safe: mysqld restarted
Sep 8 16:47:22 kvm01 mysqld: 130908 16:47:22 [Note] Plugin 'FEDERATED' is disabled.
Sep 8 16:47:22 kvm01 mysqld: 130908 16:47:22 InnoDB: The InnoDB memory heap is disabled
Sep 8 16:47:22 kvm01 mysqld: 130908 16:47:22 InnoDB: Mutexes and rw_locks use GCC atomic builtins
Sep 8 16:47:22 kvm01 mysqld: 130908 16:47:22 InnoDB: Compressed tables use zlib 1.2.7
Sep 8 16:47:22 kvm01 mysqld: 130908 16:47:22 InnoDB: Using Linux native AIO
Sep 8 16:47:22 kvm01 mysqld: 130908 16:47:22 InnoDB: Initializing buffer pool, size = 256.0M
Sep 8 16:47:22 kvm01 mysqld: 130908 16:47:22 InnoDB: Completed initialization of buffer pool
Sep 8 16:47:22 kvm01 mysqld: 130908 16:47:22 InnoDB: highest supported file format is Barracuda.
Sep 8 16:47:22 kvm01 mysqld: InnoDB: Log scan progressed past the checkpoint lsn 28753523976
Sep 8 16:47:22 kvm01 mysqld: 130908 16:47:22 InnoDB: Database was not shut down normally!
Sep 8 16:47:22 kvm01 mysqld: InnoDB: Starting crash recovery.
Sep 8 16:47:22 kvm01 mysqld: InnoDB: Reading tablespace information from the .ibd files...
Sep 8 16:47:22 kvm01 mysqld: InnoDB: Restoring possible half-written data pages from the doublewrite
Sep 8 16:47:22 kvm01 mysqld: InnoDB: buffer...
Sep 8 16:47:22 kvm01 mysqld: InnoDB: Doing recovery: scanned up to log sequence number 28753832298
Sep 8 16:47:22 kvm01 mysqld: 130908 16:47:22 InnoDB: Starting an apply batch of log records to the database...
Sep 8 16:47:22 kvm01 mysqld: InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
Sep 8 16:47:22 kvm01 mysqld: InnoDB: Apply batch completed
Sep 8 16:47:22 kvm01 mysqld: 130908 16:47:22 InnoDB: Waiting for the background threads to start
関連する可能性のある my.cnf データ:
[mysqld]
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 16
innodb_buffer_pool_size = 256M
max_heap_table_size = 64M
tmp_table_size = 64M
sort_buffer_size = 1M
read_rnd_buffer_size = 1M
query_cache_limit = 1M
query_cache_size = 16M
これは MySQL のバグである可能性はありますか? または、何が間違っているのでしょうか? 回避するにはどうすればよいでしょうか?
答え1
実行してinnochecksumテーブルが破損していないか確認してください。