In MySQL 8.0.0, the Performance Schema can now instrument server errors. There are 5 new summary tables introduced:
|
1
2
3
4
5
6
7
8
9
10
11
|
mysql> show tables like "%by_error%"; +-------------------------------------------+ | Tables_in_performance_schema (%by_error%) | +-------------------------------------------+ | 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.02 sec) |
As names suggest, statistics of errors in these tables are aggregated by error. So one can see number of time a particular error is seen by a specific user/host/account/thread or over all in the server.
Lets take a look at table’s description.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
mysql> describe performance_schema.events_errors_summary_global_by_error; +-------------------+---------------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------+---------------------+------+-----+---------------------+-------+ | ERROR_NUMBER | int(11) | YES | | NULL | | | ERROR_NAME | varchar(64) | YES | | NULL | | | SQL_STATE | varchar(5) | YES | | NULL | | | SUM_ERROR_RAISED | bigint(20) unsigned | NO | | NULL | | | SUM_ERROR_HANDLED | bigint(20) unsigned | NO | | NULL | | | FIRST_SEEN | timestamp | YES | | 0000-00-00 00:00:00 | | | LAST_SEEN | timestamp | YES | | 0000-00-00 00:00:00 | | +-------------------+---------------------+------+-----+---------------------+-------+ 7 rows in set (0.01 sec) |
ERROR_NAME/ERROR_NUMBER/SQL_STATE are self explanatory.
FIRST_SEEN/LAST_SEEN : First and last time this error was seen.
SUM_ERROR_RAISED : number of times a particular error is raised.
SUM_ERROR_HANDLED : number of times a particular error is handled.
Lets talk about SUM_ERROR_HANDLED and SUM_ERROR_RAISED. In MySQL, it is possible to handle a specific error in stored programs [via the Manual]. All those errors which were handled are counted/aggregated under SUM_ERROR_HANDLED. Whereas, SUM_ERROR_RAISED is the number of all other remaining errors which were raised but not handled.
In each of these tables, there is a specific row (NULL ROW) which would aggregate all those errors which are not falling in range of MySQL Server Errors.
Let’s see all of these in action with some examples:
|
1
2
3
4
5
6
7
8
9
|
mysql> select * from test.no_table; ERROR 1146 (42S02): Table 'test.no_table' doesn't exist mysql> SELECT * from performance_schema.events_errors_summary_global_by_error where SUM_ERROR_HANDLED>0 OR SUM_ERROR_RAISED>0; +--------------+------------------+-----------+------------------+-------------------+---------------------+---------------------+ | ERROR_NUMBER | ERROR_NAME | SQL_STATE | SUM_ERROR_RAISED | SUM_ERROR_HANDLED | FIRST_SEEN | LAST_SEEN | +--------------+------------------+-----------+------------------+-------------------+---------------------+---------------------+ | 1146 | ER_NO_SUCH_TABLE | 42S02 | 1 | 0 | 2016-06-20 15:15:21 | 2016-06-20 15:15:21 | +--------------+------------------+-----------+------------------+-------------------+---------------------+---------------------+ 1 row in set (0.02 sec) |
So we can see the error statistics is seen here. And this is aggregated under SUM_ERROR_RAISED. Now lets try to handle an error in a Stored Procedure and see its stats.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
delimiter // CREATE PROCEDURE handleError() BEGIN DECLARE no_such_table CONDITION FOR 1146; DECLARE CONTINUE HANDLER FOR no_such_table select 1; INSERT INTO test.no_table values (1); END// delimiter ; mysql> call test.handleError(); +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.02 sec) Query OK, 0 rows affected (0.02 sec) mysql> SELECT * from performance_schema.events_errors_summary_global_by_error where SUM_ERROR_HANDLED>0 OR SUM_ERROR_RAISED>0; +--------------+------------------+-----------+------------------+-------------------+---------------------+---------------------+ | ERROR_NUMBER | ERROR_NAME | SQL_STATE | SUM_ERROR_RAISED | SUM_ERROR_HANDLED | FIRST_SEEN | LAST_SEEN | +--------------+------------------+-----------+------------------+-------------------+---------------------+---------------------+ | 1146 | ER_NO_SUCH_TABLE | 42S02 | 1 | 1 | 2016-06-20 15:15:21 | 2016-06-20 15:15:34 | +--------------+------------------+-----------+------------------+-------------------+---------------------+---------------------+ 1 row in set (0.02 sec) |
Here it could be seen that handled error is aggregated under SUM_ERROR_HANDLED column whereas earlier error is still visible under SUM_ERROR_RAISED.
Now let’s see the scenario when an error is raised which is out-of-range of MySQL Server Error codes.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
delimiter // CREATE PROCEDURE test.bigError() BEGIN SIGNAL SQLSTATE "HY000" SET MYSQL_ERRNO = 20000, MESSAGE_TEXT = "A big error number"; END// delimiter ; mysql> call test.bigError(); ERROR 20000 (HY000): A big error number mysql> SELECT * from performance_schema.events_errors_summary_global_by_error where SUM_ERROR_HANDLED>0 OR SUM_ERROR_RAISED>0; +--------------+------------------+-----------+------------------+-------------------+---------------------+---------------------+ | ERROR_NUMBER | ERROR_NAME | SQL_STATE | SUM_ERROR_RAISED | SUM_ERROR_HANDLED | FIRST_SEEN | LAST_SEEN | +--------------+------------------+-----------+------------------+-------------------+---------------------+---------------------+ | NULL | NULL | NULL | 1 | 0 | 2016-06-20 15:15:50 | 2016-06-20 15:15:50 | | 1146 | ER_NO_SUCH_TABLE | 42S02 | 1 | 1 | 2016-06-20 15:15:21 | 2016-06-20 15:15:34 | +--------------+------------------+-----------+------------------+-------------------+---------------------+---------------------+ 2 rows in set (0.03 sec) |
Here, this error is collected in NULL ROW where ERROR_NUMBER, ERROR_NAME and SQL_STATE all are NULL.
In these examples, we have examined errors’ statistics aggregated globally. As mentioned earlier, these error’s statistics are also aggregated by user/host/account/thread and could be seen in respective tables.