Wrong column order in table mysql_servers
Neurozone opened this issue · 3 comments
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
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.
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
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.