Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

ProxySQL support - Wrong RW/RO HG switching #370

Closed
xo4yecTb opened this issue Jul 28, 2021 · 2 comments
Closed

ProxySQL support - Wrong RW/RO HG switching #370

xo4yecTb opened this issue Jul 28, 2021 · 2 comments

Comments

@xo4yecTb
Copy link

xo4yecTb commented Jul 28, 2021 •
edited
Loading

Good evening everyone!

# replication-manager-cli  version
Replication Manager v2.2.4 for MariaDB 10.x and MySQL 5.7 Series
Full Version:  v2.2.4
Build Time:  2021-07-21T17:21:45+0000

# proxysql --version
ProxySQL version 1.4.16-percona-1.1, codename Truls

Writer HG - 1
Reader HG - 2

Master Host - 10.0.0.2
Slave Host - 10.0.0.3

Trying to use switchover:

Before switchover:

mysql> SELECT * FROM runtime_mysql_servers;
+--------------+----------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status  | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | 10.0.0.2 | 3307 | ONLINE  | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 10.0.0.5 | 3307 | SHUNNED | 1      | 0           | 100             | 0                   | 0       | 0              |         |
| 2            | 10.0.0.3 | 3307 | ONLINE  | 1      | 0           | 100             | 0                   | 0       | 0              |         |
+--------------+----------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.01 sec)

After swithover when 10.0.0.3 becomes the Master and previous Master 10.0.0.2 is gone from runtime_mysql_servers, but now its a Slave and must be in reader HG:

mysql> SELECT * FROM runtime_mysql_servers;
+--------------+----------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status  | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | 10.0.0.3 | 3307 | ONLINE  | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 10.0.0.5 | 3307 | SHUNNED | 1      | 0           | 100             | 0                   | 0       | 0              |         |
| 2            | 10.0.0.3 | 3307 | ONLINE  | 1      | 0           | 100             | 0                   | 0       | 0              |         |
+--------------+----------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)

Next step, running swithover once again - and its worked correct

mysql> SELECT * FROM runtime_mysql_servers;
+--------------+----------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status  | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | 10.0.0.2 | 3307 | ONLINE  | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 10.0.0.5 | 3307 | SHUNNED | 1      | 0           | 100             | 0                   | 0       | 0              |         |
| 2            | 10.0.0.3 | 3307 | ONLINE  | 1      | 0           | 100             | 0                   | 0       | 0              |         |
+--------------+----------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.01 sec)

Proxysql conf for replication manager:

# cat /etc/replication-manager/cluster.d/cluster.toml | grep proxysql
proxysql = "true"
proxysql-servers = "10.0.0.2,10.0.0.3"
proxysql-port = "3306"
proxysql-admin-port = "6032"
proxysql-writer-hostgroup = 1
proxysql-reader-hostgroup = 2
proxysql-user = "cluster_admin"
proxysql-password = "***************************"
proxysql-bootstrap = false
proxysql-copy-grants = false
proxysql-bootstrap-users = false
proxysql-master-is-reader = true
proxysql-multiplexing = true

Proxysql log during Swithover manipulations:

2021-07-28 20:37:04 [INFO] Received LOAD MYSQL SERVERS TO RUNTIME command
2021-07-28 20:37:04 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 1 , address: 10.0.0.2 , port: 3307 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
HID: 2 , address: 10.0.0.3 , port: 3307 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
HID: 2 , address: 10.0.0.5 , port: 3307 , weight: 1 , status: SHUNNED , max_connections: 100 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
2021-07-28 20:37:04 [INFO] Dumping mysql_servers
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+
| hostgroup_id | hostname | port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | mem_pointer     |
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+
| 1            | 10.0.0.2 | 3307 | 1      | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 140420567207040 |
| 2            | 10.0.0.5 | 3307 | 1      | 1      | 0           | 100             | 0                   | 0       | 0              |         | 140420627776000 |
| 2            | 10.0.0.3 | 3307 | 1      | 0      | 0           | 100             | 0                   | 0       | 0              |         | 140420627776896 |
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+
2021-07-28 20:37:04 [INFO] Dumping mysql_servers_incoming
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 2            | 10.0.0.3 | 3307 | 1      | 0      | 0           | 100             | 0                   | 0       | 0              |         |
| 2            | 10.0.0.5 | 3307 | 1      | 0      | 0           | 100             | 0                   | 0       | 0              |         |
| 1            | 10.0.0.3 | 3307 | 1      | 0      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2021-07-28 20:37:04 MySQL_HostGroups_Manager.cpp:610:commit(): [WARNING] Removed server at address 140420567207040, hostgroup 1, address 10.0.0.2 port 3307. Setting status OFFLINE HARD and immediately dropping all free connections. Used connections will be dropped when trying to use them
2021-07-28 20:37:04 [INFO] New mysql_replication_hostgroups table
2021-07-28 20:37:04 [INFO] New mysql_group_replication_hostgroups table
2021-07-28 20:37:04 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 1 , address: 10.0.0.2 , port: 3307 , weight: 1 , status: OFFLINE_HARD , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
HID: 1 , address: 10.0.0.3 , port: 3307 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
HID: 2 , address: 10.0.0.3 , port: 3307 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
HID: 2 , address: 10.0.0.5 , port: 3307 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
2021-07-28 20:37:04 [INFO] Dumping mysql_servers
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+
| hostgroup_id | hostname | port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | mem_pointer     |
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+
| 1            | 10.0.0.2 | 3307 | 1      | 3      | 0           | 1000            | 0                   | 0       | 0              |         | 140420567207040 |
| 2            | 10.0.0.5 | 3307 | 1      | 0      | 0           | 100             | 0                   | 0       | 0              |         | 140420627776000 |
| 2            | 10.0.0.3 | 3307 | 1      | 0      | 0           | 100             | 0                   | 0       | 0              |         | 140420627776896 |
| 1            | 10.0.0.3 | 3307 | 1      | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 140420567207168 |
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+
2021-07-28 20:37:09 MySQL_Monitor.cpp:1437:monitor_ping(): [ERROR] Server 10.0.0.5:3307 missed 3 heartbeats, shunning it and killing all the connections. Disabling other checks until the node comes back online.
2021-07-28 20:37:38 [INFO] Received LOAD MYSQL SERVERS TO RUNTIME command
2021-07-28 20:37:38 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 1 , address: 10.0.0.3 , port: 3307 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
HID: 2 , address: 10.0.0.3 , port: 3307 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
HID: 2 , address: 10.0.0.5 , port: 3307 , weight: 1 , status: SHUNNED , max_connections: 100 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
2021-07-28 20:37:38 [INFO] Dumping mysql_servers
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+
| hostgroup_id | hostname | port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | mem_pointer     |
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+
| 1            | 10.0.0.3 | 3307 | 1      | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 140420567207168 |
| 2            | 10.0.0.5 | 3307 | 1      | 1      | 0           | 100             | 0                   | 0       | 0              |         | 140420627776000 |
| 2            | 10.0.0.3 | 3307 | 1      | 0      | 0           | 100             | 0                   | 0       | 0              |         | 140420627776896 |
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+
2021-07-28 20:37:38 [INFO] Dumping mysql_servers_incoming
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 2            | 10.0.0.3 | 3307 | 1      | 0      | 0           | 100             | 0                   | 0       | 0              |         |
| 2            | 10.0.0.5 | 3307 | 1      | 0      | 0           | 100             | 0                   | 0       | 0              |         |
| 1            | 10.0.0.2 | 3307 | 1      | 0      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2021-07-28 20:37:38 MySQL_HostGroups_Manager.cpp:610:commit(): [WARNING] Removed server at address 140420567207168, hostgroup 1, address 10.0.0.3 port 3307. Setting status OFFLINE HARD and immediately dropping all free connections. Used connections will be dropped when trying to use them
2021-07-28 20:37:38 [INFO] New mysql_replication_hostgroups table
2021-07-28 20:37:38 [INFO] New mysql_group_replication_hostgroups table
2021-07-28 20:37:38 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 1 , address: 10.0.0.3 , port: 3307 , weight: 1 , status: OFFLINE_HARD , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
HID: 1 , address: 10.0.0.2 , port: 3307 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
HID: 2 , address: 10.0.0.3 , port: 3307 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
HID: 2 , address: 10.0.0.5 , port: 3307 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
2021-07-28 20:37:38 [INFO] Dumping mysql_servers
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+
| hostgroup_id | hostname | port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | mem_pointer     |
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+
| 1            | 10.0.0.3 | 3307 | 1      | 3      | 0           | 1000            | 0                   | 0       | 0              |         | 140420567207168 |
| 2            | 10.0.0.5 | 3307 | 1      | 0      | 0           | 100             | 0                   | 0       | 0              |         | 140420627776000 |
| 2            | 10.0.0.3 | 3307 | 1      | 0      | 0           | 100             | 0                   | 0       | 0              |         | 140420627776896 |
| 1            | 10.0.0.2 | 3307 | 1      | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 140420567207296 |
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+
2021-07-28 20:37:39 MySQL_Monitor.cpp:1437:monitor_ping(): [ERROR] Server 10.0.0.5:3307 missed 3 heartbeats, shunning it and killing all the connections. Disabling other checks until the node comes back online.
@svaroqui
Copy link
Collaborator

HI, sorry for such late answer, would you please re test from later minor releases proxysql should not be touch at all when proxysql-bootstrap = false and when it's true should do the job as well

@svaroqui
Copy link
Collaborator

Please reopen if not fixed

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@svaroqui @xo4yecTb