percona 5.7.18-16 crash

[Alexey Manikin]

We use Percona on more than 300 servers. We have the latest version of 5.7.18-16

I read about this probles - https://webcache.googleusercontent.com/search?q=cache:tM2qTD69Fx0J:https://bugs.mysql.com/bug.php%3Fid%3D85042+&cd=1&hl=ru&ct=clnk&gl=ru,

If I understand correctly in new versions of the Percona Server this is not corrected?

Crach report.

09:20:30 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.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.
Please help us make Percona Server better by reporting any
bugs at http://bugs.percona.com/

key_buffer_size=10810314752
read_buffer_size=3297280
max_used_connections=352
max_threads=509
thread_count=20
connection_count=20
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 13198779 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7f5a95b456b0 db: vkonta8w_direct
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 = 7f6279ebce40 thread_stack 0x80000
/usr/bin/mysqld(my_print_stacktrace+0x2c)[0xe619cc]
/usr/bin/mysqld(handle_fatal_signal+0x31d)[0x78186d]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x10340)[0x7f6f741fd340]
/lib/x86_64-linux-gnu/libc.so.6(+0x161f0b)[0x7f6f7266ff0b]
/usr/bin/mysqld[0x128f3dc]
/usr/bin/mysqld[0xe69e76]
/usr/bin/mysqld(lf_hash_search+0xd6)[0xe6a926]
/usr/bin/mysqld(_ZN11MDL_context21try_acquire_lock_implEP11MDL_requestPP10MDL_ticket+0x184)[0xb8c734]
/usr/bin/mysqld(_ZN11MDL_context12acquire_lockEP11MDL_requestm+0xa4)[0xb8d4e4]
/usr/bin/mysqld(_Z10open_tableP3THDP10TABLE_LISTP18Open_table_context+0xd4d)[0xbdad1d]
/usr/bin/mysqld(_Z11open_tablesP3THDPP10TABLE_LISTPjjP19Prelocking_strategy+0x707)[0xbe18a7]
/usr/bin/mysqld(_Z21open_tables_for_queryP3THDP10TABLE_LISTj+0x4a)[0xbe207a]
/usr/bin/mysqld(_ZN14Sql_cmd_update23try_single_table_updateEP3THDPb+0x76)[0xcc4ee6]
/usr/bin/mysqld(_ZN14Sql_cmd_update7executeEP3THD+0x27)[0xcc5347]
/usr/bin/mysqld(_Z21mysql_execute_commandP3THDb+0x15f6)[0xc37926]
/usr/bin/mysqld(_Z11mysql_parseP3THDP12Parser_state+0x645)[0xc3e155]
/usr/bin/mysqld(_Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command+0x101b)[0xc3f1cb]
/usr/bin/mysqld(_Z10do_commandP3THD+0x1b7)[0xc405c7]
/usr/bin/mysqld(handle_connection+0x2a0)[0xd02b60]
/usr/bin/mysqld(pfs_spawn_thread+0x1b4)[0x1191fe4]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x8182)[0x7f6f741f5182]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f6f7260930d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f5adb328890): UPDATE `direct_direct_phrase` SET `id`='10102447433',`min`='1',`max`='1',`premium_min`='20.2',`premium_max`='20.2',`bid4`='20.2',`context_min`='0',`context_max`='0',`real_price`='1',`position2`='20.2',`position5`='1',`position6`='1',`price1`='20.2',`price2`='20.2',`price3`='20.2',`price4`='20.2',`price5`='1',`price6`='1',`price7`='1',`price8`='1',`min_price`='0.3',`price`='15',`place`='5',`param1`='0',`param2`='0',`param3`='0',`context_coverage`='',`time`='2017-08-11 12:20:30',`context_price`='0.00' WHERE `id` ='10102447433'
Connection ID (thread ID): 36365381
Status: NOT_KILLED

You may download the Percona Server operations manual by visiting
http://www.percona.com/software/percona-server/. You may find information
in the manual which will help you identify the cause of the crash.

atom2 : ~ [0] # free -m
              total used free shared buff/cache available
Mem: 257738 60042 17785 28328 179910 155472
Swap: 0 0 0

atom2 : ~ [0] # cat /etc/docker/mysql/my.cnf
################################################################################
# Main config
################################################################################
[mysqld]

################################################################################
# Common
################################################################################

# wait timeout (drop long sleeping clients)
interactive_timeout = 30
wait_timeout = 30

# system setting
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
basedir = /usr
datadir = /home/mysql
tmpdir = /home/tmpfs

# bind settings
# see conf.d

# external locking for myisam tables
skip_external_locking = ON
delay_key_write = ON

# default character set
character-set-server = UTF8
collation-server = utf8_general_ci

# threads settings
thread_stack = 2048K
thread_cache_size = 96

# extra connections
extra_port = 3308
extra_max_connections = 100

# connections
max_user_connections = 60
max_connections = 409
back_log = 65535

# beget cgroups

# per-connections buffers
# (join_buffer_size + read_buffer_size + sort_buffer_size + read_rnd_buffer_size) * max_connections
join_buffer_size = 3299046
read_buffer_size = 3299046
read_rnd_buffer_size = 3299046
sort_buffer_size = 3299046

# disable query cache
# (due to global mutex we may have degraded performance in high concurrence environment)
query_cache_type = 1
query_cache_size = 0
query_cache_limit = 1048576
query_cache_strip_comments = 1
query_cache_min_res_unit = 4096

# max blob size
max_allowed_packet = 33554432
group_concat_max_len = 1024

# sql mode (bitrix requirement)
sql_mode =

# tmp tables
tmp_table_size = 5405157621
max_heap_table_size = 5405157621

# max errors before kick client out
max_connect_errors = 65000
max_error_count = 65000

# open files limit (maximum possible)
open_files_limit = 1048576

# open table cache
table_open_cache = 50000
table_definition_cache = 50000
table_open_cache_instances = 64

# disable auto flush by default (do it manually by cron)
flush_time = 0

# general log settings
log_error = /var/log/mysql/error.log
log_warnings = 2

# slow log settings
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# low priority updates
low_priority_updates = ON

# metadata locks
metadata_locks_hash_instances = 64

# insert buffer
bulk_insert_buffer_size = 16M

# delayed insert
delayed_insert_limit = 1
max_delayed_threads = 0

#max search depth for query optimizer
optimizer_search_depth = 62

# performance schema
performance_schema = OFF

# enable percona userstat feature
userstat = ON

# preload buffer
preload_buffer_size = 65535

# query alloc size
query_alloc_block_size = 16384
query_prealloc_size = 16384

# transactions alloc size
transaction_alloc_block_size = 16384
transaction_prealloc_size = 16384

# transaction isolation level
transaction-isolation = READ-COMMITTED

# case sensitivity
lower_case_table_names = 0

################################################################################
# MyISAM
################################################################################

# recover type
myisam_recover_options = BACKUP

# recovery buffer
myisam_sort_buffer_size = 512M

# key buffer
key_buffer_size = 10810315243

#fulltext
ft_min_word_len = 4

################################################################################
# InnoDB
################################################################################

# separate tablespace
innodb_file_per_table = ON

# file format
innodb_file_format = 'Barracuda'

# log files
innodb_log_file_size = 256M
innodb_log_files_in_group = 4

# open files
innodb_open_files = 50000

# innodb threads
innodb_thread_concurrency = 48
innodb_adaptive_max_sleep_delay = 10000
innodb_concurrency_tickets = 5000
innodb_read_io_threads = 8
innodb_write_io_threads = 16
innodb_purge_threads = 16
innodb_sync_array_size = 64
innodb_lock_wait_timeout = 50

# I/O optimization
innodb_random_read_ahead = ON
innodb_read_ahead_threshold = 32
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

# buffer pools
innodb_buffer_pool_instances = 64
innodb_buffer_pool_size = 32430945730
innodb_log_buffer_size = 256M
innodb_sort_buffer_size = 64M

# preload buffer pool
innodb_buffer_pool_load_at_startup = ON
innodb_buffer_pool_dump_at_shutdown = ON

# percona specific feature
innodb_corrupt_table_action = warn
innodb_kill_idle_transaction = 30

# innodb fulltext
innodb_ft_enable_stopword = ON
innodb_ft_min_token_size = 3
innodb_ft_max_token_size = 84

# innodb large prefix
innodb_large_prefix = 1

# interval between successive column values
auto-increment-increment = 1
auto-increment-offset = 1

# innodb strict mode
innodb_strict_mode = 0

# innodb old block sublist
innodb_old_blocks_pct = 20

# innodb temp data file
innodb_temp_data_file_path = ibtmp1:104857600:autoextend:max:8589934592

# innodb numa policy
innodb_numa_interleave = 0

################################################################################
# client settings
################################################################################

[mysqldump]
quick
quote-names
max_allowed_packet = 32M

[mysql]

[isamchk]
key_buffer = 32M

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

################################################################################
# includes
################################################################################

!includedir /etc/mysql/conf.d/

# EOF

[Nickolay Ihalainen (ihanick)]

Hi Alexey,

Yes, latest Percona Server should be affected by this bug.

The bug you are referring require check with mysql 5.7.19, you can try to verify it with upstream mysqld or provide reproducible test case / test conditions.

[Alexey Manikin (alexey-x)]

Hi Nickolay,

Ok wait until they fix it on Percona Server. I can not provide the environment for the work of the bug, since the error occurs at random time, on random databases, on random select and on different servers.

The last version of the MySQL (and MariaDB server) did not observe the error.

ps. sorry my English.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-3730