Description:
The function SELECT group_replication_set_as_primary() has to wait for any ongoing transactions to finish before proceeding. However, for some reason, this requirement is extended to read only transactions as well. Moreover, even a BEGIN command started and not followed by any read or write query, will block the primary switchover for as long as it's not committed or session terminated.
This makes it the primary switchover potentially very disruptive.
If the timeout parameter is specified, like via:
Cluster().setPrimaryInstance("node2", { runningTransactionsTimeout: 50 })
All the new queries, both reads and writes will be rejected with:
mysql> select 1 from test.sbtest1;
ERROR 4094 (HY000): All queries have been blocked while function 'group_replication_set_as_primary()' is executing. Please refer timeout parameter of function 'group_replication_set_as_primary()'.
If no timeout specified, switchover will be stuck as long as the culprit session exists.
Now, it is very difficult to make any pre-checks, as the blocking "transactions" are not visible anywhere - a simple BEGIN command does not start anything that would be visible in SHOW ENGINE INNODB STATUS or select * from information_schema.INNODB_TRX results. Threfore, it is impossible to find out which session may be potentially blocking.
How to repeat:
Run a BEGIN command and keep the session open (on the current primary). Then try to promote a new primary node in a GR/InnoDB Cluster.
This will result in function waiting for a long time:
*************************** 9. row ***************************
ID: 4121
USER: root
HOST: 192.168.56.16:39300
DB: NULL
COMMAND: Query
TIME: 42
STATE: executing
INFO: SELECT group_replication_set_as_primary('376b49ef-7edd-11f0-8eb3-525400256914')
EXECUTION_ENGINE: PRIMARY
Suggested fix:
I don't see any good reason for blocking the primary switchover by read-only transactions, and even less so for a pre-transaction stage, where no table was even read.
I think that only write transactions should be blocking the operation, and maybe locking reads (using FOR UPDATE) too, but nothing less than these.
The idea is to remove/minimize all unncessary downtime during the primary switch.
© 2025, Oracle Corporation and/or its affiliates
Description: The function SELECT group_replication_set_as_primary() has to wait for any ongoing transactions to finish before proceeding. However, for some reason, this requirement is extended to read only transactions as well. Moreover, even a BEGIN command started and not followed by any read or write query, will block the primary switchover for as long as it's not committed or session terminated. This makes it the primary switchover potentially very disruptive. If the timeout parameter is specified, like via: Cluster().setPrimaryInstance("node2", { runningTransactionsTimeout: 50 }) All the new queries, both reads and writes will be rejected with: mysql> select 1 from test.sbtest1; ERROR 4094 (HY000): All queries have been blocked while function 'group_replication_set_as_primary()' is executing. Please refer timeout parameter of function 'group_replication_set_as_primary()'. If no timeout specified, switchover will be stuck as long as the culprit session exists. Now, it is very difficult to make any pre-checks, as the blocking "transactions" are not visible anywhere - a simple BEGIN command does not start anything that would be visible in SHOW ENGINE INNODB STATUS or select * from information_schema.INNODB_TRX results. Threfore, it is impossible to find out which session may be potentially blocking. How to repeat: Run a BEGIN command and keep the session open (on the current primary). Then try to promote a new primary node in a GR/InnoDB Cluster. This will result in function waiting for a long time: *************************** 9. row *************************** ID: 4121 USER: root HOST: 192.168.56.16:39300 DB: NULL COMMAND: Query TIME: 42 STATE: executing INFO: SELECT group_replication_set_as_primary('376b49ef-7edd-11f0-8eb3-525400256914') EXECUTION_ENGINE: PRIMARY Suggested fix: I don't see any good reason for blocking the primary switchover by read-only transactions, and even less so for a pre-transaction stage, where no table was even read. I think that only write transactions should be blocking the operation, and maybe locking reads (using FOR UPDATE) too, but nothing less than these. The idea is to remove/minimize all unncessary downtime during the primary switch.