Can not set servers_defaults field of mysql_hostgroup_attributes table from config file about proxysql HOT 5 OPEN

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.

from proxysql.

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.

from proxysql.

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.

from proxysql.

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é

from proxysql.

Good day,

@renecannao I totally understand your arguments regarding schema validation etc. but looking from a devops perspective I am on the side of @okanbasoglu.

Any linux service I can think of (except proxysql) is 100% configurable by text file(s). It is uncommon to prepare a database file with tools like ansible, puppet. These tools embrace templating config files which get transferred to a server.

I do not need the attributes right now but I can see it on the horizon and I would really like to configure it via the config file as the base config.

I would leave the error prone typing, without code completion, of JSON string to those who wants to go that way. Nobody has to :-).

I would appreciate, if we could get that feature of loading the attributes from file :-)

from proxysql.