MySQL Bugs: #84794: Cannot kill query inside GR

Bug #84794	Cannot kill query inside GR
Submitted:	2 Feb 2017 13:36	Modified:	8 Nov 2017 9:42
Reporter:	Kenny Gryp	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Group Replication	Severity:	S3 (Non-critical)
Version:	5.7.17	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When a node lost connectivity it will block all writes. GR currently does it in a way that it just stalls the queries (https://bugs.mysql.com/bug.php?id=84727)

Now I try to kill those queries and it does not work.

This means that if we use this in production, when queries get stuck, applications get stuck. Then trying to bring back the application means we have to kill those queries. but I can't kill them!

The only solution I have is to kill the mysqld member

How to repeat:
- create 3 node cluster
- take down network of 1 node
- run DDL for example, it will get stuck
- kill it, it will get stuck:

mysql> show processlist;
+----+-------------+-----------+--------------+------------+-------+--------------------------------------------------------+------------------------------+
| Id | User        | Host      | db           | Command    | Time  | State                                                  | Info                         |
+----+-------------+-----------+--------------+------------+-------+--------------------------------------------------------+------------------------------+
| 27 | system user |           | NULL         | Connect    | 36647 | executing                                              | NULL                         |
| 30 | system user |           | NULL         | Connect    | 36647 | Slave has read all relay log; waiting for more updates | NULL                         |
| 44 | root        | localhost | lefredissimo | Killed     |   346 | query end                                              | create table barbara (a int) |
| 54 | root        | localhost | lefredissimo | Field List |   198 | Waiting for table metadata lock                        |                              |
| 61 | root        | localhost | NULL         | Query      |     0 | starting                                               | show processlist             |
+----+-------------+-----------+--------------+------------+-------+--------------------------------------------------------+------------------------------+

Suggested fix:
be able to kill queries stuck in GR

I'm able to do STOP GROUP_REPLICATION, but that's just plain dangerous.... it puts the node super_read_only=off (84795)

Hello Kenny,

Thank you for the report.

Thanks,
Umesh

Posted by developer:
 
Hi Kenny,

Thank you for evaluating Group Replication, your (and all community) feedback is important!

To unblock this situation you should execute STOP GROUP_REPLICATION, having said that, we need to fix BUG#84795: "STOP GROUP_REPLICATION sets super_read_only=off", on which we are working on.

Best regards,
Nuno Carvalho

Hi Kenny,

To unblock this situation you should execute STOP GROUP_REPLICATION, having said that, we need to fix BUG#84795: "STOP GROUP_REPLICATION sets super_read_only=off", which was fixed on 5.7.20 and 8.0.2.

Best regards,
Nuno Carvalho