search

sysown / proxysql

High-performance MySQL proxy with a GPL license.
http://www.proxysql.com
GNU General Public License v3.0
5.97k stars 973 forks source link

ProxySQL v2.0.15 in RW Split not promoting Backup Writer on Primary Writer Failure in Multi-Master Galera Cluster #3245

Closed rkconsulting closed 3 years ago

rkconsulting commented 3 years ago

Hi,

we have a 3-server MariaDB v10.3.27 Galera cluster running in multi-master mode. On our application servers we're running ProxySQL in a simply Query-Match-based RW split. Here is the configuration of our mysql_servers and mysql_galera_hostgroups tables:

mysql> select * from mysql_servers;
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname     | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | 10.222.12.29 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | c441db1 |
| 2            | 10.222.12.30 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | c441db3 |
| 2            | 10.222.12.22 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | c441db2 |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)

mysql> select * from mysql_galera_hostgroups;
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| writer_hostgroup | backup_writer_hostgroup | reader_hostgroup | offline_hostgroup | active | max_writers | writer_is_also_reader | max_transactions_behind | comment |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| 1                | 2                       | 3                | 4                 | 1      | 1           | 1                     | 200                     | NULL    |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
1 row in set (0.00 sec)

So nothing fancy. In the past, this used to work in a way that, if for some reason the current active writer fails (could be any of the three), one of the backup writers would instantly be promoted to the writer HG. The runtime_mysql_servers table would at any time only contain one active server in the writer HG and the other two servers in the backup writer HG. We recently updated our ProxySQL v2.0.7 to the current v2.0.15. Now we are seeing all three servers in the writer HG, with the two inactive ones marked as being SHUNNED. When the writer now fails, ProxySQL pretty much gives up and promotes none of the backup writers to write to take over the DB write load. What are we doing wrong and how can we get back to some form of high availability with ProxySQL?

I'm suspecting this has to do with the variable evaluation switch in v2.0.11, we did not do any kind of migration of our database and noticed that ProxySQL threw out some of those now unsupported variables during the update. What is the best way of migrating pre-v2.0.11 configurations to something like the current version? Is there some way to throw out all of ProxySQL's variable settings to properly utilize the new handling of variables in favor of DB configuration? Is there an update guide?

Thanks for your help!

JavierJF commented 3 years ago

Hi,

this could be a misconfiguration, could you please share the information required by the issue template for further inspection? In this case it would be:

And if you could additionally share the the output of:

SELECT * FROM global_variables;

The information could be also handy, thank you.

rkconsulting commented 3 years ago

Hello,

thanks for your reply. If this is a case of misconfiguration, then that certainly wasnt the case under v2.0.7, because this problem occurred only once we upgraded to v2.0.15 without changing any parts of the configuration.

ProxySQL log from startup to error condition

```` 2021-01-17 01:42:48 [INFO] ProxySQL version 2.1.0-544-g17a4b4a7 2021-01-17 01:42:48 [INFO] Detected OS: Linux c441i1 4.19.86-041986-generic #201911240340 SMP Sun Nov 24 08:44:42 UTC 2019 x86_64 2021-01-17 01:42:48 [INFO] ProxySQL SHA1 checksum: f1a273815d88fe5f05c275cb53d1447dd88f9a6f 2021-01-17 01:42:48 [INFO] Starting ProxySQL 2021-01-17 01:42:48 [INFO] Sucessfully started 2021-01-17 01:42:48 [INFO] Angel process started ProxySQL process 10159 2021-01-17 01:42:48 [INFO] Loaded built-in SQLite3 Standard ProxySQL MySQL Logger rev. 2.0.0714 -- MySQL_Logger.cpp -- Mon Jan 4 21:22:57 2021 Standard ProxySQL Cluster rev. 0.4.0906 -- ProxySQL_Cluster.cpp -- Mon Jan 4 21:22:57 2021 Standard ProxySQL Statistics rev. 1.4.1027 -- ProxySQL_Statistics.cpp -- Mon Jan 4 21:22:57 2021 Standard ProxySQL HTTP Server Handler rev. 1.4.1031 -- ProxySQL_HTTP_Server.cpp -- Mon Jan 4 21:22:57 2021 Standard ProxySQL Admin rev. 2.0.6.0805 -- ProxySQL_Admin.cpp -- Tue Jan 12 19:34:07 2021 2021-01-17 01:42:48 [INFO] ProxySQL SHA1 checksum: f1a273815d88fe5f05c275cb53d1447dd88f9a6f Standard MySQL Threads Handler rev. 0.2.0902 -- MySQL_Thread.cpp -- Tue Jan 12 19:34:07 2021 Standard MySQL Authentication rev. 0.2.0902 -- MySQL_Authentication.cpp -- Mon Jan 4 21:22:57 2021 2021-01-17 01:42:48 [INFO] Dumping mysql_servers_incoming +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 1 | 10.222.12.29 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db1 | | 2 | 10.222.12.30 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db3 | | 2 | 10.222.12.22 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db2 | +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 2021-01-17 01:42:48 [INFO] Dumping mysql_servers LEFT JOIN mysql_servers_incoming +-------------+--------------+----------+------+ | mem_pointer | hostgroup_id | hostname | port | +-------------+--------------+----------+------+ +-------------+--------------+----------+------+ 2021-01-17 01:42:48 [INFO] Dumping mysql_servers JOIN mysql_servers_incoming +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | mem_pointer | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 1 | 10.222.12.29 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db1 | 0 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db1 | | 2 | 10.222.12.30 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db3 | 0 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db3 | | 2 | 10.222.12.22 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db2 | 0 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db2 | +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 2021-01-17 01:42:48 [INFO] Creating new server in HG 1 : 10.222.12.29:3306 , gtid_port=0, weight=1, status=0 2021-01-17 01:42:48 [INFO] Creating new server in HG 2 : 10.222.12.30:3306 , gtid_port=0, weight=1, status=0 2021-01-17 01:42:48 [INFO] Creating new server in HG 2 : 10.222.12.22:3306 , gtid_port=0, weight=1, status=0 2021-01-17 01:42:48 [INFO] New mysql_group_replication_hostgroups table 2021-01-17 01:42:48 [INFO] New mysql_galera_hostgroups table 2021-01-17 01:42:48 [INFO] Loading Galera info for (1,2,3,4,on,1,1,200,"(null)") 2021-01-17 01:42:48 [INFO] New mysql_aws_aurora_hostgroups table 2021-01-17 01:42:48 [INFO] Checksum for table mysql_servers is 10552302777541057860 2021-01-17 01:42:48 [INFO] Checksum for table mysql_galera_hostgroups is 10460573133796343126 2021-01-17 01:42:48 [INFO] MySQL_HostGroups_Manager::commit() locked for 2ms Standard Query Processor rev. 2.0.6.0805 -- Query_Processor.cpp -- Mon Jan 4 21:22:57 2021 In memory Standard Query Cache (SQC) rev. 1.2.0905 -- Query_Cache.cpp -- Mon Jan 4 21:22:57 2021 Standard MySQL Monitor (StdMyMon) rev. 2.0.1226 -- MySQL_Monitor.cpp -- Fri Jan 8 21:37:02 2021 2021-01-17 01:42:49 MySQL_HostGroups_Manager.cpp:5415:update_galera_set_writer(): [WARNING] Galera: setting host 10.222.12.29:3306 as writer 2021-01-17 01:42:49 [INFO] Galera: Node status changed by ProxySQL, dumping all galera nodes status: +--------------+------+------------------+------------+-------------------+-----------+------------------------+-------------------+--------------+----------------------+---------------------------------+----------------+-------+ | hostname | port | start_time | check_time | primary_partition | read_only | wsrep_local_recv_queue | wsrep_local_state | wsrep_desync | wsrep_reject_queries | wsrep_sst_donor_rejects_queries | pxc_maint_mode | error | +--------------+------+------------------+------------+-------------------+-----------+------------------------+-------------------+--------------+----------------------+---------------------------------+----------------+-------+ | 10.222.12.22 | 3306 | 1610844169070501 | 2655 | 1 | 0 | 0 | 4 | 0 | 0 | 0 | 0 | | | 10.222.12.29 | 3306 | 1610844169069415 | 3505 | 1 | 0 | 0 | 4 | 0 | 0 | 0 | 0 | | +--------------+------+------------------+------------+-------------------+-----------+------------------------+-------------------+--------------+----------------------+---------------------------------+----------------+-------+ 2021-01-17 01:42:49 [INFO] Dumping current MySQL Servers structures for hostgroup ALL HID: 1 , address: 10.222.12.29 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db1 HID: 2 , address: 10.222.12.30 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db3 HID: 2 , address: 10.222.12.22 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db2 2021-01-17 01:42:49 [INFO] Dumping mysql_servers: ALL +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ | hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment | mem_pointer | +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ | 1 | 10.222.12.29 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db1 | 139749330340160 | | 2 | 10.222.12.22 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db2 | 139749330340320 | | 2 | 10.222.12.30 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db3 | 139749330339840 | +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ 2021-01-17 01:42:49 [INFO] Dumping mysql_servers_incoming +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 1 | 10.222.12.29 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db1 | | 2 | 10.222.12.22 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db2 | | 2 | 10.222.12.30 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db3 | | 3 | 10.222.12.29 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db1 | +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 2021-01-17 01:42:49 [INFO] Dumping mysql_servers LEFT JOIN mysql_servers_incoming +-------------+--------------+----------+------+ | mem_pointer | hostgroup_id | hostname | port | +-------------+--------------+----------+------+ +-------------+--------------+----------+------+ 2021-01-17 01:42:49 [INFO] Dumping mysql_servers JOIN mysql_servers_incoming +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | mem_pointer | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 3 | 10.222.12.29 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db1 | 0 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db1 | +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 2021-01-17 01:42:49 [INFO] Creating new server in HG 3 : 10.222.12.29:3306 , gtid_port=0, weight=1, status=0 2021-01-17 01:42:49 [INFO] Dumping current MySQL Servers structures for hostgroup ALL HID: 1 , address: 10.222.12.29 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db1 HID: 2 , address: 10.222.12.30 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db3 HID: 2 , address: 10.222.12.22 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db2 HID: 3 , address: 10.222.12.29 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db1 2021-01-17 01:42:49 [INFO] Dumping mysql_servers: ALL +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ | hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment | mem_pointer | +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ | 1 | 10.222.12.29 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db1 | 139749330340160 | | 3 | 10.222.12.29 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db1 | 139749283319968 | | 2 | 10.222.12.22 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db2 | 139749330340320 | | 2 | 10.222.12.30 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db3 | 139749330339840 | +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ 2021-01-17 01:42:49 [INFO] Checksum for table mysql_servers is 14909621080884689671 2021-01-17 01:42:49 [INFO] Checksum for table mysql_galera_hostgroups is 10460573133796343126 2021-01-17 01:42:49 [INFO] MySQL_HostGroups_Manager::commit() locked for 4ms 2021-01-17 01:42:49 [INFO] Dumping current MySQL Servers structures for hostgroup 1 HID: 1 , address: 10.222.12.29 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db1 2021-01-17 01:42:49 [INFO] Dumping mysql_servers: HG 1 +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ | hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment | mem_pointer | +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ | 1 | 10.222.12.29 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db1 | 139749330340160 | +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ 2021-01-17 01:42:49 [INFO] Dumping current MySQL Servers structures for hostgroup 2 HID: 2 , address: 10.222.12.30 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db3 HID: 2 , address: 10.222.12.22 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db2 2021-01-17 01:42:49 [INFO] Dumping mysql_servers: HG 2 +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ | hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment | mem_pointer | +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ | 2 | 10.222.12.22 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db2 | 139749330340320 | | 2 | 10.222.12.30 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db3 | 139749330339840 | +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ 2021-01-17 01:42:49 [INFO] Dumping current MySQL Servers structures for hostgroup 3 HID: 3 , address: 10.222.12.29 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db1 2021-01-17 01:42:49 [INFO] Dumping mysql_servers: HG 3 +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ | hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment | mem_pointer | +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ | 3 | 10.222.12.29 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db1 | 139749283319968 | +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ 2021-01-17 01:42:49 [INFO] Dumping current MySQL Servers structures for hostgroup 4 2021-01-17 01:42:49 [INFO] Dumping mysql_servers: HG 4 +-----+----------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-------------+ | hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment | mem_pointer | +-----+----------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-------------+ +-----+----------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-------------+ 2021-01-17 01:42:49 MySQL_HostGroups_Manager.cpp:5415:update_galera_set_writer(): [WARNING] Galera: setting host 10.222.12.22:3306 as writer 2021-01-17 01:42:49 [INFO] Galera: Node status changed by ProxySQL, dumping all galera nodes status: +--------------+------+------------------+------------+-------------------+-----------+------------------------+-------------------+--------------+----------------------+---------------------------------+----------------+-------+ | hostname | port | start_time | check_time | primary_partition | read_only | wsrep_local_recv_queue | wsrep_local_state | wsrep_desync | wsrep_reject_queries | wsrep_sst_donor_rejects_queries | pxc_maint_mode | error | +--------------+------+------------------+------------+-------------------+-----------+------------------------+-------------------+--------------+----------------------+---------------------------------+----------------+-------+ | 10.222.12.22 | 3306 | 1610844169070501 | 2655 | 1 | 0 | 0 | 4 | 0 | 0 | 0 | 0 | | | 10.222.12.29 | 3306 | 1610844169069415 | 3505 | 1 | 0 | 0 | 4 | 0 | 0 | 0 | 0 | | +--------------+------+------------------+------------+-------------------+-----------+------------------------+-------------------+--------------+----------------------+---------------------------------+----------------+-------+ 2021-01-17 01:42:49 [INFO] Dumping current MySQL Servers structures for hostgroup ALL HID: 1 , address: 10.222.12.29 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db1 HID: 2 , address: 10.222.12.30 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db3 HID: 2 , address: 10.222.12.22 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db2 HID: 3 , address: 10.222.12.29 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db1 2021-01-17 01:42:49 [INFO] Dumping mysql_servers: ALL +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ | hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment | mem_pointer | +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ | 1 | 10.222.12.29 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db1 | 139749330340160 | | 3 | 10.222.12.29 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db1 | 139749283319968 | | 2 | 10.222.12.22 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db2 | 139749330340320 | | 2 | 10.222.12.30 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db3 | 139749330339840 | +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ 2021-01-17 01:42:49 [INFO] Dumping mysql_servers_incoming +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 1 | 10.222.12.29 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db1 | | 2 | 10.222.12.30 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db3 | | 1 | 10.222.12.22 | 3306 | 0 | 1 | 1 | 0 | 1000 | 0 | 0 | 0 | c441db2 | | 3 | 10.222.12.29 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db1 | | 3 | 10.222.12.22 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db2 | | 2 | 10.222.12.22 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db2 | +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 2021-01-17 01:42:49 [INFO] Dumping mysql_servers LEFT JOIN mysql_servers_incoming +-------------+--------------+----------+------+ | mem_pointer | hostgroup_id | hostname | port | +-------------+--------------+----------+------+ +-------------+--------------+----------+------+ 2021-01-17 01:42:49 [INFO] Dumping mysql_servers JOIN mysql_servers_incoming +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | mem_pointer | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 1 | 10.222.12.22 | 3306 | 0 | 1 | 1 | 0 | 1000 | 0 | 0 | 0 | c441db2 | 0 | 0 | 1 | 1 | 0 | 1000 | 0 | 0 | 0 | c441db2 | | 3 | 10.222.12.22 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db2 | 0 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db2 | +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 2021-01-17 01:42:49 [INFO] Creating new server in HG 1 : 10.222.12.22:3306 , gtid_port=0, weight=1, status=1 2021-01-17 01:42:49 [INFO] Creating new server in HG 3 : 10.222.12.22:3306 , gtid_port=0, weight=1, status=0 2021-01-17 01:42:49 [INFO] Dumping current MySQL Servers structures for hostgroup ALL HID: 1 , address: 10.222.12.29 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db1 HID: 1 , address: 10.222.12.22 , port: 3306 , gtid_port: 0 , weight: 1 , status: SHUNNED , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db2 HID: 2 , address: 10.222.12.30 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db3 HID: 2 , address: 10.222.12.22 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db2 HID: 3 , address: 10.222.12.29 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db1 HID: 3 , address: 10.222.12.22 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db2 2021-01-17 01:42:49 [INFO] Dumping mysql_servers: ALL +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ | hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment | mem_pointer | +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ | 1 | 10.222.12.29 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db1 | 139749330340160 | | 3 | 10.222.12.22 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db2 | 139749287555168 | | 3 | 10.222.12.29 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db1 | 139749283319968 | | 2 | 10.222.12.22 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db2 | 139749330340320 | | 2 | 10.222.12.30 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db3 | 139749330339840 | | 1 | 10.222.12.22 | 3306 | 0 | 1 | 1 | 0 | 1000 | 0 | 0 | 0 | c441db2 | 139749287555008 | +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ 2021-01-17 01:42:49 [INFO] Checksum for table mysql_servers is 2662414518896871143 2021-01-17 01:42:49 [INFO] Checksum for table mysql_galera_hostgroups is 10460573133796343126 2021-01-17 01:42:49 [INFO] MySQL_HostGroups_Manager::commit() locked for 4ms 2021-01-17 01:42:49 [INFO] Dumping current MySQL Servers structures for hostgroup 1 HID: 1 , address: 10.222.12.29 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db1 HID: 1 , address: 10.222.12.22 , port: 3306 , gtid_port: 0 , weight: 1 , status: SHUNNED , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db2 2021-01-17 01:42:49 [INFO] Dumping mysql_servers: HG 1 +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ | hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment | mem_pointer | +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ | 1 | 10.222.12.22 | 3306 | 0 | 1 | 1 | 0 | 1000 | 0 | 0 | 0 | c441db2 | 139749287555008 | | 1 | 10.222.12.29 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db1 | 139749330340160 | +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ 2021-01-17 01:42:49 [INFO] Dumping current MySQL Servers structures for hostgroup 2 HID: 2 , address: 10.222.12.30 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db3 HID: 2 , address: 10.222.12.22 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db2 2021-01-17 01:42:49 [INFO] Dumping mysql_servers: HG 2 +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ | hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment | mem_pointer | +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ | 2 | 10.222.12.22 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db2 | 139749330340320 | | 2 | 10.222.12.30 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db3 | 139749330339840 | +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ 2021-01-17 01:42:49 [INFO] Dumping current MySQL Servers structures for hostgroup 3 HID: 3 , address: 10.222.12.29 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db1 HID: 3 , address: 10.222.12.22 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db2 2021-01-17 01:42:49 [INFO] Dumping mysql_servers: HG 3 +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ | hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment | mem_pointer | +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ | 3 | 10.222.12.22 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db2 | 139749287555168 | | 3 | 10.222.12.29 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db1 | 139749283319968 | +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ 2021-01-17 01:42:49 [INFO] Dumping current MySQL Servers structures for hostgroup 4 2021-01-17 01:42:49 [INFO] Dumping mysql_servers: HG 4 +-----+----------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-------------+ | hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment | mem_pointer | +-----+----------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-------------+ +-----+----------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-------------+ 2021-01-17 01:42:49 MySQL_HostGroups_Manager.cpp:5415:update_galera_set_writer(): [WARNING] Galera: setting host 10.222.12.30:3306 as writer 2021-01-17 01:42:49 [INFO] Galera: Node status changed by ProxySQL, dumping all galera nodes status: +--------------+------+------------------+------------+-------------------+-----------+------------------------+-------------------+--------------+----------------------+---------------------------------+----------------+-------+ | hostname | port | start_time | check_time | primary_partition | read_only | wsrep_local_recv_queue | wsrep_local_state | wsrep_desync | wsrep_reject_queries | wsrep_sst_donor_rejects_queries | pxc_maint_mode | error | +--------------+------+------------------+------------+-------------------+-----------+------------------------+-------------------+--------------+----------------------+---------------------------------+----------------+-------+ | 10.222.12.22 | 3306 | 1610844169070501 | 2655 | 1 | 0 | 0 | 4 | 0 | 0 | 0 | 0 | | | 10.222.12.29 | 3306 | 1610844169069415 | 3505 | 1 | 0 | 0 | 4 | 0 | 0 | 0 | 0 | | | 10.222.12.30 | 3306 | 1610844169083234 | 2613 | 1 | 0 | 0 | 4 | 0 | 0 | 0 | 0 | | +--------------+------+------------------+------------+-------------------+-----------+------------------------+-------------------+--------------+----------------------+---------------------------------+----------------+-------+ 2021-01-17 01:42:49 [INFO] Dumping current MySQL Servers structures for hostgroup ALL HID: 1 , address: 10.222.12.29 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db1 HID: 1 , address: 10.222.12.22 , port: 3306 , gtid_port: 0 , weight: 1 , status: SHUNNED , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db2 HID: 2 , address: 10.222.12.30 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db3 HID: 2 , address: 10.222.12.22 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db2 HID: 3 , address: 10.222.12.29 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db1 HID: 3 , address: 10.222.12.22 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db2 2021-01-17 01:42:49 [INFO] Dumping mysql_servers: ALL +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ | hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment | mem_pointer | +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ | 1 | 10.222.12.29 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db1 | 139749330340160 | | 3 | 10.222.12.22 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db2 | 139749287555168 | | 3 | 10.222.12.29 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db1 | 139749283319968 | | 2 | 10.222.12.22 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db2 | 139749330340320 | | 2 | 10.222.12.30 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db3 | 139749330339840 | | 1 | 10.222.12.22 | 3306 | 0 | 1 | 1 | 0 | 1000 | 0 | 0 | 0 | c441db2 | 139749287555008 | +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ 2021-01-17 01:42:49 [INFO] Dumping mysql_servers_incoming +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 1 | 10.222.12.29 | 3306 | 0 | 1 | 1 | 0 | 1000 | 0 | 0 | 0 | c441db1 | | 1 | 10.222.12.22 | 3306 | 0 | 1 | 1 | 0 | 1000 | 0 | 0 | 0 | c441db2 | | 1 | 10.222.12.30 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db3 | | 2 | 10.222.12.22 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db2 | | 3 | 10.222.12.29 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db1 | | 3 | 10.222.12.22 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db2 | | 3 | 10.222.12.30 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db3 | | 2 | 10.222.12.29 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db1 | +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 2021-01-17 01:42:49 [INFO] Dumping mysql_servers LEFT JOIN mysql_servers_incoming +-----------------+--------------+--------------+------+ | mem_pointer | hostgroup_id | hostname | port | +-----------------+--------------+--------------+------+ | 139749330339840 | 2 | 10.222.12.30 | 3306 | +-----------------+--------------+--------------+------+ 2021-01-17 01:42:49 MySQL_HostGroups_Manager.cpp:1696:commit(): [WARNING] Removed server at address 139749330339840, hostgroup 2, address 10.222.12.30 port 3306. Setting status OFFLINE HARD and immediately dropping all free connections. Used connections will be dropped when trying to use them 2021-01-17 01:42:49 [INFO] Dumping mysql_servers JOIN mysql_servers_incoming +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | mem_pointer | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 1 | 10.222.12.29 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db1 | 139749330340160 | 0 | 1 | 1 | 0 | 1000 | 0 | 0 | 0 | c441db1 | | 1 | 10.222.12.30 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db3 | 0 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db3 | | 3 | 10.222.12.30 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db3 | 0 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db3 | | 2 | 10.222.12.29 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db1 | 0 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db1 | +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 2021-01-17 01:42:49 [INFO] Changing status for server 1:10.222.12.29:3306 (10.222.12.29:3306) from 0 (0) to 1 2021-01-17 01:42:49 [INFO] Creating new server in HG 1 : 10.222.12.30:3306 , gtid_port=0, weight=1, status=0 2021-01-17 01:42:49 [INFO] Creating new server in HG 3 : 10.222.12.30:3306 , gtid_port=0, weight=1, status=0 2021-01-17 01:42:49 [INFO] Creating new server in HG 2 : 10.222.12.29:3306 , gtid_port=0, weight=1, status=0 2021-01-17 01:42:49 [INFO] Dumping current MySQL Servers structures for hostgroup ALL HID: 1 , address: 10.222.12.29 , port: 3306 , gtid_port: 0 , weight: 1 , status: SHUNNED , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db1 HID: 1 , address: 10.222.12.22 , port: 3306 , gtid_port: 0 , weight: 1 , status: SHUNNED , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db2 HID: 1 , address: 10.222.12.30 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db3 HID: 2 , address: 10.222.12.30 , port: 3306 , gtid_port: 0 , weight: 1 , status: OFFLINE_HARD , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db3 HID: 2 , address: 10.222.12.22 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db2 HID: 2 , address: 10.222.12.29 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db1 HID: 3 , address: 10.222.12.29 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db1 HID: 3 , address: 10.222.12.22 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db2 HID: 3 , address: 10.222.12.30 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db3 2021-01-17 01:42:49 [INFO] Dumping mysql_servers: ALL +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ | hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment | mem_pointer | +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ | 1 | 10.222.12.29 | 3306 | 0 | 1 | 1 | 0 | 1000 | 0 | 0 | 0 | c441db1 | 139749330340160 | | 3 | 10.222.12.30 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db3 | 139749283320448 | | 3 | 10.222.12.22 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db2 | 139749287555168 | | 3 | 10.222.12.29 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db1 | 139749283319968 | | 2 | 10.222.12.29 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db1 | 139749283320608 | | 2 | 10.222.12.22 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db2 | 139749330340320 | | 2 | 10.222.12.30 | 3306 | 0 | 1 | 3 | 0 | 1000 | 0 | 0 | 0 | c441db3 | 139749330339840 | | 1 | 10.222.12.30 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db3 | 139749283320288 | | 1 | 10.222.12.22 | 3306 | 0 | 1 | 1 | 0 | 1000 | 0 | 0 | 0 | c441db2 | 139749287555008 | +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ 2021-01-17 01:42:49 [INFO] Checksum for table mysql_servers is 15849729364753652924 2021-01-17 01:42:49 [INFO] Checksum for table mysql_galera_hostgroups is 10460573133796343126 2021-01-17 01:42:49 [INFO] MySQL_HostGroups_Manager::commit() locked for 5ms 2021-01-17 01:42:49 [INFO] Dumping current MySQL Servers structures for hostgroup 1 HID: 1 , address: 10.222.12.29 , port: 3306 , gtid_port: 0 , weight: 1 , status: SHUNNED , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db1 HID: 1 , address: 10.222.12.22 , port: 3306 , gtid_port: 0 , weight: 1 , status: SHUNNED , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db2 HID: 1 , address: 10.222.12.30 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db3 2021-01-17 01:42:49 [INFO] Dumping mysql_servers: HG 1 +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ | hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment | mem_pointer | +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ | 1 | 10.222.12.22 | 3306 | 0 | 1 | 1 | 0 | 1000 | 0 | 0 | 0 | c441db2 | 139749287555008 | | 1 | 10.222.12.29 | 3306 | 0 | 1 | 1 | 0 | 1000 | 0 | 0 | 0 | c441db1 | 139749330340160 | | 1 | 10.222.12.30 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db3 | 139749283320288 | +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ 2021-01-17 01:42:49 [INFO] Dumping current MySQL Servers structures for hostgroup 2 HID: 2 , address: 10.222.12.30 , port: 3306 , gtid_port: 0 , weight: 1 , status: OFFLINE_HARD , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db3 HID: 2 , address: 10.222.12.22 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db2 HID: 2 , address: 10.222.12.29 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db1 2021-01-17 01:42:49 [INFO] Dumping mysql_servers: HG 2 +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ | hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment | mem_pointer | +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ | 2 | 10.222.12.22 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db2 | 139749330340320 | | 2 | 10.222.12.29 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db1 | 139749283320608 | | 2 | 10.222.12.30 | 3306 | 0 | 1 | 3 | 0 | 1000 | 0 | 0 | 0 | c441db3 | 139749330339840 | +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ 2021-01-17 01:42:49 [INFO] Dumping current MySQL Servers structures for hostgroup 3 HID: 3 , address: 10.222.12.29 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db1 HID: 3 , address: 10.222.12.22 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db2 HID: 3 , address: 10.222.12.30 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: c441db3 2021-01-17 01:42:49 [INFO] Dumping mysql_servers: HG 3 +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ | hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment | mem_pointer | +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ | 3 | 10.222.12.22 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db2 | 139749287555168 | | 3 | 10.222.12.29 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db1 | 139749283319968 | | 3 | 10.222.12.30 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | c441db3 | 139749283320448 | +-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+ 2021-01-17 01:42:49 [INFO] Dumping current MySQL Servers structures for hostgroup 4 2021-01-17 01:42:49 [INFO] Dumping mysql_servers: HG 4 +-----+----------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-------------+ | hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment | mem_pointer | +-----+----------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-------------+ +-----+----------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-------------+ 2021-01-17 01:42:49 [INFO] Latest ProxySQL version available: 2.1.0-544-g17a4b4a7 ````

Global variables settings

```` mysql> select * from global_variables; +--------------------------------------------------------------+-----------------------------+ | variable_name | variable_value | +--------------------------------------------------------------+-----------------------------+ | mysql-shun_on_failures | 5 | | mysql-shun_recovery_time_sec | 10 | | mysql-query_retries_on_failure | 1 | | mysql-client_multi_statements | true | | mysql-connect_retries_delay | 1 | | mysql-connection_delay_multiplex_ms | 0 | | mysql-connection_max_age_ms | 0 | | mysql-connect_timeout_server_max | 10000 | | mysql-eventslog_filename | | | mysql-eventslog_filesize | 104857600 | | mysql-eventslog_default_log | 0 | | mysql-eventslog_format | 1 | | mysql-auditlog_filename | | | mysql-auditlog_filesize | 104857600 | | mysql-default_charset | utf8 | | mysql-handle_unknown_charset | 0 | | mysql-free_connections_pct | 10 | | mysql-session_idle_ms | 1000 | | mysql-have_ssl | false | | mysql-client_found_rows | true | | mysql-monitor_enabled | true | | mysql-monitor_connect_timeout | 600 | | mysql-monitor_ping_max_failures | 3 | | mysql-monitor_ping_timeout | 1000 | | mysql-monitor_read_only_max_timeout_count | 3 | | mysql-monitor_replication_lag_interval | 10000 | | mysql-monitor_replication_lag_timeout | 1000 | | mysql-monitor_groupreplication_healthcheck_interval | 5000 | | mysql-monitor_groupreplication_healthcheck_timeout | 800 | | mysql-monitor_groupreplication_healthcheck_max_timeout_count | 3 | | mysql-monitor_galera_healthcheck_interval | 5000 | | mysql-monitor_galera_healthcheck_timeout | 5000 | | mysql-monitor_galera_healthcheck_max_timeout_count | 3 | | mysql-monitor_replication_lag_use_percona_heartbeat | | | mysql-monitor_query_interval | 60000 | | mysql-monitor_query_timeout | 100 | | mysql-monitor_slave_lag_when_null | 60 | | mysql-monitor_threads_min | 8 | | mysql-monitor_threads_max | 128 | | mysql-monitor_threads_queue_maxsize | 128 | | mysql-monitor_wait_timeout | true | | mysql-monitor_writer_is_also_reader | true | | mysql-max_allowed_packet | 67108864 | | mysql-tcp_keepalive_time | 0 | | mysql-use_tcp_keepalive | 0 | | mysql-throttle_connections_per_sec_to_hostgroup | 1000000 | | mysql-max_transaction_time | 14400000 | | mysql-multiplexing | true | | mysql-log_unhealthy_connections | true | | mysql-forward_autocommit | false | | mysql-enforce_autocommit_on_reads | false | | mysql-autocommit_false_not_reusable | false | | mysql-autocommit_false_is_transaction | false | | mysql-verbose_query_error | false | | mysql-hostgroup_manager_verbose | 1 | | mysql-binlog_reader_connect_retry_msec | 3000 | | mysql-threshold_query_length | 524288 | | mysql-threshold_resultset_size | 4194304 | | mysql-query_digests_max_digest_length | 2048 | | mysql-query_digests_max_query_length | 65000 | | mysql-wait_timeout | 28800000 | | mysql-throttle_max_bytes_per_second_to_client | 0 | | mysql-throttle_ratio_server_to_client | 0 | | mysql-max_stmts_per_connection | 20 | | mysql-max_stmts_cache | 10000 | | mysql-mirror_max_concurrency | 16 | | mysql-mirror_max_queue_length | 32000 | | mysql-default_max_latency_ms | 1000 | | mysql-query_processor_iterations | 0 | | mysql-query_processor_regex | 1 | | mysql-set_query_lock_on_hostgroup | 0 | | mysql-reset_connection_algorithm | 2 | | mysql-auto_increment_delay_multiplex | 5 | | mysql-long_query_time | 1000 | | mysql-query_cache_size_MB | 256 | | mysql-poll_timeout_on_failure | 100 | | mysql-keep_multiplexing_variables | tx_isolation,version | | mysql-kill_backend_connection_when_disconnect | true | | mysql-client_session_track_gtid | true | | mysql-session_idle_show_processlist | true | | mysql-show_processlist_extended | 0 | | mysql-query_digests | true | | mysql-query_digests_lowercase | false | | mysql-query_digests_replace_null | false | | mysql-query_digests_no_digits | false | | mysql-query_digests_normalize_digest_text | false | | mysql-query_digests_track_hostname | false | | mysql-servers_stats | true | | mysql-default_reconnect | true | | mysql-ssl_p2s_ca | | | mysql-ssl_p2s_cert | | | mysql-ssl_p2s_key | | | mysql-ssl_p2s_cipher | | | mysql-init_connect | | | mysql-ldap_user_variable | | | mysql-add_ldap_user_comment | | | mysql-default_tx_isolation | REPEATABLE READ | | mysql-default_session_track_gtids | OFF | | mysql-default_collation_connection | utf8_unicode_ci | | mysql-connpoll_reset_queue_length | 50 | | mysql-min_num_servers_lantency_awareness | 1000 | | mysql-aurora_max_lag_ms_only_read_from_replicas | 2 | | mysql-stats_time_backend_query | false | | mysql-stats_time_query_processor | false | | mysql-query_cache_stores_empty_result | true | | mysql-threads | 4 | | mysql-max_connections | 2048 | | mysql-default_query_delay | 0 | | mysql-default_query_timeout | 36000000 | | mysql-have_compress | true | | mysql-poll_timeout | 2000 | | mysql-interfaces | 0.0.0.0:6033 | | mysql-default_schema | information_schema | | mysql-stacksize | 1048576 | | mysql-server_version | 5.5.30 | | mysql-connect_timeout_server | 3000 | | mysql-monitor_username | monitor | | mysql-monitor_password | ******************** | | mysql-monitor_history | 600000 | | mysql-monitor_connect_interval | 60000 | | mysql-monitor_ping_interval | 10000 | | mysql-monitor_read_only_interval | 1500 | | mysql-monitor_read_only_timeout | 500 | | mysql-ping_interval_server_msec | 120000 | | mysql-ping_timeout_server | 500 | | mysql-commands_stats | true | | mysql-sessions_sort | true | | mysql-connect_retries_on_failure | 10 | | mysql-server_capabilities | 569899 | | mysql-connect_timeout_client | 10000 | | mysql-connection_warming | false | | mysql-monitor_replication_lag_count | 1 | | mysql-monitor_groupreplication_max_transactions_behind_count | 3 | | mysql-automatic_detect_sqli | 0 | | mysql-firewall_whitelist_enabled | 0 | | mysql-firewall_whitelist_errormsg | Firewall blocked this query | | mysql-max_transaction_idle_time | 14400000 | | mysql-query_digests_grouping_limit | 3 | | admin-stats_credentials | stats:stats | | admin-stats_mysql_connections | 60 | | admin-stats_mysql_connection_pool | 60 | | admin-stats_mysql_query_cache | 60 | | admin-stats_system_cpu | 60 | | admin-stats_system_memory | 60 | | admin-telnet_admin_ifaces | (null) | | admin-telnet_stats_ifaces | (null) | | admin-refresh_interval | 2000 | | admin-read_only | false | | admin-hash_passwords | true | | admin-vacuum_stats | true | | admin-cluster_username | | | admin-cluster_password | | | admin-cluster_check_interval_ms | 1000 | | admin-cluster_check_status_frequency | 10 | | admin-cluster_mysql_query_rules_diffs_before_sync | 3 | | admin-cluster_mysql_servers_diffs_before_sync | 3 | | admin-cluster_mysql_users_diffs_before_sync | 3 | | admin-cluster_proxysql_servers_diffs_before_sync | 3 | | admin-cluster_mysql_query_rules_save_to_disk | true | | admin-cluster_mysql_servers_save_to_disk | true | | admin-cluster_mysql_users_save_to_disk | true | | admin-cluster_proxysql_servers_save_to_disk | true | | admin-checksum_mysql_query_rules | true | | admin-checksum_mysql_servers | true | | admin-checksum_mysql_users | true | | admin-web_enabled | false | | admin-web_port | 6080 | | admin-admin_credentials | admin:******************* | | admin-mysql_ifaces | 0.0.0.0:6032 | | admin-stats_mysql_query_digest_to_disk | 0 | | admin-restapi_enabled | false | | admin-restapi_port | 6070 | | admin-cluster_mysql_variables_diffs_before_sync | 3 | | admin-cluster_admin_variables_diffs_before_sync | 3 | | admin-cluster_mysql_variables_save_to_disk | true | | admin-cluster_admin_variables_save_to_disk | true | | admin-checksum_mysql_variables | true | | admin-checksum_admin_variables | true | | admin-web_verbosity | 0 | | admin-prometheus_memory_metrics_interval | 61 | | admin-version | 2.1.0-544-g17a4b4a7 | +--------------------------------------------------------------+-----------------------------+ 181 rows in set (0.01 sec) ````

As you can see, we've now upgraded to v2.1.0 with no change to our problem. Thanks a lot for your help!

JavierJF commented 3 years ago

Hi,

sorry, but I can't see any errors in the supplied log. ProxySQL final cluster state is:

| hid | hostname     | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment | mem_pointer     |
+-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+
| 1   | 10.222.12.29 | 3306 | 0    | 1      | 1      | 0   | 1000      | 0       | 0   | 0       | c441db1 | 139749330340160 |
| 3   | 10.222.12.30 | 3306 | 0    | 1      | 0      | 0   | 1000      | 0       | 0   | 0       | c441db3 | 139749283320448 |
| 3   | 10.222.12.22 | 3306 | 0    | 1      | 0      | 0   | 1000      | 0       | 0   | 0       | c441db2 | 139749287555168 |
| 3   | 10.222.12.29 | 3306 | 0    | 1      | 0      | 0   | 1000      | 0       | 0   | 0       | c441db1 | 139749283319968 |
| 2   | 10.222.12.29 | 3306 | 0    | 1      | 0      | 0   | 1000      | 0       | 0   | 0       | c441db1 | 139749283320608 |
| 2   | 10.222.12.22 | 3306 | 0    | 1      | 0      | 0   | 1000      | 0       | 0   | 0       | c441db2 | 139749330340320 |
| 2   | 10.222.12.30 | 3306 | 0    | 1      | 3      | 0   | 1000      | 0       | 0   | 0       | c441db3 | 139749330339840 |
| 1   | 10.222.12.30 | 3306 | 0    | 1      | 0      | 0   | 1000      | 0       | 0   | 0       | c441db3 | 139749283320288 |
| 1   | 10.222.12.22 | 3306 | 0    | 1      | 1      | 0   | 1000      | 0       | 0   | 0       | c441db2 | 139749287555008 |
+-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+

The reader_hostgroup holds all the servers online an operation:

+-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+
| hid | hostname     | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment | mem_pointer     |
+-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+
| 3   | 10.222.12.22 | 3306 | 0    | 1      | 0      | 0   | 1000      | 0       | 0   | 0       | c441db2 | 139749287555168 |
| 3   | 10.222.12.29 | 3306 | 0    | 1      | 0      | 0   | 1000      | 0       | 0   | 0       | c441db1 | 139749283319968 |
| 3   | 10.222.12.30 | 3306 | 0    | 1      | 0      | 0   | 1000      | 0       | 0   | 0       | c441db3 | 139749283320448 |
+-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+

The writer_hostgroup has two shunned servers, but one ONLINE server, being 10.222.12.30:

+-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+
| hid | hostname     | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment | mem_pointer     |
+-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+
| 1   | 10.222.12.22 | 3306 | 0    | 1      | 1      | 0   | 1000      | 0       | 0   | 0       | c441db2 | 139749287555008 |
| 1   | 10.222.12.29 | 3306 | 0    | 1      | 1      | 0   | 1000      | 0       | 0   | 0       | c441db1 | 139749330340160 |
| 1   | 10.222.12.30 | 3306 | 0    | 1      | 0      | 0   | 1000      | 0       | 0   | 0       | c441db3 | 139749283320288 |
+-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+

And the backup_writer_hostgroup has two servers ONLINE and one OFFLINE HARD server, the one being the current writer 10.222.12.30:

+-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+
| hid | hostname     | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment | mem_pointer     |
+-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+
| 2   | 10.222.12.22 | 3306 | 0    | 1      | 0      | 0   | 1000      | 0       | 0   | 0       | c441db2 | 139749330340320 |
| 2   | 10.222.12.29 | 3306 | 0    | 1      | 0      | 0   | 1000      | 0       | 0   | 0       | c441db1 | 139749283320608 |
| 2   | 10.222.12.30 | 3306 | 0    | 1      | 3      | 0   | 1000      | 0       | 0   | 0       | c441db3 | 139749330339840 |
+-----+--------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+

there is nothing wrong that can be deduced from this log, cluster looks in a expected state.

renecannao commented 3 years ago

Hi @rkconsulting .

When the writer now fails, ProxySQL pretty much gives up and promotes none of the backup writers to write to take over the DB write load.

Where is this in the log?

rkconsulting commented 3 years ago

Hi @renecannao, hi @JavierJF,

thanks for looking into this. I suppose my main question is why after the update the "normal status" of the writer hostgroup is to contain all servers with two out of three marked as SHUNNED. Is that now normal? Before the update there was only ever one server in the writer hostgroup - the writer. The others were always hanging out in the backup writer hostgroup, which seems more logical to me.

In ProxySQL v2.1.0, with the configuration referenced above, should there be two SHUNNED servers in the writer hostgroup during normal operation? How can one of the backup writers be promoted to the writer hostgroup if it is already part of the writer hostgroup (tho SHUNNED)?

I will simulate a writer failure tonight and get back to you tomorrow with the resulting log excerpt.

Thanks, take care!

rkconsulting commented 3 years ago

Hi @renecannao, @JavierJF,

unfortunately I won't have time to reproduce this failover scenario for a few days, could you please let me know if the writer hostgroup status described above and indicated by the log excerpt I provided (summary: right after service start, there are two SHUNNED servers and one ONLINE server in my writer hostgroup) is expected behaviour? Thanks in advance!

JavierJF commented 3 years ago

Hi @rkconsulting ,

the behavior that you are seeing is completely normal an expected. When the promotion happens the server that was already present in the 'writer_hostgroup' stops being SHUNNED and starts being ONLINE, and therefore starts serving traffic. SHUNNED servers are servers that doesn't allow any new incoming traffic, but that doesn't close it's already open connections. In that sense preserving servers in the 'writer_hostgroup', but as SHUNNED, is a more conservative approach that allows smooth transitions between hostgroups without any effect on traffic.

This behavior has been documented here: https://proxysql.com/documentation/galera-configuration/#shunned-writters

Since the behavior described is expected, I'm closing the issue, if you experience a miss behavior, please feel free to open a new one with the usual details.

Thank you.

rkconsulting commented 3 years ago

Hi @JavierJF,

thanks a lot for the link! Sorry to bother u guys, keep up the great work, we love ProxySQL!

Kind regards, -Rob

mrorakzai commented 2 years ago

When the writer now fails, ProxySQL pretty much gives up and promotes none of the backup writers to write to take over the DB write load.

Hi, im facing the same issue, im testing proxysql for HA, that is when my writer node fails, backup writer should be promoted as a writer, but this doesn't happens until i manually load servers to runtime or query the status of backend nodes. have tried different versions of proxysql but no success. can anyone guide me how can i configure proxysql so that it automatically change backup writer to writer? btw i have 3 node galera cluster, all nodes in proxysql are configured with writer HG and i have set writer_is_also_reader to 1 and max_writers to 1. query rules are also in place to split read/write queries.

Best regards

neomoon2022 commented 1 year ago

@mrorakzai do you have any news here? I'm also stuck in the same situation :|