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.8G
The 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 %lu
However, 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.