MySQL Bugs: #114757: hang bug in mysql NDB cluster

Bug #114757	hang bug in mysql NDB cluster
Submitted:	24 Apr 2024 7:28	Modified:	25 Apr 2024 1:39
Reporter:	CunDi Fang	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S2 (Serious)
Version:	8.0.35-cluster MySQL Cluster Community S	OS:	Any (20.04)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
Hello, I found a hang bug in 8.0.35-cluster version of MYSQL cluster. 
The detail is as follow.

OS version and name:
Ubuntu 22.04.3 LTS (Jammy Jellyfish)
Linux eb1f47b08982 6.5.11-8-pve #1 SMP PREEMPT_DYNAMIC PMX 6.5.11-8 (2024-01-30T12:27Z) x86_64 x86_64 x86_64 GNU/Linux

PoC:
'''
select
  subq_0.c0 as c0,
  ref_1.column3 as c1,
  ref_1.column1 as c2,
  (select max_latency from sys.x$io_global_by_wait_by_latency limit 1 offset 4)
     as c3
from
  mytest90.test6 as ref_0
    left join mytest90.test2 as ref_1
      right join (select
            ref_2.column8 as c0,
            ref_2.column1 as c1,
            ref_2.column1 as c2
          from
            mytest90.test0 as ref_2
          where ref_2.column4 is not NULL
          limit 31) as subq_0
      on (EXISTS (
          select
              subq_0.c0 as c0,
              ref_3.column2 as c1,
              subq_0.c2 as c2,
              ref_3.column3 as c3
            from
              mytest90.test2 as ref_3
            where EXISTS (
              select
                  ref_3.column3 as c0,
                  subq_0.c1 as c1,
                  ref_4.column3 as c2,
                  ref_3.column66 as c3,
                  ref_3.column1 as c4,
                  subq_0.c1 as c5
                from
                  mytest90.test1 as ref_4
                where true
                limit 88)
            limit 110))
    on (true)
where ((ref_0.column1 is NULL)
    and ((false)
      or (((false)
          or ((false)
            or (subq_0.c1 is NULL)))
        and (true))))
  and (true)
limit 44;
'''

Architecture Information:
'''
[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=1
[NDBD]
NodeId =4
hostname =192.172.10.11
datadir =/usr/local/mysql-cluster/data
NodeGroup=0
[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
'''

Attempted and successfully reproduced!

How to repeat:
Simply execute that PoC to repeat it.

Suggested fix:
seems like a dead loop

Duplicate of Bug #114464

Thank you for your reply. May I ask what causes such a query to get stuck? Or how can I avoid such a problem? I am experiencing the same problem frequently, is this unique to NDB cluster version 8.0.35?

I suggest you contact our Support team for questions of this kind.

Basic answer is - ndbcluster is distributed real time database so is not designed for this type of queries, if your queries look like this you should be using InnoDB Cluster, not NDB Cluster.