Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I'm running MySQL on Debian stable (normally installed from the stable packages).

Everytime when I do a big ORDER BY, mysql crashes. Example query (innodb tables):

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

If I make WHERE key2 = 1 I have about 6000 datasets and everything works still okay. If I make WHERE key2 = 2 I have about 18000 datasets and mysql will crash (mysql error 2013, lost connection during query), all subsequent queries will get the "mysql gone away" error. Without the ORDER BY line even the 18000 datasets can be selected without crashing the mysql daemon.

The mysql log after such a crash:

[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

Some possibly relevant my.cnf data:

[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

Any chance that this is a bug in MySQL? Or what went wrong? How to work around it?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

Try running innochecksum to check for table corruption.

share|improve this answer
    
innochecksum <filename> returns page 16182 invalid (fails new style checksum). How to fix this? –  pimeys Sep 8 '13 at 18:48
    
    
Thanks, that helped :) –  pimeys Sep 8 '13 at 23:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.