MySQL Bugs: #95552: mysql innodb cluster shouldnt receive new transaction while change master R/W

Bug #95552	mysql innodb cluster shouldnt receive new transaction while change master R/W
Submitted:	28 May 2019 14:58	Modified:	28 Oct 2021 7:29
Reporter:	Chương Lê	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Shell AdminAPI InnoDB Cluster / ReplicaSet	Severity:	S4 (Feature request)
Version:	8.0.16	OS:	Ubuntu
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
Hi, I'm not sure this is a bug or feature request, I think it a bug (help me change Severity if it a bug).

Consider following senario, I have a 3 machines innodb cluster, single primary R/W.

Let do the following:

1. open mysqlsh (1)
2. open normal mysql client (2)
3. open a transaction on (2) (BEGIN; ...do not COMMIT...)
4. go to mysqlsh (1), setPrimaryInstance to another instance
5. at this time, mysqlsh (1) will hang indefinitely until we finish transaction on (2) (which is very good)
6. BUT, when we open another mysql client (3) and open new transaction (BEGIN), mysql client (3) accept to BEGIN (which is wrong - I think)

Why, let continue ?

7. when transaction on (2) either cancel/commit, setPrimaryInstance effect immediately, leaving transaction on (3) sure fail.

I *THINK* we shouldn't allowed to open new transaction (BEGIN) while we run setPrimaryInstance

How to repeat:
It not much related to code, basically

1. BEGIN; (and do not commit/close) on first mysql client
2. setPrimaryInstance on mysqlsh (it should hang)
3. open new mysql client, try BEGIN

It should not be allowed to BEGIN on new mysql client, because when old transaction commit/close, setPrimaryInstance will swap master

Later transaction will sure fail.

Suggested fix:
Don't allow open new transaction via BEGIN while in setPrimaryInstance period

P/s: By *sure fail*, I mean it will fail when 2nd transaction aren't commit/cancel before setPrimaryInstance taken effect

Hi,

I'm testing on latest 8.0.26 and I'm not able to reproduce a problematic behavior.

What I see in the report does not look like a bug. You can have 

connection 1: begin ............................... commit 
connection 2:         begin ......... commit 
connection 3:         begin .. commit
connection 4:         begin ................ commit
connection 5: ....................... begin .. commit

without a problem as long as they operate on different data. You need to lock some data in one transaction that is needed by other transaction to actually have a "hang indefinitely until we"

thanks

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".