Can not set servers_defaults field of mysql_hostgroup_attributes table from config file #4552
Description
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.