Broken connections #1600

mihaibrehar · 2018-07-10T23:47:43Z

Hi,

I am using Proxysql 1.4.8 on Debian 8 with Percona 5.6.38.

I have lots of messages like this:

2018-07-11 01:45:01 MySQL_Session.cpp:2816:handler(): [ERROR] Detected a broken connection during query on (1,127.0.0.1,3306) , FD (Conn:28 , MyDS:28) : 2013, Lost connection to MySQL server during query

But also a few of these:

2018-07-11 01:23:34 MySQL_Thread.cpp:3080:process_data_on_data_stream(): [WARNING] Detected broken idle connection on 127.0.0.1:3306

2018-07-11 02:00:02 MySQL_Session.cpp:959:handler_again___status_PINGING_SERVER(): [ERROR] Detected a broken connection during ping on (1,127.0.0.1,3306) , FD (Conn:30 , MyDS:30) : 2013, Lost connection to MySQL server during query

The first error happens right on the first query (well, second, after set names). The query is a simple one: SELECT *, something as name FROM table. And the table is small. Most of the time the query runs fine so I am not sure how to continue the investigation. Maybe it's a query that runs before it? Or maybe it's something specific to Percona 5.6?

I have a split read/write configuration, without monitoring (so I've got "errors" in the logs about that as well)

127.0.0.1 is used as a read-only backend.
Sometimes the error happens also on the write backend and always it's a begin or commit query.

I am trying to replicate whatever happens. Any idea besides this?

Thanks.

The text was updated successfully, but these errors were encountered:

Reiner030 · 2018-07-11T19:52:51Z

Hi, perhaps it's same problem as for me some month ago that the connection was dropped by PerconaDB because the connections got older then wait_timeout (or interactive_timeout) ?

mihaibrehar · 2018-07-12T09:29:52Z

wait_timeout and interactive_timeout are set to 28800. net_read_timeout and net_write_timeout are set to 30 seconds.
Proxysql mysql-wait_timeout is set to 28800000
So maybe, but I have other clients connecting directly to Percona and I dont want to change the timeout on that. Also, this is a very busy server so I think the timeouts are never reached.

If it matters, multiplexing is disabled.

I will do some tests. But maybe the error should be more clear in this case?

Edit: not sure if mysql-wait_timeout is seconds or miliseconds? The wiki says Type: Integer (bytes) which is clearly not the case.

Second edit: I just saw it also says Default: 28800000 (8 hours) so it is miliseconds.
In this case, could the problem be related to net_read/write_timeout?

Reiner030 · 2018-07-12T15:31:07Z

Hi,

that multiplexing is disabled could be a good hint because I have same problem and yet no idea howto debug/find the right solution in #1493
In my case Debian 8 with MariaDB is working without exceptions but ProxySQL with Debian 9 has problems with same proxysql setup.

Two days ago I found another bug which faults in a SELECT ..., COUNT(*) ... query which seems also to produce equal errors like in #1031 about a year ago discussed and not closed yet.

EDIT:
Perhaps it helps you to debug your queries, where this page is helpful:
https://github.com/sysown/proxysql/wiki/Query-Logging
but be sure to have enough space free in /var/lib/proxysql or other folder because in busy environments proxysql could write several GBs of log per hour and would crash then if no space is left.

The debugging of ProxySQL itself is not documented yet; the dbg packaged version should help here but I didn't know yet how it can be activated (so that data is written to logs).

Calming down threads to 4 (or in my case one thread/cpu core = 12 ^^) could also help a little; it needs changed and saved to disk so that after an restart the new value should be active.

In my case I could verify that ProxySQL is the cause of failures because when using master only my tasks works fine again.
If you have a single master-slave version only you can also try out maxscale to verify if it works there, too. In my case it didn't worked because AWS RDS allows only gtid_domain_id=0 and our production and staging RDS instances were mixed up by maxscale.

renecannao · 2018-07-14T06:26:01Z

Some information that could be useful here:

SHOW MYSQL VARIABLES;
SELECT * FROM stats.stats_mysql_global ORDER BY Variable_Name;
SELECT * FROM mysql_query_rules;
SELECT * FROM monitor.mysql_server_ping_log LIMIT 30;
SELECT * FROM mysql_servers;
SELECT * FROM stats_mysql_connection_pool;
SELECT * FROM stats_mysql_query_digest WHERE digest_text LIKE '%timeout%';

Reiner030 · 2018-08-06T17:56:16Z

Hi, sorry for late answer but this problems happens only in production system and only when the system is under high load. But today we got another exception - this time not on Debian Stretch/9 but on Jessie/8 also running latest ProxySQL 1.4.9.

While I saved above rules and additional system parameters I realized that just at the time when the disconnect happens ProxySQL requested more memory and seems to have problems with this task.
So perhaps this error would gone with your already applyed memory leak patch for 1.4.10 ?

After restarting ProxySQL the memory is / processes runs again fine:

Full system stats
System stats without mapping
I saw a little late that there is also Metadata memory increased

First system stats after disconnect (and all backend workers stopped);

root@server ~ # ps uf 4268 16612
USER       PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
root      4268  0.0  0.0  36284  3784 ?        S    Aug03   0:00 proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql
root     16612  118  0.0 914504 73616 ?        Sl   08:40  83:17  \_ proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql

root@server ~ # cat /proc/4268/limits
Limit                     Soft Limit           Hard Limit           Units
Max cpu time              unlimited            unlimited            seconds
Max file size             unlimited            unlimited            bytes
Max data size             unlimited            unlimited            bytes
Max stack size            8388608              unlimited            bytes
Max core file size        1099511627776        1099511627776        bytes
Max resident set          unlimited            unlimited            bytes
Max processes             515239               515239               processes
Max open files            102400               102400               files
Max locked memory         65536                65536                bytes
Max address space         unlimited            unlimited            bytes
Max file locks            unlimited            unlimited            locks
Max pending signals       515239               515239               signals
Max msgqueue size         819200               819200               bytes
Max nice priority         0                    0
Max realtime priority     0                    0
Max realtime timeout      unlimited            unlimited            us

root@server ~ # cat /proc/16612/limits
Limit                     Soft Limit           Hard Limit           Units
Max cpu time              unlimited            unlimited            seconds
Max file size             unlimited            unlimited            bytes
Max data size             unlimited            unlimited            bytes
Max stack size            8388608              unlimited            bytes
Max core file size        1099511627776        1099511627776        bytes
Max resident set          unlimited            unlimited            bytes
Max processes             515239               515239               processes
Max open files            102400               102400               files
Max locked memory         65536                65536                bytes
Max address space         unlimited            unlimited            bytes
Max file locks            unlimited            unlimited            locks
Max pending signals       515239               515239               signals
Max msgqueue size         819200               819200               bytes
Max nice priority         0                    0
Max realtime priority     0                    0
Max realtime timeout      unlimited            unlimited            us



top - 09:54:48 up 31 days,  6:58,  2 users,  load average: 0.45, 0.64, 3.56
Tasks: 354 total,   1 running, 353 sleeping,   0 stopped,   0 zombie
%Cpu0  :  0.7 us,  2.7 sy,  7.1 ni, 88.8 id,  0.0 wa,  0.0 hi,  0.7 si,  0.0 st
%Cpu1  :  2.0 us,  1.0 sy,  0.0 ni, 95.3 id,  0.0 wa,  0.0 hi,  1.7 si,  0.0 st
%Cpu2  :  1.4 us,  1.0 sy,  0.3 ni, 96.6 id,  0.0 wa,  0.0 hi,  0.7 si,  0.0 st
%Cpu3  :  1.4 us,  1.4 sy,  0.0 ni, 97.3 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
%Cpu4  :  1.4 us,  3.8 sy, 11.6 ni, 83.2 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
%Cpu5  :  1.7 us,  1.4 sy,  0.0 ni, 96.9 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
%Cpu6  :  0.7 us,  2.1 sy,  0.7 ni, 95.8 id,  0.0 wa,  0.0 hi,  0.7 si,  0.0 st
%Cpu7  :  0.3 us,  1.4 sy,  0.0 ni, 98.3 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
%Cpu8  :  1.0 us,  2.7 sy,  4.5 ni, 91.4 id,  0.0 wa,  0.0 hi,  0.3 si,  0.0 st
%Cpu9  :  0.7 us,  1.0 sy,  0.3 ni, 97.6 id,  0.0 wa,  0.0 hi,  0.3 si,  0.0 st
%Cpu10 :  0.7 us,  2.1 sy,  5.2 ni, 92.1 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
%Cpu11 :  1.0 us,  3.1 sy,  4.8 ni, 91.0 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem:  13193021+total, 24070376 used, 10785984+free,  1656984 buffers
KiB Swap: 16777212 total,        0 used, 16777212 free. 17387844 cached Mem

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
...
16612 root      20   0  914504  68296   6364 S   0.3  0.1  83:17.42 proxysql
...

and in batch mode I got these both usages multiple times::

root@server ~ # top -b | grep proxysql
  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
 4268 root      20   0   36284   3784   3008 S   0.0  0.0   0:00.00 proxysql
16612 root      20   0  914504  68724   6364 S   0.0  0.1  83:17.80 proxysql

16612 root      20   0  914504  68724   6364 S   0.3  0.1  83:17.81 proxysql
 4268 root      20   0   36284   3784   3008 S   0.0  0.0   0:00.00 proxysql

Here sanitized output of requested queries:

ProxySQL> SHOW MYSQL VARIABLES;
+-----------------------------------------------------+-----------------------------------------------------+
| Variable_name                                       | Value                                               |
+-----------------------------------------------------+-----------------------------------------------------+
| mysql-autocommit_false_is_transaction               | false                                               |
| mysql-autocommit_false_not_reusable                 | false                                               |
| mysql-client_found_rows                             | true                                                |
| mysql-client_multi_statements                       | true                                                |
| mysql-commands_stats                                | true                                                |
| mysql-connect_retries_delay                         | 500                                                 |
| mysql-connect_retries_on_failure                    | 30                                                  |
| mysql-connect_timeout_server                        | 3000                                                |
| mysql-connect_timeout_server_max                    | 60000                                               |
| mysql-connection_delay_multiplex_ms                 | 0                                                   |
| mysql-connection_max_age_ms                         | 3000                                                |
| mysql-connpoll_reset_queue_length                   | 50                                                  |
| mysql-default_charset                               | utf8                                                |
| mysql-default_max_latency_ms                        | 1000                                                |
| mysql-default_query_delay                           | 0                                                   |
| mysql-default_query_timeout                         | 36000000                                            |
| mysql-default_reconnect                             | true                                                |
| mysql-default_schema                                | information_schema                                  |
| mysql-default_sql_mode                              |                                                     |
| mysql-default_time_zone                             | SYSTEM                                              |
| mysql-enforce_autocommit_on_reads                   | false                                               |
| mysql-eventslog_filename                            |                                                     |
| mysql-eventslog_filesize                            | 104857600                                           |
| mysql-forward_autocommit                            | false                                               |
| mysql-free_connections_pct                          | 10                                                  |
| mysql-have_compress                                 | true                                                |
| mysql-hostgroup_manager_verbose                     | 1                                                   |
| mysql-init_connect                                  |                                                     |
| mysql-interfaces                                    | 127.0.0.1:6033;172.16.30.17:6033;/tmp/proxysql.sock |
| mysql-long_query_time                               | 1000                                                |
| mysql-max_allowed_packet                            | 268435456                                           |
| mysql-max_connections                               | 4096                                                |
| mysql-max_stmts_cache                               | 10000                                               |
| mysql-max_stmts_per_connection                      | 20                                                  |
| mysql-max_transaction_time                          | 14400000                                            |
| mysql-mirror_max_concurrency                        | 16                                                  |
| mysql-mirror_max_queue_length                       | 32000                                               |
| mysql-monitor_connect_interval                      | 20000                                               |
| mysql-monitor_connect_timeout                       | 6000                                                |
| mysql-monitor_enabled                               | true                                                |
| mysql-monitor_groupreplication_healthcheck_interval | 5000                                                |
| mysql-monitor_groupreplication_healthcheck_timeout  | 800                                                 |
| mysql-monitor_history                               | 600000                                              |
| mysql-monitor_password                              | XXXXXXXXXXXXXXXX                                    |
| mysql-monitor_ping_interval                         | 10000                                               |
| mysql-monitor_ping_max_failures                     | 3                                                   |
| mysql-monitor_ping_timeout                          | 6000                                                |
| mysql-monitor_query_interval                        | 60000                                               |
| mysql-monitor_query_timeout                         | 100                                                 |
| mysql-monitor_read_only_interval                    | 1500                                                |
| mysql-monitor_read_only_max_timeout_count           | 3                                                   |
| mysql-monitor_read_only_timeout                     | 500                                                 |
| mysql-monitor_replication_lag_interval              | 10000                                               |
| mysql-monitor_replication_lag_timeout               | 1000                                                |
| mysql-monitor_replication_lag_use_percona_heartbeat |                                                     |
| mysql-monitor_slave_lag_when_null                   | 60                                                  |
| mysql-monitor_username                              | proxysql_monitor                                    |
| mysql-monitor_wait_timeout                          | true                                                |
| mysql-monitor_writer_is_also_reader                 | true                                                |
| mysql-multiplexing                                  | false                                               |
| mysql-ping_interval_server_msec                     | 120000                                              |
| mysql-ping_timeout_server                           | 500                                                 |
| mysql-poll_timeout                                  | 2000                                                |
| mysql-poll_timeout_on_failure                       | 100                                                 |
| mysql-query_cache_size_MB                           | 256                                                 |
| mysql-query_digests                                 | true                                                |
| mysql-query_digests_lowercase                       | false                                               |
| mysql-query_digests_max_digest_length               | 2048                                                |
| mysql-query_digests_max_query_length                | 65000                                               |
| mysql-query_processor_iterations                    | 0                                                   |
| mysql-query_processor_regex                         | 1                                                   |
| mysql-query_retries_on_failure                      | 1                                                   |
| mysql-server_capabilities                           | 45578                                               |
| mysql-server_version                                | 5.5.30                                              |
| mysql-servers_stats                                 | true                                                |
| mysql-session_idle_ms                               | 1000                                                |
| mysql-session_idle_show_processlist                 | true                                                |
| mysql-sessions_sort                                 | true                                                |
| mysql-shun_on_failures                              | 5                                                   |
| mysql-shun_recovery_time_sec                        | 10                                                  |
| mysql-ssl_p2s_ca                                    |                                                     |
| mysql-ssl_p2s_cert                                  |                                                     |
| mysql-ssl_p2s_cipher                                |                                                     |
| mysql-ssl_p2s_key                                   |                                                     |
| mysql-stacksize                                     | 1048576                                             |
| mysql-stats_time_backend_query                      | false                                               |
| mysql-stats_time_query_processor                    | false                                               |
| mysql-threads                                       | 12                                                  |
| mysql-threshold_query_length                        | 524288                                              |
| mysql-threshold_resultset_size                      | 4194304                                             |
| mysql-throttle_connections_per_sec_to_hostgroup     | 1000000                                             |
| mysql-throttle_max_bytes_per_second_to_client       | 2147483647                                          |
| mysql-throttle_ratio_server_to_client               | 0                                                   |
| mysql-verbose_query_error                           | true                                                |
| mysql-wait_timeout                                  | 3600000                                             |
+-----------------------------------------------------+-----------------------------------------------------+
95 rows in set (0.00 sec)

ProxySQL> SELECT * FROM stats.stats_mysql_global ORDER BY Variable_Name;
+------------------------------+----------------+
| Variable_Name                | Variable_Value |
+------------------------------+----------------+
| Active_Transactions          | 0              |
| Backend_query_time_nsec      | 0              |
| Client_Connections_aborted   | 0              |
| Client_Connections_connected | 0              |
| Client_Connections_created   | 18669          |
| Client_Connections_non_idle  | 0              |
| Com_autocommit               | 0              |
| Com_autocommit_filtered      | 0              |
| Com_backend_change_user      | 0              |
| Com_backend_init_db          | 218            |
| Com_backend_set_names        | 0              |
| Com_backend_stmt_close       | 0              |
| Com_backend_stmt_execute     | 0              |
| Com_backend_stmt_prepare     | 0              |
| Com_commit                   | 133993         |
| Com_commit_filtered          | 0              |
| Com_frontend_init_db         | 0              |
| Com_frontend_set_names       | 0              |
| Com_frontend_stmt_close      | 0              |
| Com_frontend_stmt_execute    | 0              |
| Com_frontend_stmt_prepare    | 0              |
| Com_frontend_use_db          | 0              |
| Com_rollback                 | 5              |
| Com_rollback_filtered        | 0              |
| ConnPool_get_conn_failure    | 0              |
| ConnPool_get_conn_immediate  | 0              |
| ConnPool_get_conn_success    | 37301          |
| ConnPool_memory_bytes        | 1152           |
| Mirror_concurrency           | 0              |
| Mirror_queue_length          | 0              |
| MyHGM_myconnpoll_destroy     | 6              |
| MyHGM_myconnpoll_get         | 37301          |
| MyHGM_myconnpoll_get_ok      | 37301          |
| MyHGM_myconnpoll_push        | 37880          |
| MyHGM_myconnpoll_reset       | 37880          |
| MySQL_Monitor_Workers        | 16             |
| MySQL_Thread_Workers         | 12             |
| ProxySQL_Uptime              | 3853           |
| Queries_backends_bytes_recv  | 8085856442     |
| Queries_backends_bytes_sent  | 6259086298     |
| Queries_frontends_bytes_recv | 6364969234     |
| Queries_frontends_bytes_sent | 30817365258    |
| Query_Cache_Entries          | 0              |
| Query_Cache_Memory_bytes     | 0              |
| Query_Cache_Purged           | 0              |
| Query_Cache_bytes_IN         | 0              |
| Query_Cache_bytes_OUT        | 0              |
| Query_Cache_count_GET        | 0              |
| Query_Cache_count_GET_OK     | 0              |
| Query_Cache_count_SET        | 0              |
| Query_Processor_time_nsec    | 0              |
| Questions                    | 20734107       |
| SQLite3_memory_bytes         | 2761776        |
| Server_Connections_aborted   | 0              |
| Server_Connections_connected | 0              |
| Server_Connections_created   | 26885          |
| Server_Connections_delayed   | 0              |
| Servers_table_version        | 1              |
| Slow_queries                 | 2              |
| Stmt_Cached                  | 0              |
| Stmt_Client_Active_Total     | 0              |
| Stmt_Client_Active_Unique    | 0              |
| Stmt_Max_Stmt_id             | 1              |
| Stmt_Server_Active_Total     | 0              |
| Stmt_Server_Active_Unique    | 0              |
| mysql_backend_buffers_bytes  | 0              |
| mysql_frontend_buffers_bytes | 0              |
| mysql_session_internal_bytes | 56352          |
+------------------------------+----------------+
68 rows in set (0.00 sec)

For backend server is production-slave1 = staging-slave1 using internal DNS resolution because our frontend servers stays in AWS region and use production-slave1 = production-master and staging-slave1 = staging-master with same configuration setup.

The simple practical example rules fits very well for our non-AWS backend to minimize outgoing expensive AWS traffic with 0,09 $/GB (compared to 1,39 €/TB reverse from external ISP connected with IPSec tunnels) :

ProxySQL> SELECT * FROM mysql_query_rules;
+---------+--------+----------+------------------+--------+-------------+------------+------------+--------+--------------+------------------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+-----+-------+---------+
| rule_id | active | username | schemaname       | flagIN | client_addr | proxy_addr | proxy_port | digest | match_digest | match_pattern          | negate_match_pattern | re_modifiers | flagOUT | replace_pattern | destination_hostgroup | cache_ttl | reconnect | timeout | retries | delay | next_query_flagIN | mirror_flagOUT | mirror_hostgroup | error_msg | OK_msg | sticky_conn | multiplex | log | apply | comment |
+---------+--------+----------+------------------+--------+-------------+------------+------------+--------+--------------+------------------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+-----+-------+---------+
| 40010   | 1      | NULL     | STORE_staging    | 0      | NULL        | NULL       | NULL       | NULL   | NULL         | ^SELECT .* FOR UPDATE$ | 0                    | CASELESS     | NULL    | NULL            | NULL                  | NULL      | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL | 1     | NULL    |
| 40011   | 1      | NULL     | STORE_staging    | 0      | NULL        | NULL       | NULL       | NULL   | NULL         | ^SELECT                | 0                    | CASELESS     | NULL    | NULL            | 401                   | NULL      | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL | 1     | NULL    |
...

| 50010   | 1      | NULL     | STORE_production | 0      | NULL        | NULL       | NULL       | NULL   | NULL         | ^SELECT .* FOR UPDATE$ | 0                    | CASELESS     | NULL    | NULL            | NULL                  | NULL      | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL | 1     | NULL    |
| 50011   | 1      | NULL     | STORE_production | 0      | NULL        | NULL       | NULL       | NULL   | NULL         | ^SELECT                | 0                    | CASELESS     | NULL    | NULL            | 501                   | NULL      | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL | 1     | NULL   

...

+---------+--------+----------+------------------+--------+-------------+------------+------------+--------+--------------+------------------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+-----+-------+---------+
|-----+-------------------+----------------+------------------+-----------+--------+-------------+-----------+-----+-------+---------+
44 rows in set (0.00 sec)

ProxySQL> SELECT * FROM monitor.mysql_server_ping_log LIMIT 30;
+-------------------+------+------------------+----------------------+------------+
| hostname          | port | time_start_us    | ping_success_time_us | ping_error |
+-------------------+------+------------------+----------------------+------------+
| db-slave2         | 3306 | 1533548095843314 | 256                  | NULL       |
| production-slave1 | 3306 | 1533548095845459 | 268                  | NULL       |
| staging-slave1    | 3306 | 1533548095847661 | 230                  | NULL       |
| production-master | 3306 | 1533548095844387 | 6619                 | NULL       |
| staging-master    | 3306 | 1533548095846585 | 6377                 | NULL       |
| db-slave2         | 3306 | 1533548105843412 | 308                  | NULL       |
| production-slave1 | 3306 | 1533548105845609 | 243                  | NULL       |
| staging-slave1    | 3306 | 1533548105847842 | 224                  | NULL       |
| production-master | 3306 | 1533548105844495 | 6709                 | NULL       |
| staging-master    | 3306 | 1533548105846739 | 6249                 | NULL       |
| db-slave2         | 3306 | 1533548115843544 | 271                  | NULL       |
| production-slave1 | 3306 | 1533548115845730 | 275                  | NULL       |
| staging-slave1    | 3306 | 1533548115847898 | 235                  | NULL       |
| production-master | 3306 | 1533548115844619 | 6616                 | NULL       |
| staging-master    | 3306 | 1533548115846802 | 6173                 | NULL       |
| db-slave2         | 3306 | 1533548125843540 | 335                  | NULL       |
| production-slave1 | 3306 | 1533548125845756 | 231                  | NULL       |
| staging-slave1    | 3306 | 1533548125847934 | 202                  | NULL       |
| production-master | 3306 | 1533548125844613 | 6549                 | NULL       |
| staging-master    | 3306 | 1533548125846839 | 6144                 | NULL       |
| db-slave2         | 3306 | 1533548135843763 | 295                  | NULL       |
| production-slave1 | 3306 | 1533548135845908 | 243                  | NULL       |
| staging-slave1    | 3306 | 1533548135848137 | 298                  | NULL       |
| production-master | 3306 | 1533548135844860 | 6605                 | NULL       |
| staging-master    | 3306 | 1533548135847033 | 6443                 | NULL       |
| db-slave2         | 3306 | 1533548145843829 | 241                  | NULL       |
| production-slave1 | 3306 | 1533548145846016 | 236                  | NULL       |
| staging-slave1    | 3306 | 1533548145848172 | 185                  | NULL       |
| production-master | 3306 | 1533548145844941 | 6603                 | NULL       |
| staging-master    | 3306 | 1533548145847092 | 6323                 | NULL       |
+-------------------+------+------------------+----------------------+------------+
30 rows in set (0.01 sec) 

ProxySQL> SELECT * FROM monitor.mysql_server_ping_log WHERE ping_error NOT NULL LIMIT 30;
Empty set (0.00 sec)

ProxySQL> SELECT * FROM monitor.mysql_server_ping_log WHERE ping_success_time_us > 10000 LIMIT 30;
Empty set (0.00 sec)

ProxySQL> SELECT * FROM mysql_servers;
+--------------+-------------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------------+
| hostgroup_id | hostname          | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment                     |
+--------------+-------------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------------+
| 500          | production-master | 3306 | ONLINE | 1      | 1           | 8192            | 0                   | 0       | 0              | production: master          |
| 501          | production-master | 3306 | ONLINE | 1      | 1           | 8192            | 0                   | 0       | 0              | production: master-readonly |
| 501          | db-slave2         | 3306 | ONLINE | 6666   | 1           | 8192            | 10                  | 0       | 0              | production: slave db-slave2 |
| 501          | production-slave1 | 3306 | ONLINE | 9999   | 1           | 8192            | 10                  | 0       | 0              | production: slave           |
| 400          | staging-master    | 3306 | ONLINE | 1      | 1           | 8192            | 0                   | 0       | 0              | staging: master             |
| 401          | staging-master    | 3306 | ONLINE | 1      | 1           | 8192            | 0                   | 0       | 0              | staging: master-readonly    |
| 401          | db-slave2         | 3306 | ONLINE | 6666   | 1           | 8192            | 10                  | 0       | 0              | production: slave db-slave2 |
| 401          | staging-slave1    | 3306 | ONLINE | 9999   | 1           | 8192            | 10                  | 0       | 0              | staging: slave              |
+--------------+-------------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------------+
8 rows in set (0.00 sec)

 ProxySQL> SELECT * FROM stats_mysql_connection_pool;
+-----------+-------------------+----------+--------+----------+----------+--------+---------+----------+-----------------+-----------------+------------+
| hostgroup | srv_host          | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | Queries  | Bytes_data_sent | Bytes_data_recv | Latency_us |
+-----------+-------------------+----------+--------+----------+----------+--------+---------+----------+-----------------+-----------------+------------+
| 500       | production-master | 3306     | ONLINE | 0        | 0        | 11302  | 0       | 468934   | 57773127        | 17465283        | 6595       |
| 501       | production-master | 3306     | ONLINE | 0        | 0        | 0      | 0       | 0        | 0               | 0               | 6595       |
| 501       | db-slave2         | 3306     | ONLINE | 0        | 0        | 4516   | 0       | 8201812  | 2463800721      | 3015515239      | 293        |
| 501       | production-slave1 | 3306     | ONLINE | 0        | 0        | 6895   | 0       | 11419598 | 3463047888      | 4459012970      | 232        |
| 400       | staging-master    | 3306     | ONLINE | 52       | 1        | 2137   | 0       | 26669    | 4085447         | 385353          | 6248       |
| 401       | staging-master    | 3306     | ONLINE | 0        | 0        | 0      | 0       | 0        | 0               | 0               | 6248       |
| 401       | db-slave2         | 3306     | ONLINE | 17       | 3        | 813    | 0       | 233764   | 103510265       | 216100481       | 293        |
| 401       | staging-slave1    | 3306     | ONLINE | 33       | 3        | 1331   | 0       | 393772   | 171005108       | 387715168       | 236        |
+-----------+-------------------+----------+--------+----------+----------+--------+---------+----------+-----------------+-----------------+------------+
8 rows in set (0.00 sec)

ProxySQL> SELECT * FROM stats_mysql_query_digest WHERE digest_text LIKE '%timeout%';
Empty set (0.03 sec)

ewangzenefits · 2018-09-07T22:32:32Z

I have the similar issue but not that often:

mysql> \! cat /var/lib/proxysql/proxysql.log | grep '2018-09-07 07:'| grep 'Detected a broken connection' | wc -l
8

error log:
======
2018-09-07 22:17:59 MySQL_Session.cpp:2837:handler(): [ERROR] Detected a broken connection during query on (3801,10.0.33.23,3306) , FD (Conn:153 , MyDS:153) : 2013, Lost connection to MySQL server during query

mysql> select * from global_variables where variable_name like '%timeout%';
+----------------------------------------------------+----------------+
| variable_name                                      | variable_value |
+----------------------------------------------------+----------------+
| mysql-connect_timeout_server_max                   | 300000         |
| mysql-monitor_connect_timeout                      | 600            |
| mysql-monitor_ping_timeout                         | 1000           |
| mysql-monitor_read_only_max_timeout_count          | 3              |
| 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-poll_timeout_on_failure                      | 100            |
| mysql-default_query_timeout                        | 36000000       |
| mysql-poll_timeout                                 | 2000           |
| mysql-connect_timeout_server                       | 3000           |
| mysql-monitor_read_only_timeout                    | 500            |
| mysql-ping_timeout_server                          | 500            |
+----------------------------------------------------+----------------+
15 rows in set (0.00 sec)

Reiner030 · 2018-09-08T17:21:43Z

it could be that you also have multiplexing off and your backend is perhaps disconnected?
Then it could be a problem that the backend connection is "sticky" and therefore ProxySQL must hook off the connection to the client like here explained:
#1632 (comment) / #1632 (comment)

kk86bioinfo · 2019-02-15T03:02:53Z

I have tried to setup galera clusters with mysql 5.7 altogether with ProxySQL (version 1.4.12-percona-1.2, codename Truls) with one of my legacy java web application.

The application have been upgraded recently to deploy with tomcat 9, JDK 1.8 and using mysql-connector-java-8.0.14.jar, and spring-jdbc-3.2.9.RELEASE.jar to perform apache commons dbcp connection pool with Proxysql.

We have 4 nodes split with 3 read and 1 write node using mysql_query_rules to define their destination hostgroup.

The is the error messages that render our application cannot issue any query to proxysql over randomly manner.

cclose · 2019-02-21T23:57:28Z

I am getting very similar behavior. My wait_timeout is also the default 28800 (8 hours) and none of the connections used by proxysql are even close to that limit when the messages start appearing.

I tried the idea that the db was closing the backend connection, but that didn't reproduce the error. I had a specific user connect to a specific database so that I could positively identity it on the backend db. I then killed the process on the backend db and issued a query from the client. The result returned with no error message. I checked and while the proxysql processlist ID hadn't changed, the backend connection had simply moved to a different host. No errors in the proxy error log.

I have multiplexing ON. I noticed when i do SELECT @@wait_timeout when connected through proxysql, i get 3610, and a direct connection says 28800;

mysql> select * from global_variables where variable_name like '%timeout%';
+----------------------------------------------------+----------------+
| variable_name                                      | variable_value |
+----------------------------------------------------+----------------+
| mysql-connect_timeout_server_max                   | 10000          |
| mysql-monitor_connect_timeout                      | 600            |
| mysql-monitor_ping_timeout                         | 1000           |
| mysql-monitor_read_only_max_timeout_count          | 3              |
| mysql-monitor_replication_lag_timeout              | 1000           |
| mysql-monitor_groupreplication_healthcheck_timeout | 800            |
| mysql-monitor_galera_healthcheck_timeout           | 800            |
| mysql-monitor_query_timeout                        | 100            |
| mysql-monitor_wait_timeout                         | true           |
| mysql-wait_timeout                                 | 28800000       |
| mysql-poll_timeout_on_failure                      | 100            |
| mysql-default_query_timeout                        | 36000000       |
| mysql-poll_timeout                                 | 2000           |
| mysql-connect_timeout_server                       | 3000           |
| mysql-monitor_read_only_timeout                    | 500            |
| mysql-ping_timeout_server                          | 500            |
+----------------------------------------------------+----------------+

mysql> select * from global_variables where variable_name like '%multiplex%';
+--------------------------------------+----------------+
| variable_name                        | variable_value |
+--------------------------------------+----------------+
| mysql-connection_delay_multiplex_ms  | 0              |
| mysql-multiplexing                   | true           |
| mysql-auto_increment_delay_multiplex | 5              |
+--------------------------------------+----------------+

cclose · 2019-02-25T22:59:27Z

I downgraded my instance to 1.4.14, but i continue to see the same behavior with and without multiplexing.

ewangzenefits · 2019-05-01T20:53:57Z

Hey @renecannao

I recently upgraded proxysql from V1.4.15 to V2.0.3, the same DB server and the same apps, the only change is use proxysql V2.0.3 to replace proxysql1.4.15, However after upgrading, I started to see many "Detected a broken connection during query", I used to see NONE this error with proxysql1.4.15.

I noticed that after 2.0.3, there is an extra variable mysql-reset_connection_algorithm default to 2 stands for the new algorithm, but after I changed that to 1(back to old algorithm), seems nothing changed, I still see many "Detected a broken connection during query"

One more thing I noticed is that the default value of mysql-server_capabilities in v2.0.3 is 569867 which is way over the max 65535 in the doc, while in proxysql v1.4.15 the default value is 45578, I changed that number in V2.0.3 to 45578. But still see many "Detected a broken connection during query".

Btw, I disabled multiplexing both on v1.4.15 and v2.0.3, and also set multiplex=0 for all the entries on mysql_query_rules.

Any idea I can do to eliminate the error "Detected a broken connection during query"?

Thank you very much!

ewangzenefits · 2019-05-01T21:03:27Z

`v2.0.3

Admin> select * from global_variables where variable_name like '%alg%';
+----------------------------------+----------------+
| variable_name | variable_value |
+----------------------------------+----------------+
| mysql-reset_connection_algorithm | 1 |
+----------------------------------+----------------+
1 row in set (0.00 sec)

Admin> select * from global_variables where variable_name like '%capa%';
+---------------------------+----------------+
| variable_name | variable_value |
+---------------------------+----------------+
| mysql-server_capabilities | 45578 |
+---------------------------+----------------+
1 row in set (0.00 sec)

v1.4.15

yiyulinfeng · 2021-07-19T02:27:51Z

There is to many error In proxysql.log ,if I connect through proxysql,
the client shows
mysql> select * from performance_schema.replication_group_members;
ERROR 4031 (HY000): The client was disconnected by the server because of inactivity. See wait_timeout and interactive_timeout for configuring this behavior.

the proxysql.log shows
2021-07-19 10:02:48 MySQL_Session.cpp:4014:handler_minus1_LogErrorDuringQuery(): [WARNING] Error during query on (30,10.192.168.21,3307,83027): 4031, The client was disconnected by the server because of inactivity. See wait_timeout and interactive_timeout for configuring this behavior.
2021-07-19 10:05:05 MySQL_Session.cpp:1481:handler_again___status_PINGING_SERVER(): [ERROR] Detected a broken connection during ping on (30,10.192.168.21,3307) , FD (Conn:111 , MyDS:111) : 2006, MySQL server has gone away
2021-07-19 10:15:42 MySQL_Session.cpp:1481:handler_again___status_PINGING_SERVER(): [ERROR] Detected a broken connection during ping on (30,10.192.168.21,3307) , FD (Conn:111 , MyDS:111) : 4031, The client was disconnected by the server because of inactivity. See wait_timeout and interactive_timeout for configuring this behavior.
2021-07-19 10:15:42 MySQL_Session.cpp:1481:handler_again___status_PINGING_SERVER(): [ERROR] Detected a broken connection during ping on (30,10.192.168.28,3307) , FD (Conn:109 , MyDS:109) : 4031, The client was disconnected by the server because of inactivity. See wait_timeout and interactive_timeout for configuring this behavior.
But if I connect to mysql directly , the session is work normal all the time。

the timeout setting in proxysql
mysql> show variables like '%timeout%';
+--------------------------------------------------------------+----------+
| Variable_name | Value |
+--------------------------------------------------------------+----------+
| mysql-connect_timeout_client | 200000 |
| mysql-connect_timeout_server_max | 400000 |
| mysql-monitor_connect_timeout | 1600 |
| mysql-monitor_ping_timeout | 1000 |
| mysql-monitor_read_only_timeout | 500 |
| mysql-monitor_read_only_max_timeout_count | 3 |
| mysql-monitor_replication_lag_timeout | 1000 |
| mysql-monitor_groupreplication_healthcheck_timeout | 1200 |
| mysql-monitor_groupreplication_healthcheck_max_timeout_count | 3 |
| mysql-monitor_galera_healthcheck_timeout | 800 |
| mysql-monitor_galera_healthcheck_max_timeout_count | 3 |
| mysql-monitor_query_timeout | 500 |
| mysql-monitor_wait_timeout | true |
| mysql-wait_timeout | 28800000 |
| mysql-default_query_timeout | 86400000 |
| mysql-ping_timeout_server | 200000 |
| mysql-poll_timeout | 2000 |
| mysql-poll_timeout_on_failure | 100 |
| mysql-connect_timeout_server | 20000 |
+--------------------------------------------------------------+----------+

Reiner030 mentioned this issue Aug 15, 2018

ProxySQL crash 1.4.10 / seems to be query result related #1649

Open

Reiner030 mentioned this issue Sep 13, 2018

Unexpected packet from client, disconnecting the client #1689

Open

sbostick mentioned this issue Aug 3, 2019

Broken connections, Lost connections, and Ping timeouts against Aurora #2176

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Broken connections #1600

Broken connections #1600

mihaibrehar commented Jul 10, 2018

Reiner030 commented Jul 11, 2018

mihaibrehar commented Jul 12, 2018 •

edited

Loading

Reiner030 commented Jul 12, 2018 •

edited

Loading

renecannao commented Jul 14, 2018

Reiner030 commented Aug 6, 2018

ewangzenefits commented Sep 7, 2018

Reiner030 commented Sep 8, 2018

kk86bioinfo commented Feb 15, 2019

cclose commented Feb 21, 2019 •

edited

Loading

cclose commented Feb 25, 2019

ewangzenefits commented May 1, 2019

ewangzenefits commented May 1, 2019

yiyulinfeng commented Jul 19, 2021

Broken connections #1600

Broken connections #1600

Comments

mihaibrehar commented Jul 10, 2018

Reiner030 commented Jul 11, 2018

mihaibrehar commented Jul 12, 2018 • edited Loading

Reiner030 commented Jul 12, 2018 • edited Loading

renecannao commented Jul 14, 2018

Reiner030 commented Aug 6, 2018

ewangzenefits commented Sep 7, 2018

Reiner030 commented Sep 8, 2018

kk86bioinfo commented Feb 15, 2019

cclose commented Feb 21, 2019 • edited Loading

cclose commented Feb 25, 2019

ewangzenefits commented May 1, 2019

ewangzenefits commented May 1, 2019

`v2.0.3

v1.4.15

yiyulinfeng commented Jul 19, 2021

mihaibrehar commented Jul 12, 2018 •

edited

Loading

Reiner030 commented Jul 12, 2018 •

edited

Loading

cclose commented Feb 21, 2019 •

edited

Loading