Closed ejellard closed 5 years ago
ejellard
commented
5 years ago Just to add - I've now tried this on a "normal" non-virtualbox machine and it works fine. But on a clean ubuntu/xenial64 vagrant machine, it is reliably breaking as above. Very, very strange...
renecannao
commented
5 years ago Hi @ejellard , can you share Vagrantfile , so we try to reproduce it?
I want to make sure we run the same OS version, as well as PHP and client library versions.
Furthermore, does this issue happen with a specific password, or a "random" password like password or alphabeticalcharactersonly makes it fail too?
md-mkane
commented
5 years ago I am seeing this behaviour also. I am finding that if I specify a database when connecting, the intermittent errors do not occur. I'd be interested to know what happens if @ejellard changes his example above to this:
$dbconn = @mysqli_connect('127.0.0.1:6033', 'web-user', 'alphabeticalcharactersonly', 'database');
Without the database name when connecting, I find that I get the auth errors about 7% of the time.
ejellard
commented
5 years ago Hi,
Apologies for the delay - my mail filters github emails.
I can sadly reproduce this on a plain ubuntu box too (16.04, fresh install, with some standard packages installed) - so turns out this isn't just vagrant.
RE passwords - it fails with anything - my password had lowercase letters and numbers only.
However, @md-mkane 's fix is brilliant - works a treat and good enough for us, albeit somewhat strange!!!!
Thanks, Ed
Jacq
commented
5 years ago Hi, We are suffering what it seems the same issue, in our case we have several applications in several languages framework and this problem appeared just in one old PHP 5.6.38 application.
We tested both with mysql_connect and mysqli and we are having random access denied (approx 7% of tries), only for this application, there is no framework no other connections just a php file with one connection.
This is the only message logged (user and ip have been removed):
2019-03-07 12:43:39 MySQL_Session.cpp:3966:handler___status_CONNECTING_CLIENT___STATE_SERVER_HANDSHAKE(): [ERROR] ProxySQL Error: Access denied for user '<user>'@'<ip>' (using password: NO)In our case this is happening in a docker environment using docker hub image "php:5.6-apache"
Cheers, Jacq
Jacq
commented
5 years ago Hi again, We found our issue, previously our petitions were managed by 5 haproxy instances to 3 mariadb galera cluster without issues. When we have replace haproxy with proxysql we found this issue. More debugging lead that requests to mariadb "galera cluster instance 1" are using a different source IP (which is not allowed) so they are blocked. So this is not an issue in our case related to proxysql but to the different network addressing.
The "old php application" container is living in the same host as "galera cluster instance 1", we need still check why this problem was not so common with haproxy, probably because a different routing was configured and the "old php application"was not doing requests to "galera cluster instance 1" never.
The issue was more difficult to identify with proxysql due to it's balance routing that in most queries did not caused the error.
So ignore my previous comment, it seems this is not your situation as you always use the same source 127.0.0.1 address. Cheers
Jacq
commented
5 years ago Here I am again fixed the issue mentioned in the previous post, but still getting random user denied. We identified this issue in 2 of the 3 proxies. We are opening mysql users for all source ips "%" to isolated the issue and verify if it is really a "access denied due to the source ip" I will keep you posted if we are able to fix this.
Jacq
commented
5 years ago We cannot reproduce the error using php either mysqli or PDO, but we still suffer with the original mysql_connect intermitent "user denied" issue. So, as mysql_connect is really deprecated by now, we can argue that it is not reliable and probably there is an issue of its connect implementation that is not fully compliant with what proxysql is expecting.
The issue is still present but it is not practically for us spending more resources on catching a old bug probably related to the deprecated mysql_connect, we are for this old application querying directly one of the mariadb instances and avoiding the proxysql for this case.
If someone cross this issue in the future and finds the actual issue we can check the possible fix and confirm the resolution.
Cheers
mrfaiz
commented
5 years ago Hello, We decided to replace MaxScale with ProxySQL for extra caching facility. Everything was working fine except 4% ' Access denied for user' error. The exact error is
MySQL_Session.cpp:3966:handler___status_CONNECTING_CLIENT___STATE_SERVER_HANDSHAKE(): [ERROR] ProxySQL Error: Access denied for user 'user'@'ip.ip.ip.ip' (using password: YES)
And my global variable table is following (connection related)
ernstae
commented
5 years ago Unfortunately, I can also confirm that this error has been showing intermittently for my installations of 2.0.2 on CentOS 7.6
When I run an infinite loop of connections, I can reproduce this 100% on proxysql-2.0.2-1.x86_64
MySQL_Session.cpp:3966:handler___status_CONNECTING_CLIENT___STATE_SERVER_HANDSHAKE(): [ERROR] ProxySQL Error: Access denied for user 'username'@'ipaddress' (using password: YESReviewing our upgrade logs, this started appearing in version 1.4.13-1.1.el7 and is present in 2.0.2
renecannao
commented
5 years ago Which version of mysql client library are you using? Commit 8450d85e0ec36e0088e72f623ec495b079d12db5 is likely to be the fix
ernstae
commented
5 years ago Hi @renecannao - Thanks for the quick attention to this.
We have a number of client libraries being used, but I was able to validate the issue with the 8.0.15 OSX homebrew-provided mysql 8.0.15 library.
mysql --version
mysql Ver 8.0.15 for osx10.14 on x86_64 (Homebrew)I believe the majority of clients using this service are k8s pods using github.com/go-sql-driver/mysql and Centos 7 base images with the MySQL community client library 5.7.2[45] binaries.
renecannao
commented
5 years ago Hi @ernstae . Ok, it you are using client library 8.0.15, it is likely you are hitting the bug fixed in commit 8450d85e0ec36e0088e72f623ec495b079d12db5 . Around 7.8% of the authentication requests using 8.0 library should fail.
renecannao
commented
5 years ago Fixed in 2.0.3 . Closing
satya256
commented
6 months ago Hi @renecannao
I am trying to simulate the load on proxysql using the sysbench.
OS : 22.04.1-Ubuntu
sysbench version: sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)
MySQL server version: 8.0.36
and also set the following
set global validate_password.policy = LOW;
set global validate_password.length = 2;When I tried to prepare the data using sysbench I found similar issue reported here. The proxysql throwing intermittent access denied errors.
sysbench /usr/share/sysbench/oltp_insert.lua --mysql-db=sysbenchdb \ --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=appuser \ --mysql-password=appuser --db-driver=mysql --threads=10 --tables=25 \ --table-size=2500 prepare
2024-04-02 16:46:40 mysql_connection.cpp:1151:handler(): [ERROR] Failed to mysql_real_connect() on 1:127.0.0.1:3307 , FD (Conn:40 , MyDS:40) , 1045: Access denied for user 'appuser'@'localhost' (using password: YES).
2024-04-02 16:46:40 mysql_connection.cpp:1151:handler(): [ERROR] Failed to mysql_real_connect() on 1:127.0.0.1:3307 , FD (Conn:43 , MyDS:43) , 1045: Access denied for user 'appuser'@'localhost' (using password: YES).
2024-04-02 16:46:40 mysql_connection.cpp:1151:handler(): [ERROR] Failed to mysql_real_connect() on 1:127.0.0.1:3307 , FD (Conn:40 , MyDS:40) , 1045: Access denied for user 'appuser'@'localhost' (using password: YES).
2024-04-02 16:46:40 mysql_connection.cpp:1151:handler(): [ERROR] Failed to mysql_real_connect() on 1:127.0.0.1:3307 , FD (Conn:43 , MyDS:43) , 1045: Access denied for user 'appuser'@'localhost' (using password: YES).
2024-04-02 16:46:40 mysql_connection.cpp:1151:handler(): [ERROR] Failed to mysql_real_connect() on 1:127.0.0.1:3307 , FD (Conn:40 , MyDS:40) , 1045: Access denied for user 'appuser'@'localhost' (using password: YES).
2024-04-02 16:46:42 mysql_connection.cpp:1151:handler(): [ERROR] Failed to mysql_real_connect() on 1:127.0.0.1:3307 , FD (Conn:40 , MyDS:40) , 1045: Access denied for user 'appuser'@'localhost' (using password: YES).
2024-04-02 16:46:42 mysql_connection.cpp:1151:handler(): [ERROR] Failed to mysql_real_connect() on 1:127.0.0.1:3307 , FD (Conn:40 , MyDS:40) , 1045: Access denied for user 'appuser'@'localhost' (using password: YES).I found no errors when I tried to hit the db directly and no errors were found. Hence I suspect, the issue is with proxysql.
result of show global variables
+----------------------------------------------------------------------+--------------------------------------------+
| Variable_name | Value |
+----------------------------------------------------------------------+--------------------------------------------+
| admin-admin_credentials | admin:admin |
| admin-checksum_admin_variables | true |
| admin-checksum_ldap_variables | true |
| admin-checksum_mysql_query_rules | true |
| admin-checksum_mysql_servers | true |
| admin-checksum_mysql_users | true |
| admin-checksum_mysql_variables | true |
| admin-cluster_admin_variables_diffs_before_sync | 3 |
| admin-cluster_admin_variables_save_to_disk | true |
| admin-cluster_check_interval_ms | 1000 |
| admin-cluster_check_status_frequency | 10 |
| admin-cluster_ldap_variables_diffs_before_sync | 3 |
| admin-cluster_ldap_variables_save_to_disk | true |
| admin-cluster_mysql_query_rules_diffs_before_sync | 3 |
| admin-cluster_mysql_query_rules_save_to_disk | true |
| admin-cluster_mysql_servers_diffs_before_sync | 3 |
| admin-cluster_mysql_servers_save_to_disk | true |
| admin-cluster_mysql_servers_sync_algorithm | 1 |
| admin-cluster_mysql_users_diffs_before_sync | 3 |
| admin-cluster_mysql_users_save_to_disk | true |
| admin-cluster_mysql_variables_diffs_before_sync | 3 |
| admin-cluster_mysql_variables_save_to_disk | true |
| admin-cluster_password | |
| admin-cluster_proxysql_servers_diffs_before_sync | 3 |
| admin-cluster_proxysql_servers_save_to_disk | true |
| admin-cluster_username | |
| admin-coredump_generation_interval_ms | 30000 |
| admin-coredump_generation_threshold | 10 |
| admin-mysql_ifaces | 0.0.0.0:6032 |
| admin-prometheus_memory_metrics_interval | 61 |
| admin-read_only | false |
| admin-refresh_interval | 2000 |
| admin-restapi_enabled | false |
| admin-restapi_port | 6070 |
| admin-ssl_keylog_file | |
| admin-stats_credentials | stats:stats |
| admin-stats_mysql_connection_pool | 60 |
| admin-stats_mysql_connections | 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-vacuum_stats | true |
| admin-version | 2.6.1-31-g9d788c0 |
| admin-web_enabled | false |
| admin-web_port | 6080 |
| admin-web_verbosity | 0 |
| 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 | 5000 |
| mysql-connect_timeout_server_max | 10000 |
| mysql-connection_delay_multiplex_ms | 0 |
| mysql-connection_max_age_ms | 0 |
| mysql-connection_warming | true |
| 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_general_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 | false |
| 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 | |
| mysql-eventslog_filesize | 104857600 |
| mysql-eventslog_format | 1 |
| mysql-firewall_whitelist_enabled | false |
| mysql-firewall_whitelist_errormsg | Firewall blocked this query |
| mysql-free_connections_pct | 100 |
| 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-interfaces | 0.0.0.0:6033 |
| 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 | false |
| mysql-log_unhealthy_connections | true |
| mysql-long_query_time | 1000 |
| mysql-max_allowed_packet | 67108864 |
| mysql-max_connections | 2048 |
| mysql-max_stmts_cache | 10000 |
| mysql-max_stmts_per_connection | 20 |
| mysql-max_transaction_idle_time | 14400000 |
| mysql-max_transaction_time | 14400000 |
| mysql-min_num_servers_lantency_awareness | 1000 |
| mysql-mirror_max_concurrency | 16 |
| mysql-mirror_max_queue_length | 32000 |
| mysql-monitor_connect_interval | 60000 |
| mysql-monitor_connect_timeout | 600 |
| mysql-monitor_enabled | true |
| mysql-monitor_galera_healthcheck_interval | 5000 |
| mysql-monitor_galera_healthcheck_max_timeout_count | 3 |
| mysql-monitor_galera_healthcheck_timeout | 800 |
| mysql-monitor_groupreplication_healthcheck_interval | 5000 |
| mysql-monitor_groupreplication_healthcheck_max_timeout_count | 3 |
| mysql-monitor_groupreplication_healthcheck_timeout | 800 |
| mysql-monitor_groupreplication_max_transactions_behind_count | 3 |
| mysql-monitor_groupreplication_max_transactions_behind_for_read_only | 1 |
| mysql-monitor_history | 600000 |
| mysql-monitor_local_dns_cache_refresh_interval | 60000 |
| mysql-monitor_local_dns_cache_ttl | 300000 |
| mysql-monitor_local_dns_resolver_queue_maxsize | 128 |
| mysql-monitor_password | monitor |
| mysql-monitor_ping_interval | 10000 |
| mysql-monitor_ping_max_failures | 3 |
| mysql-monitor_ping_timeout | 1000 |
| mysql-monitor_query_interval | 60000 |
| mysql-monitor_query_timeout | 100 |
| mysql-monitor_read_only_interval | 1500 |
| mysql-monitor_read_only_max_timeout_count | 3 |
| mysql-monitor_read_only_timeout | 500 |
| mysql-monitor_replication_lag_count | 1 |
| mysql-monitor_replication_lag_group_by_host | false |
| mysql-monitor_replication_lag_interval | 10000 |
| mysql-monitor_replication_lag_timeout | 1000 |
| mysql-monitor_replication_lag_use_percona_heartbeat | |
| mysql-monitor_slave_lag_when_null | 60 |
| mysql-monitor_threads_max | 128 |
| mysql-monitor_threads_min | 8 |
| mysql-monitor_threads_queue_maxsize | 128 |
| mysql-monitor_username | monitor |
| mysql-monitor_wait_timeout | true |
| mysql-monitor_writer_is_also_reader | true |
| 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 | 256 |
| 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 | 2048 |
| 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 | 569867 |
| mysql-server_version | 8.0.36 |
| 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 | 120 |
| mysql-threads | 4 |
| 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 | 0 |
| mysql-use_tcp_keepalive | true |
| mysql-verbose_query_error | false |
| mysql-wait_timeout | 28800000 |
+----------------------------------------------------------------------+--------------------------------------------+and somehow proxysql can process the connections if I use the --mysql-host as localhost instead of 127.0.0.1
sysbench /usr/share/sysbench/oltp_insert.lua --mysql-db=sysbenchdb \
--mysql-host=localhost --mysql-port=3306 --mysql-user=appuser \
--mysql-password=appuser --db-driver=mysql --threads=10 --tables=25 \
--table-size=2500 prepare
We run extremely extensive testing at every PR , we do not see these errors. Therefore we need a reproducible test case please: something in your setup is triggering the issue. No matter if it is a bug or a misconfiguration, we need a reproducible test case.
I'm testing a future implementation of proxysql on ubuntu/xenial64 vagrant/virtualbox and it mostly works, however, there is an intermittent issue authorisation error at the ProxySQL level.
Test script:
Output:
When I had mysql_servers in place, logging errors, there weren't access denied messages in the mysql logs. I removed mysql_servers to double-check, and the error still happens, so imho, it's happening at the ProxySQL layer.
What I do not understand is why sometimes it says it is NOT using a password, and how/why, when a password is being supplied, it is coming back as access denied.
It's also fairly random, given the number of '.' on the end of each line changes.
It gets stranger...
When using PHP's
mysql_connect()function, ifmysql_close()is called as below, then the errors as above happen. If the connection is NOT closed, it always connects perfectly!So I think there's a bug somewhere - it's totally repeatable on two separate virtualboxes (albeit both came from the same base box, so identical environments)
If useful: