Description:
We run a mysql server on a Hetzner PX 120 (http://www.hetzner.de/en/hosting/produkte_rootserver/px120), with a max_heap_table_size variable that is set directly in a PHP script.
The script create and populate MEMORY tables (using MyISAM tables that get additional data imported every hour) to consolidate data that is written afterwards to other MyISAM tables.
max_heap_table_size is modified just before the creation of these tables. Default value is 128M, modified value is 15G.
Lately one of the memory tables reached its max allowed size, so I decided to increase max_heap_table_size in the PHP script from 15G to 18G,
This caused mysql to crash. Interesting is that it does not crash during the allocation of the memory but after, when the tables are already dropped (last time it even happened as the script was already finished).
I could reproduce this on 2 different servers, it happens as soon as I increases this parameter. Setting it back to 15G makes mysql stable again.
The server never swaps during the process (swappiness set to %) and it has free memory at al times.
######################
Stack trace:
08:29:31 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.
key_buffer_size=536870912
read_buffer_size=131072
max_used_connections=139
max_threads=700
thread_count=69
connection_count=68
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 2055554 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
Thread pointer: 0x7fd4a395da30
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7fd46a045e60 thread_stack 0x28000
/usr/sbin/mysqld(my_print_stacktrace+0x29)[0x7fd4a204b719]
/usr/sbin/mysqld(handle_fatal_signal+0x483)[0x7fd4a1f10ae3]
/lib/x86_64-linux-gnu/libpthread.so.0(+0xfcb0)[0x7fd4a0c5acb0]
/lib/x86_64-linux-gnu/libc.so.6(+0x14a714)[0x7fd4a03d9714]
/usr/sbin/mysqld(heap_write+0x163)[0x7fd4a2062253]
/usr/sbin/mysqld(_ZN7ha_heap9write_rowEPh+0x42)[0x7fd4a205df92]
/usr/sbin/mysqld(_ZN7handler12ha_write_rowEPh+0x68)[0x7fd4a1f18738]
/usr/sbin/mysqld(_ZN12select_union9send_dataER4ListI4ItemE+0x9a)[0x7fd4a1e6b10a]
/usr/sbin/mysqld(+0x32c5d4)[0x7fd4a1e1e5d4]
/usr/sbin/mysqld(+0x32264f)[0x7fd4a1e1464f]
/usr/sbin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x7e)[0x7fd4a1e165de]
/usr/sbin/mysqld(+0x3352f4)[0x7fd4a1e272f4]
/usr/sbin/mysqld(_ZN4JOIN4execEv+0xc03)[0x7fd4a1e36f43]
/usr/sbin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x130)[0x7fd4a1e32690]
/usr/sbin/mysqld(_Z21mysql_derived_fillingP3THDP3LEXP10TABLE_LIST+0x121)[0x7fd4a1dd76d1]
/usr/sbin/mysqld(_Z20mysql_handle_derivedP3LEXPFbP3THDS0_P10TABLE_LISTE+0x68)[0x7fd4a1dd7158]
/usr/sbin/mysqld(_Z20open_and_lock_tablesP3THDP10TABLE_LISTbjP19Prelocking_strategy+0x11a)[0x7fd4a1dbbcfa]
/usr/sbin/mysqld(+0x2fad15)[0x7fd4a1decd15]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x16a6)[0x7fd4a1df4876]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x10f)[0x7fd4a1df9a8f]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x1e81)[0x7fd4a1dfb9e1]
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x1bd)[0x7fd4a1ea1e1d]
/usr/sbin/mysqld(handle_one_connection+0x50)[0x7fd4a1ea1e80]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x7e9a)[0x7fd4a0c52e9a]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7fd4a03833fd]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7fcf881958c0): is an invalid pointer
Connection ID (thread ID): 105790
Status: NOT_KILLED
##############################
#################
my.cnf:
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
skip-external-locking
bind-address = 127.0.0.1
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
myisam-recover = BACKUP
query_cache_limit = 1M
query_cache_size = 16M
log_error = /var/log/mysql/error.log
expire_logs_days = 5
max_binlog_size = 100M
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
[isamchk]
key_buffer = 16M
!includedir /etc/mysql/conf.d/
#########################
#########################
extra.cnf:
[mysqld]
default-storage-engine = MyISAM
report_host = *******
port = 3306
bind-address = *******
skip-name-resolve
key_buffer = 512M
max_allowed_packet = 16M
thread_stack = 160K
thread_cache_size = 8
myisam-recover = BACKUP
myisam_sort_buffer_size = 256M
max_connections = 700
table_open_cache = 3500
open_files_limit = 65535
max_heap_table_size = 128M
tmp_table_size = 128M
query_cache_limit = 1M
query_cache_size = 16M
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 2
log-slow-slave-statements
server-id = 246
auto_increment_increment= 1
auto_increment_offset = 1
expire_logs_days = 5
max_binlog_size = 100M
character-set-server=utf8
collation-server=utf8_general_ci
skip-character-set-client-handshake
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
###############################
How to repeat:
set max_heap_table_size to more than 18G and populate and drop MEMORY tables.
