Comments (3)
JavierJF
commented on June 25, 2024
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.
from proxysql.
Neurozone
commented on June 25, 2024
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
from proxysql.
JavierJF
commented on June 25, 2024
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.
from proxysql.
Related Issues (20)
- Every uneven traffic distribution for nodes with same weight after upgrading from v2.0.10-1 to v2.5.5-1 HOT 1
- Ever increasing 'stats_mysql_query_digest' results in sporadic client timings HOT 5
- Official documentation has missing introduced version information for mysql-connection_warming
- Implement setting in `mysql_hostgroup_attributes` to define custom load balancing algorithm
- ProxySQL errorlog not getting picked up from proxysql.cnf in docker HOT 5
- crashes during cluster shutdown in CI testing HOT 1
- Support some form of partial cleanup of `stats_mysql_query_digest`
- Shunned writer node not becoming online HOT 8
- Intermittent "Access denied" errors HOT 1
- Different server_version per port
- aarch64 / i686 builds fail of v2.6.2 (narrowing conversion of timeout in clickhouse) HOT 2
- Can not set servers_defaults field of mysql_hostgroup_attributes table from config file HOT 4
- Clients seeing "Lost connection to MySQL server during query" errors HOT 1
- Improve read consistency with database replicas
- Want to send heavy costing select query to read server without splitting it on write HOT 2
- config in table mysql_group_replication_hostgroups be deleted when the service is restarted HOT 6
- Documentation error: mysql-default_query_timeout default is not correctly documented HOT 1
- The same query with and without a comment results in cache misses HOT 2
- ProxySQL HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from proxysql.