Bug #106586 after flush tables function error
Submitted: 27 Feb 2022 13:10 Modified: 1 Apr 2022 13:21
Reporter: peng gao Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Group Replication Severity:S3 (Non-critical)
Version:8.0.23 8.0.26 OS:Any
Assigned to: MySQL Verification Team CPU Architecture:Any

[27 Feb 2022 13:10] peng gao
Description:
Hi all:

  recently,we find a issue , like this, 2 nodes mgr, do like:
  
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select  test.gr_member_in_primary_partition2();
+----------------------------------------+
| test.gr_member_in_primary_partition2() |
+----------------------------------------+
| YES                                    |
+----------------------------------------+
1 row in set (0.01 sec)

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> select  test.gr_member_in_primary_partition2();
ERROR 1242 (21000): Subquery returns more than 1 row 

function is:

DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `gr_member_in_primary_partition2`() RETURNS varchar(3) CHARSET utf8mb4
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', 'NO' ) FROM performance_schema.replication_group_members JOIN
   performance_schema.replication_group_member_stats rgms USING(member_id) where rgms.MEMBER_ID=@@SERVER_UUID);
END ;;
DELIMITER ;
   

we find this diff:
    
    error scenario no join condition :
  
    899 T@25: | | | | | | | | | | | | | | | >optimize_cond
    900 T@25: | | | | | | | | | | | | | | | | opt: (null): starting struct
    901 T@25: | | | | | | | | | | | | | | | | opt: condition_processing: starting struct
    902 T@25: | | | | | | | | | | | | | | | | opt: condition: "WHERE"
    903 T@25: | | | | | | | | | | | | | | | | opt: original_condition: "(`performance_schema`.`rgms`.`MEMBER_ID` = convert( using utf8mb4))"
    904 T@25: | | | | | | | | | | | | | | | | opt: steps: starting struct
    905 T@25: | | | | | | | | | | | | | | | | opt: (null): starting struct
    906 T@25: | | | | | | | | | | | | | | | | opt: transformation: "equality_propagation"
    907 T@25: | | | | | | | | | | | | | | | | opt: subselect_evaluation: starting struct
    908 T@25: | | | | | | | | | | | | | | | | opt: subselect_evaluation: ending struct
    909 T@25: | | | | | | | | | | | | | | | | opt: resulting_condition: "multiple equal(convert( using utf8mb4), `performance_schema`.`rgms`.`MEMBER_ID`)"
    910 T@25: | | | | | | | | | | | | | | | | opt: (null): ending struct
    911 T@25: | | | | | | | | | | | | | | | | opt: (null): starting struct
    912 T@25: | | | | | | | | | | | | | | | | opt: transformation: "constant_propagation"
    913 T@25: | | | | | | | | | | | | | | | | opt: subselect_evaluation: starting struct
    914 T@25: | | | | | | | | | | | | | | | | opt: subselect_evaluation: ending struct
    915 T@25: | | | | | | | | | | | | | | | | opt: resulting_condition: "multiple equal(convert( using utf8mb4), `performance_schema`.`rgms`.`MEMBER_ID`)"
    916 T@25: | | | | | | | | | | | | | | | | opt: (null): ending struct
    917 
    918 WHERE:(after const change) 0x7fff98459750 multiple equal(convert( using utf8mb4), `performance_schema`.`rgms`.`MEMBER_ID`)
    919 T@25: | | | | | | | | | | | | | | | | opt: (null): starting struct
    920 T@25: | | | | | | | | | | | | | | | | opt: transformation: "trivial_condition_removal"
    921 T@25: | | | | | | | | | | | | | | | | opt: subselect_evaluation: starting struct
    922 T@25: | | | | | | | | | | | | | | | | opt: subselect_evaluation: ending struct
    923 T@25: | | | | | | | | | | | | | | | | opt: resulting_condition: "multiple equal(convert( using utf8mb4), `performance_schema`.`rgms`.`MEMBER_ID`)"
    924 T@25: | | | | | | | | | | | | | | | | opt: (null): ending struct
    925 T@25: | | | | | | | | | | | | | | | | opt: steps: ending struct
    926 T@25: | | | | | | | | | | | | | | | | opt: condition_processing: ending struct
    927 T@25: | | | | | | | | | | | | | | | | opt: (null): ending struct
    928 T@25: | | | | | | | | | | | | | | | <optimize_cond
    
    normal scenario:
    
    891 T@27: | | | | | | | | | | | | | | | >optimize_cond
    892 T@27: | | | | | | | | | | | | | | | | opt: (null): starting struct
    893 T@27: | | | | | | | | | | | | | | | | opt: condition_processing: starting struct
    894 T@27: | | | | | | | | | | | | | | | | opt: condition: "WHERE"
    895 T@27: | | | | | | | | | | | | | | | | opt: original_condition: "((`performance_schema`.`rgms`.`MEMBER_ID` = convert( using utf8mb4)) and (`performance_schema`.`replication_group_mem        bers`.`MEMBER_ID` = `performance_schema`.`rgms`.`MEMBER_ID`))"
    896 T@27: | | | | | | | | | | | | | | | | opt: steps: starting struct
    897 T@27: | | | | | | | | | | | | | | | | opt: (null): starting struct
    898 T@27: | | | | | | | | | | | | | | | | opt: transformation: "equality_propagation"
    899 T@27: | | | | | | | | | | | | | | | | opt: subselect_evaluation: starting struct
    900 T@27: | | | | | | | | | | | | | | | | opt: subselect_evaluation: ending struct
    901 T@27: | | | | | | | | | | | | | | | | opt: resulting_condition: "(multiple equal(convert( using utf8mb4), `performance_schema`.`rgms`.`MEMBER_ID`, `performance_schema`.`replication_        group_members`.`MEMBER_ID`))"
    902 T@27: | | | | | | | | | | | | | | | | opt: (null): ending struct
    903 T@27: | | | | | | | | | | | | | | | | opt: (null): starting struct
    904 T@27: | | | | | | | | | | | | | | | | opt: transformation: "constant_propagation"
    905 T@27: | | | | | | | | | | | | | | | | opt: subselect_evaluation: starting struct
    906 T@27: | | | | | | | | | | | | | | | | opt: subselect_evaluation: ending struct
    907 T@27: | | | | | | | | | | | | | | | | opt: resulting_condition: "(multiple equal(convert( using utf8mb4), `performance_schema`.`rgms`.`MEMBER_ID`, `performance_schema`.`replication_        group_members`.`MEMBER_ID`))"
    908 T@27: | | | | | | | | | | | | | | | | opt: (null): ending struct
    909 
    910 WHERE:(after const change) 0xc478f10 (multiple equal(convert( using utf8mb4), `performance_schema`.`rgms`.`MEMBER_ID`, `performance_schema`.`replication_group_members`.`MEMBER_ID`))
    911 T@27: | | | | | | | | | | | | | | | | opt: (null): starting struct
    912 T@27: | | | | | | | | | | | | | | | | opt: transformation: "trivial_condition_removal"
    913 T@27: | | | | | | | | | | | | | | | | opt: subselect_evaluation: starting struct
    914 T@27: | | | | | | | | | | | | | | | | opt: subselect_evaluation: ending struct
    915 T@27: | | | | | | | | | | | | | | | | opt: resulting_condition: "multiple equal(convert( using utf8mb4), `performance_schema`.`rgms`.`MEMBER_ID`, `performance_schema`.`replication_g        roup_members`.`MEMBER_ID`)"
    916 T@27: | | | | | | | | | | | | | | | | opt: (null): ending struct
    917 T@27: | | | | | | | | | | | | | | | | opt: steps: ending struct
    918 T@27: | | | | | | | | | | | | | | | | opt: condition_processing: ending struct
    919 T@27: | | | | | | | | | | | | | | | | opt: (null): ending struct
    920 T@27: | | | | | | | | | | | | | | | <optimize_cond
    
   And  get_optimizable_join_conditions function table_list in error scenario is :
     table_name = 0x621c750 "", alias = 0x61cfa85 "(nest_last_join)"
    normal  scenario is :
      "performance_schema", table_name = 0xca88820 "replication_group_member_stats"
    
    Is The parse tree problem? I think  flush tables relate "nest_last_join" "table".    
    Thanks !

How to repeat:
like Description
[1 Mar 2022 13:21] MySQL Verification Team
Hi,
You get this problem only immediately after flush tables or for a while or only in that connection or?

thanks
[2 Apr 2022 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".