Greg Smethells added a comment - 2021-02-09 20:00 Our company has run into this issue and it is affecting a medical clinic customer of ours. Any direction you can help provide would be very much appreciated. Thank you.

Greg Smethells added a comment - 2021-02-09 20:04 - edited This issue can be reproduced by using the same query from the MariaDB prompt (monitor mode) provided by the mysql client. MariaDB is running as a process in a guest OS of CentOS 8 running in an lxc container on a host OS of Ubuntu 20.04 The same set up is used on some sites without issue. We ran into this same issue on one or two sites besides the one that created the stack trace above. Rebuilding the table appeared to fix the issue, but we are not certain of that since it's only been a short period of time thus far. Because the crash is cropping up on more than one customer site, we are starting to get a bit more concerned. We did try using MariaDB 10.5.8 instead, but it had the same issue.

Greg Smethells added a comment - 2021-02-09 22:43 - edited Could this possibly be related to MDEV-13180 ? It seems to crash when no patient name (pname) matches the subquery SELECT criteria.

Daniel Black added a comment - 2021-02-09 23:21 As you are using that was above the fixed version in MDEV-13180 it isn't likely to be the cause. Can you include SHOW CREATE TABLE 774_study (and other tables in the query) and EXPLAIN query Did apport save the core dump? Are you able to create backtrace from it - https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mysqld/#getting-backtraces-for-all-threads-from-a-core-file On a test system we are just about to release the next 10.5 version. If you are able to an almost release version http://hasky.askmonty.org/archive/10.5/build-37795/kvm-rpm-centos8-amd64/rpms/ that would be useful as well. Thanks.

Greg Smethells added a comment - 2021-02-10 00:48 - edited Thank you for your quick reply. We have turned on core_file and it did crash but did not create a core file in /var/shared/apport/ but that may be because it's an lxc container. We will figure that out. The SHOW CREATE TABLE's and EXPLAIN are below. MariaDB [joints]> SHOW CREATE TABLE 774_study; | Table | Create Table | 774_study | CREATE TABLE `774_study` ( `received` datetime(6) NOT NULL, `siuid` varchar(64) NOT NULL, `patientID` int(10) unsigned NOT NULL, `refphys` varchar(100) NOT NULL, `institution` varchar(64) NOT NULL, `sdate` date NOT NULL, `stime` time NOT NULL, `sdesc` varchar(64) NOT NULL, `modality` varchar(8) NOT NULL, `bodypart` varchar(32) NOT NULL, `acnum` varchar(32) NOT NULL, `backedup` datetime(6) NOT NULL, `modified` datetime(6) NOT NULL, `accessed` datetime(6) NOT NULL, `numimages` smallint(5) unsigned NOT NULL, `numsnapshots` smallint(5) unsigned NOT NULL, `numattachments` smallint(5) unsigned DEFAULT NULL, `tsuid` varchar(28) NOT NULL, `orderedSOPs` mediumtext DEFAULT NULL, `cached` tinyint(1) NOT NULL DEFAULT 0, PRIMARY KEY (`received`), UNIQUE KEY `idx_774_study_siuid` (`siuid`), KEY `idx_774_study_patientID` (`patientID`), KEY `idx_774_study_sdate` (`sdate`), CONSTRAINT `fk_774_study_patientID` FOREIGN KEY (`patientID`) REFERENCES `774_patient` (`patientID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | | Table | Create Table | 774_patient | CREATE TABLE `774_patient` ( `patientID` int(10) unsigned NOT NULL AUTO_INCREMENT, `patientStatus` tinyint(3) unsigned NOT NULL, `pid` varchar(64) NOT NULL, `pname` varchar(100) NOT NULL, `dob` date NOT NULL, `sex` enum('M','F','O') NOT NULL DEFAULT 'O', PRIMARY KEY (`patientID`), UNIQUE KEY `idx_774_patient_pid` (`pid`) ) ENGINE=InnoDB AUTO_INCREMENT=317145 DEFAULT CHARSET=utf8 | MariaDB [joints]> explain select 774_study.patientID AS patientID, 774_patient.patientStatus AS patientStatus, 774_patient.pid AS pid, 774_patient.pname AS pname, 774_patient.dob AS dob, 774_patient.sex AS sex, 774_study.received AS received, 774_study.sdate AS sdate, 774_study.stime AS stime, 774_study.modality AS modality, 774_study.refphys AS refphys, 774_study.numimages AS numimages, 774_study.numsnapshots AS numsnapshots, 774_study.numattachments AS numattachments, 774_study.sdesc AS sdesc, 774_study.backedup AS backedup, 774_study.modified AS modified, 774_study.siuid AS siuid, 774_study.institution AS institution, 774_study.bodypart AS bodypart, 774_study.tsuid AS tsuid, 774_study.acnum AS acnum from 774_study left join 774_patient on 774_study.patientID = 774_patient.patientID where exists (select pname from 774_patient where pname like 'doe%john%^w%^%^%^%') and 774_patient.pname in (select pname from 774_patient where pname like 'doe%john%^w%^%^%^%') order by received desc limit 0,50; +------+--------------+-------------+--------+-------------------------+-------------------------+---------+------------------------------+-------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------+-------------+--------+-------------------------+-------------------------+---------+------------------------------+-------+---------------------------------+ | 1 | PRIMARY | 774_patient | ALL | PRIMARY | NULL | NULL | NULL | 28902 | Using temporary; Using filesort | | 1 | PRIMARY | <subquery3> | eq_ref | distinct_key | distinct_key | 304 | func | 1 | | | 1 | PRIMARY | 774_study | ref | idx_774_study_patientID | idx_774_study_patientID | 4 | joints.774_patient.patientID | 1 | | | 3 | MATERIALIZED | 774_patient | ALL | NULL | NULL | NULL | NULL | 28902 | Using where | | 2 | SUBQUERY | 774_patient | ALL | NULL | NULL | NULL | NULL | 28902 | Using where | +------+--------------+-------------+--------+-------------------------+-------------------------+---------+------------------------------+-------+---------------------------------+ 5 rows in set (0.001 sec) MariaDB [joints]> MariaDB [joints]> select 774_study.patientID AS patientID, 774_patient.patientStatus AS patientStatus, 774_patient.pid AS pid, 774_patient.pname AS pname, 774_patient.dob AS dob, 774_patient.sex AS sex, 774_study.received AS received, 774_study.sdate AS sdate, 774_study.stime AS stime, 774_study.modality AS modality, 774_study.refphys AS refphys, 774_study.numimages AS numimages, 774_study.numsnapshots AS numsnapshots, 774_study.numattachments AS numattachments, 774_study.sdesc AS sdesc, 774_study.backedup AS backedup, 774_study.modified AS modified, 774_study.siuid AS siuid, 774_study.institution AS institution, 774_study.bodypart AS bodypart, 774_study.tsuid AS tsuid, 774_study.acnum AS acnum from 774_study left join 774_patient on 774_study.patientID = 774_patient.patientID where exists (select pname from 774_patient where pname like 'doe%john%^w%^%^%^%') and 774_patient.pname in (select pname from 774_patient where pname like 'doe%john%^w%^%^%^%') order by received desc limit 0,50; Empty set (0.010 sec) MariaDB [joints]> SHOW GLOBAL VARIABLES LIKE 'core_file'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | core_file | ON | +---------------+-------+ $ ulimit -c 0 $ sudo cat /proc/$(pidof mysqld)/limits | grep "core file" cat: /proc//limits: No such file or directory

Daniel Black added a comment - 2021-02-10 00:54 Thanks. `Core pattern:` is set to a pipe and inside the lxc container there isn't the same program. Setting sysctl kernel.core_pattern=filename again should make it dump the the filename in the container.

Greg Smethells added a comment - 2021-02-10 01:03 - edited No dice? Never mind, one of our sys admins has got it set up. $ sysctl kernel.core_pattern="%e-%t.core" sysctl: setting key "kernel.core_pattern": Read-only file system $ FYI this clinic is in Alaska time zone and we will not be able to restart the lxc container to take up the changes for the core file until after clinic hours which will be around 9 PM central time.

Daniel Black added a comment - 2021-02-10 01:11 - edited yes, sysctl's are only settable globally outside the container not an excuse for crashing however as a potential workaround (and query improvement): left join 774_patient on 774_study.patientID = 774_patient.patientID where exists ( select pname from 774_patient where pname like 'doe%^john%^%^%^%' ) and 774_patient.pname in ( select pname from 774_patient where pname like 'doe%^john%^%^%^%' ) order by ... could be simpler with: join 774_patient on 774_study.patientID = 774_patient.patientID AND 74_patient.pname like 'doe%^john%^%^%^%' order by .... An index on pname would also help both your original query, and the updated variant.

Greg Smethells added a comment - 2021-02-10 01:29 - edited We previously used the simpler, second variation you suggest but found that wildcard searching slowed it down so much that we were forced into the stranger, first variation for speed reasons. This was on version 10.1.32 though and so perhaps we are well behind the times and the speed concern is no more. We tend to stay on stable versions because this is used by clinics and we are only recently moving ourselves up to a newer OS and newer versions of other packages one by one.

Greg Smethells added a comment - 2021-02-10 15:58 The backtrace has been uploaded.

Greg Smethells added a comment - 2021-02-10 17:35 This is affecting a few current medical clinics and potentially many/most future installations. What kind of timeframe should I anticipate? Is there more information that I should be providing? Thank you.

Daniel Black added a comment - 2021-02-11 03:20 gsmethells I've searched for duplicates of this MDEV based on your backtrace and I've been unable to find any. Attempting to reproduce with sample sample data that generated the same query plan has also been unsuccessful so far. minimized replica data CREATE TABLE `774_patient` ( `patientID` int(10) unsigned NOT NULL AUTO_INCREMENT, `pname` varchar(100) NOT NULL, PRIMARY KEY (`patientID`) ) ENGINE=InnoDB AUTO_INCREMENT=317145 DEFAULT CHARSET=utf8 ;   CREATE TABLE `774_study` ( `received` datetime(6) NOT NULL, `patientID` int(10) unsigned NOT NULL, PRIMARY KEY (`received`), KEY `idx_774_study_patientID` (`patientID`), CONSTRAINT `fk_774_study_patientID` FOREIGN KEY (`patientID`) REFERENCES `774_patient` (`patientID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;;     insert into 774_patient select seq,uuid() from seq_1_to_28902; insert into 774_study select date_add(MAKEDATE(2002 + patientID / 3650 , (patientID/10) mod 365 + 1), interval (patientID mod 10) hour), patientID from 774_patient; insert into 774_study select date_add(MAKEDATE(2015 + patientID / 3650 , (patientID/10) mod 365 + 1), interval (patientID mod 10) hour), patientID from 774_patient;   select * from 774_study left join 774_patient on 774_study.patientID = 774_patient.patientID where exists (select pname from 774_patient where pname like 'doe%john%^w%^%^%^%') and 774_patient.pname in (select pname from 774_patient where pname like 'doe%john%^w%^%^%^%') order by received desc limit 0,50; Thanks for the backtrace. Unfortunately it didn't have debug symbols installed. install debug symbols yum install MariaDB-server-debuginfo These are used by gdb so the previous core dump file will be sufficient and will have symbol resolution after the MariaDB-server-debuginfo is installed. Please also note which mariadb version the backtrace is from. Are any non-default tunings used (apart from 2G innodb buffer pool size). Hopefully with that I'll see which of the pointers in Copy_field::set is a null pointer and where in the function it crashed. Are you able to experiment with the query to get a minimal set of `SELECT` columns that generates the crash? Are tables with this minimal set of values sufficiently anonymized to be shared (SELECT INTO OUTFILE, maybe with UUID() for name and other fudged expressions) https://mariadb.com/kb/en/meta/mariadb-ftp-server/ ?

Greg Smethells added a comment - 2021-02-11 17:52 yes I have used gdb myself to track down issues using debug symbols before if I had to in grad school. I know what you mean and I’m sorry we forgot to do that originally. I will get an engineer of mine involved to help you get what you need.

Greg Smethells added a comment - 2021-02-11 17:53 i’m unsure if we will be able to give you a minimal anonymize sample set or not. We can give that a try and let you know soon.

David McMichael added a comment - 2021-02-11 18:25 Hi Daniel, I've attached a new backtrace with the debug package installed and our my.cnf file. The backtrace is from: mysql Ver 15.1 Distrib 10.5.5-MariaDB, for Linux (x86_64) using readline 5.1

Daniel Black added a comment - 2021-02-11 23:14 Thanks dmcmichael@medstrat.com , gsmethells . https://github.com/MariaDB/server/blob/mariadb-10.5.5/sql/field_conv.cc#L669 Narrowing down the field would probably be just a useful as an a minimal anonymized sample. https://github.com/MariaDB/server/blob/mariadb-10.5.5/sql/field_conv.cc#L669 indicates *from is probably not right In gdb can you `p *from` as that may be corrupted p *from up # (up frame to https://github.com/MariaDB/server/blob/mariadb-10.5.5/sql/sql_select.cc#L18824) info locals p i p m_from_field[i - 1]

Daniel Black added a comment - 2021-02-11 23:20 Or if you take a local copy of these two tables. Load them into a new instance. And try the segfaulting query eliminating a table column at a time from the result set.

Greg Smethells added a comment - 2021-02-12 00:06 - edited gdb) p *from $2 = {<Value_source> = {<No data fields>}, _vptr.Field = 0xc1, ptr = 0x0, invisible = VISIBLE, null_ptr = 0x0, table = 0x0, orig_table = 0x0, table_name = 0x0, field_name = {str = 0x0, length = 0}, comment = {str = 0x0, length = 0}, option_list = 0x0, option_struct = 0x0, key_start = { static BITS_PER_ELEMENT = 64, static ARRAY_ELEMENTS = 1, static ALL_BITS_SET = 18446744073709551615, buffer = {0}}, part_of_key = { static BITS_PER_ELEMENT = 64, static ARRAY_ELEMENTS = 1, static ALL_BITS_SET = 18446744073709551615, buffer = {0}}, part_of_key_not_clustered = { static BITS_PER_ELEMENT = 64, static ARRAY_ELEMENTS = 1, static ALL_BITS_SET = 18446744073709551615, buffer = {0}}, part_of_sortkey = { static BITS_PER_ELEMENT = 64, static ARRAY_ELEMENTS = 1, static ALL_BITS_SET = 18446744073709551615, buffer = {0}}, unireg_check = Field::NONE, --Type <RET> for more, q to quit, c to continue without paging-- field_length = 0, flags = 0, field_index = 0, null_bit = 0 '\000', is_created_from_null_item = false, cond_selectivity = 0, next_equal_field = 0x0, read_stats = 0x0, collected_stats = 0x0, vcol_info = 0x0, check_constraint = 0x0, default_value = 0x0} gdb) up #5 0x00005653610529b7 in Create_tmp_table::finalize(THD*, TABLE*, TMP_TABLE_PARAM*, bool, bool) () at /usr/src/debug/MariaDB-10.5.5/src_0/sql/sql_select.cc:18824 18824 copy->set(field, m_from_field[i], m_save_sum_fields); (gdb) info locals null_ptr = 0x0 TIME_INVALID_DATES = { m_mode = date_co nv_mode_t::INVALID_DATES} TIME_FUZZY_DATES = { m_mode = date_conv_mode_t::FUZZY_DATES} std::__ioinit = { static _S_refcount = <optimized out>, static _S_synced_with_stdio = <optimized out>} TIME_TIME_ONLY = { m_mode = date_conv_mode_t::TIME_ONLY} TIME_NO_ZERO_IN_DATE = <optimized out> TIME_FRAC_NONE = <optimized out> TIME_FRAC_TRUNCATE = <optimized out> TIME_FRAC_ROUND = <optimized out> sp_data_access_name = <optimized out> TIME_NO_ZEROS = <optimized out> TIME_INTERVAL_hhmmssff = <optimized out> TIME_INTERVAL_DAY = <optimized out> TIME_MODE_FOR_XXX_TO_DATE = <optimized out> TIME_NO_ZERO_DATE = <optimized out> TIME_CONV_NONE = <optimized out> distinct_key = { str = 0x5653618367cf "distinct_key", length = 12} --Type <RET> for more, q to quit, c to continue without paging-- join_type_str = 0x5653621411e0 <join_type_str> group_key = {str = 0x5653618367dc "group_key", length = 9} gdb) p i No symbol "i" in current context. (gdb) p m_from_field[i-1] No symbol "m_from_field" in current context. (gdb)

Greg Smethells added a comment - 2021-02-12 00:43 Did the compiler optimize some variables out?

Daniel Black added a comment - 2021-02-12 00:48 - edited on i, maybe, others probably not. p *to up p *field p *copy p *(copy - 1) might be more insightful. Thank for you help and debugging my message is hard and I appreciate your patience.

Greg Smethells added a comment - 2021-02-12 01:09 - edited gdb) up #4 0x00005653611f9713 in Copy_field::set ( this=0x7f17f40453d8, to=0x7f17f40461f0, from=0x7f17f403fdc0, save=<optimized out>) at /usr/src/debug/MariaDB-10.5.5/src_0/sql/field_conv.cc:669 669 from_length=from->pack_length_in_rec(); (gdb) p *to $1 = {<Value_source> = {<No data fields>}, _vptr.Field = 0x5653620457e8 <vtable for Field_short+16>, ptr = 0x7f17f4040110 "", invisible = VISIBLE, null_ptr = 0x0, table = 0x7f17f403e300, orig_table = 0x7f17f80a5678, table_name = 0x7f17f403e410, field_name = { str = 0x7f17f4051b08 "numsnapshots", length = 12}, comment = { str = 0x56536197b81d "", length = 0}, option_list = 0x0, option_struct = 0x0, key_start = {static BITS_PER_ELEMENT = 64, static ARRAY_ELEMENTS = 1, static ALL_BITS_SET = 18446744073709551615, buffer = {0}}, part_of_key = { static BITS_PER_ELEMENT = 64, static ARRAY_ELEMENTS = 1, static ALL_BITS_SET = 18446744073709551615, buffer = {0}}, part_of_key_not_clustered = { static BITS_PER_ELEMENT = 64, static ARRAY_ELEMENTS = 1, static ALL_BITS_SET = 18446744073709551615, buffer = {0}}, part_of_sortkey = { static BITS_PER_ELEMENT = 64, --Type <RET> for more, q to quit, c to continue without paging-- static ARRAY_ELEMENTS = 1, static ALL_BITS_SET = 18446744073709551615, buffer = {0}}, unireg_check = Field::NONE, field_length = 5, flags = 4129, field_index = 12, null_bit = 0 '\000', is_created_from_null_item = false, cond_selectivity = 1, next_equal_field = 0x0, read_stats = 0x7f17f80bcdd0, collected_stats = 0x0, vcol_info = 0x0, check_constraint = 0x0, default_value = 0x0} (gdb) up #5 0x00005653610529b7 in Create_tmp_table::finalize(THD*, TABLE*, TMP_TABLE_PARAM*, bool, bool) () at /usr/src/debug/MariaDB-10.5.5/src_0/sql/sql_select.cc:18824 18824 copy->set(field, m_from_field[i], m_save_sum_fields); (gdb) p *field No symbol "field" in current context. (gdb) p *copy No symbol "copy" in current context. (gdb) p *(copy - 1) No symbol "copy" in current context. (gdb) info locals null_ptr = 0x0 TIME_INVALID_DATES = { m_mode = date_conv_mode_t::INVALID_DATES} TIME_FUZZY_DATES = { m_mode = date_conv_mode_t::FUZZY_DATES} std::__ioinit = {static _S_refcount = 336, static _S_synced_with_stdio = true} TIME_TIME_ONLY = { m_mode = date_conv_mode_t::TIME_ONLY} TIME_NO_ZERO_IN_DATE = <optimized out> TIME_FRAC_NONE = <optimized out> TIME_FRAC_TRUNCATE = <optimized out> TIME_FRAC_ROUND = <optimized out> sp_data_access_name = <optimized out> TIME_NO_ZEROS = <optimized out> TIME_INTERVAL_hhmmssff = <optimized out> TIME_INTERVAL_DAY = <optimized out> TIME_MODE_FOR_XXX_TO_DATE = <optimized out> TIME_NO_ZERO_DATE = <optimized out> TIME_CONV_NONE = <optimized out> distinct_key = { str = 0x5653618367cf "distinct_key", length = 12} join_type_str = 0x5653621411e0 <join_type_str> --Type <RET> for more, q to quit, c to continue without paging-- group_key = {str = 0x5653618367dc "group_key", length = 9} (gdb)

Greg Smethells added a comment - 2021-02-12 01:14 Is a stray pointer overwriting memory, corrupting the stack at 18824 copy->set(field, m_from_field[i], m_save_sum_fields);

Greg Smethells added a comment - 2021-02-12 01:17 while gdb) up #4 0x00005653611f9713 in Copy_field::set ( this=0x7f17f40453d8, to=0x7f17f40461f0, from=0x7f17f403fdc0, save=<optimized out>) at /usr/src/debug/MariaDB-10.5.5/src_0/sql/field_conv.cc:669 669 from_length=from->pack_length_in_rec(); nets variables shown for this=0x7f17f40453d8, to=0x7f17f40461f0, from=0x7f17f403fdc0, save=<optimized out>) yet (gdb) up #5 0x00005653610529b7 in Create_tmp_table::finalize(THD*, TABLE*, TMP_TABLE_PARAM*, bool, bool) () at /usr/src/debug/MariaDB-10.5.5/src_0/sql/sql_select.cc:18824 18824 copy->set(field, m_from_field[i], m_save_sum_fields); nets no such info for any variables in the entire line

Daniel Black added a comment - 2021-02-12 01:44 It does list `numsnapshots` with matching length, compared to the from->_vptr.Field which looks corrupted. I'll play around with this field in the fake data to attempt to reproduce. Thanks for the late night. If tomorrow you're able to experiment around with variants of the query without this field and maybe subsequent columns to see if that alters the behavior.

Daniel Black added a comment - 2021-02-12 03:29 I'm still failing on attempting to reproduce this $ podman exec -i m1055 mysql test < /tmp/MDEV-24827.sql   ~/repos/build-mariadb-server-10.5 $ podman exec -ti m1055 mysql test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A   Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 16 Server version: 10.5.5-MariaDB-1:10.5.5+maria~focal mariadb.org binary distribution   Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.   MariaDB [test]> select pname from 774_patient limit 4 offset 32; +--------------------------------------+ | pname | +--------------------------------------+ | 114593ed-6cda-11eb-8a98-ba41f8bcbdf2 | | 1145940e-6cda-11eb-8a98-ba41f8bcbdf2 | | 1145942e-6cda-11eb-8a98-ba41f8bcbdf2 | | 1145944f-6cda-11eb-8a98-ba41f8bcbdf2 | +--------------------------------------+ 4 rows in set (0.001 sec)   MariaDB [test]> set @n = '11458%'; Query OK, 0 rows affected (0.000 sec)   MariaDB [test]> explain select 774_study.patientID AS patientID, 774_patient.patientStatus AS patientStatus, 774_patient.pid AS pid, 774_patient.pname AS pname, 774_patient.dob AS dob, 774_patient.sex AS sex, 774_study.received AS received, 774_study.sdate AS sdate, 774_study.stime AS stime, 774_study.modality AS modality, 774_study.refphys AS refphys, 774_study.numimages AS numimages, 774_study.numsnapshots AS numsnapshots, 774_study.numattachments AS numattachments, 774_study.sdesc AS sdesc, 774_study.backedup AS backedup, 774_study.modified AS modified, 774_study.siuid AS siuid, 774_study.institution AS institution, 774_study.bodypart AS bodypart, 774_study.tsuid AS tsuid, 774_study.acnum AS acnum from 774_study left join 774_patient on 774_study.patientID = 774_patient.patientID where exists (select pname from 774_patient where pname like @n) and 774_patient.pname in (select pname from 774_patient where pname like @n) order by received desc limit 0,50; +------+----------------------+-------------+--------+-------------------------+-------------------------+---------+----------------------------+-------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+----------------------+-------------+--------+-------------------------+-------------------------+---------+----------------------------+-------+---------------------------------+ | 1 | PRIMARY | 774_patient | ALL | PRIMARY | NULL | NULL | NULL | 28906 | Using temporary; Using filesort | | 1 | PRIMARY | <subquery3> | eq_ref | distinct_key | distinct_key | 304 | func | 1 | | | 1 | PRIMARY | 774_study | ref | idx_774_study_patientID | idx_774_study_patientID | 4 | test.774_patient.patientID | 1 | | | 3 | MATERIALIZED | 774_patient | ALL | NULL | NULL | NULL | NULL | 28906 | Using where | | 2 | UNCACHEABLE SUBQUERY | 774_patient | ALL | NULL | NULL | NULL | NULL | 28906 | Using where | +------+----------------------+-------------+--------+-------------------------+-------------------------+---------+----------------------------+-------+---------------------------------+ 5 rows in set (0.001 sec)   MariaDB [test]> set @n = '11458%john%^w%^%^%^%'; Query OK, 0 rows affected (0.000 sec)   MariaDB [test]> explain select 774_study.patientID AS patientID, 774_patient.patientStatus AS patientStatus, 774_patient.pid AS pid, 774_patient.pname AS pname, 774_patient.dob AS dob, 774_patient.sex AS sex, 774_study.received AS received, 774_study.sdate AS sdate, 774_study.stime AS stime, 774_study.modality AS modality, 774_study.refphys AS refphys, 774_study.numimages AS numimages, 774_study.numsnapshots AS numsnapshots, 774_study.numattachments AS numattachments, 774_study.sdesc AS sdesc, 774_study.backedup AS backedup, 774_study.modified AS modified, 774_study.siuid AS siuid, 774_study.institution AS institution, 774_study.bodypart AS bodypart, 774_study.tsuid AS tsuid, 774_study.acnum AS acnum from 774_study left join 774_patient on 774_study.patientID = 774_patient.patientID where exists (select pname from 774_patient where pname like @n) and 774_patient.pname in (select pname from 774_patient where pname like @n) order by received desc limit 0,50; +------+----------------------+-------------+--------+-------------------------+-------------------------+---------+----------------------------+-------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+----------------------+-------------+--------+-------------------------+-------------------------+---------+----------------------------+-------+---------------------------------+ | 1 | PRIMARY | 774_patient | ALL | PRIMARY | NULL | NULL | NULL | 28906 | Using temporary; Using filesort | | 1 | PRIMARY | <subquery3> | eq_ref | distinct_key | distinct_key | 304 | func | 1 | | | 1 | PRIMARY | 774_study | ref | idx_774_study_patientID | idx_774_study_patientID | 4 | test.774_patient.patientID | 1 | | | 3 | MATERIALIZED | 774_patient | ALL | NULL | NULL | NULL | NULL | 28906 | Using where | | 2 | UNCACHEABLE SUBQUERY | 774_patient | ALL | NULL | NULL | NULL | NULL | 28906 | Using where | +------+----------------------+-------------+--------+-------------------------+-------------------------+---------+----------------------------+-------+---------------------------------+ 5 rows in set (0.002 sec)   (exec query) note; UNCACHEABLE SUBQUERY is a consequence of user variables, same result with direct entry of pname criteria. What form of prepared statement are you using for this query?

Greg Smethells added a comment - 2021-02-15 15:54 After speaking with my employees, I guess I was mistaken when I said that it could be done from the console. It seems it's actually only happening when we have a search that comes in from our app which means it would involve Python's oursql and sqlalchemy sending in the SELECT.

Greg Smethells added a comment - 2021-02-15 16:49 sqlalchemy version is 1.2.13 oursql3 version is 0.9.4

Daniel Black added a comment - 2021-02-23 03:52 gsmethells I can get you to come up with a reproducible client program. If you get one that produces the problem on the real dataset and my faked up tables that would be really good. Something oursql3 only is probably ok, provided the prepares statement bindings is similar to what sqlalchemy uses. Which is the libmariadb/mysql client library and version used by outsql3? We did do a release a few hours ago so a 10.5.9 test as a quick sanity check of resolution would be appreciated too (might be quicker than test case).

Greg Smethells added a comment - 2021-03-05 18:31 Sorry for the delay in responding. I have a resolution for you even though I don’t have a reproducer for you. We couldn’t really deal with having an abandoned DB-API library (oursql) be part of our stack any longer, especially since it is recommended against by SQLAlchemy ( https://docs.sqlalchemy.org/en/14/dialects/mysql.html#module-sqlalchemy.dialects.mysql.mysqlconnector ), so we decided to switch. I have been working on making that code change since then. I can confirm that the following works: MariaDB 10.5.8 SQLAlchemy 1.3.23 PyMySQL 1.0.2 I was suggest anybody running into this crash switch because an abandoned library is probably not one worth attempting to support. Once we knew that, the course of action seemed clear. Let me know if you find this a decent resolution.

Daniel Black added a comment - 2021-03-08 03:19 > Sorry for the delay in responding. I have a resolution for you even though I don’t have a reproducer for you. Hey gsmethells Thanks for getting the stack so far and pointing at the oursql. Someone might eventually be able to make this into a reproducer. Thanks for confirming the working stack with PyMySQL. We are testing PyMySQL in our CI infrastructure ( https://buildbot.mariadb.org/#/builders/158/builds/ - known failure in 10.6 that will be corrected before release), and are keeping an eye on things to extend this with. Regardless of the OurSQL supported status, it shouldn't be able to crash the server. Whether its properly using a prepared statement protocol or not, it was working at some stage. We'll still need to investigate the cause in case other prepared statements implementations hit this case. From your query it seems the only prepared statement parameter was the patient name. It should help.

Elena Stepanova added a comment - 2021-04-05 23:41 danblack , Your test case is good, but you need to run it through a C connector or through a stored procedure/cursor, as you want to get into Select_materialize::send_result_set_metadata . With C-connector it will be as close as it can be to oursql, as server-side connectors are apparently its main (and only) distinctive feature. Probably this is also the reason why it stopped happening after switching to PyMySQL – no server-side cursors, no problem. I'm not much of a C-writer, but something like this does the trick (cleanups and fetch are skipped as unimportant for proof of concept): #include <mysql.h> #include <stddef.h> #include <stdio.h> #include <stdlib.h> #include <string.h>   int main( int argc, char **argv) { MYSQL *con = mysql_init(NULL); MYSQL_STMT *stmt = mysql_stmt_init(con);   if (mysql_real_connect(con, "127.0.0.1" , "root" , "" , "test" , 0, NULL, 0) == NULL) { fprintf (stderr, "Can't connect: %s\n" ,mysql_error(con)); exit (1); }   mysql_query(con, "CREATE OR REPLACE TABLE `774_patient` (" "`patientID` int(10) unsigned NOT NULL AUTO_INCREMENT, " "`pid` varchar(64) NOT NULL, " "`pname` varchar(100) NOT NULL, " "PRIMARY KEY (`patientID`) " ") ENGINE=InnoDB AUTO_INCREMENT=317145 DEFAULT CHARSET=utf8" );   mysql_query(con, "CREATE OR REPLACE TABLE `774_study` (" "`received` datetime(6) NOT NULL, " "`patientID` int(10) unsigned NOT NULL, " "PRIMARY KEY (`received`), " "KEY `idx_774_study_patientID` (`patientID`) " ") ENGINE=InnoDB DEFAULT CHARSET=utf8" );   mysql_query(con, "insert into 774_patient(patientID,pname,pid) select seq, uuid(), uuid() from seq_1_to_10000" ); mysql_query(con, "insert into 774_study(received,patientID) select date_add('2020-01-01', INTERVAL seq SECOND), seq from seq_1_to_20000" );   const char * query= "select 774_study.patientID AS patientID " "from 774_study left join 774_patient " "on 774_study.patientID = 774_patient.patientID " "where exists (select pname from 774_patient where pname like 'doe%john%^w%^%^%^%') " "and 774_patient.pname in (select pname from 774_patient where pname like 'doe%john%^w%^%^%^%') " "order by received desc limit 50" ;   if (mysql_stmt_prepare(stmt,query, strlen (query))) { fprintf (stderr, "Couldn't prepare: %s\n" , mysql_error(con)); mysql_close(con); exit (1); }   unsigned long cursor = CURSOR_TYPE_READ_ONLY; mysql_stmt_attr_set(stmt, STMT_ATTR_CURSOR_TYPE, &cursor);   if (mysql_stmt_execute(stmt)) fprintf (stderr, "Got error: %s\n" , mysql_stmt_error(stmt)); else fprintf (stderr, "Didn't get an error\n" );   mysql_stmt_close(stmt); mysql_close(con); exit (0); } Of course since it's SIGSEGV, the exact crash is a matter of some luck and can vary on different machines and builds. CentOS 8 optimized release from packages produces the stack trace seemingly identical to the one provided by gsmethells : 10.5.6 on CentOS 8 (my_print_stacktrace)[0x55847c5e3ece] (handle_fatal_signal)[0x55847c06eec5] sigaction.c:0(__restore_rt)[0x7f3caafa1b20] (Create_tmp_table::finalize(THD*, TABLE*, TMP_TABLE_PARAM*, bool, bool))[0x55847bec1958] (create_tmp_table(THD*, TMP_TABLE_PARAM*, List<Item>&, st_order*, bool, bool, unsigned long long, unsigned long long, st_mysql_const_lex_string const*, bool, bool))[0x55847bec260d] (select_unit::create_result_table(THD*, List<Item>*, bool, unsigned long long, st_mysql_const_lex_string const*, bool, bool, bool, unsigned int))[0x55847bf2378b] (Select_materialize::send_result_set_metadata(List<Item>&, unsigned int))[0x55847be3a3c3] (JOIN::exec_inner())[0x55847bedceca] (JOIN::exec())[0x55847beddc17] (mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x5584 7bedbf02] (handle_select(THD*, LEX*, select_result*, unsigned long))[0x55847bedc7eb] (LEX::mark_first_table_as_inserting())[0x55847be7aeed] (mysql_execute_command(THD*))[0x55847be82fbe] (mysql_open_cursor(THD*, select_result*, Server_side_cursor**))[0x55847be3a6bc] (Prepared_statement::execute(String*, bool))[0x55847be97a1e] (Prepared_statement::execute_loop(String*, bool, unsigned char*, unsigned char*))[0x55847be97de2] (Prepared_statement::execute_bulk_loop(String*, bool, unsigned char*, unsigned char*))[0x55847be98b35] (mysqld_stmt_execute(THD*, char*, unsigned int))[0x55847be98bd5] (dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55847be7f512] (do_command(THD*))[0x55847be8124f] (do_handle_one_connection(CONNECT*, bool))[0x55847bf70bf1] (handle_one_connection)[0x55847bf70f7d] (MyCTX_nopad::finish(unsigned char*, unsigned int*))[0x55847c28dd5a] pthread_create.c:0(start_thread)[0x7f3caaf9714a] :0(__GI___clone)[0x7f3ca8e04f23] And here is debug ASAN build, it fails apparently before it even reaches finalize : 10.5 7f75acc0 debug ASAN ==2554105==ERROR: AddressSanitizer: heap-use-after-free on address 0x6190000b5620 at pc 0x558c6dd18407 bp 0x7fb7dc566640 sp 0x7fb7dc566630 READ of size 8 at 0x6190000b5620 thread T13 #0 0x558c6dd18406 in Field::maybe_null() const /data/src/10.5/sql/field.h:1386 #1 0x558c6dcb9853 in Item_field::create_tmp_field_from_item_field(st_mem_root*, TABLE*, Item_ref*, Tmp_field_param const*) /data/src/10.5/sql/sql_select.cc:18023 #2 0x558c6dcb9bd5 in Item_field::create_tmp_field_ex(st_mem_root*, TABLE*, Tmp_field_src*, Tmp_field_param const*) /data/src/10.5/sql/sql_select.cc:18041 #3 0x558c6dcbade1 in create_tmp_field(TABLE*, Item*, Item***, Field**, Field**, bool, bool, bool, bool) /data/src/10.5/sql/sql_select.cc:18196 #4 0x558c6dcbf498 in Create_tmp_table::add_fields(THD*, TABLE*, TMP_TABLE_PARAM*, List<Item>&) /data/src/10.5/sql/sql_select.cc:18684 #5 0x558c6dcc7d5f in create_tmp_table(THD*, TMP_TABLE_PARAM*, List<Item>&, st_order*, bool, bool, unsigned long long, unsigned long long, st_mysql_const_lex_string const*, bool, bool) /data/src/10.5/sql/sql_select.cc:19321 #6 0x558c6de69959 in select_unit::create_result_table(THD*, List<Item>*, bool, unsigned long long, st_mysql_const_lex_string const*, bool, bool, bool, unsigned int) /data/src/10.5/sql/sql_union.cc:329 #7 0x558c6da91334 in Select_materialize::send_result_set_metadata(List<Item>&, unsigned int) /data/src/10.5/sql/sql_cursor.cc:444 #8 0x558c6dca4e63 in return_zero_rows /data/src/10.5/sql/sql_select.cc:14496 #9 0x558c6dc5b2fb in JOIN::exec_inner() /data/src/10.5/sql/sql_select.cc:4392 #10 0x558c6dc59509 in JOIN::exec() /data/src/10.5/sql/sql_select.cc:4249 #11 0x558c6dc5d958 in mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) /data/src/10.5/sql/sql_select.cc:4725 #12 0x558c6dc2f11b in handle_select(THD*, LEX*, select_result*, unsigned long) /data/src/10.5/sql/sql_select.cc:419 #13 0x558c6db98687 in execute_sqlcom_select /data/src/10.5/sql/sql_parse.cc:6307 #14 0x558c6db87979 in mysql_execute_command(THD*) /data/src/10.5/sql/sql_parse.cc:4003 #15 0x558c6da8eccb in mysql_open_cursor(THD*, select_result*, Server_side_cursor**) /data/src/10.5/sql/sql_cursor.cc:150 #16 0x558c6dbf7a8e in Prepared_statement::execute(String*, bool) /data/src/10.5/sql/sql_prepare.cc:5008 #17 0x558c6dbf30c6 in Prepared_statement::execute_loop(String*, bool, unsigned char*, unsigned char*) /data/src/10.5/sql/sql_prepare.cc:4488 #18 0x558c6dbec37d in mysql_stmt_execute_common /data/src/10.5/sql/sql_prepare.cc:3460 #19 0x558c6dbeb555 in mysqld_stmt_execute(THD*, char*, unsigned int) /data/src/10.5/sql/sql_prepare.cc:3239 #20 0x558c6db7949e in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /data/src/10.5/sql/sql_parse.cc:1813 #21 0x558c6db7653d in do_command(THD*) /data/src/10.5/sql/sql_parse.cc:1370 #22 0x558c6dfb9cf8 in do_handle_one_connection(CONNECT*, bool) /data/src/10.5/sql/sql_connect.cc:1410 #23 0x558c6dfb965c in handle_one_connection /data/src/10.5/sql/sql_connect.cc:1312 #24 0x558c6eccfa52 in pfs_spawn_thread /data/src/10.5/storage/perfschema/pfs.cc:2201 #25 0x7fb80081a608 in start_thread /build/glibc-eX1tMB/glibc-2.31/nptl/pthread_create.c:477 #26 0x7fb8003ee292 in __clone (/lib/x86_64-linux-gnu/libc.so.6+0x122292)   0x6190000b5620 is located 416 bytes inside of 1124-byte region [0x6190000b5480,0x6190000b58e4) freed by thread T13 here: #0 0x7fb800d6a7cf in __interceptor_free (/lib/x86_64-linux-gnu/libasan.so.5+0x10d7cf) #1 0x558c6f97a343 in free_memory /data/src/10.5/mysys/safemalloc.c:280 #2 0x558c6f9798ff in sf_free /data/src/10.5/mysys/safemalloc.c:198 #3 0x558c6f9471ee in my_free /data/src/10.5/mysys/my_malloc.c:211 #4 0x558c6f92321d in free_root /data/src/10.5/mysys/my_alloc.c:416 #5 0x558c6dcce45e in free_tmp_table(THD*, TABLE*) /data/src/10.5/sql/sql_select.cc:20083 #6 0x558c6dca0df9 in JOIN::cleanup(bool) /data/src/10.5/sql/sql_select.cc:13945 #7 0x558c6dca008a in JOIN::join_free() /data/src/10.5/sql/sql_select.cc:13832 #8 0x558c6dca4b28 in return_zero_rows /data/src/10.5/sql/sql_select.cc:14457 #9 0x558c6dc5b2fb in JOIN::exec_inner() /data/src/10.5/sql/sql_select.cc:4392 #10 0x558c6dc59509 in JOIN::exec() /data/src/10.5/sql/sql_select.cc:4249 #11 0x558c6dc5d958 in mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) /data/src/10.5/sql/sql_select.cc:4725 #12 0x558c6dc2f11b in handle_select(THD*, LEX*, select_result*, unsigned long) /data/src/10.5/sql/sql_select.cc:419 #13 0x558c6db98687 in execute_sqlcom_select /data/src/10.5/sql/sql_parse.cc:6307 #14 0x558c6db87979 in mysql_execute_command(THD*) /data/src/10.5/sql/sql_parse.cc:4003 #15 0x558c6da8eccb in mysql_open_cursor(THD*, select_result*, Server_side_cursor**) /data/src/10.5/sql/sql_cursor.cc:150 #16 0x558c6dbf7a8e in Prepared_statement::execute(String*, bool) /data/src/10.5/sql/sql_prepare.cc:5008 #17 0x558c6dbf30c6 in Prepared_statement::execute_loop(String*, bool, unsigned char*, unsigned char*) /data/src/10.5/sql/sql_prepare.cc:4488 #18 0x558c6dbec37d in mysql_stmt_execute_common /data/src/10.5/sql/sql_prepare.cc:3460 #19 0x558c6dbeb555 in mysqld_stmt_execute(THD*, char*, unsigned int) /data/src/10.5/sql/sql_prepare.cc:3239 #20 0x558c6db7949e in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /data/src/10.5/sql/sql_parse.cc:1813 #21 0x558c6db7653d in do_command(THD*) /data/src/10.5/sql/sql_parse.cc:1370 #22 0x558c6dfb9cf8 in do_handle_one_connection(CONNECT*, bool) /data/src/10.5/sql/sql_connect.cc:1410 #23 0x558c6dfb965c in handle_one_connection /data/src/10.5/sql/sql_connect.cc:1312 #24 0x558c6eccfa52 in pfs_spawn_thread /data/src/10.5/storage/perfschema/pfs.cc:2201 #25 0x7fb80081a608 in start_thread /build/glibc-eX1tMB/glibc-2.31/nptl/pthread_create.c:477   previously allocated by thread T13 here: #0 0x7fb800d6abc8 in malloc (/lib/x86_64-linux-gnu/libasan.so.5+0x10dbc8) #1 0x558c6f9792b3 in sf_malloc /data/src/10.5/mysys/safemalloc.c:121 #2 0x558c6f9463c8 in my_malloc /data/src/10.5/mysys/my_malloc.c:90 #3 0x558c6f92217c in alloc_root /data/src/10.5/mysys/my_alloc.c:244 #4 0x558c6f92387e in memdup_root /data/src/10.5/mysys/my_alloc.c:479 #5 0x558c6e2fa41f in Field::make_new_field(st_mem_root*, TABLE*, bool) /data/src/10.5/sql/field.cc:2477 #6 0x558c6e2fa8d6 in Field::create_tmp_field(st_mem_root*, TABLE*, bool) /data/src/10.5/sql/field.cc:2535 #7 0x558c6dcb98b6 in Item_field::create_tmp_field_from_item_field(st_mem_root*, TABLE*, Item_ref*, Tmp_field_param const*) /data/src/10.5/sql/sql_select.cc:18024 #8 0x558c6dcb9bd5 in Item_field::create_tmp_field_ex(st_mem_root*, TABLE*, Tmp_field_src*, Tmp_field_param const*) /data/src/10.5/sql/sql_select.cc:18041 #9 0x558c6dcbade1 in create_tmp_field(TABLE*, Item*, Item***, Field**, Field**, bool, bool, bool, bool) /data/src/10.5/sql/sql_select.cc:18196 #10 0x558c6dcbf498 in Create_tmp_table::add_fields(THD*, TABLE*, TMP_TABLE_PARAM*, List<Item>&) /data/src/10.5/sql/sql_select.cc:18684 #11 0x558c6dcc7d5f in create_tmp_table(THD*, TMP_TABLE_PARAM*, List<Item>&, st_order*, bool, bool, unsigned long long, unsigned long long, st_mysql_const_lex_string const*, bool, bool) /data/src/10.5/sql/sql_select.cc:19321 #12 0x558c6dc53e35 in JOIN::create_postjoin_aggr_table(st_join_table*, List<Item>*, st_order*, bool, bool, bool) /data/src/10.5/sql/sql_select.cc:3755 #13 0x558c6dc4f91c in JOIN::make_aggr_tables_info() /data/src/10.5/sql/sql_select.cc:3355 #14 0x558c6dc4b4e0 in JOIN::optimize_stage2() /data/src/10.5/sql/sql_select.cc:2999 #15 0x558c6dc43d25 in JOIN::optimize_inner() /data/src/10.5/sql/sql_select.cc:2284 #16 0x558c6dc3cef1 in JOIN::optimize() /data/src/10.5/sql/sql_select.cc:1630 #17 0x558c6dc5d763 in mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) /data/src/10.5/sql/sql_select.cc:4711 #18 0x558c6dc2f11b in handle_select(THD*, LEX*, select_result*, unsigned long) /data/src/10.5/sql/sql_select.cc:419 #19 0x558c6db98687 in execute_sqlcom_select /data/src/10.5/sql/sql_parse.cc:6307 #20 0x558c6db87979 in mysql_execute_command(THD*) /data/src/10.5/sql/sql_parse.cc:4003 #21 0x558c6da8eccb in mysql_open_cursor(THD*, select_result*, Server_side_cursor**) /data/src/10.5/sql/sql_cursor.cc:150 #22 0x558c6dbf7a8e in Prepared_statement::execute(String*, bool) /data/src/10.5/sql/sql_prepare.cc:5008 #23 0x558c6dbf30c6 in Prepared_statement::execute_loop(String*, bool, unsigned char*, unsigned char*) /data/src/10.5/sql/sql_prepare.cc:4488 #24 0x558c6dbec37d in mysql_stmt_execute_common /data/src/10.5/sql/sql_prepare.cc:3460 #25 0x558c6dbeb555 in mysqld_stmt_execute(THD*, char*, unsigned int) /data/src/10.5/sql/sql_prepare.cc:3239 #26 0x558c6db7949e in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /data/src/10.5/sql/sql_parse.cc:1813 #27 0x558c6db7653d in do_command(THD*) /data/src/10.5/sql/sql_parse.cc:1370 #28 0x558c6dfb9cf8 in do_handle_one_connection(CONNECT*, bool) /data/src/10.5/sql/sql_connect.cc:1410 #29 0x558c6dfb965c in handle_one_connection /data/src/10.5/sql/sql_connect.cc:1312   Thread T13 created by T0 here: #0 0x7fb800c97805 in pthread_create (/lib/x86_64-linux-gnu/libasan.so.5+0x3a805) #1 0x558c6ecca9f6 in my_thread_create /data/src/10.5/storage/perfschema/my_thread.h:38 #2 0x558c6eccfe45 in pfs_spawn_thread_v1 /data/src/10.5/storage/perfschema/pfs.cc:2252 #3 0x558c6d86a492 in inline_mysql_thread_create /data/src/10.5/include/mysql/psi/mysql_thread.h:1323 #4 0x558c6d8805f0 in create_thread_to_handle_connection(CONNECT*) /data/src/10.5/sql/mysqld.cc:6012 #5 0x558c6d880c6f in create_new_thread(CONNECT*) /data/src/10.5/sql/mysqld.cc:6071 #6 0x558c6d880fcc in handle_accepted_socket(st_mysql_socket, st_mysql_socket) /data/src/10.5/sql/mysqld.cc:6136 #7 0x558c6d881beb in handle_connections_sockets() /data/src/10.5/sql/mysqld.cc:6263 #8 0x558c6d87fdfd in mysqld_main(int, char**) /data/src/10.5/sql/mysqld.cc:5658 #9 0x558c6d868f5c in main /data/src/10.5/sql/main.cc:25 #10 0x7fb8002f30b2 in __libc_start_main (/lib/x86_64-linux-gnu/libc.so.6+0x270b2)   SUMMARY: AddressSanitizer: heap-use-after-free /data/src/10.5/sql/field.h:1386 in Field::maybe_null() const Shadow bytes around the buggy address: 0x0c328000ea70: fd fd fd fa fa fa fa fa fa fa fa fa fa fa fa fa 0x0c328000ea80: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa 0x0c328000ea90: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd 0x0c328000eaa0: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd 0x0c328000eab0: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd =>0x0c328000eac0: fd fd fd fd[fd]fd fd fd fd fd fd fd fd fd fd fd 0x0c328000ead0: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd 0x0c328000eae0: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd 0x0c328000eaf0: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd 0x0c328000eb00: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd 0x0c328000eb10: fd fd fd fd fd fd fd fd fd fd fd fd fd fa fa fa Shadow byte legend (one shadow byte represents 8 application bytes): Addressable: 00 Partially addressable: 01 02 03 04 05 06 07 Heap left redzone: fa Freed heap region: fd Stack left redzone: f1 Stack mid redzone: f2 Stack right redzone: f3 Stack after return: f5 Stack use after scope: f8 Global redzone: f9 Global init order: f6 Poisoned by user: f7 Container overflow: fc Array cookie: ac Intra object redzone: bb ASan internal: fe Left alloca redzone: ca Right alloca redzone: cb Shadow gap: cc ==2554105==ABORTING 210406 2:23:52 [ERROR] mysqld got signal 6 ; 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.   To report this bug, see https://mariadb.com/kb/en/reporting-bugs   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.   Server version: 10.5.10-MariaDB-debug key_buffer_size=134217728 read_buffer_size=131072 max_used_connections=1 max_threads=153 thread_count=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 467974 K bytes of memory Hope that's ok; if not, decrease some variables in the equation.   Thread pointer: 0x62b00009a288 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 = 0x7fb7dc569d50 thread_stack 0x100000 ??:0(__interceptor_tcgetattr)[0x7fb800cc9d30] /data/bld/10.5-asan-nightly/bin/mysqld(my_print_stacktrace+0xec)[0x558c6f956a10] /data/bld/10.5-asan-nightly/bin/mysqld(handle_fatal_signal+0xa1a)[0x558c6e382f03] sigaction.c:0(__restore_rt)[0x7fb8008263c0] ??:0(gsignal)[0x7fb80031218b] ??:0(abort)[0x7fb8002f1859] ??:0(__sanitizer_set_report_fd)[0x7fb800d886a2] ??:0(__sanitizer_get_module_and_offset_for_pc)[0x7fb800d9324c] ??:0(__sanitizer_ptr_cmp)[0x7fb800d748ec] ??:0(__asan_on_error)[0x7fb800d74363] ??:0(__asan_report_load8)[0x7fb800d751ab] sql/field.h:1386(Field::maybe_null() const)[0x558c6dd18407] sql/sql_select.cc:18023(Item_field::create_tmp_field_from_item_field(st_mem_root*, TABLE*, Item_ref*, Tmp_field_param const*))[0x558c6dcb9854] sql/sql_select.cc:18041(Item_field::create_tmp_field_ex(st_mem_root*, TABLE*, Tmp_field_src*, Tmp_field_param const*))[0x558c6dcb9bd6] sql/sql_select.cc:18196(create_tmp_field(TABLE*, Item*, Item***, Field**, Field**, bool, bool, bool, bool))[0x558c6dcbade2] sql/sql_select.cc:18684(Create_tmp_table::add_fields(THD*, TABLE*, TMP_TABLE_PARAM*, List<Item>&))[0x558c6dcbf499] sql/sql_select.cc:19320(create_tmp_table(THD*, TMP_TABLE_PARAM*, List<Item>&, st_order*, bool, bool, unsigned long long, unsigned long long, st_mysql_const_lex_string const*, bool, bool))[0x558c6dcc7d60] sql/sql_union.cc:329(select_unit::create_result_table(THD*, List<Item>*, bool, unsigned long long, st_mysql_const_lex_string const*, bool, bool, bool, unsigned int))[0x558c6de6995a] sql/sql_cursor.cc:444(Select_materialize::send_result_set_metadata(List<Item>&, unsigned int))[0x558c6da91335] sql/sql_select.cc:14496(return_zero_rows(JOIN*, select_result*, List<TABLE_LIST>&, List<Item>&, bool, unsigned long long, char const*, Item*, List<Item>&))[0x558c6dca4e64] sql/sql_select.cc:4392(JOIN::exec_inner())[0x558c6dc5b2fc] sql/sql_select.cc:4250(JOIN::exec())[0x558c6dc5950a] sql/sql_select.cc:4727(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x558c6dc5d959] sql/sql_select.cc:419(handle_select(THD*, LEX*, select_result*, unsigned long))[0x558c6dc2f11c] sql/sql_parse.cc:6307(execute_sqlcom_select(THD*, TABLE_LIST*))[0x558c6db98688] sql/sql_parse.cc:4003(mysql_execute_command(THD*))[0x558c6db8797a] sql/sql_cursor.cc:150(mysql_open_cursor(THD*, select_result*, Server_side_cursor**))[0x558c6da8eccc] sql/sql_prepare.cc:5008(Prepared_statement::execute(String*, bool))[0x558c6dbf7a8f] sql/sql_prepare.cc:4488(Prepared_statement::execute_loop(String*, bool, unsigned char*, unsigned char*))[0x558c6dbf30c7] sql/sql_prepare.cc:3460(mysql_stmt_execute_common(THD*, unsigned long, unsigned char*, unsigned char*, unsigned long, bool, bool))[0x558c6dbec37e] sql/sql_prepare.cc:3239(mysqld_stmt_execute(THD*, char*, unsigned int))[0x558c6dbeb556] sql/sql_parse.cc:1815(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x558c6db7949f] sql/sql_parse.cc:1370(do_command(THD*))[0x558c6db7653e] sql/sql_connect.cc:1410(do_handle_one_connection(CONNECT*, bool))[0x558c6dfb9cf9] sql/sql_connect.cc:1314(handle_one_connection)[0x558c6dfb965d] perfschema/pfs.cc:2203(pfs_spawn_thread)[0x558c6eccfa53] nptl/pthread_create.c:478(start_thread)[0x7fb80081a609] ??:0(clone)[0x7fb8003ee293]   Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0x62b0000b0e50): select 774_study.patientID AS patientID from 774_study left join 774_patient on 774_study.patientID = 774_patient.patientID where exists (select pname from 774_patient where pname like 'doe%john%^w%^%^%^%') and 774_patient.pname in (select pname from 774_patient where pname like 'doe%john%^w%^%^%^%') order by received desc limit 50   Connection ID (thread ID): 5 Status: NOT_KILLED   Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off   The manual page at https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mysqld/ contains information that should help you find out what is causing the crash. Writing a core file... Working directory at /mnt-hd8t/bld/10.5-asan-nightly/data Resource Limits: Limit Soft Limit Hard Limit Units Max cpu time unlimited unlimited seconds Max file size unlimited unlimited bytes Max data size unlimited unlimited bytes Max stack size 8388608 unlimited bytes Max core file size unlimited unlimited bytes Max resident set unlimited unlimited bytes Max processes 385874 385874 processes Max open files 32198 32198 files Max locked memory 67108864 67108864 bytes Max address space unlimited unlimited bytes Max file locks unlimited unlimited locks Max pending signals 385874 385874 signals Max msgqueue size 819200 819200 bytes Max nice priority 0 0 Max realtime priority 0 0 Max realtime timeout unlimited unlimited us Core pattern: |/usr/share/apport/apport %p %s %c %d %P %E If the same is done through a cursor inside a stored procedure, the stack trace will be different, as it will include the SP call. On the bright side, it can be run through MTR. --source include/have_innodb.inc --source include/have_sequence.inc   CREATE OR REPLACE TABLE `774_patient` ( `patientID` int (10) unsigned NOT NULL AUTO_INCREMENT, `pid` varchar (64) NOT NULL , `pname` varchar (100) NOT NULL , PRIMARY KEY (`patientID`) ) ENGINE=InnoDB CHARSET=utf8;   CREATE OR REPLACE TABLE `774_study` ( `received` datetime(6) NOT NULL , `patientID` int (10) unsigned NOT NULL , PRIMARY KEY (`received`), KEY `idx_774_study_patientID` (`patientID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;   insert into 774_patient(patientID,pname,pid) select seq, uuid(), uuid() from seq_1_to_10000; insert into 774_study(received,patientID) select date_add( '2020-01-01' , INTERVAL seq SECOND ), seq from seq_1_to_20000;   --delimiter $ create or replace procedure pr() begin DECLARE done INT DEFAULT FALSE ; DECLARE a int ;   DECLARE cur1 CURSOR FOR select 774_study.patientID AS patientID from 774_study left join 774_patient on 774_study.patientID = 774_patient.patientID where exists ( select pname from 774_patient where pname like 'doe%john%^w%^%^%^%' ) and 774_patient.pname in ( select pname from 774_patient where pname like 'doe%john%^w%^%^%^%' ) order by received desc limit 50; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE ; OPEN cur1; read_loop: LOOP FETCH cur1 INTO a; IF done THEN LEAVE read_loop; END IF ; END LOOP; CLOSE cur1; END $   --delimiter ;   call pr(); 10.2 6fe624b5 debug ASAN ==2554381==ERROR: AddressSanitizer: heap-use-after-free on address 0x619003204ce0 at pc 0x55d6bb8760fa bp 0x7fb8aee01d10 sp 0x7fb8aee01d00 READ of size 8 at 0x619003204ce0 thread T27 #0 0x55d6bb8760f9 in create_tmp_field_from_field(THD*, Field*, char const*, TABLE*, Item_field*) /data/src/10.2/sql/sql_select.cc:16274 #1 0x55d6bb877ccb in create_tmp_field(THD*, TABLE*, Item*, Item::Type, Item***, Field**, Field**, bool, bool, bool, bool) /data/src/10.2/sql/sql_select.cc:16529 #2 0x55d6bb87b13e in create_tmp_table(THD*, TMP_TABLE_PARAM*, List<Item>&, st_order*, bool, bool, unsigned long long, unsigned long long, char const*, bool, bool) /data/src/10.2/sql/sql_select.cc:16977 #3 0x55d6bb9d3449 in select_union::create_result_table(THD*, List<Item>*, bool, unsigned long long, char const*, bool, bool, bool) /data/src/10.2/sql/sql_union.cc:180 #4 0x55d6bb6d49e1 in Select_materialize::send_result_set_metadata(List<Item>&, unsigned int) /data/src/10.2/sql/sql_cursor.cc:436 #5 0x55d6bb863bb0 in return_zero_rows /data/src/10.2/sql/sql_select.cc:12918 #6 0x55d6bb821c79 in JOIN::exec_inner() /data/src/10.2/sql/sql_select.cc:3573 #7 0x55d6bb82014d in JOIN::exec() /data/src/10.2/sql/sql_select.cc:3437 #8 0x55d6bb8239d0 in mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) /data/src/10.2/sql/sql_select.cc:3840 #9 0x55d6bb8003f7 in handle_select(THD*, LEX*, select_result*, unsigned long) /data/src/10.2/sql/sql_select.cc:361 #10 0x55d6bb7770d8 in execute_sqlcom_select /data/src/10.2/sql/sql_parse.cc:6274 #11 0x55d6bb764477 in mysql_execute_command(THD*) /data/src/10.2/sql/sql_parse.cc:3585 #12 0x55d6bb6d2427 in mysql_open_cursor(THD*, select_result*, Server_side_cursor**) /data/src/10.2/sql/sql_cursor.cc:141 #13 0x55d6bb5c8b61 in sp_cursor::open(THD*) /data/src/10.2/sql/sp_rcontext.cc:464 #14 0x55d6bb5b655d in sp_instr_copen::exec_core(THD*, unsigned int*) /data/src/10.2/sql/sp_head.cc:3929 #15 0x55d6bb5b0f5c in sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*) /data/src/10.2/sql/sp_head.cc:3095 #16 0x55d6bb5b62e6 in sp_instr_copen::execute(THD*, unsigned int*) /data/src/10.2/sql/sp_head.cc:3914 #17 0x55d6bb5a6003 in sp_head::execute(THD*, bool) /data/src/10.2/sql/sp_head.cc:1326 #18 0x55d6bb5aa7ce in sp_head::execute_procedure(THD*, List<Item>*) /data/src/10.2/sql/sp_head.cc:2202 #19 0x55d6bb7600f1 in do_execute_sp /data/src/10.2/sql/sql_parse.cc:2981 #20 0x55d6bb7723b5 in mysql_execute_command(THD*) /data/src/10.2/sql/sql_parse.cc:5625 #21 0x55d6bb780669 in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /data/src/10.2/sql/sql_parse.cc:7794 #22 0x55d6bb75974b in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /data/src/10.2/sql/sql_parse.cc:1827 #23 0x55d6bb75650a in do_command(THD*) /data/src/10.2/sql/sql_parse.cc:1381 #24 0x55d6bbade8c5 in do_handle_one_connection(CONNECT*) /data/src/10.2/sql/sql_connect.cc:1336 #25 0x55d6bbade188 in handle_one_connection /data/src/10.2/sql/sql_connect.cc:1241 #26 0x55d6bce7fd6d in pfs_spawn_thread /data/src/10.2/storage/perfschema/pfs.cc:1869 #27 0x7fb8c59a3608 in start_thread /build/glibc-eX1tMB/glibc-2.31/nptl/pthread_create.c:477 #28 0x7fb8c557f292 in __clone (/lib/x86_64-linux-gnu/libc.so.6+0x122292)   0x619003204ce0 is located 352 bytes inside of 1100-byte region [0x619003204b80,0x619003204fcc) freed by thread T27 here: #0 0x7fb8c5f6e7cf in __interceptor_free (/lib/x86_64-linux-gnu/libasan.so.5+0x10d7cf) #1 0x55d6bcf9d9e6 in free_memory /data/src/10.2/mysys/safemalloc.c:279 #2 0x55d6bcf9cf32 in sf_free /data/src/10.2/mysys/safemalloc.c:197 #3 0x55d6bcf6929e in my_free /data/src/10.2/mysys/my_malloc.c:218 #4 0x55d6bcf47102 in free_root /data/src/10.2/mysys/my_alloc.c:401 #5 0x55d6bb886e96 in free_tmp_table(THD*, TABLE*) /data/src/10.2/sql/sql_select.cc:18191 #6 0x55d6bb86005a in JOIN::cleanup(bool) /data/src/10.2/sql/sql_select.cc:12383 #7 0x55d6bb85f3a4 in JOIN::join_free() /data/src/10.2/sql/sql_select.cc:12273 #8 0x55d6bb863875 in return_zero_rows /data/src/10.2/sql/sql_select.cc:12879 #9 0x55d6bb821c79 in JOIN::exec_inner() /data/src/10.2/sql/sql_select.cc:3573 #10 0x55d6bb82014d in JOIN::exec() /data/src/10.2/sql/sql_select.cc:3437 #11 0x55d6bb8239d0 in mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) /data/src/10.2/sql/sql_select.cc:3840 #12 0x55d6bb8003f7 in handle_select(THD*, LEX*, select_result*, unsigned long) /data/src/10.2/sql/sql_select.cc:361 #13 0x55d6bb7770d8 in execute_sqlcom_select /data/src/10.2/sql/sql_parse.cc:6274 #14 0x55d6bb764477 in mysql_execute_command(THD*) /data/src/10.2/sql/sql_parse.cc:3585 #15 0x55d6bb6d2427 in mysql_open_cursor(THD*, select_result*, Server_side_cursor**) /data/src/10.2/sql/sql_cursor.cc:141 #16 0x55d6bb5c8b61 in sp_cursor::open(THD*) /data/src/10.2/sql/sp_rcontext.cc:464 #17 0x55d6bb5b655d in sp_instr_copen::exec_core(THD*, unsigned int*) /data/src/10.2/sql/sp_head.cc:3929 #18 0x55d6bb5b0f5c in sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*) /data/src/10.2/sql/sp_head.cc:3095 #19 0x55d6bb5b62e6 in sp_instr_copen::execute(THD*, unsigned int*) /data/src/10.2/sql/sp_head.cc:3914 #20 0x55d6bb5a6003 in sp_head::execute(THD*, bool) /data/src/10.2/sql/sp_head.cc:1326 #21 0x55d6bb5aa7ce in sp_head::execute_procedure(THD*, List<Item>*) /data/src/10.2/sql/sp_head.cc:2202 #22 0x55d6bb7600f1 in do_execute_sp /data/src/10.2/sql/sql_parse.cc:2981 #23 0x55d6bb7723b5 in mysql_execute_command(THD*) /data/src/10.2/sql/sql_parse.cc:5625 #24 0x55d6bb780669 in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /data/src/10.2/sql/sql_parse.cc:7794 #25 0x55d6bb75974b in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /data/src/10.2/sql/sql_parse.cc:1827 #26 0x55d6bb75650a in do_command(THD*) /data/src/10.2/sql/sql_parse.cc:1381 #27 0x55d6bbade8c5 in do_handle_one_connection(CONNECT*) /data/src/10.2/sql/sql_connect.cc:1336 #28 0x55d6bbade188 in handle_one_connection /data/src/10.2/sql/sql_connect.cc:1241 #29 0x55d6bce7fd6d in pfs_spawn_thread /data/src/10.2/storage/perfschema/pfs.cc:1869   previously allocated by thread T27 here: #0 0x7fb8c5f6ebc8 in malloc (/lib/x86_64-linux-gnu/libasan.so.5+0x10dbc8) #1 0x55d6bcf9c8a4 in sf_malloc /data/src/10.2/mysys/safemalloc.c:118 #2 0x55d6bcf6882d in my_malloc /data/src/10.2/mysys/my_malloc.c:101 #3 0x55d6bcf45e9f in alloc_root /data/src/10.2/mysys/my_alloc.c:243 #4 0x55d6bcf478d5 in memdup_root /data/src/10.2/mysys/my_alloc.c:464 #5 0x55d6bbd1adbf in Field::make_new_field(st_mem_root*, TABLE*, bool) /data/src/10.2/sql/field.cc:2387 #6 0x55d6bb87619b in create_tmp_field_from_field(THD*, Field*, char const*, TABLE*, Item_field*) /data/src/10.2/sql/sql_select.cc:16273 #7 0x55d6bb877ccb in create_tmp_field(THD*, TABLE*, Item*, Item::Type, Item***, Field**, Field**, bool, bool, bool, bool) /data/src/10.2/sql/sql_select.cc:16529 #8 0x55d6bb87b13e in create_tmp_table(THD*, TMP_TABLE_PARAM*, List<Item>&, st_order*, bool, bool, unsigned long long, unsigned long long, char const*, bool, bool) /data/src/10.2/sql/sql_select.cc:16977 #9 0x55d6bb81ba46 in JOIN::create_postjoin_aggr_table(st_join_table*, List<Item>*, st_order*, bool, bool, bool) /data/src/10.2/sql/sql_select.cc:2974 #10 0x55d6bb817935 in JOIN::make_aggr_tables_info() /data/src/10.2/sql/sql_select.cc:2579 #11 0x55d6bb81389d in JOIN::optimize_inner() /data/src/10.2/sql/sql_select.cc:2250 #12 0x55d6bb8080dd in JOIN::optimize() /data/src/10.2/sql/sql_select.cc:1118 #13 0x55d6bb8237e4 in mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) /data/src/10.2/sql/sql_select.cc:3826 #14 0x55d6bb8003f7 in handle_select(THD*, LEX*, select_result*, unsigned long) /data/src/10.2/sql/sql_select.cc:361 #15 0x55d6bb7770d8 in execute_sqlcom_select /data/src/10.2/sql/sql_parse.cc:6274 #16 0x55d6bb764477 in mysql_execute_command(THD*) /data/src/10.2/sql/sql_parse.cc:3585 #17 0x55d6bb6d2427 in mysql_open_cursor(THD*, select_result*, Server_side_cursor**) /data/src/10.2/sql/sql_cursor.cc:141 #18 0x55d6bb5c8b61 in sp_cursor::open(THD*) /data/src/10.2/sql/sp_rcontext.cc:464 #19 0x55d6bb5b655d in sp_instr_copen::exec_core(THD*, unsigned int*) /data/src/10.2/sql/sp_head.cc:3929 #20 0x55d6bb5b0f5c in sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*) /data/src/10.2/sql/sp_head.cc:3095 #21 0x55d6bb5b62e6 in sp_instr_copen::execute(THD*, unsigned int*) /data/src/10.2/sql/sp_head.cc:3914 #22 0x55d6bb5a6003 in sp_head::execute(THD*, bool) /data/src/10.2/sql/sp_head.cc:1326 #23 0x55d6bb5aa7ce in sp_head::execute_procedure(THD*, List<Item>*) /data/src/10.2/sql/sp_head.cc:2202 #24 0x55d6bb7600f1 in do_execute_sp /data/src/10.2/sql/sql_parse.cc:2981 #25 0x55d6bb7723b5 in mysql_execute_command(THD*) /data/src/10.2/sql/sql_parse.cc:5625 #26 0x55d6bb780669 in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /data/src/10.2/sql/sql_parse.cc:7794 #27 0x55d6bb75974b in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /data/src/10.2/sql/sql_parse.cc:1827 #28 0x55d6bb75650a in do_command(THD*) /data/src/10.2/sql/sql_parse.cc:1381 #29 0x55d6bbade8c5 in do_handle_one_connection(CONNECT*) /data/src/10.2/sql/sql_connect.cc:1336   Thread T27 created by T0 here: #0 0x7fb8c5e9b805 in pthread_create (/lib/x86_64-linux-gnu/libasan.so.5+0x3a805) #1 0x55d6bce8015e in spawn_thread_v1 /data/src/10.2/storage/perfschema/pfs.cc:1919 #2 0x55d6bb4fb203 in inline_mysql_thread_create /data/src/10.2/include/mysql/psi/mysql_thread.h:1246 #3 0x55d6bb5130ce in create_thread_to_handle_connection(CONNECT*) /data/src/10.2/sql/mysqld.cc:6573 #4 0x55d6bb513869 in create_new_thread /data/src/10.2/sql/mysqld.cc:6643 #5 0x55d6bb5149fb in handle_connections_sockets() /data/src/10.2/sql/mysqld.cc:6901 #6 0x55d6bb51241f in mysqld_main(int, char**) /data/src/10.2/sql/mysqld.cc:6192 #7 0x55d6bb4f9abc in main /data/src/10.2/sql/main.cc:25 #8 0x7fb8c54840b2 in __libc_start_main (/lib/x86_64-linux-gnu/libc.so.6+0x270b2)   SUMMARY: AddressSanitizer: heap-use-after-free /data/src/10.2/sql/sql_select.cc:16274 in create_tmp_field_from_field(THD*, Field*, char const*, TABLE*, Item_field*) Shadow bytes around the buggy address: 0x0c3280638940: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fa 0x0c3280638950: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa 0x0c3280638960: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa 0x0c3280638970: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd 0x0c3280638980: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd =>0x0c3280638990: fd fd fd fd fd fd fd fd fd fd fd fd[fd]fd fd fd 0x0c32806389a0: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd 0x0c32806389b0: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd 0x0c32806389c0: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd 0x0c32806389d0: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd 0x0c32806389e0: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd Shadow byte legend (one shadow byte represents 8 application bytes): Addressable: 00 Partially addressable: 01 02 03 04 05 06 07 Heap left redzone: fa Freed heap region: fd Stack left redzone: f1 Stack mid redzone: f2 Stack right redzone: f3 Stack after return: f5 Stack use after scope: f8 Global redzone: f9 Global init order: f6 Poisoned by user: f7 Container overflow: fc Array cookie: ac Intra object redzone: bb ASan internal: fe Left alloca redzone: ca Right alloca redzone: cb Shadow gap: cc ==2554381==ABORTING 210406 2:27:50 [ERROR] mysqld got signal 6 ; 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.   To report this bug, see https://mariadb.com/kb/en/reporting-bugs   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.   Server version: 10.2.38-MariaDB-debug-log key_buffer_size=1048576 read_buffer_size=131072 max_used_connections=1 max_threads=153 thread_count=6 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 63104 K bytes of memory Hope that's ok; if not, decrease some variables in the equation.   Thread pointer: 0x62a0000ba270 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 = 0x7fb8aee06d90 thread_stack 0x5b000 /lib/x86_64-linux-gnu/libasan.so.5(+0x6cd30)[0x7fb8c5ecdd30] /mnt-hd8t/bld/10.2-asan-nightly/bin/mysqld(my_print_stacktrace+0xe4)[0x55d6bcf7a25b] mysys/stacktrace.c:172(my_print_stacktrace)[0x55d6bbd9c025] sigaction.c:0(__restore_rt)[0x7fb8c59af3c0] /lib/x86_64-linux-gnu/libc.so.6(gsignal+0xcb)[0x7fb8c54a318b] /lib/x86_64-linux-gnu/libc.so.6(abort+0x12b)[0x7fb8c5482859] /lib/x86_64-linux-gnu/libasan.so.5(+0x12b6a2)[0x7fb8c5f8c6a2] /lib/x86_64-linux-gnu/libasan.so.5(+0x13624c)[0x7fb8c5f9724c] /lib/x86_64-linux-gnu/libasan.so.5(+0x1178ec)[0x7fb8c5f788ec] /lib/x86_64-linux-gnu/libasan.so.5(+0x117363)[0x7fb8c5f78363] /lib/x86_64-linux-gnu/libasan.so.5(__asan_report_load8+0x3b)[0x7fb8c5f791ab] sql/sql_select.cc:16274(create_tmp_field_from_field(THD*, Field*, char const*, TABLE*, Item_field*))[0x55d6bb8760fa] sql/sql_select.cc:16529(create_tmp_field(THD*, TABLE*, Item*, Item::Type, Item***, Field**, Field**, bool, bool, bool, bool))[0x55d6bb877ccc] sql/sql_select.cc:16977(create_tmp_table(THD*, TMP_TABLE_PARAM*, List<Item>&, st_order*, bool, bool, unsigned long long, unsigned long long, char const*, bool, bool))[0x55d6bb87b13f] sql/sql_union.cc:180(select_union::create_result_table(THD*, List<Item>*, bool, unsigned long long, char const*, bool, bool, bool))[0x55d6bb9d344a] sql/sql_cursor.cc:436(Select_materialize::send_result_set_metadata(List<Item>&, unsigned int))[0x55d6bb6d49e2] sql/sql_select.cc:12918(return_zero_rows(JOIN*, select_result*, List<TABLE_LIST>&, List<Item>&, bool, unsigned long long, char const*, Item*, List<Item>&))[0x55d6bb863bb1] sql/sql_select.cc:3573(JOIN::exec_inner())[0x55d6bb821c7a] sql/sql_select.cc:3438(JOIN::exec())[0x55d6bb82014e] sql/sql_select.cc:3842(mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x55d6bb8239d1] sql/sql_select.cc:361(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55d6bb8003f8] sql/sql_parse.cc:6274(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55d6bb7770d9] sql/sql_parse.cc:3585(mysql_execute_command(THD*))[0x55d6bb764478] sql/sql_cursor.cc:141(mysql_open_cursor(THD*, select_result*, Server_side_cursor**))[0x55d6bb6d2428] sql/sp_rcontext.cc:464(sp_cursor::open(THD*))[0x55d6bb5c8b62] sql/sp_head.cc:3929(sp_instr_copen::exec_core(THD*, unsigned int*))[0x55d6bb5b655e] sql/sp_head.cc:3095(sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*))[0x55d6bb5b0f5d] sql/sp_head.cc:3914(sp_instr_copen::execute(THD*, unsigned int*))[0x55d6bb5b62e7] sql/sp_head.cc:1326(sp_head::execute(THD*, bool))[0x55d6bb5a6004] sql/sp_head.cc:2202(sp_head::execute_procedure(THD*, List<Item>*))[0x55d6bb5aa7cf] sql/sql_parse.cc:2981(do_execute_sp(THD*, sp_head*))[0x55d6bb7600f2] sql/sql_parse.cc:5625(mysql_execute_command(THD*))[0x55d6bb7723b6] sql/sql_parse.cc:7794(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55d6bb78066a] sql/sql_parse.cc:1830(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55d6bb75974c] sql/sql_parse.cc:1381(do_command(THD*))[0x55d6bb75650b] sql/sql_connect.cc:1336(do_handle_one_connection(CONNECT*))[0x55d6bbade8c6] sql/sql_connect.cc:1242(handle_one_connection)[0x55d6bbade189] perfschema/pfs.cc:1871(pfs_spawn_thread)[0x55d6bce7fd6e] nptl/pthread_create.c:478(start_thread)[0x7fb8c59a3609] /lib/x86_64-linux-gnu/libc.so.6(clone+0x43)[0x7fb8c557f293]   Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0x62b000000290): call pr()   Connection ID (thread ID): 9 Status: NOT_KILLED   Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on   The manual page at https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mysqld/ contains information that should help you find out what is causing the crash. Writing a core file... Working directory at /dev/shm/var_auto_lJQZ/mysqld.1/data Resource Limits: Limit Soft Limit Hard Limit Units Max cpu time unlimited unlimited seconds Max file size unlimited unlimited bytes Max data size unlimited unlimited bytes Max stack size 8388608 unlimited bytes Max core file size unlimited unlimited bytes Max resident set unlimited unlimited bytes Max processes 385874 385874 processes Max open files 1024 1024 files Max locked memory 67108864 67108864 bytes Max address space unlimited unlimited bytes Max file locks unlimited unlimited locks Max pending signals 385874 385874 signals Max msgqueue size 819200 819200 bytes Max nice priority 0 0 Max realtime priority 0 0 Max realtime timeout unlimited unlimited us Core pattern: |/usr/share/apport/apport %p %s %c %d %P %E 10.5.6 release on CentOS 8 stack_bottom = 0x7f0020325bd8 thread_stack 0x49000 ??:0(my_print_stacktrace)[0x559fec17dece] ??:0(handle_fatal_signal)[0x559febc08ec5] sigaction.c:0(__restore_rt)[0x7f002a667b20] ??:0(Create_tmp_table::finalize(THD*, TABLE*, TMP_TABLE_PARAM*, bool, bool))[0x559feba5b958] ??:0(create_tmp_table(THD*, TMP_TABLE_PARAM*, List<Item>&, st_order*, bool, bool, unsigned long long, unsigned long long, st_mysql_const_lex_string const*, bool, bool))[0x559feba5c60d] ??:0(select_unit::create_result_table(THD*, List<Item>*, bool, unsigned long long, st_mysql_const_lex_string const*, bool, bool, bool, unsigned int))[0x559febabd78b] ??:0(Select_materialize::send_result_set_metadata(List<Item>&, unsigned int))[0x559feb9d43c3] ??:0(JOIN::exec_inner())[0x559feba76eca] ??:0(JOIN::exec())[0x559feba77c17] ??:0(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x559feba75f02] ??:0(handle_select(THD*, LEX*, select_result*, unsigned long))[0x559feba767eb] ??:0(LEX::mark_first_table_as_inserting())[0x559feba14eed] ??:0(mysql_execute_command(THD*))[0x559feba1cfbe] ??:0(mysql_open_cursor(THD*, select_result*, Server_side_cursor**))[0x559feb9d46bc] ??:0(sp_cursor::open(THD*))[0x559feb98ac5e] ??:0(sp_instr_copen::exec_core(THD*, unsigned int*))[0x559feb97ce67] ??:0(sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*))[0x559feb984009] ??:0(sp_lex_keeper::cursor_reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*))[0x559feb98438f] ??:0(sp_head::execute(THD*, bool))[0x559feb97fd6f] ??:0(sp_head::execute_procedure(THD*, List<Item>*))[0x559feb98113a] ??:0(LEX::mark_first_table_as_inserting())[0x559feba14cf1] ??:0(Sql_cmd_call::execute(THD*))[0x559feba176dc] ??:0(mysql_execute_command(THD*))[0x559feba1cfed] ??:0(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x559feba0fc62] ??:0(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x559feba1a0fe] ??:0(do_command(THD*))[0x559feba1b24f] ??:0(do_handle_one_connection(CONNECT*, bool))[0x559febb0abf1] ??:0(handle_one_connection)[0x559febb0af7d] ??:0(MyCTX_nopad::finish(unsigned char*, unsigned int*))[0x559febe27d5a] pthread_create.c:0(start_thread)[0x7f002a65d14a] :0(__GI___clone)[0x7f00284caf23] Both are reproducible on all of 10.2-10.6. Many thanks to gsmethells for all the feedback and thorough analysis.

Elena Stepanova added a comment - 2021-04-06 00:14 Come to think of it, there is the third option – it can be run through cursor-protocol in MTR. We rarely use it, but it still works: perl ./mtr --mem --cursor-protocol <testcase name> --source include/have_innodb.inc --source include/have_sequence.inc   CREATE OR REPLACE TABLE `774_patient` ( `patientID` int (10) unsigned NOT NULL AUTO_INCREMENT, `pid` varchar (64) NOT NULL , `pname` varchar (100) NOT NULL , PRIMARY KEY (`patientID`) ) ENGINE=InnoDB CHARSET=utf8;   CREATE OR REPLACE TABLE `774_study` ( `received` datetime(6) NOT NULL , `patientID` int (10) unsigned NOT NULL , PRIMARY KEY (`received`), KEY `idx_774_study_patientID` (`patientID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;   insert into 774_patient(patientID,pname,pid) select seq, uuid(), uuid() from seq_1_to_10000; insert into 774_study(received,patientID) select date_add( '2020-01-01' , INTERVAL seq SECOND ), seq from seq_1_to_20000;   select 774_study.patientID AS patientID from 774_study left join 774_patient on 774_study.patientID = 774_patient.patientID where exists ( select pname from 774_patient where pname like 'doe%john%^w%^%^%^%' ) and 774_patient.pname in ( select pname from 774_patient where pname like 'doe%john%^w%^%^%^%' ) order by received desc limit 50; Fails with stack traces similar to above, I won't re-paste them.

Oleksandr Byelkin added a comment - 2022-01-19 14:27 OK to push