Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Crashes after 8.0.22 upgrade

Description

After upgrading the percona server to 8.0.22 i am getting a reproducible crash of the mysqld for a specific scheduled query:

Thread pointer: 0x7f18480bef90 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 = 7f23e00b1da0 thread_stack 0x30000 /usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x2e) [0x557bdffba4fe] /usr/sbin/mysqld(handle_fatal_signal+0x3b3) [0x557bdf275993] /lib/x86_64-linux-gnu/libpthread.so.0(+0x12730) [0x7f2436ee1730] /usr/sbin/mysqld(JOIN::update_depend_map(ORDER*)+0x98) [0x557bdf113578] /usr/sbin/mysqld(JOIN::remove_const(ORDER*, Item*, bool, bool*, bool)+0x1e8) [0x557bdf1195a8] /usr/sbin/mysqld(JOIN::optimize_distinct_group_order()+0x1a3) [0x557bdf120593] /usr/sbin/mysqld(JOIN::optimize()+0x2316) [0x557bdf127d06] /usr/sbin/mysqld(SELECT_LEX::optimize(THD*)+0xdc) [0x557bdf1849ac] /usr/sbin/mysqld(SELECT_LEX_UNIT::optimize(THD*, TABLE*, bool)+0x6b) [0x557bdf1fbb9b] /usr/sbin/mysqld(Sql_cmd_dml::execute_inner(THD*)+0x2e) [0x557bdf1830ae] /usr/sbin/mysqld(Sql_cmd_dml::execute(THD*)+0x6b8) [0x557bdf18daa8] /usr/sbin/mysqld(mysql_execute_command(THD*, bool)+0xac8) [0x557bdf12f108] /usr/sbin/mysqld(mysql_parse(THD*, Parser_state*, bool)+0x4b8) [0x557bdf133cb8] /usr/sbin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0x20ac) [0x557bdf13620c] /usr/sbin/mysqld(do_command(THD*)+0x1e4) [0x557bdf136eb4] /usr/sbin/mysqld(+0x1351788) [0x557bdf267788] /usr/sbin/mysqld(+0x2591834) [0x557be04a7834] /lib/x86_64-linux-gnu/libpthread.so.0(+0x7fa3) [0x7f2436ed6fa3] /lib/x86_64-linux-gnu/libc.so.6(clone+0x3f) [0x7f24366804cf]

Environment

None

Attachments

2
  • 24 Dec 2020, 01:44 PM
  • 17 Dec 2020, 02:45 PM

Smart Checklist

Activity

Show:

Lalit Choudhary January 12, 2022 at 1:17 PM

Fixed in 8.0.26:  

  • Queries that involved pushing a condition with view references down to a materialized derived table could cause a server exit. (Bug #32905044, Bug #32324234)

Lalit Choudhary December 24, 2020 at 1:59 PM

Thank you

verified as a bug, the issue also reproduciable with upstream mysql.

Lalit Choudhary December 24, 2020 at 1:45 PM

Hi

Crash reproducible when not using sql_mode=only_full_group_by

Reproduciable test:

run [^crash_data_query.sql]

 

master [localhost] {msandbox} (foo1) > select -> now() as ref_at, -> c1, -> c2, -> c3, -> c4, -> an_c1, -> pt_c2, -> pn_c3, -> convert(round(sum(a_c4),0),char) a_c4, -> convert(round(sum(case when ps_c5 = 0 and paid_at is not null then a_c4 else 0 end),0),char) as afp_c6, -> convert(round(sum(case when ps_c5 > 0 and paid_at is not null then a_c4 else 0 end),0),char) as ap_c7 -> -> from ( -> select -> date_format(ord_at,'%Y-KW-%v') c2, -> year(ord_at) c3, -> month(ord_at) c4, -> c1, -> ship_at, -> paid_at, -> off_name, -> ps_c5, -> case when c_h_p is null and p_p_n is not null then p_p_n -> when p_p_n is null and off_name = 'ddddfffff' then 'ddddfffff' -> when p_p_n is null and off_name != 'ddddfffff' then off_name -> else c_h_p -> end as pn_c3, -> case when p_a is not null then p_a * a_c4 -> when o_a is null then a_c4 -> else o_a * a_c4 -> end as a_c4, -> case when c_a_n is not null then c_a_n -> when p_a_n is not null then p_a_n -> when off_name = 'ddddfffff' then 1000 -> else 0 -> end as an_c1, -> -> case when child_type is not null then child_type -> when parent_type is not null then parent_type -> when off_name = 'ddddfffff' then 'ddddfffff' -> else 0 -> end as pt_c2 -> -> from ( -> select -> c.c1, -> ship_at, -> paid_at, -> ord_at, -> order_number, -> o.name off_name, -> cxo.product_id, -> cxo.offer_id, -> cxo.ps_c5, -> cxo.a_c4, -> p.name p_p_n, -> p2.name c_h_p, -> pxp.a_c4 p_a, -> oxp.a_c4 o_a, -> p.an_c1 p_a_n, -> p2.an_c1 c_a_n, -> p.p_t_id p_p_t_id, -> p2.p_t_id c_p_t_id, -> pt.name parent_type, -> pt2.name child_type -> -> from tbl1 c -> -> left outer join tbl2 cxo -> on c.id = cxo.cart_id -> -> left outer join tbl3 o -> on cxo.offer_id = o.id -> -> left outer join tbl4 oxp -> on o.id = oxp.offer_id -> -> left outer join tbl5 p -> on oxp.product_id = p.id -> -> left outer join tbl6 pm -> on p.id = pm.product -> -> left outer join tbl7 pxp -> on p.id = pxp.parent_id -> -> left outer join tbl5 p2 -> on pxp.child_id = p2.id -> -> left outer join tbl8 pt -> on p.p_t_id = pt.id -> -> left outer join tbl8 pt2 -> on p2.p_t_id = pt2.id -> -> where -> -> date(ord_at) >= 318010 -> group by order_number,cxo.offer_id,cxo.product_id,pxp.child_id -> order by ord_at desc -> ) as a -> -> -> ) as a -> where an_c1 in (10,1000,10012,10011) -> group by c1,c2,an_c1 -> order by c1,c2,a_c4 desc; ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'foo1.c.c1' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by   master [localhost] {msandbox} (foo1) > set sql_mode=' '; Query OK, 0 rows affected (0.00 sec) master [localhost] {msandbox} (foo1) > master [localhost] {msandbox} (foo1) > select -> now() as ref_at, -> c1, -> c2, -> c3, -> c4, -> an_c1, -> pt_c2, -> pn_c3, -> convert(round(sum(a_c4),0),char) a_c4, -> convert(round(sum(case when ps_c5 = 0 and paid_at is not null then a_c4 else 0 end),0),char) as afp_c6, -> convert(round(sum(case when ps_c5 > 0 and paid_at is not null then a_c4 else 0 end),0),char) as ap_c7 -> -> from ( -> select -> date_format(ord_at,'%Y-KW-%v') c2, -> year(ord_at) c3, -> month(ord_at) c4, -> c1, -> ship_at, -> paid_at, -> off_name, -> ps_c5, -> case when c_h_p is null and p_p_n is not null then p_p_n -> when p_p_n is null and off_name = 'ddddfffff' then 'ddddfffff' -> when p_p_n is null and off_name != 'ddddfffff' then off_name -> else c_h_p -> end as pn_c3, -> case when p_a is not null then p_a * a_c4 -> when o_a is null then a_c4 -> else o_a * a_c4 -> end as a_c4, -> case when c_a_n is not null then c_a_n -> when p_a_n is not null then p_a_n -> when off_name = 'ddddfffff' then 1000 -> else 0 -> end as an_c1, -> -> case when child_type is not null then child_type -> when parent_type is not null then parent_type -> when off_name = 'ddddfffff' then 'ddddfffff' -> else 0 -> end as pt_c2 -> -> from ( -> select -> c.c1, -> ship_at, -> paid_at, -> ord_at, -> order_number, -> o.name off_name, -> cxo.product_id, -> cxo.offer_id, -> cxo.ps_c5, -> cxo.a_c4, -> p.name p_p_n, -> p2.name c_h_p, -> pxp.a_c4 p_a, -> oxp.a_c4 o_a, -> p.an_c1 p_a_n, -> p2.an_c1 c_a_n, -> p.p_t_id p_p_t_id, -> p2.p_t_id c_p_t_id, -> pt.name parent_type, -> pt2.name child_type -> -> from tbl1 c -> -> left outer join tbl2 cxo -> on c.id = cxo.cart_id -> -> left outer join tbl3 o -> on cxo.offer_id = o.id -> -> left outer join tbl4 oxp -> on o.id = oxp.offer_id -> -> left outer join tbl5 p -> on oxp.product_id = p.id -> -> left outer join tbl6 pm -> on p.id = pm.product -> -> left outer join tbl7 pxp -> on p.id = pxp.parent_id -> -> left outer join tbl5 p2 -> on pxp.child_id = p2.id -> -> left outer join tbl8 pt -> on p.p_t_id = pt.id -> -> left outer join tbl8 pt2 -> on p2.p_t_id = pt2.id -> -> where -> -> date(ord_at) >= 318010 -> group by order_number,cxo.offer_id,cxo.product_id,pxp.child_id -> order by ord_at desc -> ) as a -> -> -> ) as a -> where an_c1 in (10,1000,10012,10011) -> group by c1,c2,an_c1 -> order by c1,c2,a_c4 desc; ERROR 2013 (HY000): Lost connection to MySQL server during query 2020-12-24T12:24:52.235149Z 0 [System] [MY-010931] [Server] /home/lalit/mysql_tar/percona/8.0.22/bin/mysqld: ready for connections. Version: '8.0.22-13' socket: '/tmp/mysql_sandbox23430.sock' port: 23430 Percona Server (GPL), Release 13, Revision 6f7822f. 12:54:05 UTC - mysqld got signal 11 ; Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware. Build ID: Not Available Server Version: 8.0.22-13 Percona Server (GPL), Release 13, Revision 6f7822f Thread pointer: 0x7f84a381e000 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 = 7f84dc06dd10 thread_stack 0x46000 /home/lalit/mysql_tar/percona/8.0.22/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x3d) [0x210c1cd] /home/lalit/mysql_tar/percona/8.0.22/bin/mysqld(handle_fatal_signal+0x3c3) [0x1260d33] /lib/x86_64-linux-gnu/libpthread.so.0(+0x128a0) [0x7f84df5c88a0] /home/lalit/mysql_tar/percona/8.0.22/bin/mysqld(JOIN::update_depend_map(ORDER*)+0x98) [0x10f19b8] /home/lalit/mysql_tar/percona/8.0.22/bin/mysqld(JOIN::remove_const(ORDER*, Item*, bool, bool*, bool)+0x1f7) [0x10f7b87] /home/lalit/mysql_tar/percona/8.0.22/bin/mysqld(JOIN::optimize_distinct_group_order()+0x1b2) [0x10fec02] /home/lalit/mysql_tar/percona/8.0.22/bin/mysqld(JOIN::optimize()+0x2336) [0x11065b6] /home/lalit/mysql_tar/percona/8.0.22/bin/mysqld(SELECT_LEX::optimize(THD*)+0xeb) [0x116616b] /home/lalit/mysql_tar/percona/8.0.22/bin/mysqld(SELECT_LEX_UNIT::optimize(THD*, TABLE*, bool)+0x7b) [0x11e185b] /home/lalit/mysql_tar/percona/8.0.22/bin/mysqld(Sql_cmd_dml::execute_inner(THD*)+0x3d) [0x11647bd] /home/lalit/mysql_tar/percona/8.0.22/bin/mysqld(Sql_cmd_dml::execute(THD*)+0x6c0) [0x116f590] /home/lalit/mysql_tar/percona/8.0.22/bin/mysqld(mysql_execute_command(THD*, bool)+0xaf8) [0x110e588] /home/lalit/mysql_tar/percona/8.0.22/bin/mysqld(mysql_parse(THD*, Parser_state*, bool)+0x4ec) [0x111327c] /home/lalit/mysql_tar/percona/8.0.22/bin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0x1be5) [0x1115325] /home/lalit/mysql_tar/percona/8.0.22/bin/mysqld(do_command(THD*)+0x204) [0x1116554] /home/lalit/mysql_tar/percona/8.0.22/bin/mysqld() [0x1251c20] /home/lalit/mysql_tar/percona/8.0.22/bin/mysqld() [0x2620e84] /lib/x86_64-linux-gnu/libpthread.so.0(+0x76db) [0x7f84df5bd6db] /lib/x86_64-linux-gnu/libc.so.6(clone+0x3f) [0x7f84dd588a3f] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (7f84a2017028): select now() as ref_at, c1, c2, c3, c4, an_c1, pt_c2, pn_c3, convert(round(sum(a_c4),0),char) a_c4, convert(round(sum(case when ps_c5 = 0 and paid_at is not null then a_c4 else 0 end),0),char) as afp_c6, convert(round(sum(case when ps_c5 > 0 and paid_at is not null then a_c4 else 0 end),0),char) as ap_c7 from ( select date_format(ord_at,'%Y-KW-%v') c2, year(ord_at) c3, month(ord_at) c4, c1, ship_at, paid_at, off_name, ps_c5, case when c_h_p is null and p_p_n is not null then p_p_n when p_p_n is null and off_name = 'ddddfffff' then 'ddddfffff' when p_p_n is null and off_name != 'ddddfffff' then off_name else c_h_p end as pn_c3, case when p_a is not null then p_a * a_c4 when o_a is null then a_c4 else o_a * a_c4 end as a_c4, case when c_a_n is not null then c_a_n when p_a_n is not null then p_a_n when off_name = 'ddddfffff' then 1000 else 0 end as an_c1, case when child_type is not null then child_type when parent_type is not null then parent_type when off_name = 'ddddfffff' then 'ddddfffff' else 0 end as pt_ Connection ID (thread ID): 11 Status: NOT_KILLED

 

Seb December 22, 2020 at 12:01 PM

Hi @lalit.choudhary i uploaded the struct.sql to the SFTP provided. I tried to execute the query with this minimal schema and the same crash occurs, so it should hopefully also be reproducible for you.

Lalit Choudhary December 21, 2020 at 2:11 PM

Hi Seb,

I tried to create genraic test case from given query but looks like we also need all tables definations  that we have  in 

can you provide that and i will create  generic test case refering same tables.

If you want keep the tables schema details private, you can upload that detail on our private sftp, Here are the details.

 

To upload files please use Percona SFTP server.

Server: `sftp.percona.com` • Port: `2222` • Protocol: `sftp` • Username: PS-7473 • Password: PS-7473 • Upload via command line: `scp -P2222 ./PS-7473.tar.gz PXB-2121@ftp.percona.com:PS-7473.tar.gz` • NOTES: BLIND UPLOAD ONLY service, directory listing disabled, directory mirroring disabled. ADVICE: upload a tar file or similar with everything included there

will not put provided table/schema detail in public comments.

Other option  is  you can create tables on test envirment with differe table name and columns  names and send us that tets case. 

Done

Details

Assignee

Reporter

Labels

Upstream Bug URL

MySQL Bugs: Access denied
MySQL Bugs: Access denied

Fix versions

Affects versions

Priority

Smart Checklist

Created December 16, 2020 at 5:08 AM
Updated March 6, 2024 at 10:47 AM
Resolved January 12, 2022 at 1:19 PM

Flag notifications