MySQL Bugs: #50963: add information_schema.error

Bug #50963	add information_schema.error_count
Submitted:	6 Feb 2010 18:09	Modified:	6 Feb 2010 18:15
Reporter:	Mark Callaghan	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S4 (Feature request)
Version:	5.1	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Connection, debug, error

Description:
See http://www.facebook.com/notes/mysqlfacebook/monitoring-for-errors/293204560932

I want to debug connection errors as the SHOW STATUS reports a high rate for "aborted connects". MySQL provides nothing to make that possible and I must look at client logs to determine the errors. That works assuming I know where the client logs are, I can read the client logs and the clients were considerate enough to write such logs. Rarely are all three true in large deployments.

MySQL can make this easier with:
* information_schema.user_statistics -- report failed connection attempts per user. There are several counters -- bad password, network error, bad db name, no access. But this request is not open for that feature.
* information_schema.error_counts - this can have 1 row per error code and increment the count per error code in the (hopefully) small number of functions where errors are set per connection

This feature request is open for information_schema.error_counts

How to repeat:
read mysqlatfacebook

Thank you for the useful feature request.

This is a related feature request:
http://bugs.mysql.com/bug.php?id=45817

These are also related:
http://bugs.mysql.com/bug.php?id=22821
http://bugs.mysql.com/bug.php?id=24906

I think this is solved in MySQL 8.0 as performance schema now exposes various error counters.

 8.0.26 127.0.0.1:18026+   SQL  SELECT * FROM performance_schema.events_errors_summary_global_by_error WHERE SUM_ERROR_RAISED>0;
+--------------+--------------------------------------+-----------+------------------+-------------------+---------------------+---------------------+
| ERROR_NUMBER | ERROR_NAME                           | SQL_STATE | SUM_ERROR_RAISED | SUM_ERROR_HANDLED | FIRST_SEEN          | LAST_SEEN           |
+--------------+--------------------------------------+-----------+------------------+-------------------+---------------------+---------------------+
|         1287 | ER_WARN_DEPRECATED_SYNTAX            | HY000     |                5 |                 0 | 2021-09-16 08:54:48 | 2021-09-16 08:56:28 |
|         1426 | ER_TOO_BIG_PRECISION                 | 42000     |                5 |                 0 | 2021-09-15 16:59:09 | 2021-09-16 08:59:54 |
|         3554 | ER_NO_SYSTEM_TABLE_ACCESS            | HY000     |               46 |                 0 | 2021-09-15 17:00:16 | 2021-09-16 09:04:34 |
|         3719 | ER_DEPRECATED_UTF8_ALIAS             | HY000     |               34 |                 0 | 2021-09-15 09:37:27 | 2021-09-15 09:37:27 |
|         3778 | ER_WARN_DEPRECATED_UTF8MB3_COLLATION | HY000     |               73 |                 0 | 2021-09-15 09:37:27 | 2021-09-15 09:37:27 |
|         4031 | ER_CLIENT_INTERACTION_TIMEOUT        | HY000     |                1 |                 0 | 2021-09-16 01:00:25 | 2021-09-16 01:00:25 |
+--------------+--------------------------------------+-----------+------------------+-------------------+---------------------+---------------------+
6 rows in set (0.0056 sec)
8.0.26 127.0.0.1:18026+   SQL  SHOW TABLES FROM performance_schema like 'events\_errors\_%';
+--------------------------------------------------+
| Tables_in_performance_schema (events\_errors\_%) |
+--------------------------------------------------+
| events_errors_summary_by_account_by_error        |
| events_errors_summary_by_host_by_error           |
| events_errors_summary_by_thread_by_error         |
| events_errors_summary_by_user_by_error           |
| events_errors_summary_global_by_error            |
+--------------------------------------------------+
5 rows in set (0.0019 sec)
8.0.26 127.0.0.1:18026+   SQL  DESCRIBE performance_schema.host_cache;
+--------------------------------------------+------------------+------+-----+---------------------+-------+
| Field                                      | Type             | Null | Key | Default             | Extra |
+--------------------------------------------+------------------+------+-----+---------------------+-------+
| IP                                         | varchar(64)      | NO   | PRI | NULL                |       |
| HOST                                       | varchar(255)     | YES  | MUL | NULL                |       |
| HOST_VALIDATED                             | enum('YES','NO') | NO   |     | NULL                |       |
| SUM_CONNECT_ERRORS                         | bigint           | NO   |     | NULL                |       |
| COUNT_HOST_BLOCKED_ERRORS                  | bigint           | NO   |     | NULL                |       |
| COUNT_NAMEINFO_TRANSIENT_ERRORS            | bigint           | NO   |     | NULL                |       |
| COUNT_NAMEINFO_PERMANENT_ERRORS            | bigint           | NO   |     | NULL                |       |
| COUNT_FORMAT_ERRORS                        | bigint           | NO   |     | NULL                |       |
| COUNT_ADDRINFO_TRANSIENT_ERRORS            | bigint           | NO   |     | NULL                |       |
| COUNT_ADDRINFO_PERMANENT_ERRORS            | bigint           | NO   |     | NULL                |       |
| COUNT_FCRDNS_ERRORS                        | bigint           | NO   |     | NULL                |       |
| COUNT_HOST_ACL_ERRORS                      | bigint           | NO   |     | NULL                |       |
| COUNT_NO_AUTH_PLUGIN_ERRORS                | bigint           | NO   |     | NULL                |       |
| COUNT_AUTH_PLUGIN_ERRORS                   | bigint           | NO   |     | NULL                |       |
| COUNT_HANDSHAKE_ERRORS                     | bigint           | NO   |     | NULL                |       |
| COUNT_PROXY_USER_ERRORS                    | bigint           | NO   |     | NULL                |       |
| COUNT_PROXY_USER_ACL_ERRORS                | bigint           | NO   |     | NULL                |       |
| COUNT_AUTHENTICATION_ERRORS                | bigint           | NO   |     | NULL                |       |
| COUNT_SSL_ERRORS                           | bigint           | NO   |     | NULL                |       |
| COUNT_MAX_USER_CONNECTIONS_ERRORS          | bigint           | NO   |     | NULL                |       |
| COUNT_MAX_USER_CONNECTIONS_PER_HOUR_ERRORS | bigint           | NO   |     | NULL                |       |
| COUNT_DEFAULT_DATABASE_ERRORS              | bigint           | NO   |     | NULL                |       |
| COUNT_INIT_CONNECT_ERRORS                  | bigint           | NO   |     | NULL                |       |
| COUNT_LOCAL_ERRORS                         | bigint           | NO   |     | NULL                |       |
| COUNT_UNKNOWN_ERRORS                       | bigint           | NO   |     | NULL                |       |
| FIRST_SEEN                                 | timestamp        | NO   |     | 0000-00-00 00:00:00 |       |
| LAST_SEEN                                  | timestamp        | NO   |     | 0000-00-00 00:00:00 |       |
| FIRST_ERROR_SEEN                           | timestamp        | YES  |     | 0000-00-00 00:00:00 |       |
| LAST_ERROR_SEEN                            | timestamp        | YES  |     | 0000-00-00 00:00:00 |       |
+--------------------------------------------+------------------+------+-----+---------------------+-------+
29 rows in set (0.0023 sec)

https://dev.mysql.com/doc/refman/8.0/en/performance-schema-host-cache-table.html