Multiple admin-stats_credentials not working after proxysql restart

gpipito commented 5 months ago

[ ] A clear description of the issue

admin-stats_credentials are not working after proxysql restarts when multiple users are configured. Note that they were previously saved with the following commands:

LOAD ADMIN VARIABLES TO RUNTIME;
SAVE ADMIN VARIABLES TO DISK

The issue only happens when multiple users are configured, like the following example:

admin-stats_credentials="admin:admin;radminuser:radminpass"

With the configuration above, only radminuser is working after a proxysql restart.

[ ] ProxySQL version
```
2.6.2-41-gb368fc9
```

[ ] OS version

Distributor ID: Ubuntu
Description:    Ubuntu 22.04.2 LTS
Release:        22.04
Codename:       jammy

[ ] The steps to reproduce the issue

I login with admin credentials:

[root@hostname ~]# mysql -u admin -P 6032 -h 127.0.0.1 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 41
Server version: 8.0.36 (ProxySQL Admin Module)

Copyright (c) 2009-2024 Percona LLC and/or its affiliates
Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Configure admin-stats_credentials:

mysql> UPDATE global_variables SET variable_value='localuser:********;remoteuser:********' WHERE variable_name='admin-stats_credentials';
Query OK, 1 row affected (0.00 sec)

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

mysql> SAVE ADMIN VARIABLES TO DISK;
Query OK, 49 rows affected (0.01 sec)

Verify configuration:

mysql> select * from global_variables where variable_name like "%admin%";
+---------------------------------------------------+--------------------------------------------+
| variable_name                                     | variable_value                             |
+---------------------------------------------------+--------------------------------------------+
| admin-stats_credentials                           | localuser:********;remoteuser:******** |
| admin-stats_mysql_connections                     | 60                                         |
| admin-stats_mysql_connection_pool                 | 60                                         |
| admin-stats_mysql_query_cache                     | 60                                         |
| admin-stats_mysql_query_digest_to_disk            | 0                                          |
| admin-stats_system_cpu                            | 60                                         |
| admin-stats_system_memory                         | 60                                         |
...

Exit proxysql cli and try login with locauser

mysql> exit
Bye
[root@hostname  ~]# mysql -u localuser -P 6032 -h 127.0.0.1 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 143
Server version: 8.0.36 (ProxySQL Admin Module)

Copyright (c) 2009-2024 Percona LLC and/or its affiliates
Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye

Restart proxysql

[root@hostname  ~]# systemctl restart proxysql

Login with localuser fails:

[root@hostname ~]# mysql -u localuser-P 6032 -h 127.0.0.1 -p
Enter password: 
ERROR 1045 (28000): ProxySQL Error: Access denied for user 'localuser'@'127.0.0.1' (using password: YES)

Login with remotuser works:

[root@hostname ~]# mysql -u remoteuser-P 6032 -h 127.0.0.1 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 77
Server version: 8.0.36 (ProxySQL Admin Module)

Copyright (c) 2009-2024 Percona LLC and/or its affiliates
Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit

I login again with admin credentials to verify the configuration:

[root@hostname ~]# mysql -u admin -P 6032 -h 127.0.0.1 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 41
Server version: 8.0.36 (ProxySQL Admin Module)

Copyright (c) 2009-2024 Percona LLC and/or its affiliates
Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from global_variables where variable_name like "%admin%";
+---------------------------------------------------+--------------------------------------------+
| variable_name                                     | variable_value                             |
+---------------------------------------------------+--------------------------------------------+
| admin-stats_credentials                           | localuser:********;remoteuser:******** |
| admin-stats_mysql_connections                     | 60                                         |
| admin-stats_mysql_connection_pool                 | 60                                         |
| admin-stats_mysql_query_cache                     | 60                                         |
| admin-stats_mysql_query_digest_to_disk            | 0                                          |
| admin-stats_system_cpu                            | 60                                         |
| admin-stats_system_memory                         | 60                                         |
...

[ ] The full ProxySQL error log (default location: /var/lib/proxysql/proxysql.log)

2024-06-27 08:51:00 MySQL_Protocol.cpp:1722:PPHR_1(): [ERROR] User 'localuser'@'127.0.0.1' is disconnecting during switch auth
2024-06-27 08:51:00 MySQL_Session.cpp:6056:handler___status_CONNECTING_CLIENT___STATE_SERVER_HANDSHAKE(): [ERROR] ProxySQL Error: Access denied for user 'localuser'@'127.0.0.1' (using password: YES)

renecannao commented 5 months ago

Ciao Giovanni.

User 'localuser'@'127.0.0.1' is disconnecting during switch auth suggests that the client is disconnecting instead of performing a switch auth.

We need a full reproducible test case, that specifically means:

we need all the configuration in global_variables
we need the passwords , not ******** . ******** doesn't allow to reproduce your issue, we don't know if it is a clear text password, a sha1 or sha2 password, etc . Of course we do not want you to share sensitive information, so we need you to provide a reproducible test case with generic and not confidential passwords, like "example" or "password" or "localpassword".

A final note: it is not clear if the restart causes mysql variables to be lost. I see you executed SAVE ADMIN VARIABLES TO DISK , but mysql authentication is affected by mysql variables.

gpipito commented 5 months ago

Hi,

thanks for your reply. I repeated the steps with test credentials (using the same character set of my real credentials) and it works:

[root@hostname ~]# mysql -u admin -P 6032 -h 127.0.0.1 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 60
Server version: 8.0.36 (ProxySQL Admin Module)

Copyright (c) 2009-2024 Percona LLC and/or its affiliates
Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> UPDATE global_variables SET variable_value='localuser:l0c@lU53r;remoteuser:r3m0+3U53r' WHERE variable_name='admin-stats_credentials';
Query OK, 1 row affected (0.01 sec)

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

mysql> SAVE ADMIN VARIABLES TO DISK;
Query OK, 49 rows affected (0.00 sec)

mysql> select * from global_variables;
+----------------------------------------------------------------------+--------------------------------------------------+
| variable_name                                                        | variable_value                                   |
+----------------------------------------------------------------------+--------------------------------------------------+
| mysql-default_charset                                                | utf8                                             |
| mysql-default_collation_connection                                   | utf8_general_ci                                  |
| mysql-shun_on_failures                                               | 5                                                |
| mysql-shun_recovery_time_sec                                         | 10                                               |
| mysql-unshun_algorithm                                               | 0                                                |
| mysql-query_retries_on_failure                                       | 1                                                |
| mysql-client_host_cache_size                                         | 0                                                |
| mysql-client_host_error_counts                                       | 0                                                |
| mysql-connect_retries_delay                                          | 1                                                |
| mysql-connection_delay_multiplex_ms                                  | 0                                                |
| mysql-connection_max_age_ms                                          | 0                                                |
| mysql-connect_timeout_client                                         | 10000                                            |
| mysql-connect_timeout_server_max                                     | 10000                                            |
| mysql-enable_client_deprecate_eof                                    | true                                             |
| mysql-enable_server_deprecate_eof                                    | true                                             |
| mysql-enable_load_data_local_infile                                  | false                                            |
| mysql-eventslog_filename                                             |                                                  |
| mysql-eventslog_filesize                                             | 104857600                                        |
| mysql-eventslog_default_log                                          | 0                                                |
| mysql-eventslog_format                                               | 1                                                |
| mysql-auditlog_filename                                              |                                                  |
| mysql-auditlog_filesize                                              | 104857600                                        |
| mysql-handle_unknown_charset                                         | 1                                                |
| mysql-free_connections_pct                                           | 10                                               |
| mysql-connection_warming                                             | false                                            |
| mysql-session_idle_ms                                                | 1                                                |
| mysql-have_ssl                                                       | true                                             |
| mysql-log_mysql_warnings_enabled                                     | false                                            |
| mysql-monitor_enabled                                                | true                                             |
| mysql-monitor_connect_timeout                                        | 600                                              |
| mysql-monitor_ping_max_failures                                      | 3                                                |
| mysql-monitor_ping_timeout                                           | 1000                                             |
| mysql-monitor_aws_rds_topology_discovery_interval                    | 1000                                             |
| mysql-monitor_read_only_max_timeout_count                            | 3                                                |
| mysql-monitor_replication_lag_group_by_host                          | false                                            |
| mysql-monitor_replication_lag_interval                               | 10000                                            |
| mysql-monitor_replication_lag_timeout                                | 1000                                             |
| mysql-monitor_replication_lag_count                                  | 1                                                |
| mysql-monitor_groupreplication_healthcheck_interval                  | 5000                                             |
| mysql-monitor_groupreplication_healthcheck_timeout                   | 800                                              |
| mysql-monitor_groupreplication_healthcheck_max_timeout_count         | 3                                                |
| mysql-monitor_groupreplication_max_transactions_behind_count         | 3                                                |
| mysql-monitor_groupreplication_max_transactions_behind_for_read_only | 1                                                |
| mysql-monitor_galera_healthcheck_interval                            | 5000                                             |
| mysql-monitor_galera_healthcheck_timeout                             | 800                                              |
| mysql-monitor_galera_healthcheck_max_timeout_count                   | 3                                                |
| mysql-monitor_replication_lag_use_percona_heartbeat                  |                                                  |
| mysql-monitor_query_interval                                         | 60000                                            |
| mysql-monitor_query_timeout                                          | 100                                              |
| mysql-monitor_slave_lag_when_null                                    | 60                                               |
| mysql-monitor_threads_min                                            | 8                                                |
| mysql-monitor_threads_max                                            | 128                                              |
| mysql-monitor_threads_queue_maxsize                                  | 128                                              |
| mysql-monitor_local_dns_cache_ttl                                    | 300000                                           |
| mysql-monitor_local_dns_cache_refresh_interval                       | 60000                                            |
| mysql-monitor_local_dns_resolver_queue_maxsize                       | 128                                              |
| mysql-monitor_wait_timeout                                           | true                                             |
| mysql-monitor_writer_is_also_reader                                  | true                                             |
| mysql-max_allowed_packet                                             | 67108864                                         |
| mysql-tcp_keepalive_time                                             | 120                                              |
| mysql-use_tcp_keepalive                                              | true                                             |
| mysql-automatic_detect_sqli                                          | false                                            |
| mysql-firewall_whitelist_enabled                                     | false                                            |
| mysql-firewall_whitelist_errormsg                                    | Firewall blocked this query                      |
| mysql-throttle_connections_per_sec_to_hostgroup                      | 1000000                                          |
| mysql-max_transaction_idle_time                                      | 14400000                                         |
| mysql-max_transaction_time                                           | 14400000                                         |
| mysql-multiplexing                                                   | true                                             |
| mysql-log_unhealthy_connections                                      | true                                             |
| mysql-enforce_autocommit_on_reads                                    | false                                            |
| mysql-autocommit_false_not_reusable                                  | false                                            |
| mysql-autocommit_false_is_transaction                                | false                                            |
| mysql-verbose_query_error                                            | false                                            |
| mysql-hostgroup_manager_verbose                                      | 1                                                |
| mysql-binlog_reader_connect_retry_msec                               | 3000                                             |
| mysql-threshold_query_length                                         | 524288                                           |
| mysql-threshold_resultset_size                                       | 4194304                                          |
| mysql-query_digests_max_digest_length                                | 2048                                             |
| mysql-query_digests_max_query_length                                 | 65000                                            |
| mysql-query_digests_grouping_limit                                   | 3                                                |
| mysql-query_digests_groups_grouping_limit                            | 10                                               |
| mysql-query_rules_fast_routing_algorithm                             | 1                                                |
| mysql-wait_timeout                                                   | 28800000                                         |
| mysql-throttle_max_bytes_per_second_to_client                        | 0                                                |
| mysql-throttle_ratio_server_to_client                                | 0                                                |
| mysql-max_stmts_per_connection                                       | 20                                               |
| mysql-max_stmts_cache                                                | 10000                                            |
| mysql-mirror_max_concurrency                                         | 16                                               |
| mysql-mirror_max_queue_length                                        | 32000                                            |
| mysql-default_max_latency_ms                                         | 1000                                             |
| mysql-query_processor_iterations                                     | 0                                                |
| mysql-query_processor_regex                                          | 1                                                |
| mysql-set_query_lock_on_hostgroup                                    | 1                                                |
| mysql-set_parser_algorithm                                           | 2                                                |
| mysql-reset_connection_algorithm                                     | 2                                                |
| mysql-auto_increment_delay_multiplex                                 | 5                                                |
| mysql-auto_increment_delay_multiplex_timeout_ms                      | 10000                                            |
| mysql-long_query_time                                                | 1000                                             |
| mysql-query_cache_size_MB                                            | 256                                              |
| mysql-query_cache_soft_ttl_pct                                       | 0                                                |
| mysql-query_cache_handle_warnings                                    | 0                                                |
| mysql-poll_timeout_on_failure                                        | 100                                              |
| mysql-server_capabilities                                            | 569867                                           |
| mysql-keep_multiplexing_variables                                    | tx_isolation,transaction_isolation,version       |
| mysql-default_authentication_plugin                                  | caching_sha2_password                            |
| mysql-kill_backend_connection_when_disconnect                        | true                                             |
| mysql-client_session_track_gtid                                      | true                                             |
| mysql-session_idle_show_processlist                                  | true                                             |
| mysql-show_processlist_extended                                      | 0                                                |
| mysql-query_digests                                                  | true                                             |
| mysql-query_digests_lowercase                                        | false                                            |
| mysql-query_digests_replace_null                                     | false                                            |
| mysql-query_digests_no_digits                                        | false                                            |
| mysql-query_digests_normalize_digest_text                            | false                                            |
| mysql-query_digests_track_hostname                                   | false                                            |
| mysql-query_digests_keep_comment                                     | false                                            |
| mysql-parse_failure_logs_digest                                      | false                                            |
| mysql-servers_stats                                                  | true                                             |
| mysql-default_reconnect                                              | true                                             |
| mysql-ssl_p2s_ca                                                     | /var/lib/proxysql/wildcard_skylogic_com_ca.pem   |
| mysql-ssl_p2s_capath                                                 |                                                  |
| mysql-ssl_p2s_cert                                                   | /var/lib/proxysql/wildcard_skylogic_com_cert.pem |
| mysql-ssl_p2s_key                                                    | /var/lib/proxysql/wildcard_skylogic_com_key.pem  |
| mysql-ssl_p2s_cipher                                                 | ECDHE-RSA-AES256-SHA                             |
| mysql-ssl_p2s_crl                                                    |                                                  |
| mysql-ssl_p2s_crlpath                                                |                                                  |
| mysql-init_connect                                                   |                                                  |
| mysql-ldap_user_variable                                             |                                                  |
| mysql-add_ldap_user_comment                                          |                                                  |
| mysql-default_session_track_gtids                                    | OFF                                              |
| mysql-connpoll_reset_queue_length                                    | 50                                               |
| mysql-min_num_servers_lantency_awareness                             | 1000                                             |
| mysql-aurora_max_lag_ms_only_read_from_replicas                      | 2                                                |
| mysql-stats_time_backend_query                                       | false                                            |
| mysql-stats_time_query_processor                                     | false                                            |
| mysql-query_cache_stores_empty_result                                | true                                             |
| mysql-data_packets_history_size                                      | 0                                                |
| mysql-handle_warnings                                                | 1                                                |
| mysql-evaluate_replication_lag_on_servers_load                       | 1                                                |
| mysql-threads                                                        | 4                                                |
| mysql-max_connections                                                | 2048                                             |
| mysql-default_query_delay                                            | 0                                                |
| mysql-default_query_timeout                                          | 36000000                                         |
| mysql-have_compress                                                  | true                                             |
| mysql-poll_timeout                                                   | 2000                                             |
| mysql-interfaces                                                     | 0.0.0.0:6033                                     |
| mysql-default_schema                                                 | information_schema                               |
| mysql-stacksize                                                      | 1048576                                          |
| mysql-server_version                                                 | 8.0.36                                           |
| mysql-connect_timeout_server                                         | 3000                                             |
| mysql-monitor_username                                               | monitor                                          |
| mysql-monitor_password                                               | password                                         |
| mysql-monitor_history                                                | 600000                                           |
| mysql-monitor_connect_interval                                       | 60000                                            |
| mysql-monitor_ping_interval                                          | 10000                                            |
| mysql-monitor_read_only_interval                                     | 1500                                             |
| mysql-monitor_read_only_timeout                                      | 500                                              |
| mysql-ping_interval_server_msec                                      | 120000                                           |
| mysql-ping_timeout_server                                            | 500                                              |
| mysql-commands_stats                                                 | true                                             |
| mysql-sessions_sort                                                  | true                                             |
| mysql-connect_retries_on_failure                                     | 10                                               |
| admin-stats_credentials                                              | localuser:l0c@lU53r;remoteuser:r3m0+3U53r        |
| admin-stats_mysql_connections                                        | 60                                               |
| admin-stats_mysql_connection_pool                                    | 60                                               |
| admin-stats_mysql_query_cache                                        | 60                                               |
| admin-stats_mysql_query_digest_to_disk                               | 0                                                |
| admin-stats_system_cpu                                               | 60                                               |
| admin-stats_system_memory                                            | 60                                               |
| admin-telnet_admin_ifaces                                            | (null)                                           |
| admin-telnet_stats_ifaces                                            | (null)                                           |
| admin-refresh_interval                                               | 2000                                             |
| admin-read_only                                                      | false                                            |
| admin-vacuum_stats                                                   | true                                             |
| admin-version                                                        | 2.6.2-41-gb368fc9                                |
| admin-cluster_username                                               |                                                  |
| admin-cluster_password                                               |                                                  |
| admin-cluster_check_interval_ms                                      | 1000                                             |
| admin-cluster_check_status_frequency                                 | 10                                               |
| admin-cluster_mysql_query_rules_diffs_before_sync                    | 3                                                |
| admin-cluster_mysql_servers_diffs_before_sync                        | 3                                                |
| admin-cluster_mysql_users_diffs_before_sync                          | 3                                                |
| admin-cluster_proxysql_servers_diffs_before_sync                     | 3                                                |
| admin-cluster_mysql_variables_diffs_before_sync                      | 3                                                |
| admin-cluster_admin_variables_diffs_before_sync                      | 3                                                |
| admin-cluster_ldap_variables_diffs_before_sync                       | 3                                                |
| admin-cluster_mysql_query_rules_save_to_disk                         | true                                             |
| admin-cluster_mysql_servers_save_to_disk                             | true                                             |
| admin-cluster_mysql_users_save_to_disk                               | true                                             |
| admin-cluster_proxysql_servers_save_to_disk                          | true                                             |
| admin-cluster_mysql_variables_save_to_disk                           | true                                             |
| admin-cluster_admin_variables_save_to_disk                           | true                                             |
| admin-cluster_ldap_variables_save_to_disk                            | true                                             |
| admin-cluster_mysql_servers_sync_algorithm                           | 1                                                |
| admin-checksum_mysql_query_rules                                     | true                                             |
| admin-checksum_mysql_servers                                         | true                                             |
| admin-checksum_mysql_users                                           | true                                             |
| admin-checksum_mysql_variables                                       | true                                             |
| admin-checksum_admin_variables                                       | true                                             |
| admin-checksum_ldap_variables                                        | true                                             |
| admin-restapi_enabled                                                | false                                            |
| admin-restapi_port                                                   | 6070                                             |
| admin-web_enabled                                                    | false                                            |
| admin-web_port                                                       | 6080                                             |
| admin-web_verbosity                                                  | 0                                                |
| admin-prometheus_memory_metrics_interval                             | 61                                               |
| admin-coredump_generation_interval_ms                                | 30000                                            |
| admin-coredump_generation_threshold                                  | 10                                               |
| admin-ssl_keylog_file                                                |                                                  |
| admin-admin_credentials                                              | admin:password                       |
| admin-mysql_ifaces                                                   | 127.0.0.1:6032                                   |
+----------------------------------------------------------------------+--------------------------------------------------+
211 rows in set (0.00 sec)

mysql> exit
Bye
[root@hostname ~]# mysql -u localuser -P 6032 -h 127.0.0.1 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 153
Server version: 8.0.36 (ProxySQL Admin Module)

Copyright (c) 2009-2024 Percona LLC and/or its affiliates
Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye
[root@hostname ~]# mysql -u remoteuser -P 6032 -h 127.0.0.1 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 163
Server version: 8.0.36 (ProxySQL Admin Module)

Copyright (c) 2009-2024 Percona LLC and/or its affiliates
Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye
[root@hostname ~]# systemctl restart proxysql
[root@hostname ~]# mysql -u localuser -P 6032 -h 127.0.0.1 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.36 (ProxySQL Admin Module)

Copyright (c) 2009-2024 Percona LLC and/or its affiliates
Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye
[root@hostname ~]# mysql -u remoteuser -P 6032 -h 127.0.0.1 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 8.0.36 (ProxySQL Admin Module)

Copyright (c) 2009-2024 Percona LLC and/or its affiliates
Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

The problem is that if I configure my real credentials (the one I can't share here) it doesn't work. It just replicates the steps I posted in my first message. As already reported, I am using the same character set (alphanumeric + symbols and uppercases) for both test and real credentials. So it looks like there is something wrong with my exact username/password combo.

Follows the logs:

2024-06-27 10:13:00 MySQL_Session.cpp:6056:handler___status_CONNECTING_CLIENT___STATE_SERVER_HANDSHAKE(): [ERROR] ProxySQL Error: Access denied for user 'telegraf'@'127.0.0.1' (using password: YES)
2024-06-27 10:13:00 MySQL_Session.cpp:6056:handler___status_CONNECTING_CLIENT___STATE_SERVER_HANDSHAKE(): [ERROR] ProxySQL Error: Access denied for user 'telegraf'@'127.0.0.1' (using password: YES)

gpipito commented 5 months ago

Further testing.

Here I am using my real username and (very) fake password:

mysql> select * from global_variables where variable_name like "%admin-stats%";
+----------------------------------------+-----------------------------------------+
| variable_name                          | variable_value                          |
+----------------------------------------+-----------------------------------------+
| admin-stats_credentials                | telegraf:paperino;remoteuser:r3m0+3U53r |
| admin-stats_mysql_connections          | 60                                      |
| admin-stats_mysql_connection_pool      | 60                                      |
| admin-stats_mysql_query_cache          | 60                                      |
| admin-stats_mysql_query_digest_to_disk | 0                                       |
| admin-stats_system_cpu                 | 60                                      |
| admin-stats_system_memory              | 60                                      |
+----------------------------------------+-----------------------------------------+
7 rows in set (0.00 sec)

mysql> exit
Bye
[root@hostname ~]# mysql -u telegraf -P 6032 -h 127.0.0.1 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 186
Server version: 8.0.36 (ProxySQL Admin Module)

Copyright (c) 2009-2024 Percona LLC and/or its affiliates
Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye
[root@hostname ~]# mysql -u remoteuser -P 6032 -h 127.0.0.1 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 213
Server version: 8.0.36 (ProxySQL Admin Module)

Copyright (c) 2009-2024 Percona LLC and/or its affiliates
Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye
[root@hostname ~]# systemctl restart proxysql
[root@hostname ~]# mysql -u telegraf -P 6032 -h 127.0.0.1 -p
Enter password: 
ERROR 1045 (28000): ProxySQL Error: Access denied for user 'telegraf'@'127.0.0.1' (using password: YES)
[root@hostname ~]# mysql -u telegraf -P 6032 -h 127.0.0.1 -p
Enter password: 
ERROR 1045 (28000): ProxySQL Error: Access denied for user 'telegraf'@'127.0.0.1' (using password: YES)
[root@hostname ~]# mysql -u remoteuser -P 6032 -h 127.0.0.1 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 8.0.36 (ProxySQL Admin Module)

Copyright (c) 2009-2024 Percona LLC and/or its affiliates
Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye
[root@hostname ~]# mysql -u telegraf -P 6032 -h 127.0.0.1 -p
Enter password: 
ERROR 1045 (28000): ProxySQL Error: Access denied for user 'telegraf'@'127.0.0.1' (using password: YES)

It looks like I can't use "telegraf" as my username with multiple admin-stats_credentials.

gpipito commented 5 months ago

Hi,

I found the problem:

users defined in admin-admin_credentials or admin-stats_credentials cannot be used also in mysql_users table.

https://proxysql.com/documentation/global-variables/admin-variables/#admin-admin_credentials

Closing the issue. Thanks for your support.

sysown / proxysql

Multiple admin-stats_credentials not working after proxysql restart #4575