Repository: sysown/proxysql (language: cpp)

Issue:
Consistent timeouts/retries in multi-layer proxysql architecture routing queries to RDS Aurora MySQL read replicas
We're encountering issues in production using proxysql for sending SELECTs to our read replicas in RDS Aurora. Unfortunately, I've been unable to create a reproducible test case, though this behaviour is consistently producible in our production environment. When we route SELECT traffic through two layers of proxysql to our read replicas we immediately begin to see a small percentage of queries failing and being retried. Our production architecture has a local proxysql process on every instance. Multiple processes of our Rails application connect to that local proxysql, and it forwards queries through an NLB to another proxysql process running on dedicated hardware, which then forwards the queries to our backend databases.

As soon as we begin routing traffic, the "local layer" proxysql logs begin to fill with log lines of this form:

```
2023-03-14 12:50:31 MySQL_Session.cpp:3014:handler_again___status_CHANGING_USER_SERVER(): [ERROR] Change user timeout during COM_CHANGE_USER on proxysql2-production-nlb-7b615f5e1135497e.elb.us-east-1.amazonaws.com , 3306
2023-03-14 12:50:32 MySQL_Session.cpp:3014:handler_again___status_CHANGING_USER_SERVER(): [ERROR] Change user timeout during COM_CHANGE_USER on proxysql2-production-nlb-7b615f5e1135497e.elb.us-east-1.amazonaws.com , 3306
2023-03-14 12:50:32 MySQL_Session.cpp:3014:handler_again___status_CHANGING_USER_SERVER(): [ERROR] Change user timeout during COM_CHANGE_USER on proxysql2-production-nlb-7b615f5e1135497e.elb.us-east-1.amazonaws.com , 3306
2023-03-14 12:50:33 MySQL_Session.cpp:3014:handler_again___status_CHANGING_USER_SERVER(): [ERROR] Change user timeout during COM_CHANGE_USER on proxysql2-production-nlb-7b615f5e1135497e.elb.us-east-1.amazonaws.com , 3306
2023-03-14 12:50:33 MySQL_Session.cpp:3014:handler_again___status_CHANGING_USER_SERVER(): [ERROR] Change user timeout during COM_CHANGE_USER on proxysql2-production-nlb-7b615f5e1135497e.elb.us-east-1.amazonaws.com , 3306
```

And at the same time, the "remote layer" proxysql logs begin to fill with log lines of this form:

```
2023-03-14 12:50:31 MySQL_Thread.cpp:3894:process_all_sessions(): [WARNING] Closing unhealthy client connection 10.0.40.196:63922
2023-03-14 12:50:31 MySQL_Thread.cpp:3894:process_all_sessions(): [WARNING] Closing unhealthy client connection 10.0.40.196:63932
2023-03-14 12:50:32 MySQL_Thread.cpp:3894:process_all_sessions(): [WARNING] Closing unhealthy client connection 10.0.40.196:63934
2023-03-14 12:50:32 MySQL_Thread.cpp:3894:process_all_sessions(): [WARNING] Closing unhealthy client connection 10.0.40.196:63936
2023-03-14 12:50:33 MySQL_Thread.cpp:3894:process_all_sessions(): [WARNING] Closing unhealthy client connection 10.0.40.196:63950
```

Once this starts happening we see some queries randomly being delayed by one or more seconds. Despite not needing very significant traffic in production to create the problem, I've been unable to reproduce it anywhere other than in our production environment. The "local layer" proxysql is version `2.4.4-41-g83ffb72` running on Amazon Linux 2 installed from the officially distributed 2.4.4 RPM for centos7. The "remote layer" proxysql has been tried on versions 2.4.4, 2.4.8 and now `2.5.1-90-gbedaa6c` and all have exhibited the same behaviour.

Enabling `mysql-connection_warming` on the local layer goes some way to mitigating the issue. With it enabled we don't see it occurring constantly, only in a burst once `mysql-connection_max_age_ms` is reached.

We don't see the same behaviour in our existing legacy proxysql deployment which only serves queries for our primary replicas and not readers. That deployment uses the same local layer but the "remote layer" runs proxysql version `1.4.13-15-g69d4207`.

`global_variables` table from the local layer:

```+----------------------------------------------------------------------+----------------------------------------------------------+
| variable_name                                                        | variable_value                                           |
+----------------------------------------------------------------------+----------------------------------------------------------+
| mysql-default_charset                                                | utf8                                                     |
| mysql-default_collation_connection                                   | utf8_general_ci                                          |
| 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-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-monitor_history                                                | 600000                                                   |
| mysql-monitor_connect_interval                                       | 120000                                                   |
| mysql-monitor_connect_timeout                                        | 600                                                      |
| mysql-monitor_ping_interval                                          | 8000                                                     |
| mysql-monitor_ping_max_failures                                      | 3                                                        |
| mysql-monitor_ping_timeout                                           | 1000                                                     |
| mysql-monitor_read_only_interval                                     | 1000                                                     |
| mysql-monitor_read_only_timeout                                      | 800                                                      |
| 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_username                                               | monitor                                                  |
| mysql-monitor_password                                               | monitor                                                  |
| 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_wait_timeout                                           | true                                                     |
| mysql-monitor_writer_is_also_reader                                  | true                                                     |
| 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-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                            | 0                                                        |
| 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-poll_timeout_on_failure                                        | 100                                                      |
| mysql-server_capabilities                                            | 569867                                                   |
| 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_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                                                     |
| admin-stats_credentials                                              | stats:stats                                              |
| 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-hash_passwords                                                 | true                                                     |
| admin-vacuum_stats                                                   | true                                                     |
| admin-version                                                        | 2.4.4-41-g83ffb72                                        |
| 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-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                                                       |
| mysql-commands_stats                                                 | true                                                     |
| mysql-connect_timeout_server                                         | 1000                                                     |
| mysql-default_query_delay                                            | 0                                                        |
| mysql-default_query_timeout                                          | 600000                                                   |
| mysql-default_schema                                                 | information_schema                                       |
| mysql-have_compress                                                  | true                                                     |
| mysql-interfaces                                                     | 0.0.0.0:3306;/var/run/proxysql.sock                      |
| mysql-max_allowed_packet                                             | 64000000                                                 |
| mysql-max_connections                                                | 50000                                                    |
| mysql-max_transaction_time                                           | 1728000000                                               |
| mysql-ping_interval_server_msec                                      | 10000                                                    |
| mysql-ping_timeout_server                                            | 1000                                                     |
| mysql-poll_timeout                                                   | 2000                                                     |
| mysql-server_version                                                 | 5.5.30                                                   |
| mysql-sessions_sort                                                  | true                                                     |
| mysql-shun_on_failures                                               | 10000000                                                 |
| mysql-connect_retries_on_failure                                     | 1000                                                     |
| mysql-shun_recovery_time_sec                                         | 1                                                        |
| mysql-stacksize                                                      | 1048576                                                  |
| mysql-threads                                                        | 2                                                        |
| mysql-wait_timeout                                                   | 28800000                                                 |
| mysql-monitor_enabled                                                | false                                                    |
| mysql-connection_max_age_ms                                          | 900000                                                   |
| mysql-query_digests_no_digits                                        | true                                                     |
| mysql-query_digests_normalize_digest_text                            | true                                                     |
+----------------------------------------------------------------------+----------------------------------------------------------+
194 rows in set (0.00 sec)
```

Remote layer:
```
+----------------------------------------------------------------------+--------------------------------------------+
| variable_name                                                        | variable_value                             |
+----------------------------------------------------------------------+--------------------------------------------+
| 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-hash_passwords                                                 | true                                       |
| admin-vacuum_stats                                                   | true                                       |
| 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-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-mysql_ifaces                                                   | 0.0.0.0:6032                               |
| 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                                       | 0                                          |
| 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                                          | 900000                                     |
| 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-monitor_enabled                                                | true                                       |
| mysql-monitor_connect_timeout                                        | 600                                        |
| mysql-monitor_ping_max_failures                                      | 3                                          |
| mysql-monitor_ping_timeout                                           | 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                                             | 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                                      | 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                            | 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-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                                        | true                                       |
| 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                                                     |                                            |
| 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                                                        | 64                                         |
| mysql-max_connections                                                | 1000000                                    |
| 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                               |
| mysql-default_schema                                                 | information_schema                         |
| mysql-stacksize                                                      | 1048576                                    |
| mysql-server_version                                                 | 5.5.30                                     |
| mysql-connect_timeout_server                                         | 3000                                       |
| mysql-monitor_username                                               | proxysql-monitor                           |
| 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                                         |
| mysql-server_capabilities                                            | 569899                                     |
| mysql-query_cache_soft_ttl_pct                                       | 0                                          |
| admin-version                                                        | 2.5.1-90-gbedaa6c                          |
+----------------------------------------------------------------------+--------------------------------------------+
198 rows in set (0.00 sec)
```

Let me know if there's anything else you need me to provide.


Discussion / hints:
From the description of the issue my guess is that you are being affected by [NLB Connection idle timeout](https://docs.aws.amazon.com/elasticloadbalancing/latest/network/network-load-balancers.html#connection-idle-timeout) .
You can configure the local proxysql instance to use TCP keepalive, setting `mysql-use_tcp_keepalive='true'` and [mysql-tcp_keepalive_time](https://proxysql.com/documentation/global-variables/mysql-variables/#mysql-tcp_keepalive_time) to a reasonable value (for example 60) .
Please let me know if this solves the issue.
Thanks for the speedy reply @renecannao 

Unfortunately enabling TCP keepalives has not resolved the problem:

```
mysql> select * from runtime_global_variables where variable_name like "%keepalive%";
+--------------------------+----------------+
| variable_name            | variable_value |
+--------------------------+----------------+
| mysql-tcp_keepalive_time | 60             |
| mysql-use_tcp_keepalive  | true           |
+--------------------------+----------------+
2 rows in set (0.00 sec)
```

```
2023-03-21 16:24:35 MySQL_Session.cpp:3014:handler_again___status_CHANGING_USER_SERVER(): [ERROR] Change user timeout during COM_CHANGE_USER on proxysql2-production-nlb-7b615f5e1135497e.elb.us-east-1.amazonaws.com , 3306
2023-03-21 16:24:35 MySQL_Session.cpp:3014:handler_again___status_CHANGING_USER_SERVER(): [ERROR] Change user timeout during COM_CHANGE_USER on proxysql2-production-nlb-7b615f5e1135497e.elb.us-east-1.amazonaws.com , 3306
2023-03-21 16:24:35 MySQL_Session.cpp:3014:handler_again___status_CHANGING_USER_SERVER(): [ERROR] Change user timeout during COM_CHANGE_USER on proxysql2-production-nlb-7b615f5e1135497e.elb.us-east-1.amazonaws.com , 3306
2023-03-21 16:24:35 MySQL_Session.cpp:3014:handler_again___status_CHANGING_USER_SERVER(): [ERROR] Change user timeout during COM_CHANGE_USER on proxysql2-production-nlb-7b615f5e1135497e.elb.us-east-1.amazonaws.com , 3306
2023-03-21 16:24:35 MySQL_Session.cpp:3014:handler_again___status_CHANGING_USER_SERVER(): [ERROR] Change user timeout during COM_CHANGE_USER on proxysql2-production-nlb-7b615f5e1135497e.elb.us-east-1.amazonaws.com , 3306
2023-03-21 16:24:35 MySQL_Session.cpp:3014:handler_again___status_CHANGING_USER_SERVER(): [ERROR] Change user timeout during COM_CHANGE_USER on proxysql2-production-nlb-7b615f5e1135497e.elb.us-east-1.amazonaws.com , 3306
2023-03-21 16:24:35 MySQL_Session.cpp:3014:handler_again___status_CHANGING_USER_SERVER(): [ERROR] Change user timeout during COM_CHANGE_USER on proxysql2-production-nlb-7b615f5e1135497e.elb.us-east-1.amazonaws.com , 3306
```
Can you share the full error log?

You probably also need to use `tcpdump` to get an idea to what happens at TCP level to identify who is terminating the connection: the local proxy, the remote proxy, or NLB.
I'll dig back in with `tcpdump` - I was doing that investigation last Thursday but it was inconclusive. I'll see about sharing the error log - unfortunately since I've only been able to reproduce this serving real production traffic I'm not at liberty to share the logs or packet captures publicly.
Sorry for the delayed response. I'm looking into sharing the error log and packet captures, but the data contained could be sensitive so that might take a while and won't be shareable on Github.

Since my last post I've removed the NLB from the equation - the issue still arises even with the local proxysql talking directly to the remote proxysql. It does not happen with the local proxysql talking directly to the database.

I'm not very familiar with using `tcpdump` for cases like this, but I can post some findings that just look at metadata or don't have anything sensitive in them. This is a packet capture taken on the remote layer. The server is ip 10.0.41.224. 

```
$ tshark -r server_capture.pcap -Y 'tcp.flags.syn == 1 or tcp.flags.fin == 1' -T fields -e ip.src -e ip.dst -e tcp.srcport -e tcp.dstport -e tcp.seq -e tcp.ack -e tcp.flags.syn -e tcp.flags.ack -e tcp.flags.fin
10.0.54.64      10.0.41.224     33036   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    33036   0       1       1       1       0
10.0.54.64      10.0.41.224     33044   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    33044   0       1       1       1       0
10.0.54.64      10.0.41.224     33052   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    33052   0       1       1       1       0
10.0.54.64      10.0.41.224     33068   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    33068   0       1       1       1       0
10.0.54.64      10.0.41.224     33078   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    33078   0       1       1       1       0
10.0.54.64      10.0.41.224     33082   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    33082   0       1       1       1       0
10.0.54.64      10.0.41.224     33098   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    33098   0       1       1       1       0
10.0.54.64      10.0.41.224     33036   3306    1294    1516    0       1       1
10.0.41.224     10.0.54.64      3306    33036   1516    1295    0       1       1
10.0.54.64      10.0.41.224     33052   3306    475     435     0       1       1
10.0.41.224     10.0.54.64      3306    33052   435     476     0       1       1
10.0.54.64      10.0.41.224     33102   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    33102   0       1       1       1       0
10.0.54.64      10.0.41.224     33068   3306    786     750     0       1       1
10.0.41.224     10.0.54.64      3306    33068   750     787     0       1       1
10.0.54.64      10.0.41.224     33082   3306    476     434     0       1       1
10.0.41.224     10.0.54.64      3306    33082   434     477     0       1       1
10.0.54.64      10.0.41.224     33110   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    33110   0       1       1       1       0
10.0.54.64      10.0.41.224     33078   3306    742     781     0       1       1
10.0.41.224     10.0.54.64      3306    33078   781     743     0       1       1
10.0.54.64      10.0.41.224     33120   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    33120   0       1       1       1       0
10.0.54.64      10.0.41.224     33136   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    33136   0       1       1       1       0
10.0.54.64      10.0.41.224     33152   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    33152   0       1       1       1       0
10.0.54.64      10.0.41.224     33044   3306    4175    4875    0       1       1
10.0.41.224     10.0.54.64      3306    33044   4875    4176    0       1       1
10.0.54.64      10.0.41.224     63900   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    63900   0       1       1       1       0
10.0.54.64      10.0.41.224     33098   3306    7474    21884   0       1       1
10.0.41.224     10.0.54.64      3306    33098   21884   7475    0       1       1
10.0.54.64      10.0.41.224     63910   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    63910   0       1       1       1       0
10.0.54.64      10.0.41.224     33102   3306    5403    5780    0       1       1
10.0.41.224     10.0.54.64      3306    33102   5780    5404    0       1       1
10.0.54.64      10.0.41.224     33110   3306    1027    1180    0       1       1
10.0.41.224     10.0.54.64      3306    33110   1180    1028    0       1       1
10.0.54.64      10.0.41.224     63920   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    63920   0       1       1       1       0
10.0.54.64      10.0.41.224     63934   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    63934   0       1       1       1       0
10.0.54.64      10.0.41.224     33152   3306    1007    1062    0       1       1
10.0.41.224     10.0.54.64      3306    33152   1062    1008    0       1       1
10.0.54.64      10.0.41.224     63936   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    63936   0       1       1       1       0
10.0.54.64      10.0.41.224     33120   3306    1603    1826    0       1       1
10.0.41.224     10.0.54.64      3306    33120   1826    1604    0       1       1
10.0.54.64      10.0.41.224     63950   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    63950   0       1       1       1       0
10.0.54.64      10.0.41.224     63958   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    63958   0       1       1       1       0
10.0.54.64      10.0.41.224     33136   3306    7261    19174   0       1       1
10.0.41.224     10.0.54.64      3306    33136   19174   7262    0       1       1
10.0.54.64      10.0.41.224     63960   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    63960   0       1       1       1       0
10.0.54.64      10.0.41.224     63920   3306    835     921     0       1       1
10.0.41.224     10.0.54.64      3306    63920   921     836     0       1       1
10.0.54.64      10.0.41.224     63968   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    63968   0       1       1       1       0
10.0.54.64      10.0.41.224     63984   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    63984   0       1       1       1       0
10.0.54.64      10.0.41.224     63910   3306    4828    5362    0       1       1
10.0.41.224     10.0.54.64      3306    63910   5362    4829    0       1       1
10.0.54.64      10.0.41.224     63936   3306    2213    8836    0       1       1
10.0.41.224     10.0.54.64      3306    63936   8836    2214    0       1       1
10.0.54.64      10.0.41.224     63996   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    63996   0       1       1       1       0
10.0.54.64      10.0.41.224     63960   3306    768     853     0       1       1
10.0.41.224     10.0.54.64      3306    63960   853     769     0       1       1
10.0.54.64      10.0.41.224     64000   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64000   0       1       1       1       0
10.0.54.64      10.0.41.224     63900   3306    2653    3225    0       1       1
10.0.41.224     10.0.54.64      3306    63900   3225    2654    0       1       1
10.0.54.64      10.0.41.224     64016   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64016   0       1       1       1       0
10.0.54.64      10.0.41.224     63984   3306    4136    9209    0       1       1
10.0.41.224     10.0.54.64      3306    63984   9209    4137    0       1       1
10.0.54.64      10.0.41.224     64024   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64024   0       1       1       1       0
10.0.54.64      10.0.41.224     64028   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64028   0       1       1       1       0
10.0.54.64      10.0.41.224     63996   3306    2154    7272    0       1       1
10.0.41.224     10.0.54.64      3306    63996   7272    2155    0       1       1
10.0.54.64      10.0.41.224     64044   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64044   0       1       1       1       0
10.0.54.64      10.0.41.224     64000   3306    1934    3971    0       1       1
10.0.41.224     10.0.54.64      3306    64000   3971    1935    0       1       1
10.0.54.64      10.0.41.224     63968   3306    3298    3997    0       1       1
10.0.41.224     10.0.54.64      3306    63968   3997    3299    0       1       1
10.0.54.64      10.0.41.224     64056   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64056   0       1       1       1       0
10.0.54.64      10.0.41.224     64024   3306    475     435     0       1       1
10.0.41.224     10.0.54.64      3306    64024   435     476     0       1       1
10.0.54.64      10.0.41.224     64066   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64066   0       1       1       1       0
10.0.54.64      10.0.41.224     64028   3306    634     669     0       1       1
10.0.41.224     10.0.54.64      3306    64028   669     635     0       1       1
10.0.54.64      10.0.41.224     64076   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64076   0       1       1       1       0
10.0.54.64      10.0.41.224     64080   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64080   0       1       1       1       0
10.0.54.64      10.0.41.224     63958   3306    7587    8978    0       1       1
10.0.41.224     10.0.54.64      3306    63958   8978    7588    0       1       1
10.0.54.64      10.0.41.224     63950   3306    704     596     0       1       1
10.0.41.224     10.0.54.64      3306    63950   596     705     0       1       1
10.0.54.64      10.0.41.224     64092   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64092   0       1       1       1       0
10.0.54.64      10.0.41.224     63934   3306    15179   17576   0       1       1
10.0.41.224     10.0.54.64      3306    63934   17576   15180   0       1       1
10.0.54.64      10.0.41.224     64016   3306    8050    64988   0       1       1
10.0.41.224     10.0.54.64      3306    64016   64988   8051    0       1       1
10.0.54.64      10.0.41.224     64094   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64094   0       1       1       1       0
10.0.54.64      10.0.41.224     64076   3306    1340    1493    0       1       1
10.0.41.224     10.0.54.64      3306    64076   1493    1341    0       1       1
10.0.54.64      10.0.41.224     64108   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64108   0       1       1       1       0
10.0.54.64      10.0.41.224     64114   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64114   0       1       1       1       0
10.0.54.64      10.0.41.224     64044   3306    1004    748     0       1       1
10.0.41.224     10.0.54.64      3306    64044   748     1005    0       1       1
10.0.54.64      10.0.41.224     64124   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64124   0       1       1       1       0
10.0.54.64      10.0.41.224     64140   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64140   0       1       1       1       0
10.0.54.64      10.0.41.224     64152   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64152   0       1       1       1       0
10.0.54.64      10.0.41.224     64056   3306    6394    6861    0       1       1
10.0.41.224     10.0.54.64      3306    64056   6861    6395    0       1       1
10.0.54.64      10.0.41.224     64162   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64162   0       1       1       1       0
10.0.54.64      10.0.41.224     64092   3306    3266    3766    0       1       1
10.0.41.224     10.0.54.64      3306    64092   3766    3267    0       1       1
10.0.54.64      10.0.41.224     64066   3306    2631    2967    0       1       1
10.0.41.224     10.0.54.64      3306    64066   2967    2632    0       1       1
10.0.54.64      10.0.41.224     64094   3306    1872    2203    0       1       1
10.0.41.224     10.0.54.64      3306    64094   2203    1873    0       1       1
10.0.54.64      10.0.41.224     64172   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64172   0       1       1       1       0
10.0.54.64      10.0.41.224     64108   3306    1801    5020    0       1       1
10.0.41.224     10.0.54.64      3306    64108   5020    1802    0       1       1
10.0.54.64      10.0.41.224     64176   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64176   0       1       1       1       0
10.0.54.64      10.0.41.224     64178   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64178   0       1       1       1       0
10.0.54.64      10.0.41.224     64194   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64194   0       1       1       1       0
10.0.54.64      10.0.41.224     64206   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64206   0       1       1       1       0
10.0.54.64      10.0.41.224     64222   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64222   0       1       1       1       0
10.0.54.64      10.0.41.224     64152   3306    2406    2774    0       1       1
10.0.41.224     10.0.54.64      3306    64152   2774    2407    0       1       1
10.0.54.64      10.0.41.224     64226   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64226   0       1       1       1       0
10.0.54.64      10.0.41.224     64080   3306    10704   107379  0       1       1
10.0.41.224     10.0.54.64      3306    64080   107379  10705   0       1       1
10.0.54.64      10.0.41.224     64114   3306    8026    9336    0       1       1
10.0.41.224     10.0.54.64      3306    64114   9336    8027    0       1       1
10.0.54.64      10.0.41.224     64124   3306    7588    9333    0       1       1
10.0.41.224     10.0.54.64      3306    64124   9333    7589    0       1       1
10.0.54.64      10.0.41.224     64176   3306    743     770     0       1       1
10.0.41.224     10.0.54.64      3306    64176   770     744     0       1       1
10.0.54.64      10.0.41.224     64194   3306    2916    3373    0       1       1
10.0.41.224     10.0.54.64      3306    64194   3373    2917    0       1       1
10.0.54.64      10.0.41.224     64240   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64240   0       1       1       1       0
10.0.54.64      10.0.41.224     64242   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64242   0       1       1       1       0
10.0.54.64      10.0.41.224     64258   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64258   0       1       1       1       0
10.0.54.64      10.0.41.224     64264   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64264   0       1       1       1       0
10.0.54.64      10.0.41.224     64274   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64274   0       1       1       1       0
10.0.54.64      10.0.41.224     64284   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64284   0       1       1       1       0
10.0.54.64      10.0.41.224     64300   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64300   0       1       1       1       0
```

And a similar packet capture from the same time on the local side:

```
$ tshark -r client_capture.pcap -Y 'tcp.flags.syn == 1 or tcp.flags.fin == 1' -T fields -e ip.src -e ip.dst -e tcp.srcport -e tcp.dstport -e tcp.seq -e tcp.ack -e tcp.flags.syn -e tcp.flags.ack -e tcp.flags.fin
10.0.54.64      10.0.41.224     33036   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    33036   0       1       1       1       0
10.0.54.64      10.0.41.224     33044   3306    0               1       0       0
10.0.54.64      10.0.41.224     33052   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    33044   0       1       1       1       0
10.0.41.224     10.0.54.64      3306    33052   0       1       1       1       0
10.0.54.64      10.0.41.224     33068   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    33068   0       1       1       1       0
10.0.54.64      10.0.41.224     33078   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    33078   0       1       1       1       0
10.0.54.64      10.0.41.224     33082   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    33082   0       1       1       1       0
10.0.54.64      10.0.41.224     33098   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    33098   0       1       1       1       0
10.0.54.64      10.0.41.224     33036   3306    1294    1516    0       1       1
10.0.41.224     10.0.54.64      3306    33036   1516    1295    0       1       1
10.0.54.64      10.0.41.224     33052   3306    475     435     0       1       1
10.0.54.64      10.0.41.224     33102   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    33052   435     476     0       1       1
10.0.41.224     10.0.54.64      3306    33102   0       1       1       1       0
10.0.54.64      10.0.41.224     33068   3306    786     750     0       1       1
10.0.41.224     10.0.54.64      3306    33068   750     787     0       1       1
10.0.54.64      10.0.41.224     33082   3306    476     434     0       1       1
10.0.41.224     10.0.54.64      3306    33082   434     477     0       1       1
10.0.54.64      10.0.41.224     33110   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    33110   0       1       1       1       0
10.0.54.64      10.0.41.224     33078   3306    742     781     0       1       1
10.0.41.224     10.0.54.64      3306    33078   781     743     0       1       1
10.0.54.64      10.0.41.224     33120   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    33120   0       1       1       1       0
10.0.54.64      10.0.41.224     33136   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    33136   0       1       1       1       0
10.0.54.64      10.0.41.224     33152   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    33152   0       1       1       1       0
10.0.54.64      10.0.41.224     33044   3306    4175    4875    0       1       1
10.0.41.224     10.0.54.64      3306    33044   4875    4176    0       1       1
10.0.54.64      10.0.41.224     63900   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    63900   0       1       1       1       0
10.0.54.64      10.0.41.224     33098   3306    7474    21884   0       1       1
10.0.41.224     10.0.54.64      3306    33098   21884   7475    0       1       1
10.0.54.64      10.0.41.224     63910   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    63910   0       1       1       1       0
10.0.54.64      10.0.41.224     33102   3306    5403    5780    0       1       1
10.0.41.224     10.0.54.64      3306    33102   5780    5404    0       1       1
10.0.54.64      10.0.41.224     33110   3306    1027    1180    0       1       1
10.0.41.224     10.0.54.64      3306    33110   1180    1028    0       1       1
10.0.54.64      10.0.41.224     63920   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    63920   0       1       1       1       0
10.0.54.64      10.0.41.224     63934   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    63934   0       1       1       1       0
10.0.54.64      10.0.41.224     33152   3306    1007    1062    0       1       1
10.0.41.224     10.0.54.64      3306    33152   1062    1008    0       1       1
10.0.54.64      10.0.41.224     63936   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    63936   0       1       1       1       0
10.0.54.64      10.0.41.224     33120   3306    1603    1826    0       1       1
10.0.41.224     10.0.54.64      3306    33120   1826    1604    0       1       1
10.0.54.64      10.0.41.224     63950   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    63950   0       1       1       1       0
10.0.54.64      10.0.41.224     63958   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    63958   0       1       1       1       0
10.0.54.64      10.0.41.224     33136   3306    7261    19174   0       1       1
10.0.54.64      10.0.41.224     63960   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    33136   19174   7262    0       1       1
10.0.41.224     10.0.54.64      3306    63960   0       1       1       1       0
10.0.54.64      10.0.41.224     63920   3306    835     921     0       1       1
10.0.41.224     10.0.54.64      3306    63920   921     836     0       1       1
10.0.54.64      10.0.41.224     63968   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    63968   0       1       1       1       0
10.0.54.64      10.0.41.224     63984   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    63984   0       1       1       1       0
10.0.54.64      10.0.41.224     63910   3306    4828    5362    0       1       1
10.0.41.224     10.0.54.64      3306    63910   5362    4829    0       1       1
10.0.54.64      10.0.41.224     63936   3306    2213    8836    0       1       1
10.0.41.224     10.0.54.64      3306    63936   8836    2214    0       1       1
10.0.54.64      10.0.41.224     63996   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    63996   0       1       1       1       0
10.0.54.64      10.0.41.224     63960   3306    768     853     0       1       1
10.0.41.224     10.0.54.64      3306    63960   853     769     0       1       1
10.0.54.64      10.0.41.224     64000   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64000   0       1       1       1       0
10.0.54.64      10.0.41.224     63900   3306    2653    3225    0       1       1
10.0.41.224     10.0.54.64      3306    63900   3225    2654    0       1       1
10.0.54.64      10.0.41.224     64016   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64016   0       1       1       1       0
10.0.54.64      10.0.41.224     63984   3306    4136    9209    0       1       1
10.0.41.224     10.0.54.64      3306    63984   9209    4137    0       1       1
10.0.54.64      10.0.41.224     64024   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64024   0       1       1       1       0
10.0.54.64      10.0.41.224     64028   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64028   0       1       1       1       0
10.0.54.64      10.0.41.224     63996   3306    2154    7272    0       1       1
10.0.41.224     10.0.54.64      3306    63996   7272    2155    0       1       1
10.0.54.64      10.0.41.224     64044   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64044   0       1       1       1       0
10.0.54.64      10.0.41.224     64000   3306    1934    3971    0       1       1
10.0.41.224     10.0.54.64      3306    64000   3971    1935    0       1       1
10.0.54.64      10.0.41.224     63968   3306    3298    3997    0       1       1
10.0.41.224     10.0.54.64      3306    63968   3997    3299    0       1       1
10.0.54.64      10.0.41.224     64056   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64056   0       1       1       1       0
10.0.54.64      10.0.41.224     64024   3306    475     435     0       1       1
10.0.41.224     10.0.54.64      3306    64024   435     476     0       1       1
10.0.54.64      10.0.41.224     64066   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64066   0       1       1       1       0
10.0.54.64      10.0.41.224     64028   3306    634     669     0       1       1
10.0.41.224     10.0.54.64      3306    64028   669     635     0       1       1
10.0.54.64      10.0.41.224     64076   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64076   0       1       1       1       0
10.0.54.64      10.0.41.224     64080   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64080   0       1       1       1       0
10.0.54.64      10.0.41.224     63958   3306    7587    8978    0       1       1
10.0.41.224     10.0.54.64      3306    63958   8978    7588    0       1       1
10.0.54.64      10.0.41.224     63950   3306    704     596     0       1       1
10.0.54.64      10.0.41.224     64092   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    63950   596     705     0       1       1
10.0.41.224     10.0.54.64      3306    64092   0       1       1       1       0
10.0.54.64      10.0.41.224     63934   3306    15179   17576   0       1       1
10.0.41.224     10.0.54.64      3306    63934   17576   15180   0       1       1
10.0.54.64      10.0.41.224     64016   3306    8050    64988   0       1       1
10.0.41.224     10.0.54.64      3306    64016   64988   8051    0       1       1
10.0.54.64      10.0.41.224     64094   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64094   0       1       1       1       0
10.0.54.64      10.0.41.224     64076   3306    1340    1493    0       1       1
10.0.54.64      10.0.41.224     64108   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64076   1493    1341    0       1       1
10.0.41.224     10.0.54.64      3306    64108   0       1       1       1       0
10.0.54.64      10.0.41.224     64114   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64114   0       1       1       1       0
10.0.54.64      10.0.41.224     64044   3306    1004    748     0       1       1
10.0.41.224     10.0.54.64      3306    64044   748     1005    0       1       1
10.0.54.64      10.0.41.224     64124   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64124   0       1       1       1       0
10.0.54.64      10.0.41.224     64140   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64140   0       1       1       1       0
10.0.54.64      10.0.41.224     64152   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64152   0       1       1       1       0
10.0.54.64      10.0.41.224     64056   3306    6394    6861    0       1       1
10.0.41.224     10.0.54.64      3306    64056   6861    6395    0       1       1
10.0.54.64      10.0.41.224     64162   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64162   0       1       1       1       0
10.0.54.64      10.0.41.224     64092   3306    3266    3766    0       1       1
10.0.41.224     10.0.54.64      3306    64092   3766    3267    0       1       1
10.0.54.64      10.0.41.224     64066   3306    2631    2967    0       1       1
10.0.41.224     10.0.54.64      3306    64066   2967    2632    0       1       1
10.0.54.64      10.0.41.224     64094   3306    1872    2203    0       1       1
10.0.41.224     10.0.54.64      3306    64094   2203    1873    0       1       1
10.0.54.64      10.0.41.224     64172   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64172   0       1       1       1       0
10.0.54.64      10.0.41.224     64108   3306    1801    5020    0       1       1
10.0.41.224     10.0.54.64      3306    64108   5020    1802    0       1       1
10.0.54.64      10.0.41.224     64176   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64176   0       1       1       1       0
10.0.54.64      10.0.41.224     64178   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64178   0       1       1       1       0
10.0.54.64      10.0.41.224     64194   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64194   0       1       1       1       0
10.0.54.64      10.0.41.224     64206   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64206   0       1       1       1       0
10.0.54.64      10.0.41.224     64222   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64222   0       1       1       1       0
10.0.54.64      10.0.41.224     64152   3306    2406    2774    0       1       1
10.0.41.224     10.0.54.64      3306    64152   2774    2407    0       1       1
10.0.54.64      10.0.41.224     64226   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64226   0       1       1       1       0
10.0.54.64      10.0.41.224     64080   3306    10704   107379  0       1       1
10.0.41.224     10.0.54.64      3306    64080   107379  10705   0       1       1
10.0.54.64      10.0.41.224     64114   3306    8026    9336    0       1       1
10.0.41.224     10.0.54.64      3306    64114   9336    8027    0       1       1
10.0.54.64      10.0.41.224     64124   3306    7588    9333    0       1       1
10.0.41.224     10.0.54.64      3306    64124   9333    7589    0       1       1
10.0.54.64      10.0.41.224     64176   3306    743     770     0       1       1
10.0.41.224     10.0.54.64      3306    64176   770     744     0       1       1
10.0.54.64      10.0.41.224     64194   3306    2916    3373    0       1       1
10.0.41.224     10.0.54.64      3306    64194   3373    2917    0       1       1
10.0.54.64      10.0.41.224     64240   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64240   0       1       1       1       0
10.0.54.64      10.0.41.224     64242   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64242   0       1       1       1       0
10.0.54.64      10.0.41.224     64258   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64258   0       1       1       1       0
10.0.54.64      10.0.41.224     64264   3306    0               1       0       0
10.0.41.224     10.0.54.64      3306    64264   0       1       1       1       0
```

Nothing in particular stands out to me here, but as I said this isn't my area of expertise.

One thing I did find interesting when looking at the packets using `tshark` is there are a lot of packets like this:

```
Frame 3089: 332 bytes on wire (2656 bits), 332 bytes captured (2656 bits)
Ethernet II, Src: 0e:5c:fd:36:33:6b (0e:5c:fd:36:33:6b), Dst: 0e:fa:64:80:dd:dd (0e:fa:64:80:dd:dd)
Internet Protocol Version 4, Src: 10.0.54.64 (10.0.54.64), Dst: 10.0.41.224 (10.0.41.224)
Transmission Control Protocol, Src Port: 51726 (51726), Dst Port: mysql (3306), Seq: 115, Ack: 90, Len: 266
MySQL Protocol
    Packet Length: 259
    Packet Number: 0
    Request Command Unknown (164)
        Command: Unknown (164)
        Payload: 0100789c7d90d14ac330148627be48eeaaa534b593ae0978...
            [Expert Info (Warn/Protocol): Unknown/invalid command code]
                [Message: Unknown/invalid command code]
                [Severity level: Warn]
                [Group: Protocol]
```

But with various different unknown commands:

```
$ tshark -r client_capture.pcap -Y "mysql && ip.dst == 10.0.41.224" -O mysql | grep 
'Command Unknown' | sort | uniq -c | sort -n
      1     Request Command Unknown (114)
      1     Request Command Unknown (146)
      1     Request Command Unknown (186)
      1     Request Command Unknown (199)
      1     Request Command Unknown (229)
      1     Request Command Unknown (230)
      1     Request Command Unknown (248)
      1     Request Command Unknown (66)
      1     Request Command Unknown (97)
      2     Request Command Unknown (139)
      2     Request Command Unknown (141)
      2     Request Command Unknown (168)
      2     Request Command Unknown (240)
      2     Request Command Unknown (68)
      3     Request Command Unknown (164)
      3     Request Command Unknown (232)
      4     Request Command Unknown (67)
      5     Request Command Unknown (162)
      5     Request Command Unknown (166)
      7     Request Command Unknown (234)
     11     Request Command Unknown (236)
```

I don't know if that's relevant or just user error on my part.

Fix patch:
diff --git a/lib/mysql_connection.cpp b/lib/mysql_connection.cpp
index 9e2bfe5bf9..d3300bb2e9 100644
--- a/lib/mysql_connection.cpp
+++ b/lib/mysql_connection.cpp
@@ -2779,9 +2779,12 @@ int MySQL_Connection::async_send_simple_command(short event, char *stmt, unsigne
 
 void MySQL_Connection::reset() {
 	bool old_no_multiplex_hg = get_status(STATUS_MYSQL_CONNECTION_NO_MULTIPLEX_HG);
+	bool old_compress = get_status(STATUS_MYSQL_CONNECTION_COMPRESSION);
 	status_flags=0;
 	// reconfigure STATUS_MYSQL_CONNECTION_NO_MULTIPLEX_HG
 	set_status(old_no_multiplex_hg,STATUS_MYSQL_CONNECTION_NO_MULTIPLEX_HG);
+	// reconfigure STATUS_MYSQL_CONNECTION_COMPRESSION
+	set_status(old_compress,STATUS_MYSQL_CONNECTION_COMPRESSION);
 	reusable=true;
 	options.last_set_autocommit=-1; // never sent
 


Test patch:
diff --git a/test/tap/tests/reg_test_4158_change_user-t.cpp b/test/tap/tests/reg_test_4158_change_user-t.cpp
new file mode 100644
index 0000000000..8b01a319c4
--- /dev/null
+++ b/test/tap/tests/reg_test_4158_change_user-t.cpp
@@ -0,0 +1,123 @@
+/**
+ * @file reg_test_4158_change_user-t.cpp
+ * @brief This test verifies COM_CHANGE_USER and COM_RESET_CONNECTION with compression
+ *
+ * @details It run COM_CHANGE_USER and COM_RESET_CONNECTION with compression.
+ * This seems broken in 2.x (not sure yet in which version the regression was
+ * first introduced), while working fine in 1.4
+ */
+
+#include "mysql.h"
+
+#include "proxysql_utils.h"
+#include "tap.h"
+#include "utils.h"
+
+#include <unistd.h>
+#include <iostream>
+
+using std::string;
+
+//#include "json.hpp"
+//using nlohmann::json;
+
+
+using namespace std;
+
+int loop1 = 3;
+int loop2 = 3;
+
+CommandLine cl;
+
+int work_mysql() {
+	MYSQL* proxy = mysql_init(NULL);
+	if (!mysql_real_connect(proxy, cl.host, cl.username, cl.password, NULL, cl.port, NULL, CLIENT_COMPRESS)) {
+		fprintf(stderr, "File %s, line %d, Error: %s\n", __FILE__, __LINE__, mysql_error(proxy));
+		return EXIT_FAILURE;
+	}
+
+	for (int j=0; j < loop1 ; j++) {
+		diag("We run multiple queries just to verify that the connection is still healthy after the firstone");
+		for (int i=0; i < loop2 ; i++) {
+			MYSQL_QUERY_T(proxy, "SELECT CONNECTION_ID()");
+			MYSQL_RES* myres;
+			myres = mysql_store_result(proxy);
+			int nr = mysql_num_rows(myres);
+			ok(nr == 1, "Rows returned: %d" , nr);
+			mysql_free_result(myres);
+		}
+		int rb;
+		diag("Running mysql_reset_connection()");
+		rb = mysql_reset_connection(proxy);
+		ok(rb == 0 , "mysql_reset_connection(): %s", (rb == 0 ? "OK" : mysql_error(proxy)));
+		diag("Running mysql_change_user()");
+		rb = mysql_change_user(proxy, cl.username, cl.password, NULL);
+		ok(rb == 0 , "mysql_change_user(): %s", (rb == 0 ? "OK" : mysql_error(proxy)));
+	}
+	mysql_close(proxy);
+	return 0;
+}
+
+void * work(void *arg) {
+	work_mysql(); // this return an int
+	return NULL;
+}
+
+int run_funct_timeout(void *(*start_routine)(void *), int timeout) {
+	// we run the test on a separate thread because we have a built-in timeout
+	pthread_t thread_id;
+	if (pthread_create(&thread_id, NULL, start_routine, NULL)) {
+		fprintf(stderr, "Error calling pthread_create()");
+		return EXIT_FAILURE; 
+	}
+
+	if (timeout != 0) {
+		int tr = 0;
+		while (timeout != 0) {
+			sleep(1);
+			tr = pthread_tryjoin_np(thread_id, NULL);
+			if (tr == 0) {
+				timeout = 0;
+			} else {
+				timeout--;
+				diag("Waiting up to %d seconds", timeout);
+			}
+		}
+		if (tr =! 0) {
+			return EXIT_FAILURE;
+		}
+	} else {
+		// if timeout == 0 , the timeout is disabled
+		// This is useful during debugging, while running the TAP test in gdb
+		diag("Built-in timeout DISABLED");
+		pthread_join(thread_id, NULL);
+		return 0;
+	}
+	return 0;
+}
+int main(int, char**) {
+
+	plan(loop1 * (loop2 + 2));
+
+	if (cl.getEnv()) {
+		diag("Failed to get the required environmental variables.");
+		return EXIT_FAILURE;
+	}
+
+/*
+	// PLACEHOLDER
+	MYSQL* admin = mysql_init(NULL);
+	if (!mysql_real_connect(admin, cl.host, cl.admin_username, cl.admin_password, NULL, cl.admin_port, NULL, 0)) {
+		fprintf(stderr, "File %s, line %d, Error: %s\n", __FILE__, __LINE__, mysql_error(admin));
+		return EXIT_FAILURE;
+	}
+
+	MYSQL_QUERY_T(admin, "LOAD MYSQL VARIABLES TO RUNTIME");
+
+	mysql_close(admin);
+*/
+
+	run_funct_timeout(work, 10);
+
+	return exit_status();
+}