Description:
When promoting a new primary in an InnoDB Cluster — Cluster.setPrimaryInstance(target, {runningTransactionsTimeout: N}), i.e. the server UDF group_replication_set_as_primary() — the primary election waits up to N seconds for in-flight transactions on the current primary to complete. Two related shortcomings make this harder to operate than it should be:
1-) Over-restriction. A transaction that is read-only or merely open-and-idle — a bare BEGIN with no statements, or an uncommitted SELECT — is treated as a "running transaction" and stalls the whole election for the full timeout, even though it cannot be lost by a switchover. These transactions do not appear in information_schema.INNODB_TRX (COUNT(*) = 0), so they are invisible for diagnosis.
2-) No observability. Even at log_error_verbosity = 3, a switch that had to wait (or that was forced through when the timeout expired) is indistinguishable in the error log from an instant, clean switch — the same message sequence is written; only the time gap between MY-013214 (Starting … Primary election change) and MY-011507 (A new primary … elected) differs. When the timeout is reached, the monitor thread disconnects binloggable transactions that have not committed (client sees ERROR 2013 Lost connection, transaction is rolled back), but nothing is logged to indicate the timeout was reached or that connections were closed.
Requested feature: (a) restrict the switchover wait to transactions that can actually be lost (modified data / hold record locks / assigned a GTID) so read-only and idle-open transactions no longer delay a controlled promotion; (b) add error-log notes when the switchover begins waiting and when runningTransactionsTimeout is reached and forces disconnects, optionally exposing the blocking transactions via Performance Schema and/or INNODB_TRX.
How to repeat:
1-) Create a 3-node single-primary InnoDB Cluster (tested on 8.0.46 and 8.4.10).
2-) On the current PRIMARY, run BEGIN; and leave it idle (or BEGIN; SELECT 1; for the read-only case). Confirm it's invisible: SELECT COUNT(*) FROM information_schema.INNODB_TRX; → 0.
3-) From MySQL Shell, time the switch: dba.getCluster().setPrimaryInstance('node2:3306', { runningTransactionsTimeout: 50 })
4-) Observed: the call blocks ~50 s, then completes; the error log shows Primary election change and A new primary … elected exactly 50 s apart, with no note explaining the wait or that the timeout was reached. N=15 / N=20 make the stall 15 s / 20 s (it tracks the timeout).
5-) Control: with no open transaction, the identical switch completes in ~1 s.
Expected: a switch blocked only by a read-only/idle transaction should complete promptly (as in the control), and any real wait or timeout-forced disconnect should be visible in the error log and runtime views.
Suggested fix:
* Gate the switchover wait on whether a transaction can be lost (modified rows / holds locks / assigned GTID), rather than treating any open session as a running transaction.
* In Transaction_monitor_thread::transaction_thread_handle() (plugin/group_replication/src/group_actions/group_actions_transaction_controller.cc), emit an INFORMATION_LEVEL note when waiting starts on N blocking transactions and when the timeout is reached and connections are closed (the branch calling mysql_close_connection_of_binloggable_transaction_not_reached_commit->close()).
* Optionally surface the blocking transactions/threads so setPrimaryInstance can report what is holding the switch.
Committed transactions are unaffected; the durability tradeoff for real uncommitted writes at the timeout boundary is preserved. Change can default on or be gated behind an option for compatibility.
Description: When promoting a new primary in an InnoDB Cluster — Cluster.setPrimaryInstance(target, {runningTransactionsTimeout: N}), i.e. the server UDF group_replication_set_as_primary() — the primary election waits up to N seconds for in-flight transactions on the current primary to complete. Two related shortcomings make this harder to operate than it should be: 1-) Over-restriction. A transaction that is read-only or merely open-and-idle — a bare BEGIN with no statements, or an uncommitted SELECT — is treated as a "running transaction" and stalls the whole election for the full timeout, even though it cannot be lost by a switchover. These transactions do not appear in information_schema.INNODB_TRX (COUNT(*) = 0), so they are invisible for diagnosis. 2-) No observability. Even at log_error_verbosity = 3, a switch that had to wait (or that was forced through when the timeout expired) is indistinguishable in the error log from an instant, clean switch — the same message sequence is written; only the time gap between MY-013214 (Starting … Primary election change) and MY-011507 (A new primary … elected) differs. When the timeout is reached, the monitor thread disconnects binloggable transactions that have not committed (client sees ERROR 2013 Lost connection, transaction is rolled back), but nothing is logged to indicate the timeout was reached or that connections were closed. Requested feature: (a) restrict the switchover wait to transactions that can actually be lost (modified data / hold record locks / assigned a GTID) so read-only and idle-open transactions no longer delay a controlled promotion; (b) add error-log notes when the switchover begins waiting and when runningTransactionsTimeout is reached and forces disconnects, optionally exposing the blocking transactions via Performance Schema and/or INNODB_TRX. How to repeat: 1-) Create a 3-node single-primary InnoDB Cluster (tested on 8.0.46 and 8.4.10). 2-) On the current PRIMARY, run BEGIN; and leave it idle (or BEGIN; SELECT 1; for the read-only case). Confirm it's invisible: SELECT COUNT(*) FROM information_schema.INNODB_TRX; → 0. 3-) From MySQL Shell, time the switch: dba.getCluster().setPrimaryInstance('node2:3306', { runningTransactionsTimeout: 50 }) 4-) Observed: the call blocks ~50 s, then completes; the error log shows Primary election change and A new primary … elected exactly 50 s apart, with no note explaining the wait or that the timeout was reached. N=15 / N=20 make the stall 15 s / 20 s (it tracks the timeout). 5-) Control: with no open transaction, the identical switch completes in ~1 s. Expected: a switch blocked only by a read-only/idle transaction should complete promptly (as in the control), and any real wait or timeout-forced disconnect should be visible in the error log and runtime views. Suggested fix: * Gate the switchover wait on whether a transaction can be lost (modified rows / holds locks / assigned GTID), rather than treating any open session as a running transaction. * In Transaction_monitor_thread::transaction_thread_handle() (plugin/group_replication/src/group_actions/group_actions_transaction_controller.cc), emit an INFORMATION_LEVEL note when waiting starts on N blocking transactions and when the timeout is reached and connections are closed (the branch calling mysql_close_connection_of_binloggable_transaction_not_reached_commit->close()). * Optionally surface the blocking transactions/threads so setPrimaryInstance can report what is holding the switch. Committed transactions are unaffected; the durability tradeoff for real uncommitted writes at the timeout boundary is preserved. Change can default on or be gated behind an option for compatibility.