AI‑Assisted Diagnosis of Long‑Running Undo Log Cleanup Causing MySQL Backup Failure

Problem

Problem description: A client reported that three MySQL Group Replication (MGR) environments failed full backup. The MySQL version is 8.0.18 and Xtrabackup version is 8.0.9. The error log is shown below.

xtrabackup: Generating a list of tablespaces
Directories to scan '.;./;.'
Scanning './'
Completed space ID check of 2 files.
Allocated tablespace ID 12 for zxc/a, old maximum was 0
Undo tablespace number 1 was being truncated when mysqld quit.
Cannot recover a truncated undo tablespace in read-only mode
xtrabackup: error: xb_load_tablespaces() failed with error code 57

Experiment

1. Submit the issue to ChatDBA

We first fed the problem description to ChatDBA. The AI generated an initial reference answer, suggested relevant search keywords, and listed potential bugs. It also requested the error log for deeper analysis and offered some preliminary actions.

2. First interaction round

ChatDBA produced a preliminary analysis, identified possible root causes, and suggested collecting additional logs. It also generated a flow‑chart image (omitted here) illustrating its reasoning process.

3. Second interaction round

After providing the error log, ChatDBA confirmed that InnoDB attempted to access a missing tablespace, which caused the failure. It recommended increasing the innodb_max_undo_log_size parameter so that the undo log is purged before reaching the truncation threshold, thereby avoiding the bug.

Experiment Summary

The investigation revealed that the undo log remained in the cleanup state for more than ten hours, which is abnormal. The root cause was a bug triggered by the super_read_only setting. Adjusting innodb_max_undo_log_size allows the undo log to be purged earlier, preventing the truncation bug and the backup failure.

Third‑Party Large Model Comparison: ChatGPT‑4o

Because the issue included version information, ChatGPT‑4o also focused on version‑specific guidance. It offered solutions, but the suggestions were coarser and less targeted than those from ChatDBA.

Upcoming Activity

A preview image (omitted) announces the next event related to AI‑assisted DBA tools.

Past Issues Overview

Links to previous issues such as "520献给DBA们的AI", "MySQL 半同步复制频繁报错", "MySQL 创建了用户却无法登陆", "MySQL Too many open files 报错", and others are provided for reference.

What Is ChatDBA?

ChatDBA is an AI assistant designed to help database administrators diagnose and resolve problems by analysing logs, suggesting configuration changes, and generating troubleshooting workflows.