MariaDB表列示但无法访问，Collation显示‘in use’问题排查

问题背景 #

我的MariaDB中有三个数据库：两个是仅含少量表的小型库（单库约3MB），另一个包含一张测试用大表，原本存有约50万条数据，对应.ibd文件大小为104857600字节。

向该大表导入大量数据并删除约15万条记录后，MariaDB一度无响应。由于运行在Raspberry Pi 2上，起初以为是数据整理需要时间，但恢复响应后出现以下异常：

异常现象 #

• phpMyAdmin可正常列出数据库
• 点击数据库能看到表列表，但Rows列无数据统计，Collation列显示‘in use’
• 点击该大表时提示错误：Table xyz doesn't exist in engine
• 数据库目录下db.opt文件、各表的tablename.frm和tablename.ibd文件均存在，且大小合理
• 重启MariaDB后问题未解决
• top命令显示无相关数据库维护进程在运行

补充信息 #

• 版本：10.5.15-MariaDB-0+deb11u1
• 未手动修改任何数据库文件，仅确认文件存在
• 启用错误日志并重启MariaDB，日志内容如下（访问异常表时无新日志生成）：

2023-07-18  9:07:00 0 [Note] InnoDB: Uses event mutexes
2023-07-18  9:07:00 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2023-07-18  9:07:00 0 [Note] InnoDB: Number of pools: 1
2023-07-18  9:07:00 0 [Note] InnoDB: Using generic crc32 instructions
2023-07-18  9:07:00 0 [Note] InnoDB: Using Linux native AIO
2023-07-18  9:07:00 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
2023-07-18  9:07:00 0 [Note] InnoDB: Completed initialization of buffer pool
2023-07-18  9:07:00 0 [Note] InnoDB: 128 rollback segments are active.
2023-07-18  9:07:00 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2023-07-18  9:07:00 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2023-07-18  9:07:00 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2023-07-18  9:07:00 0 [Note] InnoDB: 10.5.15 started; log sequence number 35076; transaction id 8
2023-07-18  9:07:00 0 [Note] Plugin 'FEEDBACK' is disabled.
2023-07-18  9:07:00 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2023-07-18  9:07:00 0 [Note] InnoDB: Buffer pool(s) load completed at 230718  9:07:00
2023-07-18  9:07:00 0 [Note] Server socket created on IP: '127.0.0.1'.
2023-07-18  9:07:00 1 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1932: Table 'mysql.gtid_slave_pos' doesn't exist in engine
2023-07-18  9:07:00 0 [Note] Reading of all Master_info entries succeeded
2023-07-18  9:07:00 0 [Note] Added new Master_info '' to hash table
2023-07-18  9:07:00 0 [Note] /usr/sbin/mariadbd: ready for connections.
Version: '10.5.15-MariaDB-0+deb11u1'  socket: '/run/mysqld/mysqld.sock'  port: 3306  Raspbian 11

问题分析 #

1. 异常原因 #

核心问题是InnoDB表的元数据与数据文件关联失效，结合环境和操作过程，大概率是以下因素导致：

• Raspberry Pi 2资源有限（CPU、内存、IO性能弱），大规模数据导入+删除触发InnoDB后台碎片回收、统计信息更新等操作，但资源不足导致操作中断，破坏了表的元数据标记
• 操作过程中可能出现隐式事务回滚或InnoDB日志文件（ib_logfile*）写入异常，导致表的状态信息损坏
• 日志中出现mysql.gtid_slave_pos表不存在的警告，说明系统表也存在元数据关联问题，进一步验证了InnoDB表空间的一致性异常

2. Collation列显示‘in use’的含义 #

这个状态表示phpMyAdmin检测到该表被InnoDB引擎锁定或处于未完成的内部处理状态：

• 可能是之前的中断操作导致表的"使用中"标记未被正常清除
• 也可能是InnoDB内部认为表处于活跃状态，但实际已无法正常访问

解决方法 #

方法1：尝试修复表关联（优先尝试） #

1. 停止MariaDB服务：

   sudo systemctl stop mariadb
   

2. 备份异常数据库的所有文件（db.opt、*.frm、*.ibd）到安全目录，避免操作失误丢失数据
3. 修改MariaDB配置文件（通常为/etc/mysql/mariadb.conf.d/50-server.cnf），添加以下内容：

   [mysqld]
   innodb_force_recovery = 1
   

   注：innodb_force_recovery取值1-6，从低到高尝试，1是最安全级别，仅跳过损坏的事务

4. 启动MariaDB服务：

   sudo systemctl start mariadb
   

5. 登录MariaDB，导出异常表数据：

   SELECT * INTO OUTFILE '/tmp/xyz_data.csv' FIELDS TERMINATED BY ',' FROM xyz;
   

   或用mysqldump导出整个数据库：

   mysqldump -u root -p your_database_name > /tmp/db_backup.sql
   

6. 导出成功后，停止服务，移除innodb_force_recovery配置，重启服务后重建表并导入数据

方法2：通过表空间导入重建表 #

若方法1无法导出数据，且文件完好：

1. 停止MariaDB，备份原数据库文件
2. 创建新数据库，在新库中创建与异常表结构完全一致的空表
3. 停止服务，将原表的.ibd文件替换新表的.ibd文件，修改文件权限为mysql:mysql
4. 启动服务，执行表空间导入：

   ALTER TABLE xyz DISCARD TABLESPACE;
   ALTER TABLE xyz IMPORT TABLESPACE;
   

   该操作仅适用于开启独立表空间的环境（innodb_file_per_table = ON，默认开启）

预防措施 #

• Raspberry Pi 2性能有限，避免直接在上面执行大规模数据导入/删除操作，建议先在高性能机器处理数据后再同步
• 操作前关闭phpMyAdmin等客户端，避免并发访问干扰
• 保持innodb_file_per_table开启状态，便于单表恢复
• 定期备份数据库，同时备份表结构和数据
• 调整InnoDB参数适配低资源环境：

  innodb_buffer_pool_size = 64M  # 根据RPi2内存调整，通常设为64-128M
  innodb_log_file_size = 32M
  innodb_flush_log_at_trx_commit = 2  # 降低IO压力，牺牲少量数据实时性
  

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