Intermittent broken backend connections when using have_ssl=true
amickael opened this issue · 16 comments
amickael commented
Recently we have been trying to enable frontend SSL support for ProxySQL, i.e. have_ssl=true. We have had backend SSL enabled for a few weeks (use_ssl=1) without any issues, however when setting have_ssl=true we intermittently get the following errors:
MySQL_Session.cpp:4407:handler_minus1_ClientLibraryError(): [ERROR] Detected a broken connection while running query on (20,<redacted>,3306,1199180) , FD (Conn:583 , MyDS:583) , user <redacted> , last_used 19ms ago : 2013, Lost connection to MySQL server during query
MySQL_Session.cpp:1694:handler_again___status_PINGING_SERVER(): [ERROR] Detected a broken connection while during ping on (20,<redacted>,3306,272711) , FD (Conn:162 , MyDS:162) , user <redacted> , last_used 0ms ago : 2013, Lost connection to MySQL server during query
On the application side we use Django with mysqlclient / MySQLdb, and these errors are surfaced as:
MySQLdb._exceptions.OperationalError: (2026, 'TLS/SSL error: Success (0)')
Disabling frontend SSL immediately causes the errors to stop.
We are using version 2.5.5 and our ProxySQL configuration is as follows:
ProxySQL> SHOW MYSQL VARIABLES;
+----------------------------------------------------------------------+----------------------------------------------------------------+
| Variable_name | Value |
+----------------------------------------------------------------------+----------------------------------------------------------------+
| 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 | true |
| 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 | 1000 |
| mysql-connect_timeout_server_max | 10000 |
| mysql-connection_delay_multiplex_ms | 0 |
| mysql-connection_max_age_ms | 14400000 |
| mysql-connection_warming | false |
| mysql-connpoll_reset_queue_length | 1000 |
| mysql-data_packets_history_size | 0 |
| 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 | 60000 |
| mysql-default_reconnect | true |
| mysql-default_schema | information_schema |
| mysql-default_session_track_gtids | OFF |
| mysql-default_tx_isolation | READ-COMMITTED |
| mysql-enable_client_deprecate_eof | true |
| mysql-enable_load_data_local_infile | false |
| mysql-enable_server_deprecate_eof | true |
| mysql-enforce_autocommit_on_reads | true |
| 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 | 30 |
| mysql-handle_unknown_charset | 1 |
| mysql-have_compress | true |
| mysql-have_ssl | true |
| mysql-hostgroup_manager_verbose | 1 |
| mysql-init_connect | |
| mysql-interfaces | 0.0.0.0:3306;0.0.0.0:6033;0.0.0.0:6034;/proxysql/proxysql.sock |
| 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 | false |
| mysql-long_query_time | 1000 |
| mysql-max_allowed_packet | 67108864 |
| mysql-max_connections | 6000 |
| 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 | 8 |
| mysql-mirror_max_queue_length | 100 |
| mysql-monitor_connect_interval | 200000 |
| 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 | 60000 |
| 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 | <redacted> |
| mysql-monitor_ping_interval | 200000 |
| 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 | 1000 |
| mysql-monitor_read_only_max_timeout_count | 3 |
| mysql-monitor_read_only_timeout | 800 |
| 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 | proxysql |
| 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 | 10000 |
| mysql-ping_timeout_server | 200 |
| mysql-poll_timeout | 2000 |
| mysql-poll_timeout_on_failure | 100 |
| 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 | 0 |
| 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 | 571947 |
| mysql-server_version | 8.0.35 |
| mysql-servers_stats | true |
| mysql-session_idle_ms | 1 |
| mysql-session_idle_show_processlist | true |
| mysql-sessions_sort | true |
| mysql-set_parser_algorithm | 1 |
| 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 | 0 |
| 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 | false |
| mysql-verbose_query_error | true |
| mysql-wait_timeout | 28800000 |
+----------------------------------------------------------------------+----------------------------------------------------------------+
158 rows in set (0.002 sec)
ProxySQL> SELECT * FROM stats.stats_mysql_global ORDER BY Variable_Name;
+----------------------------------------------+----------------+
| Variable_Name | Variable_Value |
+----------------------------------------------+----------------+
| Access_Denied_Max_Connections | 0 |
| Access_Denied_Max_User_Connections | 0 |
| Access_Denied_Wrong_Password | 0 |
| Active_Transactions | 1 |
| Backend_query_time_nsec | 0 |
| Client_Connections_aborted | 0 |
| Client_Connections_connected | 464 |
| Client_Connections_created | 1851051 |
| Client_Connections_hostgroup_locked | 0 |
| Client_Connections_non_idle | 4 |
| Com_autocommit | 3399742 |
| Com_autocommit_filtered | 3396252 |
| Com_backend_change_user | 7 |
| Com_backend_init_db | 0 |
| Com_backend_set_names | 2699 |
| Com_backend_stmt_close | 0 |
| Com_backend_stmt_execute | 0 |
| Com_backend_stmt_prepare | 0 |
| Com_commit | 3632 |
| Com_commit_filtered | 172 |
| Com_frontend_init_db | 0 |
| Com_frontend_set_names | 0 |
| Com_frontend_stmt_close | 0 |
| Com_frontend_stmt_execute | 0 |
| Com_frontend_stmt_prepare | 0 |
| Com_frontend_use_db | 0 |
| Com_rollback | 30 |
| Com_rollback_filtered | 0 |
| ConnPool_get_conn_failure | 0 |
| ConnPool_get_conn_immediate | 7477 |
| ConnPool_get_conn_latency_awareness | 0 |
| ConnPool_get_conn_success | 30188323 |
| ConnPool_memory_bytes | 2567920 |
| GTID_consistent_queries | 0 |
| GTID_session_collected | 0 |
| Mirror_concurrency | 0 |
| Mirror_queue_length | 0 |
| MyHGM_myconnpoll_destroy | 87 |
| MyHGM_myconnpoll_get | 30180846 |
| MyHGM_myconnpoll_get_ok | 30180846 |
| MyHGM_myconnpoll_push | 30229951 |
| MyHGM_myconnpoll_reset | 2746 |
| MySQL_Monitor_Workers | 0 |
| MySQL_Monitor_Workers_Aux | 0 |
| MySQL_Monitor_Workers_Started | 0 |
| MySQL_Monitor_connect_check_ERR | 0 |
| MySQL_Monitor_connect_check_OK | 0 |
| MySQL_Monitor_dns_cache_lookup_success | 203 |
| MySQL_Monitor_dns_cache_queried | 203 |
| MySQL_Monitor_dns_cache_record_updated | 3 |
| MySQL_Monitor_ping_check_ERR | 0 |
| MySQL_Monitor_ping_check_OK | 0 |
| MySQL_Monitor_read_only_check_ERR | 0 |
| MySQL_Monitor_read_only_check_OK | 0 |
| MySQL_Monitor_replication_lag_check_ERR | 0 |
| MySQL_Monitor_replication_lag_check_OK | 0 |
| MySQL_Thread_Workers | 4 |
| ProxySQL_Uptime | 93179 |
| Queries_backends_bytes_recv | 18494802445 |
| Queries_backends_bytes_sent | 19345506404 |
| Queries_frontends_bytes_recv | 19882710065 |
| Queries_frontends_bytes_sent | 50513656686 |
| Query_Cache_Entries | 4 |
| Query_Cache_Memory_bytes | 16840 |
| Query_Cache_Purged | 0 |
| Query_Cache_bytes_IN | 1480 |
| Query_Cache_bytes_OUT | 2324340 |
| Query_Cache_count_GET | 6286 |
| Query_Cache_count_GET_OK | 6282 |
| Query_Cache_count_SET | 4 |
| Query_Processor_time_nsec | 0 |
| Questions | 35334351 |
| SQLite3_memory_bytes | 3200528 |
| Selects_for_update__autocommit0 | 0 |
| Server_Connections_aborted | 0 |
| Server_Connections_connected | 25 |
| Server_Connections_created | 203 |
| Server_Connections_delayed | 0 |
| Servers_table_version | 1 |
| Slow_queries | 35 |
| Stmt_Cached | 0 |
| Stmt_Client_Active_Total | 0 |
| Stmt_Client_Active_Unique | 0 |
| Stmt_Max_Stmt_id | 1 |
| Stmt_Server_Active_Total | 0 |
| Stmt_Server_Active_Unique | 0 |
| automatic_detected_sql_injection | 0 |
| backend_lagging_during_query | 0 |
| backend_offline_during_query | 0 |
| client_host_error_killed_connections | 0 |
| generated_error_packets | 0 |
| get_aws_aurora_replicas_skipped_during_query | 0 |
| hostgroup_locked_queries | 0 |
| hostgroup_locked_set_cmds | 0 |
| max_connect_timeouts | 0 |
| mysql_backend_buffers_bytes | 0 |
| mysql_frontend_buffers_bytes | 30408704 |
| mysql_killed_backend_connections | 0 |
| mysql_killed_backend_queries | 1 |
| mysql_session_internal_bytes | 663008 |
| mysql_unexpected_frontend_com_quit | 0 |
| mysql_unexpected_frontend_packets | 0 |
| new_req_conns_count | 0 |
| queries_with_max_lag_ms | 0 |
| queries_with_max_lag_ms__delayed | 0 |
| queries_with_max_lag_ms__total_wait_time_us | 0 |
| whitelisted_sqli_fingerprint | 0 |
+----------------------------------------------+----------------+
107 rows in set (0.002 sec)
ProxySQL> SELECT * FROM mysql_servers;
+--------------+---------------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10 | <redacted> | 3306 | 0 | ONLINE | 1000 | 0 | 50 | 0 | 1 | 0 | |
| 20 | <redacted> | 3306 | 0 | ONLINE | 1 | 0 | 50 | 0 | 1 | 0 | |
| 20 | <redacted> | 3306 | 0 | ONLINE | 1000 | 0 | 50 | 2 | 1 | 0 | |
| 20 | <redacted> | 3306 | 0 | ONLINE | 1000 | 0 | 50 | 2 | 1 | 0 | |
+--------------+---------------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
4 rows in set (0.000 sec)JavierJF commented
Hi @amickael,
fronted and backend connections are isolated from each other, enabling or disabling SSL shouldn't influence each other in meaningful ways. Since the error you mentioned are regarding backend connections apparently being closed by MySQL itself, have you checked MySQL error log in search for these errors? Maybe MySQL is explicit in the reason why the connections where closed.
Thanks, Javier.
amickael commented
Hi @JavierJF,
Thanks for replying. We don't see any corresponding errors in the MySQL errorlog which is the oddest part about this. We also have a much longer wait_timeout set in MySQL as well.
JavierJF commented
Hi @amickael,
it's weird that you can't find errors at MySQL side, this scenario has two possibilities:
- MySQL is terminating the connection because some configuration.
- The connection is abnormally being terminated by the client.
(EDIT: There are other possible scenarios, but for the sake of simplicity, let's consider these two for now, until it's clear that it's neither one of these posibilities)
In both cases, the connection is being terminated in an abnormal way, and MySQL should at least have an entry in the error log for this. This logging wont take place with the default verbosity configuration, you should increase it for seeing them:
SET GLOBAL log_error_verbosity=3;
Hope this helps to reveal the cause of the broken connections.
Thanks, regards, Javier.
joshuahunt commented
fronted and backend connections are isolated from each other, enabling or disabling
SSLshouldn't influence each other in meaningful ways.
@JavierJF is that entirely true? My understanding is that SSL errors and such are queued on a per-thread basis. Do the backend and frontend conns not share the same proxysql thread?
JavierJF commented
Hi @joshuahunt,
it's not entirely true, the scenario you describe is a possible one, but as my previous comment mentions:
(EDIT: There are other possible scenarios, but for the sake of simplicity, let's consider these two for now, until it's clear that it's neither one of these posibilities)
First it would be required to rule out other potential scenarios, before starting making hypothesis without having enough evidence of the problem itself. For this issue, MySQL error log in combination with ProxySQL error log (with the previous requested flag), should be the first piece of evidence for diagnosing a potential issue.
Thanks, Javier.
amickael commented
Thanks @JavierJF, we increased the MySQL error log verbosity and still did not see any related errors. We will take a look at the way the client is terminating the connections. When you say:
The connection is abnormally being terminated by the client.
Is there anything specific we should be looking for?
JavierJF commented
Hi @amickael,
MySQL will log whenever a connection hasn't been correctly closed by a client. This goes for when MySQL is going to kill the connection due to a timeout:
2024-04-23T15:16:58.621851Z 2209 [Note] [MY-010914] [Server] Aborted connection 2209 to db: 'unconnected' user: 'sbtest1' host: '172.21.0.1' (The client was disconnected by the server because of inactivity.).
Or, in case the client broken protocol in any way, or disconnected in a unhealthy way, it's also usual to see a error like the following in MySQL error log:
2024-04-23T15:19:17.429137Z 2211 [Note] [MY-010914] [Server] Aborted connection 2211 to db: 'unconnected' user: 'sbtest1' host: '172.21.0.1' (Got an error reading communication packets).
I was interested in if any error of this shape was present in MySQL error log (when set to log_error_verbosity=3). This is important because will help us tracing down the nature of the issue.
Thanks, regards. Javier.
artemvovk commented
We're experiencing a similar issue (also version 2.5.5). In the proxysql logs we're seeing a lot of errors in the following format:
secondary-proxysql-0 proxysql 2024-05-03T06:40:09.225995603-06:00 2024-05-03 12:40:09 MySQL_Session.cpp:2642:handler_again___status_CHANGING_CHARSET(): [ERROR] Detected a broken connection while during SET NAMES on (10,xxxxxxxxxxx,3306,317035) , user xxxxxxx , last_used 19900ms ago : 2013, Lost connection to server during query
this generally correlates with the following logs on mysql server:
2024-05-03T12:40:09.226269Z 317035 [Note] [MY-010914] [Server] Aborted connection 317035 to db: 'xxxxxx' user: 'xxxxx' host: 'xxxx.xxx.xxxx' (Got an error reading communication packets).
and in general mysql log we can correlate the events with:
2024-05-03T02:34:37.037437Z 316511 Change user xxxxx@10.xx.xx.x.x.x on xxxx using SSL/TLS
It is hard to tell if the general log is from the retry tho.
In the application we see related:
[mysql] 2023/11/20 15:44:11 packets.go:37: unexpected EOF
[mysql] 2023/11/20 15:44:11 connection.go:150: tls: failed to send closeNotify alert (but connection was closed anyway): write tcp 10.xxx.xxx.x.x.x:35826->10.xx.x.x.x.x.x:3306: write: broken pipe
Turning off have_ssl immediately quiets down the whole thing. We have tried changing packet size and keepalive settings and all kinds of timeouts, but that had no effect.
at this point our config looks like
mysql> show global variables;
+----------------------------------------------------------------------+-------------------------------------------------+
| Variable_name | Value |
+----------------------------------------------------------------------+-------------------------------------------------+
| admin-admin_credentials | xxxxx |
| 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 | 200 |
| admin-cluster_check_status_frequency | 100 |
| admin-cluster_ldap_variables_diffs_before_sync | 3 |
| admin-cluster_ldap_variables_save_to_disk | true |
| admin-cluster_mysql_query_rules_diffs_before_sync | 1 |
| admin-cluster_mysql_query_rules_save_to_disk | true |
| admin-cluster_mysql_servers_diffs_before_sync | 1 |
| admin-cluster_mysql_servers_save_to_disk | true |
| admin-cluster_mysql_servers_sync_algorithm | 1 |
| admin-cluster_mysql_users_diffs_before_sync | 1 |
| 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 | xxxxx |
| admin-cluster_proxysql_servers_diffs_before_sync | 1 |
| admin-cluster_proxysql_servers_save_to_disk | true |
| admin-cluster_username | useradmin |
| admin-coredump_generation_interval_ms | 30000 |
| admin-coredump_generation_threshold | 10 |
| admin-mysql_ifaces | 0.0.0.0:6032 |
| admin-prometheus_memory_metrics_interval | 15 |
| admin-read_only | false |
| admin-refresh_interval | 2000 |
| admin-restapi_enabled | true |
| 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 | 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_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 | 10 |
| mysql-handle_unknown_charset | 1 |
| mysql-handle_warnings | 1 |
| mysql-have_compress | true |
| mysql-have_ssl | 1 |
| mysql-hostgroup_manager_verbose | 1 |
| mysql-init_connect | |
| mysql-interfaces | 0.0.0.0:3306;/tmp/proxysql.sock |
| 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 | false |
| mysql-long_query_time | 1000 |
| mysql-max_allowed_packet | 67108864 |
| mysql-max_connections | 1000 |
| 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 | 1000 |
| 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 | xxxxx |
| mysql-monitor_ping_interval | 10000 |
| mysql-monitor_ping_max_failures | 3 |
| mysql-monitor_ping_timeout | 10000 |
| mysql-monitor_query_interval | 60000 |
| mysql-monitor_query_timeout | 100 |
| mysql-monitor_read_only_interval | 1000 |
| mysql-monitor_read_only_max_timeout_count | 3 |
| mysql-monitor_read_only_timeout | 10000 |
| mysql-monitor_replication_lag_count | 1 |
| mysql-monitor_replication_lag_group_by_host | false |
| mysql-monitor_replication_lag_interval | 1000 |
| mysql-monitor_replication_lag_timeout | 1000 |
| mysql-monitor_replication_lag_use_percona_heartbeat | |
| mysql-monitor_slave_lag_when_null | 300 |
| mysql-monitor_threads_max | 128 |
| mysql-monitor_threads_min | 8 |
| mysql-monitor_threads_queue_maxsize | 128 |
| mysql-monitor_username | proxysql_recurly |
| 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 | 569899 |
| mysql-server_version | 8.0.32 |
| 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 | 6 |
| 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 |
+----------------------------------------------------------------------+-------------------------------------------------+
JavierJF commented
Hi @artemvovk,
thanks for adding more details about the behavior you are experiencing. As you mention, the change user messages that you see could be the result of the retry, but we wont rule out the correlation just yet. I'm interested in this:
Turning off have_ssl immediately quiets down the whole thing. We have tried changing packet size and keepalive settings and all kinds of timeouts, but that had no effect.
When you say Turning off have_ssl immediately quiets down the whole thing you mean the errors on both sides, or just the errors in client side? Can you confirm if the errors in the server side are no longer present after have_ssl have been turned down?
Also, even if the previous hold, what would really help to investigate the issue without a clear reproduction case would be a tcpdump of the complete interaction between client, ProxySQL and MySQL. Capturing the moment in which the backend connection is broken would certainly help to point the nature of the issue.
Thank you, Javier.