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

Question

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.

SQL Statement:

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?

http://dev.mysql.com/doc/refman/5.1/en/create-tablespace.html — Yogesh Suthar, Apr 15 '13 at 06:23
This may be useful: https://stackoverflow.com/questions/15694168/error-tablespace-for-table-xxx-exists-please-discard-the-tablespace-before-imp Otherwise, the way I solved it was to drop the databases in question using the following solution: https://dba.stackexchange.com/questions/192484/how-do-you-drop-a-corrupted-innodb-database-in-mariadb — Tim Rogers, Jun 08 '20 at 15:55
Does this answer your question? [Error: Tablespace for table xxx exists. Please DISCARD the tablespace before IMPORT](https://stackoverflow.com/questions/15694168/error-tablespace-for-table-xxx-exists-please-discard-the-tablespace-before-imp) — Tim Rogers, Jun 08 '20 at 15:57

score 11 · Answer 1 · edited Feb 21 '14 at 04:37

11

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

Open the directory where xampp is installed, for example

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.

In my case my file is karyawan.idb,

Hope this works for you.

edited Feb 21 '14 at 04:37

crockeea

21,651
10
48
101

answered Feb 21 '14 at 04:12

user3335616

111
1
3

this also resolve my issue but it is not good approach follow this : https://dev.mysql.com/doc/refman/8.0/en/drop-tablespace.html – Mohsin AR Feb 11 '19 at 21:55

score 5 · Answer 2 · answered Dec 27 '16 at 22:34

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;

thanks man, this actually helped repair my local xampp with such a defect still existing database. — rnd, Jan 28 '21 at 14:13

score 4 · Answer 3 · edited May 23 '17 at 11:54

4

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

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

Remove your whole database folder & restart apache.

Create your database again & import database.

It works.

edited May 23 '17 at 11:54

Community

1
1

answered Apr 27 '15 at 07:15

test

464
5
14

score 2 · Answer 4 · answered Sep 07 '17 at 03:24

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

Step 2: Delete all mysql ib_logfile*

Step 3: Restart mysql service

Step 4: Login into mysql from shell

Step 5: Run:

- 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

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

- use database;
- ALTER TABLE table_name IMPORT TABLESPACE;

score 1 · Answer 5 · answered Jun 18 '13 at 04:47

Trying to drop the tablespace can give you other errors. For me, I got the following error:

DROP TABLESPACE `tablename`

Error Code: 1478. Table storage engine 'InnoDB' does not support the create option 'TABLESPACE or LOGFILE GROUP'

My solution was to drop the database. This will remove any tablespaces related to it and allow you to create the tables again.

score 1 · Answer 6 · answered Feb 05 '19 at 00:06

1

The following works on 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

Everything should work and be clean after this.

answered Feb 05 '19 at 00:06

Robert D

1,878
15
19

score 1 · Answer 7 · answered Apr 29 '19 at 06:50

Open the directory where MAMP is installed, for example

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.

For Example file : ‎⁨Macintosh⁩ ▸ ⁨Applications⁩ ▸ ⁨MAMP⁩ ▸ ⁨db⁩ ▸ ⁨mysql57⁩ ▸ ⁨example_db ▸ amc_customer.ibd

Try this. It's work for me.

score 1 · Answer 8 · answered Oct 03 '19 at 08:52

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

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

score 0 · Answer 9 · answered Apr 15 '13 at 06:25

0

Before creating this table run this query for removing the TABLESPACE

DROP TABLESPACE `zorkif`.`sys_user_accounts`

TABLESPACE can contain one or more data files, providing storage space for tables.

answered Apr 15 '13 at 06:25

Yogesh Suthar

30,424
18
72
100

2

Error Code: 1478. Table storage engine 'InnoDB' does not support the create option 'TABLESPACE or LOGFILE GROUP' – Sizzling Code Apr 15 '13 at 06:28

Marco Aurélio · Answer 10 · 2016-03-23T16:49:05.773

0

Steps to resolve this matter:

export your schema to backup file
drop schema
modify your SQL script exported, adding the 'create table' you need
create the same schema
import your schema from backup file

edited Mar 23 '16 at 16:49

answered Mar 23 '16 at 13:01

Marco Aurélio

1
1

score 0 · Answer 11 · answered Mar 22 '17 at 22:59

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.

Just solve this on my system now

score 0 · Answer 12 · answered Aug 23 '18 at 14:28

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.

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

12 Answers12

Linked