ProxySQL connection giving General error: 2006 MySQL server has gone away

[Gyanendra Singh]

Hi there,

Our application is seeing substantial amount of `General error: 2006 MySQL server has gone away`.
While trying to check for the fix we understood these 2 proxySQL variables can be tweaked:
1.) wait_timeout
2.) max_allowed_packet

On proxySQL:
1.) wait_timeout: 70000 (70 secs, we did not change it as we thought this is already high enough).
2.) max_allowed_packet: we changed it from default 4194304 (4MB) to 256000000 (256MB).

On Db:
1.) wait_timeout: 180 secs
2.) max_allowed_packet: 1GB

Another thing to note is that few of the queries where we are seeing `General error: 2006 MySQL server has gone away` error are on primary_key with limit 1, so this problem does not seems to be due to long running query. 

Sharing all the proxySQL configured variables and their values:
```
mysql_variables=
{
  autocommit_false_is_transaction=true
  auditlog_filename="{{PROXYSQL_DATA_DIR}}/audit.log"
  auto_increment_delay_multiplex=0
  commands_stats=true
  connect_retries_on_failure=0
  connect_timeout_server=5000
  connect_timeout_server_max=5000
  connection_max_age_ms=80000
  connpoll_reset_queue_length=0
  default_max_latency_ms=10000
  default_query_delay=0
  default_query_timeout=36000000
 default_sql_mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
  enforce_autocommit_on_reads=true
  eventslog_default_log=1
  eventslog_filename="{{PROXYSQL_DATA_DIR}}/audit.log"
  eventslog_format=2
  free_connections_pct={{FREE_CONNECTIONS_PCT}}
  interfaces="0.0.0.0:3306;/container-share/proxysql.sock"
  kill_backend_connection_when_disconnect=true
  max_connections=150
  max_stmts_per_connection=512
  monitor_connect_interval=200000
  monitor_enabled=false
  monitor_history=60000
  monitor_password="{{MONITOR_PASSWORD}}"
  monitor_ping_interval=200000
  monitor_ping_max_failures=30
  monitor_ping_timeout=5000
  monitor_read_only_interval=1500
  monitor_read_only_timeout=500
  monitor_username="{{MONITOR_USER}}"
  ping_interval_server_msec=30000
  ping_timeout_server=1000
  poll_timeout=2000
  query_cache_size_MB=0
  query_retries_on_failure=0
  reset_connection_algorithm=2
  server_version="8.0.21"
  sessions_sort=true
  shun_on_failures=10000000
  shun_recovery_time_sec=0
  stacksize=1048576
  threads={{THREADS}}
  wait_timeout=70000
  query_digests=false
  max_allowed_packet=256000000
}```

Thanks

[Gyanendra Singh]

Hi,
Updating few more details,
ProxySQL version: 2.4.7

Another observation is that prior to moving to a separate proxySQL deployment as central service, we were running proxySQL on a sidecar setup i.e. proxySQL container was part of the same application k8s pod and application connects to proxySQL via socket file. The proxySQL version for sidecar setup is 2.0.18.
We do not observe General error: 2006 MySQL server has gone away, while running on the sidecar setup.
This arises to thinking that can this be due to any of these:
1.) Due to version upgrade of proxySQL.
2.) Due to network issues in central proxySQL (behind a Network Load Balancer) vs sidecar (socket) causing this.

[René Cannaò]

The problem is the load balancer

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

IMPORTANT: The contents of this email and any attachments are confidential and protected by applicable laws. If you have received this email by mistake, please (i) notify the sender immediately; (ii) delete it from your database; and (iii) do not disclose the contents to anyone or make copies thereof. Razorpay accepts no liability caused due to any inadvertent/ unintentional data transmitted through this email.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

--
You received this message because you are subscribed to the Google Groups "proxysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/proxysql/4c3fbf3c-0b82-4b14-9144-ca857c230169n%40googlegroups.com.

[Gyanendra Singh]

Hi Rene,
Thanks for the reply, we also suspected Load Balancer was the issue and thus we tried out svc.cluster.local endpoint instead of NLB endpoint but still getting `General error: 2006 MySQL server has gone away`.
Any particular architectural change in the way connections are being established between client application and proxySQL you would want to suggest to be tried out?