hdep
commented
2 months ago Hello,
we move back to version 2.5.5-1.2.bullseye issue is gone.
Closed hdep closed 1 month ago
hdep
commented
2 months ago Hello,
we move back to version 2.5.5-1.2.bullseye issue is gone.
renecannao
commented
2 months ago Hi @hdep . Thank you for the detailed report!
Can you please share the output of this?
SELECT * FROM global_variables
WHERE variable_name LIKE 'mysql-%' AND variable_name NOT LIKE '%monitor%';We will investigate and report back.
Thanks!
hdep
commented
2 months ago Hello,
you are welcome, I did my best ;-) Here is the output from current 2.5.5 I currently don't have anymore 2.6.3 available.
ProxySQL>SELECT * FROM global_variables
WHERE variable_name LIKE 'mysql-%' AND variable_name NOT LIKE '%monitor%';
+-------------------------------------------------+--------------------------------------------+
| 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 | 1000 |
| mysql-client_host_cache_size | 0 |
| mysql-client_host_error_counts | 0 |
| mysql-connect_retries_on_failure | 10 |
| 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 | false |
| mysql-log_mysql_warnings_enabled | false |
| mysql-max_allowed_packet | 67108864 |
| mysql-tcp_keepalive_time | 0 |
| mysql-use_tcp_keepalive | false |
| 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 | false |
| mysql-enforce_autocommit_on_reads | false |
| mysql-autocommit_false_not_reusable | false |
| mysql-autocommit_false_is_transaction | false |
| mysql-verbose_query_error | true |
| 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 | 0 |
| 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-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-ping_interval_server_msec | 10000 |
| mysql-poll_timeout_on_failure | 100 |
| mysql-keep_multiplexing_variables | tx_isolation,transaction_isolation,version |
| 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-servers_stats | true |
| mysql-default_reconnect | true |
| mysql-ssl_p2s_ca | |
| mysql-ssl_p2s_capath | |
| mysql-ssl_p2s_cert | |
| mysql-ssl_p2s_key | |
| mysql-ssl_p2s_cipher | |
| mysql-ssl_p2s_crl | |
| mysql-ssl_p2s_crlpath | |
| mysql-init_connect | |
| mysql-ldap_user_variable | |
| mysql-add_ldap_user_comment | |
| mysql-default_tx_isolation | READ-COMMITTED |
| 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-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:3306;/tmp/proxysql.sock |
| mysql-default_schema | information_schema |
| mysql-stacksize | 1048576 |
| mysql-server_version | 5.1.30 |
| mysql-connect_timeout_server | 10000 |
| mysql-ping_timeout_server | 200 |
| mysql-commands_stats | true |
| mysql-sessions_sort | true |
| mysql-server_capabilities | 569899 |
| mysql-query_cache_soft_ttl_pct | 0 |
| mysql-parse_failure_logs_digest | false |
| mysql-query_rules_fast_routing_algorithm | 1 |
| mysql-set_parser_algorithm | 1 |
| mysql-data_packets_history_size | 0 |
+-------------------------------------------------+--------------------------------------------+
122 rows in set (0,00 sec)you are welcome, I did my best ;-)
Thanks!!
Here is the output from current 2.5.5
Thanks!
I currently don't have anymore 2.6.3 available.
Can I assume you didn't make any change related to authentication?
Specifically, 2.6.0 introduces caching_sha2_password and some changes in settings, therefore in order to try to reproduce it we need to know if any default was changed or not.
hdep
commented
2 months ago Can I assume you didn't make any change related to authentication? Specifically, 2.6.0 introduces caching_sha2_password and some changes in settings, therefore in order to try to reproduce it we need to know if any default was changed or not.
Indeed we saw this point in release notes afterwards. But no from our point of view we just upgraded without any modification from proxysql.
I don't know if this helps but I saw on the packet capture we are still in mysql_native_password :
umitdogu
commented
1 month ago @renecannao @hdep is there any improvement regarding this ticket? We are also facing this issue.
EagleEyeJohn
commented
1 month ago @renecannao having seen 2.6.x appear stable with no releases beyond 2.6.3 we were starting to roll it out today, but alas we too are encountering this error.
Admin> select version();
+--------------------+
| version() |
+--------------------+
| 2.6.3-107-gcdfcfdc |
+--------------------+Admin> SELECT * FROM global_variables
-> WHERE variable_name LIKE 'mysql-%' AND variable_name NOT LIKE '%monitor%';
+-------------------------------------------------+--------------------------------------------+
| variable_name | variable_value |
+-------------------------------------------------+--------------------------------------------+
| mysql-interfaces | 0.0.0.0:6033 |
| mysql-add_ldap_user_comment | |
| mysql-auditlog_filename | |
| mysql-auditlog_filesize | 104857600 |
| mysql-aurora_max_lag_ms_only_read_from_replicas | 2 |
| mysql-auto_increment_delay_multiplex | 5 |
| mysql-auto_increment_delay_multiplex_timeout_ms | 10000 |
| mysql-autocommit_false_is_transaction | false |
| mysql-autocommit_false_not_reusable | false |
| mysql-automatic_detect_sqli | false |
| mysql-binlog_reader_connect_retry_msec | 3000 |
| mysql-client_host_cache_size | 0 |
| mysql-client_host_error_counts | 0 |
| mysql-client_session_track_gtid | true |
| mysql-commands_stats | true |
| mysql-connect_retries_delay | 1 |
| mysql-connect_retries_on_failure | 10 |
| mysql-connect_timeout_client | 10000 |
| mysql-connect_timeout_server | 3000 |
| mysql-connect_timeout_server_max | 10000 |
| mysql-connection_delay_multiplex_ms | 0 |
| mysql-connection_max_age_ms | 0 |
| mysql-connection_warming | false |
| mysql-connpoll_reset_queue_length | 50 |
| mysql-data_packets_history_size | 0 |
| mysql-default_authentication_plugin | mysql_native_password |
| mysql-default_charset | utf8 |
| mysql-default_collation_connection | utf8_unicode_ci |
| mysql-default_max_latency_ms | 1000 |
| mysql-default_query_delay | 0 |
| mysql-default_query_timeout | 36000000 |
| mysql-default_reconnect | true |
| mysql-default_schema | information_schema |
| mysql-default_session_track_gtids | OFF |
| mysql-enable_client_deprecate_eof | true |
| mysql-enable_load_data_local_infile | true |
| mysql-enable_server_deprecate_eof | true |
| mysql-enforce_autocommit_on_reads | false |
| mysql-evaluate_replication_lag_on_servers_load | 1 |
| mysql-eventslog_default_log | 0 |
| mysql-eventslog_filename | /var/log/proxysql/events_log |
| mysql-eventslog_filesize | 104857600 |
| mysql-eventslog_format | 2 |
| mysql-firewall_whitelist_enabled | false |
| mysql-firewall_whitelist_errormsg | Firewall blocked this query |
| mysql-free_connections_pct | 3 |
| mysql-handle_unknown_charset | 1 |
| mysql-handle_warnings | 1 |
| mysql-have_compress | true |
| mysql-have_ssl | true |
| mysql-hostgroup_manager_verbose | 1 |
| mysql-init_connect | |
| mysql-keep_multiplexing_variables | tx_isolation,transaction_isolation,version |
| mysql-kill_backend_connection_when_disconnect | true |
| mysql-ldap_user_variable | |
| mysql-log_mysql_warnings_enabled | true |
| mysql-log_unhealthy_connections | true |
| mysql-long_query_time | 1000 |
| mysql-max_allowed_packet | 16777216 |
| mysql-max_connections | 16384 |
| mysql-max_stmts_cache | 10000 |
| mysql-max_stmts_per_connection | 20 |
| mysql-max_transaction_idle_time | 14400000 |
| mysql-max_transaction_time | 1728000000 |
| mysql-min_num_servers_lantency_awareness | 1000 |
| mysql-mirror_max_concurrency | 16 |
| mysql-mirror_max_queue_length | 32000 |
| mysql-multiplexing | true |
| mysql-parse_failure_logs_digest | false |
| mysql-ping_interval_server_msec | 120000 |
| mysql-ping_timeout_server | 500 |
| mysql-poll_timeout | 2000 |
| mysql-poll_timeout_on_failure | 100 |
| mysql-query_cache_handle_warnings | 0 |
| mysql-query_cache_size_MB | 512 |
| mysql-query_cache_soft_ttl_pct | 0 |
| mysql-query_cache_stores_empty_result | true |
| mysql-query_digests | true |
| mysql-query_digests_grouping_limit | 3 |
| mysql-query_digests_groups_grouping_limit | 10 |
| mysql-query_digests_keep_comment | false |
| mysql-query_digests_lowercase | false |
| mysql-query_digests_max_digest_length | 8192 |
| mysql-query_digests_max_query_length | 65000 |
| mysql-query_digests_no_digits | false |
| mysql-query_digests_normalize_digest_text | false |
| mysql-query_digests_replace_null | false |
| mysql-query_digests_track_hostname | false |
| mysql-query_processor_iterations | 0 |
| mysql-query_processor_regex | 1 |
| mysql-query_retries_on_failure | 1 |
| mysql-query_rules_fast_routing_algorithm | 1 |
| mysql-reset_connection_algorithm | 2 |
| mysql-server_capabilities | 569899 |
| mysql-server_version | 8.0.37-29 |
| mysql-servers_stats | true |
| mysql-session_idle_ms | 1 |
| mysql-session_idle_show_processlist | true |
| mysql-sessions_sort | true |
| mysql-set_parser_algorithm | 2 |
| mysql-set_query_lock_on_hostgroup | 1 |
| mysql-show_processlist_extended | 0 |
| mysql-shun_on_failures | 5 |
| mysql-shun_recovery_time_sec | 10 |
| mysql-ssl_p2s_ca | |
| mysql-ssl_p2s_capath | |
| mysql-ssl_p2s_cert | |
| mysql-ssl_p2s_cipher | |
| mysql-ssl_p2s_crl | |
| mysql-ssl_p2s_crlpath | |
| mysql-ssl_p2s_key | |
| mysql-stacksize | 1048576 |
| mysql-stats_time_backend_query | false |
| mysql-stats_time_query_processor | false |
| mysql-tcp_keepalive_time | 60 |
| mysql-threshold_query_length | 524288 |
| mysql-threshold_resultset_size | 4194304 |
| mysql-throttle_connections_per_sec_to_hostgroup | 1000000 |
| mysql-throttle_max_bytes_per_second_to_client | 0 |
| mysql-throttle_ratio_server_to_client | 0 |
| mysql-unshun_algorithm | 1 |
| mysql-use_tcp_keepalive | true |
| mysql-verbose_query_error | true |
| mysql-wait_timeout | 28800000 |
| mysql-threads | 4 |
+-------------------------------------------------+--------------------------------------------+
125 rows in set (0.00 sec)2024-08-15 18:01:39 MySQL_Session.cpp:7437:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_CHANGE_USER(): [ERROR] ProxySQL Error: Access denied for user 'user1'@'10.999.999.999' (using password: YES)
2024-08-15 18:02:09 MySQL_Session.cpp:7437:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_CHANGE_USER(): [ERROR] ProxySQL Error: Access denied for user 'user2'@'10.999.999.999' (using password: YES)
2024-08-15 19:57:30 MySQL_Session.cpp:7437:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_CHANGE_USER(): [ERROR] ProxySQL Error: Access denied for user 'user1'@'10.999.999.999' (using password: YES)
2024-08-15 19:58:00 MySQL_Session.cpp:7437:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_CHANGE_USER(): [ERROR] ProxySQL Error: Access denied for user 'user3'@'10.999.999.999' (using password: YES)
2024-08-15 19:58:31 MySQL_Session.cpp:7437:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_CHANGE_USER(): [ERROR] ProxySQL Error: Access denied for user 'user4'@'10.999.999.999' (using password: YES)These errors occur in PHP 8.2.13 (PHP-FPM) when we attempt to connect to ProxySQL using a persistent connection ('p:<hostname>')
$this->dbConn = new \mysqli($hostname, $this->user, $this->password, $this->database, $this->port);We run Percona Server
Server version: 8.0.37-29 Percona Server (GPL), Release 29, Revision 30dc4e71As @hdep noted, the issue goes away when persistent connections are not used.
hdep
commented
1 month ago @renecannao @hdep is there any improvement regarding this ticket? We are also facing this issue.
No news on my side, we rollback to 2.5.5, But I think this is "good news" if you also facing same issue this means that our assumption is right.
JavierJF
commented
1 month ago Hi @hdep, @EagleEyeJohn and others,
an issue has been created #4618 for the support of change_user in combination with caching_sha2_password. This issue may be related to that one, yet, it seems that you both say that you are experiencing issues with change_user without caching_sha2_password enable. This is something we haven't been able to reproduce, and should work as expected. To be more clear, change_user should work in v2.6.x the same as it did in v2.5.x when mysql-default_authentication_plugin=mysql_native_password if that's not the case for you. Could you please share any of the following?
mysql-* and admin-* variables? Just for completion.Thank you, Javier.
hdep
commented
1 month ago Hello @JavierJF
I have a tcpdump I took during the issue, can you share your e-mail and I will send you the tcpdump directly ?
JavierJF
commented
1 month ago Hi @hdep,
you can share the details with me at this email address: javier@proxysql.com.
Thanks, Javier.
EagleEyeJohn
commented
4 weeks ago Thank you @renecannao
Description
we upgraded from 2.5.5 to 2.6.3 since then our php application is working but generate huge amount of error logs in
/var/lib/proxysql/proxysql.logLog is :
User credential are correctly set into the application.
after debug, we figure that :
We only upgraded the proxysql, PHP code remain the same, so we try ton understand why 2.6.3 bring this behaviour.
PHP version
ProxySQL version 2.6.3 OS version Debian 11 The steps to reproduce the issue
2024-07-16 16:35:41 MySQL_Session.cpp:7437:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_CHANGE_USER(): [ERROR] ProxySQL Error: Access denied for user 'psql-master'@'10.207.201.217' (using password: YES)Note that you can also run a tcpdump on port 3306 where is code is run to see the access denied
Code without issue
feel free to askfor more details if needed.
thanks