MySQL Bugs: #114594: Read only issue while switching the primary node in innodb cluster

Bug #114594	Read only issue while switching the primary node in innodb cluster
Submitted:	9 Apr 2024 9:48	Modified:	9 Jul 2024 22:19
Reporter:	Arun Sahu	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Router	Severity:	S3 (Non-critical)
Version:	8.2.0	OS:	CentOS
Assigned to:		CPU Architecture:	ARM
Tags:	mysql router 8.2.0

Description:
Hello Team,

First of all we are using MySQL 8.2.0 in our service and using MySQL Router 8.2.0 for load balancing to get read/write split benefit, but while switching the primary node to another node the existing Write connection is giving read only error in code.

please suggest any solution to overcome this issue.

 

How to repeat:

Please setup a Innodb cluster with 3 nodes with router :

1. Make a connection using mysql router read/write split port 6450 and Make a table and insert some data on the same table.
2. Open second connection and using admin access and make the another node secondary as primary.
3. After switch the primary node,insert some more data with old connection. 
4. It will show the error "Super_Read_only".

Note: I am using default configuration of router for read/write split configuration details:

[routing:bootstrap_rw_split]
bind_address=0.0.0.0
bind_port=6450
socket=/tmp/mysqlrouter/mysqlsplit.sock
destinations=metadata-cache://local/?role=PRIMARY_AND_SECONDARY
routing_strategy=round-robin
protocol=classic
connection_sharing=1
client_ssl_mode=PREFERRED
server_ssl_mode=PREFERRED
access_mode=auto

We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

For details on getting support for MySQL products see http://www.mysql.com/support/
You can also check our forums (free) at http://forums.mysql.com/

Thank you for your interest in MySQL.

Hi Arun, we verified your report. Thanks

Posted by developer:
 
Fixed as of the upcoming MySQL Router 8.4.2 and 9.1.0 releases, and here's the proposed changelog entry from the documentation team:

With Read-Write splitting enabled and after the PRIMARY switched its role
to SECONDARY, all write statements would fail with an error indicating
that the MySQL server is running in super_read_only mode. Now it drops the
client connection like it does without Read-Write splitting.

Thank you for the bug report.

Posted by developer:
 
Bumped fixed version to 8.4.3.