MySQL 什么是以及如何从我的数据库中删除表空间错误？

what is and how to remove tablespace error from my database?

提问by Sizzling Code

Because of loadshading issue, one of the table in my database got currupted. I dropped the table and now I want to create the table again.

由于负载阴影问题，我的数据库中的一个表被破坏了。我删除了表，现在我想再次创建表。

I'm getting this error:

我收到此错误：

ERROR 1813: Tablespace for table 'zorkif.sys_user_accounts' exists. Please DISCARD the tablespace before IMPORT.

错误 1813：表 ' 的表空间zorkif。sys_user_accounts' 存在。请在 IMPORT 之前丢弃表空间。

SQL Statement:

SQL语句：

CREATE  TABLE `zorkif`.`sys_user_accounts` (
    `UserID` INT NOT NULL AUTO_INCREMENT ,    
    PRIMARY KEY (`UserID`) ,    
    UNIQUE INDEX `UserID_UNIQUE` (`UserID` ASC)
)

What is tablespace and how to discard this tablespace? Is there any command I have to run in query? How to deal with this issue?

什么是表空间以及如何丢弃该表空间？有没有我必须在查询中运行的命令？如何处理这个问题？

回答by user3335616

This works for me, but I use this in local server.

这对我有用，但我在本地服务器中使用它。

Open the directory where xampp is installed, for example

打开安装xampp的目录，例如

C:/xampp/mysql/data/...

Select the folder (it's like a database name) of the tablespace that you want to remove, then delete the file with extension idb. Delete only one file corresponding to the table that you want to create/remove.

选择要删除的表空间的文件夹（类似于数据库名称），然后删除扩展名为idb. 仅删除与要创建/删除的表对应的一个文件。

In my case my file is karyawan.idb,

就我而言，我的文件是karyawan.idb，

Hope this works for you.

希望这对你有用。

回答by Christoph Bitzner

It took me quite a while to fix this issue on one of my production dbs. The following steps actually solved the problem pretty cleanly:

我花了很长时间在我的一个生产数据库上解决这个问题。以下步骤实际上非常干净地解决了问题：

-> deactivate your app/page - any requests to your db (if you dont have a service mode in your app - build one :P)
-> mysqldump your db (optionally with --routines=true);
-> kill the folder with db name
-> create schema db name;
-> drop schema db name;
-> restart mysql (service mysql restart - or similar depending on your os)
-> create schema db name;
-> mysql import your previously created dump;

回答by test

Duplicate Error: Tablespace for table xxx exists. Please DISCARD the tablespace before IMPORT

重复错误：表 xxx 的表空间存在。请在 IMPORT 之前丢弃表空间

<WAMP or XAMPP path >\bin\mysql\mysqlX.X.XX\data\<your database name>

Remove your whole database folder & restart apache.

删除整个数据库文件夹并重新启动 apache。

Create your database again & import database.

再次创建您的数据库并导入数据库。

It works.

有用。

回答by Noat Tran

Step 1: Backup mysql/database/*.ibd to another folder

第一步：备份mysql/database/*.ibd到另一个文件夹

Step 2: Delete all mysql ib_logfile*

第二步：删除所有mysql ib_logfile*

Step 3: Restart mysql service

第三步：重启mysql服务

Step 4: Login into mysql from shell

第四步：从shell登录mysql

Step 5: Run:

第 5 步：运行：

- use database;
- ALTER TABLE table_name DISCARD TABLESPACE; 

(Repeat for all tables that has problem with tablespace)

（对所有表空间有问题的表重复）

Step 6: Copy *.ibd from backup folder to mysql/database/ folder

第 6 步：将 *.ibd 从备份文件夹复制到 mysql/database/ 文件夹

Step 7: Repeat step 3,4 with running query:

第 7 步：通过运行查询重复第 3,4 步：

- use database;
- ALTER TABLE table_name IMPORT TABLESPACE; 

回答by Josh Pule

If you're on a Mac, go to Applications->XAMPP->xamppfiles->var-mysql look for your database name. You might have to give 'read&write' access to 'everyone' in privileges. Thereafter, delete the relevant .idb file. Restart XAMPP and you should be good to go.

如果您使用的是 Mac，请转到 Applications->XAMPP->xamppfiles->var-mysql 查找您的数据库名称。您可能必须以特权授予“所有人”的“读写”访问权限。此后，删除相关的 .idb 文件。重新启动 XAMPP，您应该一切顺利。

回答by Robert D

The following works on MariaDB 10.3.12:

以下适用于 MariaDB 10.3.12：

• create another table with the same name in another database
• sudo cp -p otherdb/tablename.frm problemdb/tablename.frm
• drop table problemdb.tablename

• 在另一个数据库中创建另一个同名表
• sudo cp -p otherdb/tablename.frm problemdb/tablename.frm
• 删除表问题db.tablename

Everything should work and be clean after this.

在此之后，一切都应该正常工作并保持清洁。

回答by SAVe

Open the directory where MAMP is installed, for example

打开安装MAMP的目录，例如

Macintosh? ? ?Applications? ? ?MAMP? ? ?db? ? ... Select the folder (it's like a database name) of the tablespace that you want to remove, then delete the file with extension idb. Delete only one file corresponding to the table that you want to create/remove.

麦金塔电脑？? ?应用程序？? ？MAMP？? ？D b？? ... 选择要删除的表空间的文件夹（类似于数据库名称），然后删除扩展名为 idb 的文件。仅删除与要创建/删除的表对应的一个文件。

For Example file : ??Macintosh? ? ?Applications? ? ?MAMP? ? ?db? ? ?mysql57? ? ?example_db ? amc_customer.ibd

例如文件：??Macintosh? ? ?应用程序？? ？MAMP？? ？D b？? ？mysql57？? ?example_db ? amc_customer.ibd

Try this. It's work for me.

尝试这个。这对我有用。

回答by Berk Can

In my case even after I deleted table before Import, I could not discard TABLESPACE because mysql was saying that no TABLESPACE exists, if that's the case it's because indexes of that table remains so you need to delete those indexes

就我而言，即使在导入之前删除表后，我也无法丢弃 TABLESPACE，因为 mysql 说不存在 TABLESPACE，如果是这种情况，那是因为该表的索引仍然存在，因此您需要删除这些索引

DELETE FROM innodb_index_stats WHERE table_name='table_name_here';

After that Discard tablespace error is not occurred anymore and I could successfully import table

之后不再发生丢弃表空间错误，我可以成功导入表

回答by Marco Aurélio

Steps to resolve this matter:

解决此问题的步骤：

1. export your schema to backup file
2. drop schema
3. modify your SQL script exported, adding the 'create table' you need
4. create the same schema
5. import your schema from backup file

1. 将您的架构导出到备份文件
2. 删除模式
3. 修改导出的 SQL 脚本，添加您需要的“创建表”
4. 创建相同的架构
5. 从备份文件导入您的架构

回答by Lob

if you encounter this error on your local host, just go to the xamp folder, move to mysql folder, move to data folder, inside the data folder you will see the list of database based on the database name, select the folder of the database that is giving you error, the the particular table name that is give you the error and re-import your database.

如果您在本地主机上遇到此错误，只需转到 xamp 文件夹，移动到 mysql 文件夹，移动到数据文件夹，在数据文件夹中您将看到基于数据库名称的数据库列表，选择数据库的文件夹这给你错误，给你错误的特定表名并重新导入你的数据库。

Just solve this on my system now

现在就在我的系统上解决这个问题