执行TRUNCATE含外键约束表时MariaDB崩溃的问题求助

问题背景 #

运行Pterodactyl Panel面板，因磁盘空间耗尽（activity_logs表占用近120GB）触发崩溃。尝试清理该表时遇到外键约束限制，关闭外键检查执行TRUNCATE命令后数据库连接断开，重启MariaDB失败，日志显示InnoDB引擎初始化错误。

报错详情 #

初始清理报错 #

Database changed
MariaDB [panel]> TRUNCATE TABLE activity_logs;
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`panel`.`activity_log_subjects`, CONSTRAINT `activity_log_subjects_activity_log_id_foreign` FOREIGN KEY (`activity_log_id`) REFERENCES `panel`.`activity_logs` (`id`))

关闭外键约束后的执行结果 #

MariaDB [panel]> SET FOREIGN_KEY_CHECKS = 0;
Query OK, 0 rows affected (0.000 sec)

MariaDB [panel]> TRUNCATE TABLE activity_logs;
ERROR 2013 (HY000): Lost connection to MySQL server during query
MariaDB [panel]> TRUNCATE TABLE activity_logs;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)
ERROR: Can't connect to the server

unknown [panel]> exit
Bye

数据库重启失败日志 #

root@Panel:/var/lib/mysql/panel# systemctl status mysql
● mariadb.service - MariaDB 10.4.27 database server
   Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
  Drop-In: /etc/systemd/system/mariadb.service.d
           └─migrated-from-my.cnf-settings.conf
   Active: failed (Result: exit-code) since Tue 2023-01-24 09:20:30 CET; 2s ago
     Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/
  Process: 4680 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
  Process: 4677 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
  Process: 6101 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=1/FAILURE)
  Process: 5952 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ]   &  Process: 5943 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
  Process: 5931 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
 Main PID: 6101 (code=exited, status=1/FAILURE)
   Status: "MariaDB server is down"

Jan 24 09:20:29 Panel mysqld[6101]: 2023-01-24  9:20:29 0 [Note] InnoDB: Starting shutdown...
Jan 24 09:20:30 Panel mysqld[6101]: 2023-01-24  9:20:30 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
Jan 24 09:20:30 Panel mysqld[6101]: 2023-01-24  9:20:30 0 [ERROR] Plugin 'InnoDB' init function returned error.
Jan 24 09:20:30 Panel mysqld[6101]: 2023-01-24  9:20:30 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
Jan 24 09:20:30 Panel mysqld[6101]: 2023-01-24  9:20:30 0 [Note] Plugin 'FEEDBACK' is disabled.
Jan 24 09:20:30 Panel mysqld[6101]: 2023-01-24  9:20:30 0 [ERROR] Unknown/unsupported storage engine: InnoDB
Jan 24 09:20:30 Panel mysqld[6101]: 2023-01-24  9:20:30 0 [ERROR] Aborting
Jan 24 09:20:30 Panel systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE
Jan 24 09:20:30 Panel systemd[1]: mariadb.service: Failed with result 'exit-code'.
Jan 24 09:20:30 Panel systemd[1]: Failed to start MariaDB 10.4.27 database server.

修复步骤 #

1. 紧急释放磁盘空间 #

磁盘满是根源，先清理出至少2-5GB可用空间：

• 删除系统旧日志：rm -rf /var/log/*.gz
• 转移或删除非必要的备份文件、大文件
• 检查/var/lib/mysql/目录下的临时文件，如ibtmp1（若存在且过大可删除，重启时会重建）

2. 修复InnoDB引擎启动问题 #

• 停止MariaDB服务：

  systemctl stop mariadb
  

• 编辑MariaDB配置文件（路径通常为/etc/mysql/mariadb.conf.d/50-server.cnf或/etc/my.cnf），在[mysqld]区块添加：

  innodb_force_recovery = 1
  

  提示：innodb_force_recovery取值1-6，优先用1级（最低恢复级别），若启动失败再逐步提高数值，6级为只读模式，尽量避免使用。

• 启动MariaDB：

  systemctl start mariadb
  

3. 安全清理日志表数据 #

进入MariaDB控制台：

mysql -u root -p panel

• 先清理关联表数据（因外键关联，必须先处理子表）：

  DELETE FROM activity_log_subjects;
  

• 再清理主表：

  DELETE FROM activity_logs;
  

  说明：用DELETE而非TRUNCATE，避免大动作触发数据库崩溃；若需快速清理，可先删除外键约束，TRUNCATE后重建外键，但DELETE更稳妥。

4. 恢复正常启动模式 #

• 退出MariaDB控制台，编辑配置文件删除innodb_force_recovery = 1这一行
• 重启MariaDB：

  systemctl restart mariadb
  

5. 优化表（可选） #

清理后表可能存在碎片，执行优化：

OPTIMIZE TABLE activity_logs;
OPTIMIZE TABLE activity_log_subjects;

6. 设置自动清理规则（预防复发） #

• 面板后台设置：进入Pterodactyl面板系统设置，配置日志保留天数（如7天或30天）
• 数据库事件自动清理：

  CREATE EVENT clean_activity_logs
  ON SCHEDULE EVERY 1 DAY
  DO
  DELETE FROM activity_logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);
  

  确保MariaDB事件调度器开启，在配置文件[mysqld]区块添加event_scheduler = ON，重启生效。

内容的提问来源于stack exchange，提问作者w41k3r