MySQL (innodb) siempre falla en declaraciones ORDER BY grandes

2024-6-20 • tag-icon

MySQL (innodb) siempre falla en declaraciones ORDER BY grandes

Estoy ejecutando MySQL en Debian estable (normalmente instalado desde los paquetes estables).

Cada vez que hago un gran ORDER BY, mysql falla. Consulta de ejemplo (tablas 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

Si lo hago, WHERE key2 = 1tengo alrededor de 6000 conjuntos de datos y todo funciona bien. Si hago que WHERE key2 = 2tengo alrededor de 18000 conjuntos de datos y mysql fallará (error de mysql 2013, se perdió la conexión durante la consulta), todas las consultas posteriores obtendrán el error "mysql desapareció". Sin la línea ORDER BY, incluso se pueden seleccionar 18000 conjuntos de datos sin bloquear el demonio mysql.

El registro de MySQL después de tal accidente:

[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

Algunos datos my.cnf posiblemente relevantes:

[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

¿Alguna posibilidad de que esto sea un error en MySQL? ¿O qué salió mal? ¿Cómo solucionarlo?

Respuesta1

Intente ejecutar innochecksumpara comprobar si hay daños en la tabla.

Respuesta1

información relacionada