For this third article of the series dedicated on how a DBA can find the info he needs with MySQL Database Service in Oracle Cloud Infrastructure, we will see how we can find the error log.
When using MySQL DBAAS, the DBA doesn’t have direct access to the files on the filesystem. Hopefully, with MySQL 8.0, the error log is also available in Performance_Schema.
This is exactly where you will find the information present also in the error log file when using MDS in OCI:
select * from (select * from performance_schema.error_log order by logged desc limit 10) a order by logged\G
*************************** 1. row ***************************
LOGGED: 2023-03-19 08:41:09.950266
THREAD_ID: 0
PRIO: System
ERROR_CODE: MY-011323
SUBSYSTEM: Server
DATA: X Plugin ready for connections. Bind-address: '10.0.1.33' port: 33060, socket: /var/run/mysqld/mysqlx.sock
*************************** 2. row ***************************
LOGGED: 2023-03-19 08:41:09.950328
THREAD_ID: 0
PRIO: System
ERROR_CODE: MY-010931
SUBSYSTEM: Server
DATA: /usr/sbin/mysqld: ready for connections. Version: '8.0.32-u1-cloud' socket: '/var/run/mysqld/mysql.sock' port: 3306 MySQL Enterprise - Cloud.
*************************** 3. row ***************************
LOGGED: 2023-03-19 08:41:09.950342
THREAD_ID: 0
PRIO: System
ERROR_CODE: MY-013292
SUBSYSTEM: Server
DATA: Admin interface ready for connections, address: '127.0.0.1' port: 7306
*************************** 4. row ***************************
LOGGED: 2023-03-19 08:51:09.000200
THREAD_ID: 0
PRIO: Note
ERROR_CODE: MY-013694
SUBSYSTEM: Health
DATA: DISK: mount point='/db', available=84.9G, total=99.9G, used=15.1%, low limit=4.0G, critical=2.0G, warnings=23.2G/13.6G/8.8G
*************************** 5. row ***************************
LOGGED: 2023-03-19 10:49:18.394291
THREAD_ID: 0
PRIO: Warning
ERROR_CODE: MY-010055
SUBSYSTEM: Server
DATA: IP address '10.0.0.159' could not be resolved: Name or service not known
*************************** 6. row ***************************
LOGGED: 2023-03-19 10:49:18.452995
THREAD_ID: 0
PRIO: Warning
ERROR_CODE: MY-010968
SUBSYSTEM: Server
DATA: Can't set mandatory_role: There's no such authorization ID public@%.
*************************** 7. row ***************************
LOGGED: 2023-03-19 10:52:13.818505
THREAD_ID: 0
PRIO: Note
ERROR_CODE: MY-011287
SUBSYSTEM: Server
DATA: Plugin mysqlx reported: '2.1: Maximum number of authentication attempts reached, login failed.'
*************************** 8. row ***************************
LOGGED: 2023-03-19 18:52:16.600274
THREAD_ID: 0
PRIO: Note
ERROR_CODE: MY-013993
SUBSYSTEM: Server
DATA: Thread pool closed connection id 39 for `admin`@`%` after 28800.004878 seconds of inactivity. Attributes: priority:normal, type:normal, last active:2023-03-19T10:52:16.595189Z, expired:2023-03-19T18:52:16.595199Z (4868 microseconds ago)
*************************** 9. row ***************************
LOGGED: 2023-03-19 18:52:16.600328
THREAD_ID: 0
PRIO: Note
ERROR_CODE: MY-013730
SUBSYSTEM: Server
DATA: 'wait_timeout' period of 28800 seconds was exceeded for `admin`@`%`. The idle time since last command was too long.
*************************** 10. row ***************************
LOGGED: 2023-03-20 13:47:28.843589
THREAD_ID: 365
PRIO: Warning
ERROR_CODE: MY-010055
SUBSYSTEM: Server
DATA: IP address '10.0.1.237' could not be resolved: Name or service not known
10 rows in set (0.0015 sec)The example above lists the last 10 entries in error log.
It’s possible to get some statistics on the entries in error log much easily then parsing the file with sed and awk:
select subsystem, count(*)
from performance_schema.error_log
group by subsystem order by subsystem;
+-----------+----------+
| subsystem | count(*) |
+-----------+----------+
| Health | 112 |
| InnoDB | 1106 |
| RAPID | 51 |
| Repl | 4 |
| Server | 483 |
+-----------+----------+
5 rows in set (0.0018 sec)
select prio, count(*)
from performance_schema.error_log
group by prio order by prio;
+---------+----------+
| prio | count(*) |
+---------+----------+
| System | 105 |
| Error | 2 |
| Warning | 50 |
| Note | 1599 |
+---------+----------+
4 rows in set (0.0014 sec)The error log provides a lot of information about how healthy is your system, about health monitor, InnoDB, replication, authentication failures, etc…
For example, we can see the disk usage (see the previous post) in the error_log table too:
select * from error_log where subsystem="Health"
and data like 'DISK:%' order by logged desc limit 4\G
*************************** 1. row ***************************
LOGGED: 2023-03-19 08:51:09.000200
THREAD_ID: 0
PRIO: Note
ERROR_CODE: MY-013694
SUBSYSTEM: Health
DATA: DISK: mount point='/db', available=84.9G, total=99.9G, used=15.1%,
low limit=4.0G, critical=2.0G, warnings=23.2G/13.6G/8.8G
*************************** 2. row ***************************
LOGGED: 2023-03-17 15:24:57.000133
THREAD_ID: 0
PRIO: Note
ERROR_CODE: MY-013694
SUBSYSTEM: Health
DATA: DISK: mount point='/db', available=84.9G, total=99.9G, used=15.1%,
low limit=4.0G, critical=2.0G, warnings=23.2G/13.6G/8.8G
*************************** 3. row ***************************
LOGGED: 2023-03-16 19:24:57.000122
THREAD_ID: 0
PRIO: Note
ERROR_CODE: MY-013694
SUBSYSTEM: Health
DATA: DISK: mount point='/db', available=74.9G, total=99.9G, used=25.1%,
low limit=4.0G, critical=2.0G, warnings=23.2G/13.6G/8.8G
*************************** 4. row ***************************
LOGGED: 2023-03-16 16:34:57.000175
THREAD_ID: 0
PRIO: Note
ERROR_CODE: MY-013694
SUBSYSTEM: Health
DATA: DISK: mount point='/db', available=46.7G, total=99.9G, used=53.2%,
low limit=4.0G, critical=2.0G, warnings=23.2G/13.6G/8.8GThe log_error_verbosity is set to 3 in MySQL Database Service, meaning it will log the errors, the warnings and the different information messages.
These are the configuration settings related to the error log in MDS:
select * from performance_schema.global_variables
where variable_name like 'log_error%';
+----------------------------+-------------------------------------------------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+----------------------------+-------------------------------------------------------+
| log_error | /db/log/error.log |
| log_error_services | log_filter_internal; log_sink_internal; log_sink_json |
| log_error_suppression_list | MY-012111 |
| log_error_verbosity | 3 |
+----------------------------+-------------------------------------------------------+In MySQL Database Service, we can also see that the error MY-012111 is not logged:
show global variables like 'log_error_sup%';
+----------------------------+-----------+
| Variable_name | Value |
+----------------------------+-----------+
| log_error_suppression_list | MY-012111 |
+----------------------------+-----------+This error is related to MySQL trying to access a missing tablespace:
$ perror MY-012111
MySQL error code MY-012111 (ER_IB_WARN_ACCESSING_NONEXISTINC_SPACE):
Trying to access missing tablespace %luHowever, a user doesn’t have the possibility to change any settings related to the error log, neither using SET GLOBAL, neither by creating a MDS configuration using the OCI console.
Conclusion
In MDS you don’t have access to the error log file but its content is available in Performance_Schema and easier to parse using SQL.
It’s a really good source of information that I invite every users to parse regularly.
