Percona 8.0 bug: Subquery returns more than 1 row

最新推荐文章于 2025-11-06 11:25:45 发布

原创

最新推荐文章于 2025-11-06 11:25:45 发布 · 454 阅读

0 ·

CC 4.0 BY-SA版权

在Percona Server 8.0.22环境中使用MGR+ProxySQL进行mydumper备份时遇到一个问题，ProxySQL日志显示`Subquery returns more than 1 row`错误。问题源于`gr_member_routing_candidate_status`视图中`gr_member_in_primary_partition()`函数，在FTWRL期间执行导致。通过模拟发现，当在Session1中执行`flush tables with read lock`后，Session2执行`gr_member_in_primary_partition()`会报错。进一步分析发现，问题可能与`performance_schema.replication_group_members`视图有关。为解决这个问题，建议不使用函数，而是直接使用函数内的SQL语句替换，更新`gr_member_routing_candidate_status`视图的定义。

这是一个Percona Server8.0.22下使用MGR+ProxySQL时遇到的bug

使用mydumper备份mgr时发现ProxySQL报错了

# cat proxysql.log | grep -i subquery
2021-04-28 15:14:22 MySQL_HostGroups_Manager.cpp:3875:update_group_replication_set_offline(): [WARNING] Group Replication: setting host 172.16.23.224:3309 offline because: Subquery returns more than 1 row
2021-04-28 15:14:22 MySQL_Monitor.cpp:1472:monitor_group_replication_thread(): [ERROR] Got error. mmsd 0x7fd62cee3540 , MYSQL 0x7fd62b804600 , FD 39 : Subquery returns more than 1 row
2021-04-28 15:14:27 MySQL_Monitor.cpp:1472:monitor_group_replication_thread(): [ERROR] Got error. mmsd 0x7fd62cee1440 , MYSQL 0x7fd62b800000 , FD 41 : Subquery returns more than 1 row
2021-04-28 15:17:37 MySQL_HostGroups_Manager.cpp:3875:update_group_replication_set_offline(): [WARNING] Group Replication: setting host 172.16.23.151:3309 offline because: Subquery returns more than 1 row
2021-04-28 15:17:37 MySQL_Monitor.cpp:1472:monitor_group_replication_thread(): [ERROR] Got error. mmsd 0x7fd62cee78c0 , MYSQL 0x7fd62bc06400 , FD 51 : Subquery returns more than 1 row
2021-04-28 15:17:42 MySQL_Monitor.cpp:1472:monitor_group_replication_thread(): [ERROR] Got error. mmsd 0x7fd62cee0300 , MYSQL 0x7fd62bc00000 , FD 40 : Subquery returns more than 1 row

看到这个错误我怀疑是gr_member_routing_candidate_status视图有问题(因为之前被这个视图坑过, 详见issue3406), 并且猜测是由于备份时执行FTWRL导致的. 进行了几次模拟, 发现了复现方法

环境

[root@bj2-mysql-huoyun-prod-01 data]# mysql -uroot -p -S /data/mysql_3310/run/mysql.sock
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 126072
Server version: 8.0.22-13 Percona Server (GPL), Release 13, Revision 6f7822f

Copyright (c) 2009-2020 Percona LLC and/or its affiliates
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost 17:54:49 [(none)]> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST   | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 06c6f8b7-a195-11eb-994b-fa163e39df9a | 172.16.xx.151 |        3310 | ONLINE       | PRIMARY     | 8.0.22         |
| group_replication_applier | 0710790b-a195-11eb-a621-fa163eb36fc8 | 172.16.xx.219 |        3310 | ONLINE       | SECONDARY   | 8.0.22         |
| group_replication_applier | 073ebcfe-a195-11eb-8b08-fa163efdcb19 | 172.16.xx.224 |        3310 | ONLINE       | SECONDARY   | 8.0.22         |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

ProxySQL监控检查需要查询gr_member_routing_candidate_status表, 定义如下

DELIMITER $$

CREATE FUNCTION gr_applier_queue_length()
RETURNS INT
DETERMINISTIC
BEGIN
  RETURN (SELECT sys.gtid_count( GTID_SUBTRACT( (SELECT
Received_transaction_set FROM performance_schema.replication_connection_status
WHERE Channel_name = 'group_replication_applier' ), (SELECT
@@global.GTID_EXECUTED) )));
END$$

CREATE FUNCTION gr_member_in_primary_partition()
RETURNS VARCHAR(3)
DETERMINISTIC
BEGIN
  RETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM
performance_schema.replication_group_members WHERE MEMBER_STATE != 'ONLINE') >=
((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0),
'YES'

最低0.47元/天解锁文章