Attempted to open a previously opened tablespace

原创

运维湖司宁 2021-03-02 19:10:32 博主文章分类：数据库 ©著作权

©著作权归作者所有：来自51CTO博客作者运维湖司宁的原创作品，请联系作者获取转载授权，否则将追究法律责任

2021-02-26 13:42:29 6559 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace mysql/innodb_table_stats uses space ID: 1 at filepath: ./mysql/innodb_table_stats.ibd. Cannot open tablespace live_video/live_ask_answer which uses space ID: 1 at filepath: ./live_video/live_ask_answer.ibd 2021-02-26 13:42:29 7fecfd904880 InnoDB: Operating system error number 2 in a file operation. ###问题产生原因：具体原因可能是异常关机导致的，数据库表的默认引擎为MyISAM，但有部分表在建表时的引擎为InnoDB。

##解决问题思路：更改配置(innodb_force_recovery = 1)重启服务-->备份以下（坏）表（更改引擎为MyISAM）-->删除表-->重建表导入数据-->配置注释 #innodb_force_recovery = 1 ##有些表无法删除，只能删除库（会删除db.opt文件），当无法删除时把data目录下以库名的目录移走

SELECT CONCAT('DROP TABLE ', table_schema, '.', table_name, ';') sql_text FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','performance_schema','sys') AND engine <> 'MyISAM';

DROP TABLE dba.account_union; DROP TABLE dba.active_user_log; DROP TABLE dba.assess_manager; DROP TABLE dba.assess_records; DROP TABLE dba.assess_room; DROP TABLE dba.candidate;