Bug #97917 ERROR 1192 running START/STOP group_replication after query on slave_master_info
Submitted: 6 Dec 2019 15:23 Modified: 10 Dec 2019 13:05
Reporter: SERGEY KUZMICHEV Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.7.22, 5.7.27, 5.7.28, 8.0.18 OS:Any
Assigned to: CPU Architecture:Any

[6 Dec 2019 15:23] SERGEY KUZMICHEV
Description:
With autocommit=0, after running a SELECT on mysql.slave_master_info, neither START GROUP_REPLICATION nor STOP GROUP_REPLICATION will work in the same connection.
Error reported is:
ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction

This has impact on mysql shell, as it will fail to remove an instance from the cluster under some circumstances.

How to repeat:
In the same connection, run:

SET autocommit=0;
SELECT * FROM mysql.slave_master_info;
START GROUP_REPLICATION;

Error reported:

ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction
[9 Dec 2019 8:46] SERGEY KUZMICHEV
Since with autocommit=0 transaction is always open for a session, the error will be reported for start/stop after any table is queried and transaction is actually started. This is not a bug.

MySQL Shell, however, might end up not being able to remove an instance due to this, but that's a different issue.
[10 Dec 2019 10:56] SERGEY KUZMICHEV
After some further consideration, it's still at least curious that start/stop group_replication behaves differently than regular start/stop slave does. The latter command will implicitly commit the transaction. Is there a reason for the difference in behavior?

#autocommit=0
mysql> select * from test;
Empty set (0.00 sec)

mysql> start group_replication;
ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction
mysql> start slave;
ERROR 1794 (HY000): Slave is not configured or failed to initialize properly. You must at least set --server-id to enable either a master or a slave. Additional error messages can be found in the MySQL error log.
mysql> start group_replication;
ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.
[10 Dec 2019 13:05] Bogdan Kecman
Hi,

Thanks for the report, verified as described. Can't say really if the code or only documentation will change as the workaround is rather simple.

thanks