Bug #71186 P_S.host_cache does not collect connections aborted entries
Submitted: 20 Dec 2013 11:53 Modified: 11 Apr 2014 10:23
Reporter: Simon Mudd (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S4 (Feature request)
Version:5.6.14 OS:Any
Assigned to: Marc ALFF CPU Architecture:Any
Tags: aborted, performance_schema

[20 Dec 2013 11:53] Simon Mudd 
Description:
Warnings about aborted connections are not collected into P_S.host_cache. This information would be most useful as it can give us an idea of many possible problems.

Other warnings on a per host level may also be worth including here too.

How to repeat:
root@myserver [performance_schema]> select @@version;
+------------+
| @@version  |
+------------+
| 5.6.14-log |
+------------+
1 row in set (0.00 sec)

root@myserver [performance_schema]> select * from host_cache where host = 'myremoteclient.mask-realhostname.com'\G
*************************** 1. row ***************************
                                        IP: 111.111.111.111
                                      HOST: myremoteclient.mask-realhostname.com
                            HOST_VALIDATED: YES
                        SUM_CONNECT_ERRORS: 0
                 COUNT_HOST_BLOCKED_ERRORS: 0
           COUNT_NAMEINFO_TRANSIENT_ERRORS: 0
           COUNT_NAMEINFO_PERMANENT_ERRORS: 0
                       COUNT_FORMAT_ERRORS: 0
           COUNT_ADDRINFO_TRANSIENT_ERRORS: 0
           COUNT_ADDRINFO_PERMANENT_ERRORS: 0
                       COUNT_FCRDNS_ERRORS: 0
                     COUNT_HOST_ACL_ERRORS: 0
               COUNT_NO_AUTH_PLUGIN_ERRORS: 0
                  COUNT_AUTH_PLUGIN_ERRORS: 0
                    COUNT_HANDSHAKE_ERRORS: 0
                   COUNT_PROXY_USER_ERRORS: 0
               COUNT_PROXY_USER_ACL_ERRORS: 0
               COUNT_AUTHENTICATION_ERRORS: 611
                          COUNT_SSL_ERRORS: 0
         COUNT_MAX_USER_CONNECTIONS_ERRORS: 0
COUNT_MAX_USER_CONNECTIONS_PER_HOUR_ERRORS: 0
             COUNT_DEFAULT_DATABASE_ERRORS: 0
                 COUNT_INIT_CONNECT_ERRORS: 0
                        COUNT_LOCAL_ERRORS: 0
                      COUNT_UNKNOWN_ERRORS: 0
                                FIRST_SEEN: 2013-12-18 13:25:13
                                 LAST_SEEN: 2013-12-20 12:27:13
                          FIRST_ERROR_SEEN: 2013-12-18 13:25:13
                           LAST_ERROR_SEEN: 2013-12-18 13:28:07
1 row in set (0.00 sec)

[root@myserver ~]# grep myremoteclient.mask-realhostname.com /var/log/mysqld.log
2013-12-20 12:25:19 61547 [Warning] Aborted connection 8037795 to db: 'unconnected' user: 'haproxy' host: 'myremoteclient.mask-realhostname.com' (Got an error reading communication packets)
2013-12-20 12:25:20 61547 [Warning] Aborted connection 8037800 to db: 'unconnected' user: 'haproxy' host: 'myremoteclient.mask-realhostname.com' (Got an error reading communication packets)
2013-12-20 12:25:20 61547 [Warning] Aborted connection 8037810 to db: 'unconnected' user: 'haproxy' host: 'myremoteclient.mask-realhostname.com' (Got an error reading communication packets)
2013-12-20 12:25:20 61547 [Warning] Aborted connection 8037813 to db: 'unconnected' user: 'haproxy' host: 'myremoteclient.mask-realhostname.com' (Got an error reading communication packets)
2013-12-20 12:25:21 61547 [Warning] Aborted connection 8037856 to db: 'unconnected' user: 'haproxy' host: 'myremoteclient.mask-realhostname.com' (Got an error reading communication packets)
2013-12-20 12:25:21 61547 [Warning] Aborted connection 8037857 to db: 'unconnected' user: 'haproxy' host: 'myremoteclient.mask-realhostname.com' (Got an error reading communication packets)
2013-12-20 12:25:21 61547 [Warning] Aborted connection 8037873 to db: 'unconnected' user: 'haproxy' host: 'myremoteclient.mask-realhostname.com' (Got an error reading communication packets)
2013-12-20 12:25:21 61547 [Warning] Aborted connection 8037874 to db: 'unconnected' user: 'haproxy' host: 'myremoteclient.mask-realhostname.com' (Got an error reading communication packets)
2013-12-20 12:25:21 61547 [Warning] Aborted connection 8037875 to db: 'unconnected' user: 'haproxy' host: 'myremoteclient.mask-realhostname.com' (Got an error reading communication packets)
2013-12-20 12:25:22 61547 [Warning] Aborted connection 8037895 to db: 'unconnected' user: 'haproxy' host: 'myremoteclient.mask-realhostname.com' (Got an error reading communication packets)
2013-12-20 12:25:22 61547 [Warning] Aborted connection 8037908 to db: 'unconnected' user: 'haproxy' host: 'myremoteclient.mask-realhostname.com' (Got an error reading communication packets)
[smudd@mc101dwrdb-15 ~]$ 

Suggested fix:
Add an Aborted_connection column

and if you consider these as warnings and not errors add FIRST_WARNING_SEEN and LAST_WARNING_SEEN columns too.

Would be nice for 5.7 if this can be easily added.
[20 Dec 2013 12:20] Arnaud Adant 
Thank you for this interesting feature request.

Please note that   Aborted connection in the error log means Aborted_clients in the global status.

see http://dev.mysql.com/doc/refman/5.6/en/communication-errors.html

So I guess we should add aborted_clients there and more information on the root cause (if known).
[23 Dec 2013 7:01] Simon Mudd 
It would be good if the error / state reported is the same for consistency, but yes we are talking about the same thing. How this extra column would be named does not worry me too much.

Since often aborted connections are an issue on the client side, though they could be network related, it is most helpful to identify which client(s) might be triggering this.

Setting the global log_warnings = 2 can also be used, but that clutters up the log file much more so is not something that can be easily left enabled.
[11 Apr 2014 10:23] Simon Mudd 
Really we want to capture (per host) Aborted_clients and Aborted_connects.
This helps diagnosis of errors in determining if problems are from a specific location or are general.