Description:
This particular subquery:
select count(*) from user_rights where user_rights.uid in ( select reg_user.mobile from reg_user where status = '0' )
hangs the mysqld server, i.e., it gets "stuck" in preparing phase. This subquery only gets stuck when the storage engine is NDB.
mysql> explain select count(*) from user_rights where user_rights.uid in ( select reg_user.mobile from reg_user where status = '0' );
+----+--------------------+-------------+----------------+-----------------------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------------+----------------+-----------------------------+---------+---------+------+------+-------------+
| 1 | PRIMARY | user_rights | ALL | NULL | NULL | NULL | NULL | 799 | Using where |
| 2 | DEPENDENT SUBQUERY | reg_user | index_subquery | Index_1,reg_user_status_idx | Index_1 | 153 | func | 1 | Using where |
+----+--------------------+-------------+----------------+-----------------------------+---------+---------+------+------+-------------+
2 rows in set (0.00 sec)
mysql> mysql> show processlist;
+----+-------------+-----------+-------+---------+------+-----------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+-------+---------+------+-----------------------------------+------------------------------------------------------------------------------------------------------+
| 1 | system user | | | Daemon | 0 | Waiting for event from ndbcluster | NULL |
| 2 | root | localhost | green | Query | 226 | preparing | select count(*) from user_rights where user_rights.uid in ( select reg_user.mobile from reg_user whe |
| 19 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+-------------+-----------+-------+---------+------+-----------------------------------+------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
It's been verified on 5.1.30-ndb-6.3.20 and 5.1.34-ndb-7.07 with the same result.
A workaround is using a join:
mysql> select count(*) from user_rights left join reg_user on user_rights.uid = reg_user.mobile where reg_user.status = '0';
+----------+
| count(*) |
+----------+
| 527 |
+----------+
1 row in set (0.02 sec)
How to repeat:
Use attached schema and data dump file. You probably will get: Error 783 - "Too many schema operations" when loading/creating the schema. Do the index creation for the 'reg_user' table separately using alter table.
mysql> select count(*) from user_rights where user_rights.uid in ( select reg_user.mobile from reg_user where status = '0' );
Expected Result: 527
A workaround is using a join:
mysql> select count(*) from user_rights left join reg_user on user_rights.uid = reg_user.mobile where reg_user.status = '0';
+----------+
| count(*) |
+----------+
| 527 |
+----------+
1 row in set (0.02 sec)
Suggested fix:
Make the subquery not hang the mysqld server.
