Wrong column order in table mysql_servers

[Neurozone]

Hello.
We encounter a quite strange issue after running the LOAD MYSQL SERVERS TO RUNTIME
The issue is after that whe have the following warning: "Galera: too many writers in HG 101"
writer hostgroup id is 101

Checking into the code, I found a really strange thing, the definition of our mysql_servers table is the following one:
MariaDB [port]> show create table mysql_servers; +---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | table | Create Table | +---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | mysql_servers | CREATE TABLE mysql_servers ( hostgroup_id INT CHECK (hostgroup_id>=0) NOT NULL DEFAULT 0, hostname VARCHAR NOT NULL, port INT CHECK (port >= 0 AND port <= 65535) NOT NULL DEFAULT 3306, gtid_port INT CHECK ((gtid_port <> port OR gtid_port=0) AND gtid_port >= 0 AND gtid_port <= 65535) NOT NULL DEFAULT 0, status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE', weight INT CHECK (weight >= 0 AND weight <=10000000) NOT NULL DEFAULT 1, compression INT CHECK (compression IN(0,1)) NOT NULL DEFAULT 0, max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000, max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0, use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0, max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0, comment VARCHAR NOT NULL DEFAULT '', PRIMARY KEY (hostgroup_id, hostname, port) ) | +---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.001 sec)

Here, the column status in the sqlite db is before the column weight.

Is it normal ???

proxysql --version
ProxySQL version 2.3.2-10-g8cd66cf, codename Truls on CentOS 7.9.2009

I can't find this exact version on your releases. (I'm not the one who downloaded it)
the rpm name is proxysql-2.3.2-1-centos7.x86_x64.rpm

[JavierJF]

Hi,

I don't see the problem with the ordering on the mysql_servers columns, that ordering has always being the same. In ProxySQL source code:

ack "mysql_servers .* status .* weight" lib/ProxySQL_Admin.cpp:

#define ADMIN_SQLITE_TABLE_MYSQL_SERVERS_V2_0_0c "CREATE TABLE mysql_servers (hostgroup_id INT CHECK (hostgroup_id>=0) NOT NULL DEFAULT 0 , hostname VARCHAR NOT NULL , port INT CHECK (port >= 0 AND port <= 65535) NOT NULL DEFAULT 3306 , gtid_port INT CHECK (gtid_port <> port AND gtid_port >= 0 AND gtid_port <= 65535) NOT NULL DEFAULT 0 , status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE' , weight INT CHECK (weight >= 0 AND weight <=10000000) NOT NULL DEFAULT 1 , compression INT CHECK (compression IN(0,1)) NOT NULL DEFAULT 0 , max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000 , max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0 , use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0 , max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0 , comment VARCHAR NOT NULL DEFAULT '' , PRIMARY KEY (hostgroup_id, hostname, port) )"
#define ADMIN_SQLITE_TABLE_MYSQL_SERVERS_V2_0_11 "CREATE TABLE mysql_servers (hostgroup_id INT CHECK (hostgroup_id>=0) NOT NULL DEFAULT 0 , hostname VARCHAR NOT NULL , port INT CHECK (port >= 0 AND port <= 65535) NOT NULL DEFAULT 3306 , gtid_port INT CHECK ((gtid_port <> port OR gtid_port=0) AND gtid_port >= 0 AND gtid_port <= 65535) NOT NULL DEFAULT 0 , status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE' , weight INT CHECK (weight >= 0 AND weight <=10000000) NOT NULL DEFAULT 1 , compression INT CHECK (compression IN(0,1)) NOT NULL DEFAULT 0 , max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000 , max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0 , use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0 , max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0 , comment VARCHAR NOT NULL DEFAULT '' , PRIMARY KEY (hostgroup_id, hostname, port) )"
#define ADMIN_SQLITE_TABLE_RUNTIME_MYSQL_SERVERS "CREATE TABLE runtime_mysql_servers (hostgroup_id INT CHECK (hostgroup_id>=0) NOT NULL DEFAULT 0 , hostname VARCHAR NOT NULL , port INT CHECK (port >= 0 AND port <= 65535) NOT NULL DEFAULT 3306 , gtid_port INT CHECK ((gtid_port <> port OR gtid_port=0) AND gtid_port >= 0 AND gtid_port <= 65535) NOT NULL DEFAULT 0 , status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE' , weight INT CHECK (weight >= 0 AND weight <=10000000) NOT NULL DEFAULT 1 , compression INT CHECK (compression IN(0,1)) NOT NULL DEFAULT 0 , max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000 , max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0 , use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0 , max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0 , comment VARCHAR NOT NULL DEFAULT '' , PRIMARY KEY (hostgroup_id, hostname, port) )"

That release can be found in our repository, https://repo.proxysql.com/ProxySQL/proxysql-2.3.x/centos/7/proxysql-2.3.2-1-centos7.x86_64.rpm, and the version also looks correct:

[root@ workdir]# proxysql --version
ProxySQL version 2.3.2-10-g8cd66cf, codename Truls

Regarding this:

We encounter a quite strange issue after running the LOAD MYSQL SERVERS TO RUNTIME
The issue is after that whe have the following warning: "Galera: too many writers in HG 101"
writer hostgroup id is 101

There is no description on which one was the issue you faced, logs, or any of the information that is required by the issue template. Please elaborate on the issue you faced and provide the required information, so far, there is no evidence of any issue.

Thanks, Javier.

[Neurozone]

proxysql_too_many_writers.txt

Adding proxysql log file for you to check.
Happened on the same node, 003
Just changed the name of the severs to remove possibly sensitive informations

Look line 1: 2024-05-20 12:26:47 [INFO] Received LOAD MYSQL SERVERS TO RUNTIME command

And line 103: 2024-05-20 12:26:48 MySQL_HostGroups_Manager.cpp:5548:update_galera_set_writer(): [WARNING] Galera: too many writers in HG 101. Max=1, current=3

when it happened we encounter certifications issues during intensive load windows

[JavierJF]

There is no issue present in these logs. The [WARNING] message is issued because the configuration is setting 3 servers in the writer hostgroup, when the maximum is set to be 1. The next monitoring action, when finding this extra servers in the hostgroup sets them as SHUNNED.

mysql_servers configuration supplied:

2024-05-20 12:26:47 [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                                             |
+--------------+-----------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------------------------------------+
| 101          | node002 | 3301 | 0         | 1000   | 0      | 0           | 15000           | 0                   | 1       | 0              | node002 |
| 103          | node003 | 3301 | 0         | 999    | 0      | 0           | 15000           | 0                   | 1       | 0              | node003 |
| 103          | node002 | 3301 | 0         | 1000   | 0      | 0           | 15000           | 0                   | 1       | 0              | node002 |
| 102          | node001 | 3301 | 0         | 1001   | 0      | 0           | 15000           | 0                   | 1       | 0              | node001 |
| 102          | node003 | 3301 | 0         | 999    | 0      | 0           | 15000           | 0                   | 1       | 0              | node003 |
| 102          | node002 | 3301 | 0         | 1000   | 0      | 0           | 15000           | 0                   | 1       | 0              | node002 |
| 101          | node003 | 3301 | 0         | 999    | 0      | 0           | 15000           | 0                   | 1       | 0              | node003 |
| 101          | node001 | 3301 | 0         | 1001   | 0      | 0           | 15000           | 0                   | 1       | 0              | node001 |
+--------------+-----------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------------------------------------+

All nodes are loaded into hostgroup 101, the writer hostgroup. The next monitoring action ProxySQL issues the WARNING and sets the nodes as SHUNNED:

2024-05-20 12:26:48 MySQL_HostGroups_Manager.cpp:5548:update_galera_set_writer(): [WARNING] Galera: too many writers in HG 101. Max=1, current=3
2024-05-20 12:26:48 MySQL_HostGroups_Manager.cpp:5643:update_galera_set_writer(): [WARNING] Galera: setting host node001:3301 as writer
2024-05-20 12:26:48 [INFO] Galera: Node status changed by ProxySQL, dumping all galera nodes status:
...
2024-05-20 12:26:48 [INFO] Changing status for server 101:node002:3301 (node002:3301) from 0 (0) to 1
2024-05-20 12:26:48 [INFO] Changing status for server 101:node003:3301 (node003:3301) from 0 (0) to 1

There are no miss-behaviors present in this log. Regarding this:

when it happened we encounter certifications issues during intensive load windows

Which certification issues? They haven't been added as the info supplied here, also, there is no evidence at all of correlation between those events.

What we can see here, is a warning regarding a potential undesired configuration, since the user is placing a number of servers in the writer_hostgroup that contradicts the maximum number of writers, so the next monitoring action reports about this, and corrects the server placement.

I'm closing this issue for now, as it's an amalgamation of several questions that not issues themselves. If you want to create a new ticket about his:

when it happened we encounter certifications issues during intensive load windows

Please provide all the information requested in the issue template.

Thanks, Javier.