MySQL Bugs: #86330: MySQL spontaneously restarts with error 'mysqld got signal 11;'

Bug #86330	MySQL spontaneously restarts with error 'mysqld got signal 11;'
Submitted:	15 May 2017 21:57	Modified:	16 May 2017 9:00
Reporter:	Bazard Shoxer	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.7.18 and 5.7.12	OS:	Ubuntu
Assigned to:		CPU Architecture:	Any
Tags:	msyqld, MySQL, Signal 11

Description:
We're developing a web-application with some average load (+/- hundred of SELECT/INSERT/UPDATE queries per second), since last week, we're experiencing some spontaneously restarts on our MySQL server. Already searched a lot and tried a few things. Got the same problem om MySQL 5.7.18-0ubuntu0.16.04.1 - (Ubuntu) as well as MySQL5.7.12-1~exp1+deb.sury.org~wily+1 - (Ubuntu).

Tried to figure out what query causes the mysql-server to fail and exit, by logging all queries, but it seems to happen randomly. Database contains InnoDB, MyISAM and MEMORY-tables. Some of them have some JSON-columns, where we store (JSON_SET/JSON_MERGE) and get (JSON_EXTRACT) data from.

What could cause this problem? Is it a bug? Are we doing something wrong?

----

18:07:07 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.

key_buffer_size=16777216
read_buffer_size=131072
max_used_connections=2
max_threads=151
thread_count=1
connection_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 76381 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7f01b40142a0
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 = 7f01df72ee70 thread_stack 0x30000
/usr/sbin/mysqld(my_print_stacktrace+0x3b)[0xe7a8eb]
/usr/sbin/mysqld(handle_fatal_signal+0x489)[0x783049]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x10d10)[0x7f021cfc2d10]
/usr/sbin/mysqld(_ZNK10Item_field11used_tablesEv+0x19)[0x7dcb39]
/usr/sbin/mysqld(_ZNK4Item10const_itemEv+0x15)[0x7b8ae5]
/usr/sbin/mysqld(_ZN19Item_func_concat_ws7val_strEP6String+0x102)[0xb3b892]
/usr/sbin/mysqld(_ZN12Item_func_if7val_strEP6String+0xa0)[0x7fea80]
/usr/sbin/mysqld(_ZN4Item19save_in_field_innerEP5Fieldb+0x8d)[0x7df25d]
/usr/sbin/mysqld(_ZN4Item13save_in_fieldEP5Fieldb+0x15)[0x7ef3a5]
/usr/sbin/mysqld(_Z28update_generated_read_fieldsPhP5TABLEj+0xa2)[0xce7592]
/usr/sbin/mysqld(_ZN7handler17ha_index_read_mapEPhPKhm16ha_rkey_function+0xf4)[0x7d68d4]
/usr/sbin/mysqld(_ZN7handler21multi_range_read_nextEPPc+0x208)[0x7d7528]
/usr/sbin/mysqld(_ZN18QUICK_RANGE_SELECT8get_nextEv+0x5a)[0xd7ba8a]
/usr/sbin/mysqld[0xbaa07f]
/usr/sbin/mysqld(_Z12mysql_updateP3THDR4ListI4ItemES4_y15enum_duplicatesPyS6_+0x84c)[0xcc8aac]
/usr/sbin/mysqld(_ZN14Sql_cmd_update23try_single_table_updateEP3THDPb+0x1cc)[0xccba6c]
/usr/sbin/mysqld(_ZN14Sql_cmd_update7executeEP3THD+0x36)[0xccbde6]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THDb+0x2323)[0xc41f23]
/usr/sbin/mysqld(_Z11mysql_parseP3THDP12Parser_state+0x3dd)[0xc4688d]
/usr/sbin/mysqld(_Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command+0xfb0)[0xc47890]
/usr/sbin/mysqld(_Z10do_commandP3THD+0x1c7)[0xc48cf7]
/usr/sbin/mysqld(handle_connection+0x288)[0xd06e08]
/usr/sbin/mysqld(pfs_spawn_thread+0x1b4)[0xef56d4]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76aa)[0x7f021cfb96aa]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f021c44e13d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f01b4ab4730): [ Query in private comments ]
Connection ID (thread ID): 45
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

How to repeat:
-

Thank you for the bug report. There is no test case provided in the bug report and hence there is nothing we can verify here.  If you are able to reproduce the bug with one of the latest versions, please attach the exact reproducible
test case and change the version on this bug report to the version you
tested and change the status back to "Open". Also check for corrupted tables (your log shows errors messages about crashed tables, check hardware issue memory, disks etc).

After lots and lots of searching, debugging, trying... and a lot of crashes! I think we found it

We're having a table which has at least the following two columns: `prefix` VARCHAR(248) utf8mb4 and `parentPrefix`  VARCHAR(248) utf8mb4. The parentPrefix is a dot (.) seperated path of all parents in the hierarchy. We use a VIRTUAL column to combine them into a 'fullPrefix' TEXT. For example:

Node A has prefix 'NodeA', parentPrefix NULL.
Node B (child of Node A), has prefix 'NodeB', parentPrefix 'NodeA'
Node C (child of Node B), has prefix 'NodeC', parentPrefix 'NodeA.NodeB'

The hierarchy will look something like this:

 - Node A (prefix 'NodeA', parentPrefix: NULL, fullPrefix: NodeA)
  - Node B (prefix 'NodeB', parentPrefix: 'NodeA', fullPrefix: NodeA.NodeB)
    - Node C (prefix 'NodeC', parentPrefix: 'NodeA.NodeB', fullPrefix: NodeA.NodeB.NodeC)

Below is the export of the `fullPrefix` column:

ALTER TABLE [tablename] ADD `fullPrefix` TEXT AS (if((`parentPrefix` <> ''),concat_ws('.',`parentPrefix`,`prefix`),`prefix`)) VIRTUAL AFTER `prefix`;

When we delete the colom / make it a normal column, everything goes well. When using the VIRTUAL column, using CONCAT_WS, which also uses the dot (.) as seperator, even the most simple query like 'SELECT * FROM [tablename] WHERE 1', !SOMETIMES! results into mysqld exiting with signal 11.

In the logs from the previous post, we found the following line:
/usr/sbin/mysqld(_ZN19Item_func_concat_ws7val_strEP6String+0x102)[0xb3ec82]

The CONCAT_WS function on a VIRTUAL column seems to be the problem. Strangely enough, it doesn't !ALWAYS! give the error. Sometime it does, sometimes it doesn't... We're having the problem on both MySQL versions (5.7.12 and 5.7.18). Problems on both servers can be fixed to remove the CONCAT_WS function on the VIRTUAL column.

We now changed our tables, using a trigger before insert/update to CONCAT_WS the values of the `parentPrefix` and `prefix` columns into `fullPrefix`. For now, this works for us, but there seems to be something very wrong in the CONCAT_WS function, combined with a VIRTUAL column!