I have a remote MySQL database, and my deployed application (a few java microservice applications) consistently encounters the error ERROR 2013 (HY000): Lost connection to MySQL during query with some SQL queries. Then I tried to connect to mysql with MySQL command line client, executed the same query, and I managed to replicate the same error. Below is Mysql setup after following this page https://dev.mysql.com/doc/refman/5.7/en/error-lost-connection.html
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| connect_timeout | 3600 |
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 5 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 7200 |
| lock_wait_timeout | 50 |
| net_read_timeout | 3000 |
| net_write_timeout | 6000 |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 4 |
| wait_timeout | 7200 |
+------------------------------+----------+
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| max_allowed_packet | 1073741824 |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+
However, I systematically have the same error after roughly 10 minutes after executing a big SQL query from the mysql command line client.
Note: On the DB server side, I notice the log [Note] Aborted connection xxxxx to db: 'xxxxx' user 'xxxxx' host 'xxxxx' (Got an error writing communication packets)
DBA executed the query successfully on the server on which mysql is installed. But we have to use mysql remotely as a service.
I will be grateful if anybody can help.
Finally, the root cause was a network issue. Our remote MySQL instance is exposed as service. There's an additional proxy between the client and MySQL DB. By default, the connection will be closed if no communication between client and server over 10 mins.