Proxysql: The proxysql MySQL interface configuration is counter-intuitive and confusing

[ovaistariq]

Suppose I have configured ProxySQL to listen to 6033 for client connections. And then later on decided to change it to 3306. I make this change in the configuration file and my expectation is (based on how MySQL works or any other system that utilizes configuration files) that ProxySQL will now listen to 3306. However it doesn't work like that.

I have to take the following steps:

• mysql> UPDATE global_variables SET variable_value='0.0.0.0:3306;/var/lib/proxysql/proxysql.sock' WHERE variable_name='mysql-interfaces';
• SAVE MYSQL VARIABLES TO DISK;
• Restart ProxySQL

Not only is this confusing but it is counter-intuitive and can easily lead to a situation where the config file and the actual configuration is different.
The other thing is that this particular configuration is not even a dynamic one, i.e., I cannot change it on the fly so it makes little sense for it to go through the steps above. In fact the whole purpose of configuration files is to define how a particular system should be configured. So my advice would be to let configuration option in the config file override whatever was set through global_variables table.

[renecannao]

The concept of not re-reading the config file unless forced to do so is a concept borrowed from MySQL Cluster and from Oracle's Server Parameter File.
In other words, a binary configuration takes precedence over the text config file, that is completely ignored unless forced to re-read it.

Interestingly, MySQL 8 is following a similar path with SET PERSISTENT : the variables set in standard config (my.cnf) are overwritten by variables set with SET PERSISTENT, although these variables are still in a text file (mysqld-auto.cnf) instead of a binary file.

The fact that this variable cannot be changed at runtime is indeed an issue that needs to be fixed, and there is an issue for this : #440 .

What I see missing in the steps above is the fact you aren't using PROXYSQL RESTART command. Checking the doc, it seems I haven't documented that, sorry!
PROXYSQL RESTART is an Admin command that allows to restart ProxySQL directly from the Admin interface. Doesn't restart the process (the pid will be the same), but shutdown and restarts all the modules.
Therefore, going through the step above and issuing PROXYSQL RESTART instead of restarting the process, without the need of modifying the config file has few big advantages I can think of:

• it is possible to manage the ProxySQL remotely without having ssh access to the instance where ProxySQL is running: that also allow to start proxysql with a very simple configuration file and perform a bootstrap afterward
• in case you are running proxysql as the only process in a docker container, you do not need to modify the config file inside the container, neither the container will be stopped

[akuzminsky]

In other words, a binary configuration takes precedence over the text config file, that is completely ignored unless forced to re-read it.

This is going to break a lot of things, imo. Let's say the config is managed by puppet. Then if it changes notify => Service['proxysql'] will have no effect.

[renecannao]

Hi @akuzminsky !
I disagree, isn't necessarily going to break a lot of things, it only changes the way things are deployed.
I know of ansible and puppet deployments in which what is changed is an SQL file: when an SQL file changes, it will be processed by the Admin interface.

[ovaistariq]

@renecannao the point of the ticket is that as a user it is not very clear and user-friendly way of managing configuration. Being able to change configuration through the admin interface is definitely a very nice feature but from usability and ease-of-use perspective it is quite confusing. I am leaving this feedback as a customer in hope that this feedback will improve the product. If a seasoned DBA like me was unable to make sense of it quickly then you can imagine how difficult it would be for a NOOB.

The way I think it can be improved is

• allow configuration to be handled from config file solely (as an option to the user)
• document the admin interface clearly
• don't allow variables that are static to be set through the admin interface

[renecannao]

@ovaistariq : I really appreciate the feedback!

allow configuration to be handled from config file solely (as an option to the user)

This is already possible using the --initial or --reload flags.
The flags names were both borrowed by MySQL Cluster (is it clear I am a MySQL Cluster fan? 😄 ) :

• --initial
• --reload (although ProxySQL implementation is a bit different)

document the admin interface clearly

+1000 on this!
It is documented, but clearly it is still confusing and I should improve its visibility. I was thinking about:

• the sample configuration files should have explicitly written that their content is ignored by default if a database file is already present
• it both a config file and a a database files are present and none of the --initial or --reload are specified, ProxySQL should report an info/warning in the error log specifying that the configurations in the config files are being ignored.
  ndb_mgmd (MySQL Cluster Manager) reports something like Loaded config from '/var/lib/mysql-cluster/ndb_1_config.bin.1' . ProxySQL could do the same, specifying from where is loading its config and eventually also from where it is not loading it.

don't allow variables that are static to be set through the admin interface

I disagree on this for two reasons:

• as described in the second part of https://github.com/sysown/proxysql/issues/854#issuecomment-269002924 , PROXYSQL RESTART allows to makes these variables "semi-dynamic" : it is possible to internally restart proxysql without terminating the process
• the fact that these variables cannot be loaded directly at runtime is a limitation that will be addressed in future. Right now, you can set them through Admin, but when you try to load them at runtime they are reverted. You can only save them to disk.

[mason-chase]

The concept of not re-reading the config file unless forced to do so is a concept borrowed from MySQL Cluster and from Oracle's Server Parameter File.
In other words, a binary configuration takes precedence over the text config file, that is completely ignored unless forced to re-read it.

I have made a test on 'proxysql-1.4.6-1-centos67.x86_64.rpm' and it appears the /etc/proxysql.conf is overriding mysql_interfaces on the binary settings.

How is this happening?

Regards

[pondix]

Hi @mason-chase

Can you please provide every step to reproduce the issue (including commands executed and output of the mysql interfaces variable value throughout the various steps in the process), proxysql.cnf and the error log?

We'll need the exact steps to try reproduce the issue.

Please keep in mind that if you change mysql_interfaces you'll need to SAVE MYSQL VARIABLES TO DISK and then restart ProxySQL, I have tested this and it works however please send the steps which you followed and the error log for analysis.

Nick

[mason-chase]

Hi

These are the series of command:

mysql> SELECT * FROM global_variables WHERE variable_name LIKE 'mysql_i%';
+--------------------+----------------------------+
| variable_name      | variable_value             |
+--------------------+----------------------------+
| mysql-init_connect | (null)                     |
| mysql-interfaces   | 0.0.0.0:3306;/tmp/proxysql |
+--------------------+----------------------------+
2 rows in set (0.01 sec)

mysql> SET mysql-interfaces="/var/lib/mysql/mysql.sock";
Query OK, 1 row affected (0.00 sec)

mysql> LOAD MYSQL VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

mysql> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM global_variables WHERE variable_name LIKE 'mysql_i%';
+--------------------+----------------------------+
| variable_name      | variable_value             |
+--------------------+----------------------------+
| mysql-init_connect | (null)                     |
| mysql-interfaces   | 0.0.0.0:3306;/tmp/proxysql |
+--------------------+----------------------------+
2 rows in set (0.00 sec)

mysql> SAVE MYSQL VARIABLES TO DISK;
Query OK, 93 rows affected (0.02 sec)

mysql> SAVE MYSQL SERVERS TO DISK;
Query OK, 0 rows affected (0.02 sec)

mysql> proxysql restart;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> SELECT * FROM global_variables WHERE variable_name LIKE 'mysql_i%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    7
Current database: *** NONE ***

+--------------------+----------------------------+
| variable_name      | variable_value             |
+--------------------+----------------------------+
| mysql-init_connect | (null)                     |
| mysql-interfaces   | 0.0.0.0:3306;/tmp/proxysql |
+--------------------+----------------------------+
2 rows in set (0.01 sec)

[pondix]

Please check your logfile - it is likely that ProxySQL is rejecting that command because the path doesn't exist or the location is not writable e.g.:

2018-02-27 17:20:36 ProxySQL_Admin.cpp:3858:flush_mysql_variables___database_to_runtime(): [WARNING] Impossible to set variable interfaces with value "/var/lib/mysql/mysql.sock". Resetting to current "0.0.0.0:6033".

[renecannao]

You shouldn't run this after changing mysql-interfaces .

mysql> LOAD MYSQL VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

After changing mysql-interfaces or mysql-threads, you should not run LOAD MYSQL VARIABLES TO RUNTIME because these 2 variables cannot be loaded at runtime. Attempt to load them at runtime will cause their reset.
In other words, after changing mysql-interfaces or mysql-threads, you need to SAVE MYSQL VARIABLES TO DISK and then restart

[mason-chase]

Thanks Rene for the tip, It would be awesome to verbose reset variables.

[renecannao]

Error log should be verbose already.
But users using the CLI won't see the issue. #1288 is relevant here