can not query global_variables with stats user

[shawn0915]

hi, there,

when I checking proxysql status, I found stats user only can check one parameter in the table main.global_variables, stats is a read only user, it can't modify variables, but it can check/select all variables, right?

steps:

podman run -d --name proxysql proxysql:2.6.2
podman exec -it proxysql mysql -ustats -pstats -P6032
SELECT * FROM main.global_variables ;

result:

MySQL [(none)]> SELECT * FROM main.global_variables ;
+--------------------------+----------------+
| variable_name            | variable_value |
+--------------------------+----------------+
| mysql-max_allowed_packet | 4194304        |
+--------------------------+----------------+
1 row in set (0.000 sec)

[renecannao]

Hi @shawn0915 ,

stats user can only access to stats tables. That means it cannot check variables: it can't access to any configuration setting.

The global_variables table with only 1 row is there to avoid errors from client libraries executing SELECT @@max_allowed_packet when connecting. A table with just that row was an easy way to avoid errors, but in future we can completely remove it and reply to the same query in a different way

[shawn0915]

stats user can only access to stats tables.

@renecannao thanks for ur reply,

looks like stats user can only access the stats_history schema but not stats.

MySQL [(none)]> select version()\G
*************************** 1. row ***************************
version(): 2.6.2-41-gb368fc9
1 row in set (0.000 sec)

MySQL [(none)]> show tables from stats;
ERROR 1045 (28000): ProxySQL Admin Error: no such table: stats.sqlite_master

MySQL [(none)]> show schemas;
+-----+---------------+-------------------------------------+
| seq | name          | file                                |
+-----+---------------+-------------------------------------+
| 0   | main          |                                     |
| 2   | monitor       |                                     |
| 3   | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
3 rows in set (0.000 sec)

[renecannao]

Hi @shawn0915 ,

In SQLite , main is the name of the "main database". For admin users, the main database is the configuration database. For stats users, the main database is the stats database.

MySQL [(none)]> show tables from stats;

The "stats" database is now the "main" database. We could attach it also to "stats" (if SQLite allows to attach the same schema twice, I need to test): it will be completely redundant, but may help scripts explicitly using stats.

[shawn0915]

@renecannao

get, thank you. 👍‍

[renecannao]

Hi @shawn0915 . You are welcome!