Can not set servers_defaults field of mysql_hostgroup_attributes table from config file
okanbasoglu opened this issue · 4 comments
Hey there,
I am trying to set the servers_defaults from the configuration file but it fails silently during the container start.
ProxySQL version: 2.6.2
OS: Ubuntu 20.04 on ARM
Here is how you can reproduce this issue.
Create these two files.
Dockerfile
FROM proxysql/proxysql:2.6.2
COPY proxysql.cnf /etc/proxysql.cnf
CMD [ "sh", "-c", "/usr/bin/proxysql --initial --foreground --idle-threads --exit-on-error --no-version-check --config /etc/proxysql.cnf" ]
proxysql.cnf
datadir="/var/lib/proxysql"
admin_variables=
{
admin_credentials="root:root"
mysql_ifaces="0.0.0.0:3300"
}
mysql_variables=
{
interfaces="0.0.0.0:3306"
}
mysql_hostgroup_attributes=
{
hostgroup_id=1
servers_defaults="{\"weight\":1,\"max_connections\":1000}"
}
Run the following command to start the container.
docker build -t proxysql_test . && docker run -d --name proxysql_test proxysql_test
Run the following command to connect to the proxysql admin to see if the setting is there.
docker exec proxysql_test sh -c 'mysql -h 127.0.0.1 -P 3300 -u root -proot -e "select * from mysql_hostgroup_attributes;"'
If you run the following command which has the same json from the config file you can see that it is a legit configuration.
docker exec proxysql_test sh -c 'mysql -h 127.0.0.1 -P 3300 -u root -proot -e "insert into mysql_hostgroup_attributes (hostgroup_id, servers_defaults) values (1, '"'"'{\"weight\":1,\"max_connections\":1000}'"'"');"'
If you query the mysql_hostgroup_attributes table you can see the configuration is there in this case.
docker exec proxysql_test sh -c 'mysql -h 127.0.0.1 -P 3300 -u root -proot -e "select * from mysql_hostgroup_attributes;"'
Here are the proxysql container logs
docker logs proxysql_test
2024-05-21 10:10:59 [INFO] Using config file /etc/proxysql.cnf
Renaming database file /var/lib/proxysql/proxysql.db
2024-05-21 10:10:59 [INFO] Current RLIMIT_NOFILE: 1048576
2024-05-21 10:10:59 [INFO] Using OpenSSL version: OpenSSL 3.2.1 30 Jan 2024
2024-05-21 10:10:59 [INFO] No SSL keys/certificates found in datadir (/var/lib/proxysql). Generating new keys/certificates.
2024-05-21 10:10:59 [INFO] ProxySQL version 2.6.2-41-gb368fc9
2024-05-21 10:10:59 [INFO] Detected OS: Linux a3b10a57eedb 5.4.0-152-generic #169-Ubuntu SMP Tue Jun 6 22:25:45 UTC 2023 aarch64
2024-05-21 10:10:59 [INFO] ProxySQL SHA1 checksum: f896679bcb504604d645dfd1d765f1eae953dc27
2024-05-21 10:10:59 [INFO] SSL keys/certificates found in datadir (/var/lib/proxysql): loading them.
2024-05-21 10:10:59 [INFO] Loaded built-in SQLite3
Standard ProxySQL MySQL Logger rev. 2.5.0421 -- MySQL_Logger.cpp -- Tue Apr 2 06:41:55 2024
Standard ProxySQL Cluster rev. 0.4.0906 -- ProxySQL_Cluster.cpp -- Tue Apr 2 06:41:55 2024
Standard ProxySQL Statistics rev. 1.4.1027 -- ProxySQL_Statistics.cpp -- Tue Apr 2 06:41:55 2024
Standard ProxySQL HTTP Server Handler rev. 1.4.1031 -- ProxySQL_HTTP_Server.cpp -- Tue Apr 2 06:41:55 2024
2024-05-21 10:10:59 [INFO] Using UUID: 54520b98-a250-43f9-90bf-b188636d883c , randomly generated. Writing it to database
2024-05-21 10:10:59 [INFO] Computed checksum for 'LOAD ADMIN VARIABLES TO RUNTIME' was '0xB0660676C2C31DEF', with epoch '1716286259'
2024-05-21 10:10:59 [INFO] Computed checksum for 'LOAD MYSQL VARIABLES TO RUNTIME' was '0xD31A91ECF10BC702', with epoch '1716286259'
Standard ProxySQL Admin rev. 2.0.6.0805 -- ProxySQL_Admin.cpp -- Tue Apr 2 06:41:55 2024
2024-05-21 10:10:59 [INFO] ProxySQL SHA1 checksum: f896679bcb504604d645dfd1d765f1eae953dc27
Standard MySQL Threads Handler rev. 0.2.0902 -- MySQL_Thread.cpp -- Tue Apr 2 06:41:55 2024
Standard MySQL Authentication rev. 0.2.0902 -- MySQL_Authentication.cpp -- Tue Apr 2 06:41:55 2024
2024-05-21 10:10:59 [INFO] Computed checksum for 'LOAD MYSQL USERS TO RUNTIME' was '0x0000000000000000', with epoch '1716286259'
2024-05-21 10:10:59 [INFO] Generating runtime mysql servers and mysql servers v2 records.
2024-05-21 10:10:59 [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 |
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2024-05-21 10:10:59 [INFO] Dumping mysql_servers LEFT JOIN mysql_servers_incoming
+-------------+--------------+----------+------+
| mem_pointer | hostgroup_id | hostname | port |
+-------------+--------------+----------+------+
+-------------+--------------+----------+------+
2024-05-21 10:10:59 [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 |
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2024-05-21 10:10:59 [INFO] New mysql_group_replication_hostgroups table
2024-05-21 10:10:59 [INFO] New mysql_galera_hostgroups table
2024-05-21 10:10:59 [INFO] New mysql_aws_aurora_hostgroups table
2024-05-21 10:10:59 [INFO] New mysql_hostgroup_attributes table
2024-05-21 10:10:59 [INFO] New mysql_servers_ssl_params table
2024-05-21 10:10:59 [INFO] Checksum for table mysql_servers_v2 is 0x0000000000000000
2024-05-21 10:10:59 [INFO] New computed global checksum for 'mysql_servers_v2' is '0x0000000000000000'
2024-05-21 10:10:59 [INFO] Checksum for table mysql_servers is 0x0000000000000000
2024-05-21 10:10:59 [INFO] MySQL_HostGroups_Manager::commit() locked for 1ms
2024-05-21 10:10:59 [INFO] Computed checksum for 'LOAD PROXYSQL SERVERS TO RUNTIME' was '0x0000000000000000', with epoch '1716286259'
Standard Query Processor rev. 2.0.6.0805 -- Query_Processor.cpp -- Tue Apr 2 06:41:55 2024
2024-05-21 10:10:59 [INFO] Computed checksum for 'LOAD MYSQL QUERY RULES TO RUNTIME' was '0x0000000000000000', with epoch '1716286259'
In memory Standard Query Cache (SQC) rev. 1.2.0905 -- Query_Cache.cpp -- Tue Apr 2 06:41:55 2024
Standard MySQL Monitor (StdMyMon) rev. 2.0.1226 -- MySQL_Monitor.cpp -- Tue Apr 2 06:41:55 2024
2024-05-21 10:10:59 [INFO] For information about products and services visit: https://proxysql.com/
2024-05-21 10:10:59 [INFO] For online documentation visit: https://proxysql.com/documentation/
2024-05-21 10:10:59 [INFO] For support visit: https://proxysql.com/services/support/
2024-05-21 10:10:59 [INFO] For consultancy visit: https://proxysql.com/services/consulting/
What was expected
Using the following configuration block in the config file should create a record at the mysql_hostgroup_attributes table.
mysql_hostgroup_attributes=
{
hostgroup_id=1
servers_defaults="{\"weight\":1,\"max_connections\":1000}"
}
What happened
The mysql_hostgroup_attributes table does not have the record.
Hi @okanbasoglu .
Indeed, mysql_hostgroup_attributes is not read at all from config file.
Function Read_MySQL_Servers_from_configfile makes no reference to it.
It is probably an easy feature to implement, even if generally we prefer to keep "complex" configuration away from config file.
Hi Rene,
Thanks for the quick reply. If this is implemented it would be really helpful in our case. We want to use ProxySQL with the AWS Aurora with auto discovery feature but the discovered nodes gets their default values from this field according to the docs. Of course there is the default value but we can not rely on it for our case.
https://proxysql.com/documentation/aws-aurora-configuration/
Emphasizing the previously stated, the servers_defaults used for autodiscovered servers, always assume REPLICA servers are being discovered, which means that the mysql_hostgroup_attributes used are the ones corresponding to the reader_hostgroup configured at mysql_aws_aurora_hostgroups.
Because we use ProxySQL as a sidecar container with each of our app container using the configuration file is really easy and consistent to rely on for configuring the ProxySQL in our case.
Hi @okanbasoglu ,
Would you consider using a database file for configuration, instead of text file configuration?
A text file configuration is really rudimentary an error prone, especially in case of nested configuration.
For example, mysql_hostgroup_attributes section, servers_defaults value will be a JSON .
There is no input validation on a text file, while in proxysql.db there are input validation and several constraints in place at database level.
Admin> SHOW CREATE TABLE mysql_hostgroup_attributes\G
*************************** 1. row ***************************
table: mysql_hostgroup_attributes
Create Table: CREATE TABLE mysql_hostgroup_attributes (
hostgroup_id INT NOT NULL PRIMARY KEY,
max_num_online_servers INT CHECK (max_num_online_servers>=0 AND max_num_online_servers <= 1000000) NOT NULL DEFAULT 1000000,
autocommit INT CHECK (autocommit IN (-1, 0, 1)) NOT NULL DEFAULT -1,
free_connections_pct INT CHECK (free_connections_pct >= 0 AND free_connections_pct <= 100) NOT NULL DEFAULT 10,
init_connect VARCHAR NOT NULL DEFAULT '',
multiplex INT CHECK (multiplex IN (0, 1)) NOT NULL DEFAULT 1,
connection_warming INT CHECK (connection_warming IN (0, 1)) NOT NULL DEFAULT 0,
throttle_connections_per_sec INT CHECK (throttle_connections_per_sec >= 1 AND throttle_connections_per_sec <= 1000000) NOT NULL DEFAULT 1000000,
ignore_session_variables VARCHAR CHECK (JSON_VALID(ignore_session_variables) OR ignore_session_variables = '') NOT NULL DEFAULT '',
hostgroup_settings VARCHAR CHECK (JSON_VALID(hostgroup_settings) OR hostgroup_settings = '') NOT NULL DEFAULT '',
servers_defaults VARCHAR CHECK (JSON_VALID(servers_defaults) OR servers_defaults = '') NOT NULL DEFAULT '',
comment VARCHAR NOT NULL DEFAULT '')
Sorry to stress on this, but storing ProxySQL configuration in a variety of tables in a relational database instead of a text file offers several significant advantages:
- Relational databases enforce a schema, ensuring that data is stored in a structured manner. This reduces the risk of errors and inconsistencies compared to a text file where structure is harder to enforce
- Relational databases allow the definition of constraints (e.g., primary keys, foreign keys, unique constraints) that maintain data integrity and prevent invalid data from being entered
- Each column in a relational database table has a specific data type, ensuring that only valid data is stored (integer, strings, JSON, etc)
In contrast, while text files are simpler and easier to set up initially, they can become difficult to manage as complexity grows, leading to potential issues with data integrity, consistency, and parsing errors.
Because of the above:
- we highly recommend to not use the configuration file for anything more complex that a very basic configuration
- I have a hard time in accommodating the use of "not trivial" configuration in a configuration text file
Back to "Would you consider using a database file for configuration, instead of text file configuration?" , please note that you could COPY proxysql.db /var/lib/proxysql/proxysql.db instead of COPY proxysql.cnf /etc/proxysql.cnf .
Furthermore, applying the query to proxysql.db will ensure correctness of the data: a further reason why passing proxysql.db instead of proxysql.cnf is a better option.
And you could stop using --initial (a feature that we also discourage using, because we believe a database is a better source of configuration than a text file).
Thanks,
René
Hi Rene,
Thanks for the detailed explanation.
The reason I did not go with a database configuration file is that we have a lot of configuration options carried with environment variables. Like username, password, hostname, maximum conn num. etc. and it makes the container more versatile because it allows us to use the same container on any other environment we have like development, test, staging or production so we know that it is the same container that is running on that environment because they all share the same tag.
If we used a static db configuration for each environment then we need to build it per environment which is cumbersome and each environment would end up with a different container tag.
With a text configuration file in the entrypoint script we just replace the environment variables in the config file with the values and the container is ready to run immediately. I know that we can play around a bit in the entrypoint like start the process in background configure it with SQL queries so we can use environment vairables during the configuration then pass the execution to ProxySQL but that is a lot of hoops to make it work.
Where if we could have the options in the configuration file it is a very simple operation and makes the solution more container friendly imho.
DB configuration solution would have worked great if we could store environment variables in the DB configuration later on they are expanded during the initial ProxySQL boot but I assume that would be a lot of work.
Hope I am not bothering you with all use case details.