Description:
I have a MySQL cluster consisting of 1 control node and 4 SQL nodes, the 4 SQL nodes are also data nodes, see the REPEAT section for the exact configuration file.
Now I have a long select which is a 5-page long select statement consisting of 19009 characters.
Then this node is hanged, he won't react no matter what I do. When I look at it using "show processlist", I see that the select statement is in the PREPARING phase.
Nearly all statements executed later at this node, even for different tables, including ALTER TABLE, UPDATE, and even SELECT, will react in "TIME OUT (waiting for lock)" or "Commands out of sync; you can't run this command now".
This means that the node is completely obsolete and I can't really kill the select statement with either KILL QUERY or KILL CONNECTION, I end up needing to restart the SQL node.
How to repeat:
This scenario is actually quite common, when the SELECT involves multiple tables or is very long, it will often stop at PREPARING all the time. Here is one of my select statements that triggered this scenario:
timeline:
start time|end time|node|sql|result
-------------------------------------------------------------------------
1703395977.875610 |1703396044.624620 |node1|select below|Time_Out!!! fail!!! error!!!.
-------------------------------------------------------------------------
1703395977.875770 |1703395977.876090 |node2|"MERGE INTO mytest.test14 as target_0USING (select subq_0.c1 as c0, subq_0.c0 as c1, subq_0.c1 as c2, subq_0.c1 as c3, subq_0.c0 as c4, subq_0.c1 as c5, subq_0.c0 as c6, subq_0.c1 as c7, subq_0.c1 as c8, subq_0.c1 as c9, subq_0.c0 as c10, subq_0.c1 as c11, subq_0.c0 as c12, subq_0.c1 as c13, subq_0.c1 as c14, subq_0.c0 as c15, subq_0.c1 as c16, subq_0.c0 as c17, cast(coalesce(subq_0.c1, subq_0.c1) as varchar) as c18, subq_0.c1 as c19, subq_0.c1 as c20, subq_0.c0 as c21 from (select ref_2.column2 as c0, ref_0.column6 as c1 from mytest.test49 as ref_0 inner join mytest.test88 as ref_1 right join mytest.test41 as ref_2 on ((true) and (ref_2.column1 is NULL)) on (ref_2.column1 is NULL) where (false) and (ref_2.column2 is NULL) limit 143) as subq_0 where subq_0.c1 is not NULL) as subq_1ON target_0.column2 = subq_1.c0 WHEN NOT MATCHED AND false THEN DO NOTHINGWHEN NOT MATCHED AND true THEN DO NOTHINGWHEN MATCHED AND true THEN UPDATE set column44 = target_0.column70, column70 = cast(nullif(false, target_0.column70) as tinyint)" |mysql_store_result() failed:Commands out of sync; you can't run this command now
-------------------------------------------------------------------------
1703395977.875840 |1703396008.196990 |node3|ALTER TABLE test14 ALTER COLUMN column44 DROP DEFAULT|Query executed successfully, but no result set was returned.
-------------------------------------------------------------------------
1703395977.876040 |1703395977.876510 |node4|MERGE INTO mytest.test14 as target_0USING (select ref_1.column4 as c0, subq_0.c1 as c1, ref_0.column56 as c2, ref_5.column3 as c3, subq_0.c3 as c4, subq_1.c1 as c5, (select rows_sorted from sys.x$statements_with_sorting limit 1 offset 4) as c6, (select AUTOINC from information_schema.INNODB_TABLESTATS limit 1 offset 4) as c7, subq_0.c1 as c8 from mytest.test34 as ref_0 inner join mytest.test42 as ref_1 on ((ref_0.column1 is not NULL) and ((((EXISTS ( select ref_0.column1 as c0 from mytest.test81 as ref_2 where (true) or (((true) or (EXISTS ( select ref_2.column3 as c0, ref_3.column1 as c1, ref_0.column50 as c2, ref_2.column4 as c3, ref_1.column2 as c4, ref_1.column2 as c5, 29 as c6, ref_0.column23 as c7, ref_3.column35 as c8, ref_0.column23 as c9, 40 as c10 from mytest.test96 as ref_3 where (false) or (false) limit 86))) and (false)))) or ((((false) and (true)) and (false)) and (true))) and (EXISTS ( select ref_4.column1 as c0, ref_4.column1 as c1, ref_0.column1 as c2, ref_1.column3 as c3, ref_4.column35 as c4, ref_1.column1 as c5, ref_4.column1 as c6, ref_4.column35 as c7, ref_0.column50 as c8, ref_4.column35 as c9, ref_1.column2 as c10, ref_4.column1 as c11, ref_4.column45 as c12, ref_4.column45 as c13, (select column3 from mytest.test105 limit 1 offset 66) as c14, ref_4.column45 as c15, ref_0.column50 as c16 from mytest.test117 as ref_4 where false))) and ((ref_0.column1 is NULL) and (false)))) right join mytest.test79 as ref_5 inner join mytest.test25 as ref_6 on (ref_5.column2 is not NULL) on (EXISTS ( select (select column68 from mytest.test73 limit 1 offset 4) as c0, ref_6.column4 as c1, ref_5.column3 as c2, ref_7.column2 as c3, ref_7.column2 as c4 from mytest.test32 as ref_7 where (((EXISTS ( select ref_0.column23 as c0, ref_0.column56 as c1, ref_5.column3 as c2, ref_6.column8 as c3, ref_6.column3 as c4, ref_0.column1 as c5, ref_1.column1 as c6, (select blocking_trx_rows_locked from sys.x$innodb_lock_waits limit 1 offset 5) as c7, ref_1.column2 as c8 from mytest.test90 as ref_8 where ref_5.column1 is NULL)) or (ref_1.column2 is not NULL)) and ((true) or ((true) and (false)))) and ((EXISTS ( select ref_7.column2 as c0, (select USER from performance_schema.events_stages_summary_by_account_by_event_name limit 1 offset 6) as c1, ref_7.column3 as c2, ref_5.column2 as c3, 83 as c4, ref_9.column3 as c5, ref_6.column10 as c6, (select column6 from mytest.test25 limit 1 offset 46) as c7, ref_6.column8 as c8 from mytest.test55 as ref_9 where false)) or ((ref_7.column1 is NULL) and ((true) or (true)))))) inner join mytest.test58 as ref_10 on (ref_6.column4 = ref_10.column1 ) left join (select ref_11.column1 as c0, ref_11.column19 as c1, ref_11.column19 as c2, ref_11.column1 as c3, ref_11.column103 as c4, ref_11.column48 as c5, 49 as c6, 8 as c7 from mytest.test24 as ref_11 where ref_11.column34 is NULL limit 86) as subq_0 on (EXISTS ( select ref_5.column1 as c0, ref_6.column4 as c1 from mytest.test48 as ref_12 where ((false) or (EXISTS ( select ref_12.column16 as c0, ref_0.column50 as c1, subq_0.c7 as c2 from mytest.test116 as ref_13 where ref_6.column4 is not NULL limit 105))) or (false) limit 104)), lateral (select ref_1.column3 as c0, ref_0.column56 as c1 from mytest.test112 as ref_14 where ref_6.column5 is NULL limit 83) as subq_1 where EXISTS ( select subq_1.c1 as c0, subq_2.c2 as c1 from mytest.test102 as ref_15 inner join mytest.test43 as ref_16 on (29 is NULL) left join mytest.test90 as ref_17 on (((false) and (ref_6.column10 is NULL)) or (((false) and (ref_5.column2 is NULL)) and (((ref_10.column2 is not NULL) or ((((ref_6.column4 is not NULL) and ((false) and (false))) or (ref_1.column4 is NULL)) and ((ref_0.column50 is not NULL) and ((ref_17.column1 is NULL) or (subq_1.c1 is NULL))))) and ((true) and (EXISTS ( select ref_6.column6 as c0 from mytest.test45 as ref_18 where true limit 173)))))), lateral (select subq_1.c0 as c0, ref_5.column2 as c1, ref_1.column4 as c2, ref_10.column2 as c3, ref_5.column2 as c4, ref_17.column2 as c5, subq_0.c5 as c6, ref_19.column2 as c7, ref_16.column13 as c8, ref_15.column2 as c9, subq_1.c1 as c10, subq_1.c1 as c11, ref_10.column1 as c12, (select MIN_TIMER_READ_WRITE from performance_schema.events_transactions_summary_by_user_by_event_name limit 1 offset 6) as c13, ref_10.column4 as c14, ref_19.column1 as c15, (select uncompress_ops from information_schema.INNODB_CMP_RESET limit 1 offset 4) as c16, ref_10.column1 as c17, ref_16.column83 as c18, ref_1.column3 as c19, ref_16.column1 as c20, ref_6.column4 as c21, ref_19.column1 as c22, ref_16.column1 as c23 from mytest.test90 as ref_19 where (false) and (EXISTS ( select ref_15.column2 as c0, ref_19.column1 as c1, ref_1.column4 as c2 from mytest.test41 as ref_20 where (false) or (true)))) as subq_2 where ref_1.column4 is NULL) limit 158) as subq_3ON target_0.column90 = subq_3.c2 WHEN NOT MATCHED AND EXISTS ( select ref_21.column84 as c0 from mytest.test65 as ref_21 where EXISTS ( select ref_21.column74 as c0 from mytest.test13 as ref_22 where EXISTS ( select ref_22.column2 as c0 from mytest.test112 as ref_23 where false) limit 75)) THEN DO NOTHING |mysql_store_result() failed:Commands out of sync; you can't run this command now
The long select statement is below:
select ref_13.column3 as c0, subq_4.c0 as c1, subq_0.c0 as c2, 72 as c3, 41 as c4, ref_1.column1 as c5 from mytest.test8 as ref_0 left join mytest.test60 as ref_1 inner join mytest.test51 as ref_2 on (true) left join mytest.test92 as ref_3 on (ref_1.column3 = ref_3.column33 ) on (ref_0.column2 is not NULL) inner join mytest.test74 as ref_4 on ((70 is NULL) or (false)) right join (select ref_5.column5 as c0 from mytest.test23 as ref_5 where (true) or (ref_5.column9 is NULL)) as subq_0 right join mytest.test50 as ref_6 on (ref_6.column5 is NULL) left join (select ref_7.column105 as c0, ref_8.column6 as c1 from mytest.test39 as ref_7 inner join mytest.test13 as ref_8 on (EXISTS ( select ref_8.column1 as c0, ref_8.column6 as c1, ref_8.column5 as c2, ref_8.column4 as c3, ref_9.column3 as c4, ref_8.column2 as c5, ref_9.column3 as c6, ref_8.column4 as c7, ref_7.column26 as c8, ref_8.column5 as c9, (select column99 from mytest.test82 limit 1 offset 6) as c10, ref_9.column10 as c11 from mytest.test25 as ref_9 where ref_8.column3 is not NULL)), lateral (select ref_8.column7 as c0, ref_7.column21 as c1, ref_7.column105 as c2, ref_8.column6 as c3, ref_7.column106 as c4, ref_10.column98 as c5 from mytest.test71 as ref_10 where ref_10.column38 is NULL) as subq_1 where false limit 64) as subq_2 left join (select ref_11.column1 as c0 from mytest.test67 as ref_11, lateral (select ref_12.column7 as c0, ref_11.column21 as c1, ref_12.column2 as c2, (select Time_zone_id from mysql.time_zone_name limit 1 offset 87) as c3 from mytest.test2 as ref_12 where ((false) and ((select column10 from mytest.test25 limit 1 offset 6) is not NULL)) or (false)) as subq_3 where subq_3.c3 is not NULL limit 136) as subq_4 on (7 is NULL) inner join mytest.test40 as ref_13 inner join mytest.test108 as ref_14 on (ref_14.column2 is not NULL) on (EXISTS ( select subq_4.c0 as c0, subq_2.c0 as c1 from mytest.test106 as ref_15 where (true) or (((subq_4.c0 is not NULL) or ((ref_15.column6 is NULL) and (false))) or ((ref_15.column2 is NULL) and ((true) and ((((true) or ((EXISTS ( select ref_16.column7 as c0, ref_16.column1 as c1, subq_2.c0 as c2, ref_13.column2 as c3, ref_14.column3 as c4, ref_15.column5 as c5, ref_14.column2 as c6, ref_13.column1 as c7, subq_2.c1 as c8, ref_14.column2 as c9, subq_4.c0 as c10 from mytest.test23 as ref_16 where ((subq_2.c0 is not NULL) or (ref_15.column2 is NULL)) or (false) limit 118)) or ((false) or (EXISTS ( select subq_4.c0 as c0 from mytest.test93 as ref_17 where true limit 89))))) or ((false) and ((false) and ((subq_4.c0 is NULL) or (true))))) and (subq_2.c0 is not NULL))))) limit 124)) on (((subq_0.c0 is NULL) or ((true) or (subq_2.c1 is not NULL))) or (((false) and (false)) and ((ref_13.column1 is NULL) and (((EXISTS ( select ref_6.column7 as c0, ref_14.column3 as c1, ref_6.column6 as c2, subq_2.c0 as c3 from mytest.test43 as ref_18 where ref_14.column1 is NULL limit 101)) and ((subq_2.c1 is not NULL) or ((ref_6.column6 is not NULL) and (true)))) and ((false) or (false)))))) on (((ref_0.column3 is not NULL) and ((true) and (ref_6.column1 is NULL))) or (((((subq_2.c0 is NULL) or (EXISTS ( select ref_1.column2 as c0, ref_19.column2 as c1, ref_19.column2 as c2, subq_4.c0 as c3, ref_2.column10 as c4, subq_0.c0 as c5, (select PAGE_SIZE from information_schema.INNODB_TABLESPACES limit 1 offset 1) as c6, subq_2.c0 as c7, ref_3.column77 as c8, subq_4.c0 as c9, ref_4.column1 as c10, subq_0.c0 as c11, ref_13.column3 as c12, ref_3.column33 as c13, ref_0.column1 as c14, ref_0.column1 as c15, ref_19.column4 as c16, ref_19.column2 as c17, ref_4.column1 as c18, ref_19.column3 as c19, ref_14.column2 as c20, subq_4.c0 as c21, (select column49 from mytest.test47 limit 1 offset 1) as c22, ref_2.column16 as c23, ref_19.column4 as c24, ref_4.column1 as c25, subq_4.c0 as c26, ref_2.column1 as c27, subq_4.c0 as c28, (select column1 from mytest.test78 limit 1 offset 2) as c29 from mytest.test81 as ref_19 where (false) or ((true) and (((true) and (false)) or ((((subq_4.c0 is not NULL) or (ref_3.column56 is NULL)) and (((EXISTS ( select subq_4.c0 as c0, subq_2.c1 as c1, subq_2.c1 as c2, ref_0.column3 as c3, ref_14.column3 as c4, ref_19.column4 as c5, 99 as c6, ref_1.column1 as c7, 56 as c8 from mytest.test87 as ref_20 where (false) or ((ref_3.column1 is NULL) or ((false) or ((false) and (EXISTS ( select ref_13.column4 as c0 from mytest.test51 as ref_21 where (EXISTS ( select ref_14.column3 as c0, ref_0.column2 as c1 from mytest.test119 as ref_22, lateral (select ref_23.column3 as c0, ref_21.column42 as c1 from mytest.test12 as ref_23 where false) as subq_5 where false limit 58)) and (false) limit 93))))))) and (ref_4.column1 is NULL)) or ((ref_0.column2 is NULL) or (((ref_4.column21 is not NULL) and (EXISTS ( select ref_14.column2 as c0, ref_24.column4 as c1, ref_3.column49 as c2, ref_14.column2 as c3, subq_0.c0 as c4, subq_4.c0 as c5, ref_13.column1 as c6 from mytest.test113 as ref_24 where false limit 94))) or ((33 is NULL) or (68 is NULL)))))) or (true)))) limit 50))) or (subq_0.c0 is NULL)) and ((EXISTS ( select (select column2 from mytest.test86 limit 1 offset 6) as c0, ref_4.column1 as c1, ref_3.column77 as c2, subq_2.c1 as c3, subq_4.c0 as c4, subq_13.c4 as c5, ref_25.column2 as c6, ref_13.column2 as c7 from mytest.test79 as ref_25, lateral (select ref_3.column97 as c0, ref_13.column1 as c1, ref_2.column10 as c2, (select current_alloc from sys.x$memory_global_by_current_bytes limit 1 offset 3) as c3, ref_26.column3 as c4, ref_6.column1 as c5, ref_1.column2 as c6, ref_3.column49 as c7, (select CARDINALITY from information_schema.STATISTICS limit 1 offset 6) as c8, ref_13.column4 as c9, subq_2.c1 as c10, subq_2.c1 as c11, ref_25.column3 as c12, ref_2.column10 as c13, ref_6.column1 as c14, ref_3.column28 as c15 from mytest.test37 as ref_26 where EXISTS ( select ref_1.column6 as c0, ref_13.column3 as c1, 76 as c2, ref_25.column1 as c3 from mytest.test93 as ref_27 where EXISTS ( select ref_13.column2 as c0, ref_28.column4 as c1, ref_25.column2 as c2, ref_4.column1 as c3, ref_27.column2 as c4, ref_13.column3 as c5, ref_28.column6 as c6, subq_0.c0 as c7, ref_0.column2 as c8, ref_2.column33 as c9, ref_25.column3 as c10, (select duration_millis from ndbinfo.server_locks limit 1 offset 4) as c11, ref_2.column33 as c12, ref_25.column1 as c13, ref_0.column2 as c14, subq_0.c0 as c15, ref_2.column33 as c16, ref_26.column8 as c17, ref_4.column1 as c18 from mytest.test104 as ref_28 where (EXISTS ( select ref_14.column3 as c0, ref_3.column28 as c1 from mytest.test102 as ref_29, lateral (select ref_13.column4 as c0, ref_2.column16 as c1, ref_2.column16 as c2, subq_4.c0 as c3, ref_3.column49 as c4, subq_0.c0 as c5 from mytest.test105 as ref_30 where EXISTS ( select ref_31.column4 as c0, ref_27.column1 as c1, ref_3.column51 as c2, ref_28.column5 as c3, ref_13.column3 as c4, ref_14.column2 as c5, ref_4.column1 as c6, ref_0.column1 as c7, subq_0.c0 as c8, ref_14.column2 as c9, ref_13.column1 as c10, subq_2.c0 as c11 from mytest.test60 as ref_31, lateral (select ref_26.column10 as c0, (select THREAD_ID from performance_schema.user_variables_by_thread limit 1 offset 55) as c1, ref_14.column1 as c2, ref_4.column21 as c3 from mytest.test2 as ref_32, lateral (select ref_6.column3 as c0, subq_2.c0 as c1, (select waiting_account from sys.x$schema_table_lock_waits limit 1 offset 2) as c2, ref_27.column3 as c3, ref_13.column1 as c4, ref_4.column1 as c5, (select OBJECT_NAME from performance_schema.metadata_locks limit 1 offset 2) as c6, ref_33.column1 as c7, ref_33.column13 as c8, (select total from sys.x$user_summary_by_stages limit 1 offset 1) as c9, (select column6 from mytest.test1 limit 1 offset 5) as c10, ref_26.column7 as c11, 68 as c12, subq_0.c0 as c13, subq_0.c0 as c14, ref_29.column4 as c15, ref_25.column3 as c16, ref_26.column1 as c17, ref_1.column2 as c18, (select column5 from mytest.test60 limit 1 offset 3) as c19, ref_30.column5 as c20, (select type_id from ndbinfo.dict_obj_types limit 1 offset 4) as c21, ref_27.column1 as c22, ref_29.column2 as c23 from mytest.test43 as ref_33 where subq_0.c0 is not NULL limit 61) as subq_6, lateral (select ref_30.column3 as c0, ref_0.column2 as c1 from mytest.test97 as ref_34, lateral (select ref_26.column4 as c0 from mytest.test49 as ref_35 where ref_14.column1 is NULL limit 76) as subq_7 where false) as subq_8, lateral (select ref_30.column3 as c0, ref_14.column1 as c1, subq_4.c0 as c2, (select PARTITION_NAME from performance_schema.data_locks limit 1 offset 2) as c3, 64 as c4, (select column1 from mytest.test27 limit 1 offset 3) as c5, ref_29.column4 as c6 from mytest.test84 as ref_36 where true limit 159) as subq_9, lateral (select ref_30.column4 as c0, (select ATTR_NAME from performance_schema.session_account_connect_attrs limit 1 offset 2) as c1, ref_1.column2 as c2, ref_14.column3 as c3, ref_2.column1 as c4, subq_9.c2 as c5, ref_13.column4 as c6, ref_3.column77 as c7, ref_27.column2 as c8 from mytest.test74 as ref_37 where (true) and (true) limit 97) as subq_10 where EXISTS ( select ref_29.column2 as c0, subq_10.c2 as c1 from mytest.test33 as ref_38 where ref_38.column2 is not NULL limit 117) limit 115) as subq_11 where true limit 81) limit 59) as subq_12 where (subq_0.c0 is NULL) or (35 is not NULL) limit 30)) or (subq_2.c1 is NULL)))) as subq_13 where EXISTS ( select ref_4.column21 as c0, ref_0.column2 as c1, ref_6.column7 as c2, ref_13.column3 as c3, ref_25.column2 as c4, ref_39.column2 as c5, ref_13.column3 as c6, ref_25.column2 as c7, subq_13.c9 as c8, ref_14.column3 as c9, subq_13.c14 as c10, ref_25.column2 as c11 from mytest.test12 as ref_39, lateral (select 41 as c0, ref_40.column40 as c1, ref_40.column82 as c2, subq_13.c7 as c3, subq_4.c0 as c4, subq_0.c0 as c5 from mytest.test71 as ref_40 where true limit 151) as subq_14, lateral (select 88 as c0, subq_14.c4 as c1, subq_13.c14 as c2, ref_0.column3 as c3, subq_2.c1 as c4, ref_39.column2 as c5, ref_14.column2 as c6, ref_0.column1 as c7, 66 as c8, (select column1 from mytest.test127 limit 1 offset 98) as c9, ref_2.column33 as c10, (select SUM_TIMER_WAIT from performance_schema.events_waits_summary_global_by_event_name limit 1 offset 1) as c11 from mytest.test89 as ref_41 where ((true) or ((55 is NULL) or (false))) or (true)) as subq_15 where false limit 135) limit 154)) and ((ref_14.column2 is NULL) and (true)))) or (((false) and ((subq_2.c0 is NULL) and (EXISTS ( select ref_13.column4 as c0, ref_1.column7 as c1, subq_16.c4 as c2, ref_13.column2 as c3, subq_4.c0 as c4, ref_42.column4 as c5, subq_2.c1 as c6, subq_16.c7 as c7, ref_42.column2 as c8, ref_4.column1 as c9, ref_2.column1 as c10, ref_2.column71 as c11, subq_2.c0 as c12, subq_0.c0 as c13, subq_0.c0 as c14, subq_4.c0 as c15, 85 as c16, (select COUNT_TRANSACTIONS_ROWS_VALIDATING from performance_schema.replication_group_member_stats limit 1 offset 4) as c17, subq_4.c0 as c18, ref_3.column25 as c19 from mytest.test125 as ref_42, lateral (select ref_0.column1 as c0, ref_14.column3 as c1, subq_0.c0 as c2, subq_4.c0 as c3, 74 as c4, ref_13.column4 as c5, ref_6.column7 as c6, ref_42.column2 as c7, subq_0.c0 as c8, subq_0.c0 as c9, ref_14.column1 as c10 from mytest.test88 as ref_43 where ref_6.column5 is NULL limit 149) as subq_16 where false limit 27)))) and (EXISTS ( select ref_1.column5 as c0, ref_13.column3 as c1, ref_4.column21 as c2, ref_1.column3 as c3, ref_3.column33 as c4, subq_18.c5 as c5, ref_4.column1 as c6, subq_2.c0 as c7 from mytest.test106 as ref_44, lateral (select subq_2.c0 as c0, ref_6.column5 as c1, ref_2.column71 as c2, ref_6.column5 as c3, (select EVENT_NAME from performance_schema.memory_summary_by_user_by_event_name limit 1 offset 3) as c4, subq_17.c0 as c5, ref_45.column1 as c6, ref_45.column2 as c7, ref_6.column1 as c8, subq_4.c0 as c9 from mytest.test94 as ref_45, lateral (select subq_2.c1 as c0, ref_0.column1 as c1, ref_14.column2 as c2 from mytest.test68 as ref_46 where ((false) and (true)) and ((ref_1.column2 is not NULL) or (((true) and (EXISTS ( select (select SCHEMA_NAME from information_schema.SCHEMATA_EXTENSIONS limit 1 offset 3) as c0, ref_2.column96 as c1, ref_44.column4 as c2, ref_47.column1 as c3 from mytest.test64 as ref_47 where ref_45.column1 is NULL limit 115))) and (true))) limit 157) as subq_17 where EXISTS ( select ref_0.column3 as c0, subq_2.c1 as c1, ref_3.column97 as c2, subq_0.c0 as c3, ref_4.column1 as c4, ref_4.column21 as c5, ref_13.column4 as c6, ref_2.column96 as c7 from mytest.test106 as ref_48 where true limit 158) limit 26) as subq_18 where true limit 192))))) where EXISTS ( select ref_3.column49 as c0 from (select ref_1.column3 as c0, subq_4.c0 as c1, 49 as c2, subq_0.c0 as c3, ref_13.column3 as c4, ref_3.column64 as c5, 86 as c6, 97 as c7 from mytest.test107 as ref_49 where (select TO_USER from mysql.role_edges limit 1 offset 1) is not NULL limit 71) as subq_19 where (EXISTS ( select ref_14.column1 as c0, subq_0.c0 as c1, subq_19.c4 as c2 from mytest.test49 as ref_50 where ((false) and (((true) or (false)) or ((((EXISTS ( select subq_4.c0 as c0, subq_0.c0 as c1, subq_19.c3 as c2, subq_4.c0 as c3, ref_4.column21 as c4, subq_0.c0 as c5, ref_13.column2 as c6, ref_4.column21 as c7, 93 as c8 from mytest.test43 as ref_51 where (ref_6.column7 is not NULL) or ((false) or ((false) and (false))))) or ((false) and ((true) or ((((ref_4.column21 is NULL) or (true)) or ((true) and ((EXISTS ( select subq_0.c0 as c0, subq_19.c1 as c1, ref_4.column21 as c2, ref_4.column21 as c3, ref_50.column6 as c4 from mytest.test91 as ref_52 where (false) or ((EXISTS ( select (select ERROR_NUMBER from performance_schema.events_errors_summary_by_account_by_error limit 1 offset 4) as c0, ref_53.column3 as c1, ref_2.column71 as c2, subq_2.c1 as c3, subq_2.c0 as c4, ref_0.column3 as c5, 14 as c6, ref_52.column6 as c7, ref_1.column6 as c8 from mytest.test62 as ref_53 where true limit 65)) and ((true) and (EXISTS ( select (select thr_no from ndbinfo.cpustat limit 1 offset 52) as c0, ref_0.column3 as c1, ref_2.column13 as c2 from mytest.test81 as ref_54 where true)))))) and (subq_4.c0 is not NULL)))) or (true))))) or (((true) and (EXISTS ( select ref_1.column7 as c0, ref_0.column3 as c1, ref_1.column7 as c2, ref_4.column21 as c3, ref_3.column25 as c4, ref_0.column3 as c5, ref_14.column2 as c6, ref_14.column1 as c7, ref_55.column3 as c8, ref_14.column3 as c9, subq_0.c0 as c10, ref_13.column4 as c11 from mytest.test105 as ref_55 where (true) and (EXISTS ( select subq_4.c0 as c0, subq_0.c0 as c1, ref_3.column77 as c2, subq_4.c0 as c3, ref_14.column3 as c4, subq_19.c6 as c5, ref_0.column1 as c6 from mytest.test67 as ref_56 where (false) or (((subq_19.c6 is NULL) and ((true) or (false))) and (true)))) limit 184))) or (EXISTS ( select ref_13.column4 as c0, ref_0.column2 as c1, ref_14.column1 as c2, ref_57.column4 as c3, subq_4.c0 as c4, ref_6.column5 as c5, subq_4.c0 as c6, ref_6.column8 as c7, subq_0.c0 as c8, ref_13.column3 as c9, ref_6.column1 as c10, ref_0.column3 as c11, 57 as c12 from mytest.test97 as ref_57 where subq_2.c1 is not NULL limit 81)))) and (((true) or (ref_13.column1 is NULL)) or (false))))) or (EXISTS ( select subq_2.c1 as c0, ref_50.column6 as c1, ref_1.column4 as c2, ref_13.column4 as c3, subq_2.c0 as c4, ref_58.column2 as c5, subq_4.c0 as c6, ref_1.column3 as c7 from mytest.test0 as ref_58 where EXISTS ( select ref_2.column33 as c0, subq_19.c6 as c1, ref_13.column4 as c2, ref_14.column1 as c3, ref_2.column42 as c4, ref_58.column1 as c5 from mytest.test42 as ref_59 where subq_2.c1 is NULL limit 28) limit 76)) limit 118)) or (EXISTS ( select subq_0.c0 as c0, subq_19.c4 as c1, ref_14.column2 as c2 from mytest.test68 as ref_60 inner join mytest.test95 as ref_61 inner join mytest.test15 as ref_62 on (true) on ((EXISTS ( select ref_13.column2 as c0, subq_4.c0 as c1, ref_60.column2 as c2 from mytest.test72 as ref_63 where ((((false) or ((ref_60.column2 is NULL) or (true))) and (ref_4.column1 is NULL)) and ((EXISTS ( select ref_14.column1 as c0, ref_64.column1 as c1, ref_1.column2 as c2, ref_14.column3 as c3 from mytest.test85 as ref_64 where false)) and (false))) or (false) limit 91)) or ((ref_3.column64 is not NULL) or ((EXISTS ( select subq_19.c3 as c0 from mytest.test17 as ref_65 where true limit 62)) or (subq_4.c0 is not NULL)))) where (EXISTS ( select ref_60.column2 as c0, ref_1.column5 as c1, ref_1.column3 as c2, ref_2.column41 as c3, subq_4.c0 as c4, (select TABLE_NAME from information_schema.VIEW_TABLE_USAGE limit 1 offset 47) as c5, ref_6.column7 as c6 from mytest.test62 as ref_66 where (((((true) and ((false) and ((true) or (true)))) or ((((ref_60.column3 is NULL) and ((EXISTS ( select ref_14.column1 as c0, ref_62.column1 as c1, ref_0.column2 as c2, ref_1.column1 as c3, ref_60.column2 as c4, ref_4.column1 as c5, subq_2.c0 as c6, subq_2.c1 as c7, ref_2.column10 as c8, (select db from sys.schema_object_overview limit 1 offset 2) as c9, ref_0.column3 as c10, subq_0.c0 as c11, (select DATA_LENGTH from information_schema.FILES limit 1 offset 4) as c12, (select WORD from information_schema.KEYWORDS limit 1 offset 2) as c13, ref_13.column3 as c14 from mytest.test107 as ref_67 where true limit 94)) and (false))) and (ref_61.column2 is NULL)) and (true))) and ((true) or (true))) or ((ref_1.column4 is NULL) and (EXISTS ( select subq_2.c0 as c0, subq_4.c0 as c1 from mytest.test94 as ref_68 where false limit 105)))) and ((false) and ((true) and (ref_13.column2 is not NULL))))) or ((((ref_14.column2 is NULL) and ((EXISTS ( select ref_3.column33 as c0, ref_60.column1 as c1 from mytest.test69 as ref_69 where EXISTS ( select subq_4.c0 as c0 from mytest.test10 as ref_70 where false) limit 24)) or (EXISTS ( select ref_0.column1 as c0, ref_60.column1 as c1, ref_71.column57 as c2, ref_62.column69 as c3, subq_4.c0 as c4, ref_61.column5 as c5 from mytest.test18 as ref_71 where EXISTS ( select subq_2.c1 as c0, ref_14.column3 as c1, ref_72.column70 as c2, ref_72.column2 as c3, ref_60.column2 as c4, ref_14.column2 as c5, ref_4.column21 as c6, ref_4.column21 as c7, ref_1.column1 as c8, ref_4.column1 as c9, ref_3.column56 as c10, ref_4.column21 as c11, subq_19.c3 as c12, ref_71.column18 as c13 from mytest.test14 as ref_72 where ((false) or (true)) and ((EXISTS ( select ref_3.column75 as c0, ref_1.column5 as c1, ref_72.column2 as c2 from mytest.test79 as ref_73 where false limit 78)) or (ref_72.column2 is NULL)) limit 54) limit 101)))) or (subq_19.c4 is NULL)) or (((false) or (EXISTS ( select ref_4.column1 as c0, ref_61.column3 as c1, ref_74.column3 as c2, ref_6.column7 as c3, ref_0.column2 as c4, ref_0.column2 as c5, subq_19.c4 as c6, ref_60.column2 as c7, ref_0.column1 as c8, ref_1.column3 as c9, subq_2.c1 as c10, ref_62.column1 as c11, ref_0.column1 as c12, subq_19.c1 as c13, subq_2.c1 as c14, ref_0.column1 as c15, (select MIN_TIMER_READ_WRITE from performance_schema.events_transactions_summary_by_user_by_event_name limit 1 offset 100) as c16 from mytest.test86 as ref_74 where false))) or (true))))) limit 43) limit 85;
The configuration of my network is as follows:
[NDBD DEFAULT]
NoOfReplicas =2
DataMemory = 512M
IndexMemory = 64M
[NDB_MGMD]
NodeId=1
hostname =192.172.10.8
datadir =/var/lib/mysql-cluster
[NDBD]
NodeId =2
hostname =192.172.10.9
datadir =/usr/local/mysql-cluster/data
NodeGroup=0
[NDBD]
NodeId =3
hostname =192.172.10.10
datadir =/usr/local/mysql-cluster/data
NodeGroup=0
[NDBD]
NodeId =4
hostname =192.172.10.11
datadir =/usr/local/mysql-cluster/data
NodeGroup=1
[NDBD]
NodeId =5
hostname =192.172.10.12
datadir =/usr/local/mysql-cluster/data
NodeGroup=1
[mysqld]
NodeId =6
hostname =192.172.10.9
[mysqld]
NodeId =7
hostname =192.172.10.10
[mysqld]
NodeId =8
hostname =192.172.10.11
[mysqld]
NodeId =9
hostname =192.172.10.12
Suggested fix:
I would suggest setting a similar threshold for the waiting time of the execution phase for the preparing phase, and forcing termination and error reporting when the threshold is exceeded.
