connection to clients being dropped

[Kostas]

Hello,

yesterday we started using proxysql in our production system. Right away we noticed lots of queries failing with "MySQL server has gone away" error, eg:

Err:         2006

Errstr:      MySQL server has gone away

State:       HY000

Statement:

#011SELECT app_id, user_id, credits, diamonds, active_addon, games_played, games_won, games_lost, tourns_won, level, points, points_won, credits_won, credits_won_7, credits_won_24, bonus_date, bonus_days, minus_user_id, create_date, may_gift, casi_rewarded, game_specific, minigames_next, minigames_rolls, country, tickets, ticket_regen, last_report

#011FROM llgames.game_user

#011WHERE app_id = ? AND user_id = ?

ParamValues: 45, 21451528

DBD::mysql::st execute failed: MySQL server has gone away

So it looks like the connection to proxysql is being dropped. Some more info:

- The statements failing are either commit, rollback or execute (of prepare statements).

- proxysql runs locally and clients connect via unix socket

- we use group replication monitored by proxysql

- read/write separation is done based on the mysql user (via mysql_users.default_hostgroup), so mysql_query_rules is empty

- proxysql.log shows nothing weird, apart form some "Duplicate entry" sql errors that are probably irrelevant:

  2017-10-11 06:59:29 MySQL_Session.cpp:2763:handler(): [WARNING] Error during query on   (0,db-3-172-30-4-196,3306): 1062, Duplicate entry '204-44-2017-10-11-19053327' for key  'PRIMARY'

- the same queries run fine in our testing system

- proxysql timeout values (given below) look reasonable

I had to quickly revert our production system, so I don't have any good debug data, and I'm still trying to reproduce it in our testing system. But I though to make a post in the meanwhile in case anyone has some good idea about what might be happening.

Thanks in advance.

Cheers,

Kostas

> select * from global_variables where variable_name like '%timeout%';

+----------------------------------------------------+----------------+

| variable_name                                      | variable_value |

+----------------------------------------------------+----------------+

| mysql-connect_timeout_server                       | 1000           |

| mysql-connect_timeout_server_max                   | 10000          |

| mysql-monitor_connect_timeout                      | 600            |

| mysql-monitor_ping_timeout                         | 1000           |

| mysql-monitor_read_only_timeout                    | 800            |

| mysql-monitor_replication_lag_timeout              | 1000           |

| mysql-monitor_groupreplication_healthcheck_timeout | 800            |

| mysql-monitor_query_timeout                        | 100            |

| mysql-monitor_wait_timeout                         | true           |

| mysql-wait_timeout                                 | 28800000       |

| mysql-default_query_timeout                        | 86400000       |

| mysql-ping_timeout_server                          | 200            |

| mysql-poll_timeout                                 | 2000           |

| mysql-poll_timeout_on_failure                      | 100            |

+----------------------------------------------------+----------------+

[Gyanendra Singh]

Hi Kostas,
We are facing similar issue in out production system with proxySQL, was your issue resolved?
If yes, it would be great if you could help us with the changes that worked.

Thanks,
Gyan