Bug #118188 mysqlrouter rw socket Unexpected Error When kill MGR Primary node
Submitted: 14 May 6:34 Modified: 19 May 11:55
Reporter: zhenxing yu Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Router Severity:S3 (Non-critical)
Version:8.0.26 OS:CentOS (CentOS Linux release 7.5.1804 (Core))
Assigned to: MySQL Verification Team CPU Architecture:x86 (AMD EPYC 9554 64-Core Processor)
Tags: mysqlrouter, mysqlrouter socket

[14 May 6:34] zhenxing yu
Description:
I have a MySQL 8.0.26 InnoDB Cluster running in single-primary mode. One day, the primary node's disk filled up unexpectedly, triggering a primary-secondary switchover. Although MGR successfully completed the switchover, I encountered an issue where I couldn't log in via the RW socket locally through the router. The RO socket works fine, but the RW socket thread unexpectedly exited.

## MGR Status
select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 44c08813-3070-11f0-884c-02000aba3c15 | 10.186.60.21 |        3316 | ONLINE       | SECONDARY   | 8.0.26         |
| group_replication_applier | 48612ebb-3070-11f0-9896-02000aba3c1d | 10.186.60.29 |        3316 | ONLINE       | PRIMARY     | 8.0.26         |
| group_replication_applier | 4d23ce4a-3070-11f0-a652-02000aba3c19 | 10.186.60.25 |        3316 | ONLINE       | SECONDARY   | 8.0.26         |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+

## mysqlrouter RW & RO sock
[root@localhost ~]# ss -an|grep mysql|grep router|grep sock
u_str  LISTEN     0      128    /home/mysql/myrouter/mysqlro.sock 919278                * 0
u_str  LISTEN     0      128    /home/mysql/myrouter/mysqlxro.sock 920558                * 0
u_str  LISTEN     0      128    /home/mysql/myrouter/mysql.sock 926023                * 0
u_str  LISTEN     0      128    /home/mysql/myrouter/mysqlx.sock 923913                * 0

## RW connect
/usr/local/mysql/base/8.0.26/bin/mysql -S /home/mysql/myrouter/mysql.sock  -p123456 -e "select @@server_uuid,@@server_id,@@super_read_only,@@read_only"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------------------------+-------------+-------------------+-------------+
| @@server_uuid                        | @@server_id | @@super_read_only | @@read_only |
+--------------------------------------+-------------+-------------------+-------------+
| 48612ebb-3070-11f0-9896-02000aba3c1d |     8026422 |                 0 |           0 |
+--------------------------------------+-------------+-------------------+-------------+

## RO connect
[root@localhost ~]# /usr/local/mysql/base/8.0.26/bin/mysql -S /home/mysql/myrouter/mysqlro.sock  -p123456 -e "select @@server_uuid,@@server_id,@@super_read_only,@@read_only"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------------------------+-------------+-------------------+-------------+
| @@server_uuid                        | @@server_id | @@super_read_only | @@read_only |
+--------------------------------------+-------------+-------------------+-------------+
| 4d23ce4a-3070-11f0-a652-02000aba3c19 |     8026866 |                 1 |           1 |
+--------------------------------------+-------------+-------------------+-------------+
[root@localhost ~]# /usr/local/mysql/base/8.0.26/bin/mysql -S /home/mysql/myrouter/mysqlro.sock  -p123456 -e "select @@server_uuid,@@server_id,@@super_read_only,@@read_only"
+--------------------------------------+-------------+-------------------+-------------+
| @@server_uuid                        | @@server_id | @@super_read_only | @@read_only |
+--------------------------------------+-------------+-------------------+-------------+
| 44c08813-3070-11f0-884c-02000aba3c15 |     8026283 |                 1 |           1 |
+--------------------------------------+-------------+-------------------+-------------+
[root@localhost ~]# /usr/local/mysql/base/8.0.26/bin/mysql -S /home/mysql/myrouter/mysqlro.sock  -p123456 -e "select @@server_uuid,@@server_id,@@super_read_only,@@read_only"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------------------------+-------------+-------------------+-------------+
| @@server_uuid                        | @@server_id | @@super_read_only | @@read_only |
+--------------------------------------+-------------+-------------------+-------------+
| 4d23ce4a-3070-11f0-a652-02000aba3c19 |     8026866 |                 1 |           1 |
+--------------------------------------+-------------+-------------------+-------------+
[root@localhost ~]# /usr/local/mysql/base/8.0.26/bin/mysql -S /home/mysql/myrouter/mysqlro.sock  -p123456 -e "select @@server_uuid,@@server_id,@@super_read_only,@@read_only"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------------------------+-------------+-------------------+-------------+
| @@server_uuid                        | @@server_id | @@super_read_only | @@read_only |
+--------------------------------------+-------------+-------------------+-------------+
| 44c08813-3070-11f0-884c-02000aba3c15 |     8026283 |                 1 |           1 |
+--------------------------------------+-------------+-------------------+-------------+

## KILL MGR Primary node
[root@localhost ~]# ps -ef|grep mysqld
[root@localhost ~]# kill -9 626

## Use RW socket Login Failed
[root@localhost ~]# /usr/local/mysql/base/8.0.26/bin/mysql -S /home/mysql/myrouter/mysql.sock  -p123456 -e "select @@server_uuid,@@server_id,@@super_read_only,@@read_only"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/home/mysql/myrouter/mysql.sock' (111)

## ## 10.186.60.29,RW socket Disappeared
[root@localhost ~]# ss -an|grep mysql|grep router|grep sock
u_str  LISTEN     0      128    /home/mysql/myrouter/mysqlro.sock 1007484               * 0
u_str  LISTEN     0      128    /home/mysql/myrouter/mysqlxro.sock 1015509               * 0

## 10.186.60.21,RW socket Disappeared
[root@localhost myrouter]# ss -an|grep mysql|grep router|grep sock
u_str  LISTEN     0      128    /home/mysql/myrouter/mysqlro.sock 1007484               * 0
u_str  LISTEN     0      128    /home/mysql/myrouter/mysqlxro.sock 1015509               * 0

## 10.186.60.25,RW socket Disappeared
[root@localhost ~]# ss -an|grep mysql|grep router|grep sock
u_str  LISTEN     0      128    /home/mysql/myrouter/mysqlro.sock 919278                * 0
u_str  LISTEN     0      128    /home/mysql/myrouter/mysqlxro.sock 920558                * 0

## RW Process PORT Running
[root@localhost ~]# netstat -lntp|grep mysqlrouter
tcp        0      0 0.0.0.0:8443            0.0.0.0:*               LISTEN      27318/mysqlrouter
tcp        0      0 127.0.0.1:6446          0.0.0.0:*               LISTEN      27318/mysqlrouter
tcp        0      0 127.0.0.1:6447          0.0.0.0:*               LISTEN      27318/mysqlrouter
tcp        0      0 127.0.0.1:6448          0.0.0.0:*               LISTEN      27318/mysqlrouter
tcp        0      0 127.0.0.1:6449          0.0.0.0:*               LISTEN      27318/mysqlrouter

## Use 127.0.0.1 Login successful. 
[root@localhost ~]# /usr/local/mysql/base/8.0.26/bin/mysql -h127.0.0.1 -uroot -P6446 -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7512
Server version: 8.0.26 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

How to repeat:
1. Deploy 8.0.26 InnoDB Cluster With single-primary mode
2. Kill Primary node
3. use router RW socket mode connect MySQL(ERROR)

Suggested fix:
Evaluate whether this issue qualifies as a bug.
[14 May 6:35] zhenxing yu
mysqrouter log file

Attachment: mysqlrouter.log (application/octet-stream, text), 1.05 MiB.

[14 May 6:36] zhenxing yu
mysqlrouter config file

Attachment: mysqlrouter.conf (application/octet-stream, text), 1.99 KiB.

[14 May 6:58] zhenxing yu
mysqlrouter 8.0.35 release note

Attachment: 20250514-145726.jpeg (image/jpeg, text), 780.54 KiB.

[19 May 11:55] MySQL Verification Team
Hi,

I cannot reproduce this with modern MySQL Server + Router

(You already notice that there is a fix in router 8.0.35)

Please upgrade and retry. Note that there are still issues, unrecoverable ones, when MySQL datadir storage system is full, please use monitoring tool to never allow disk to be full.