Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 241
Sizzling Code Avatar asked Apr 15 '13 06:04

Sizzling Code


People also ask

How to remove tablespace in mysql?

The tablespace must be dropped explicitly using DROP TABLESPACE tablespace_name . Similar to the system tablespace, truncating or dropping tables stored in a general tablespace creates free space internally in the general tablespace . ibd data file which can only be used for new InnoDB data.

How do I drop a tablespace in phpmyadmin?

CREATE TABLE <table_name> Discard the tablespace to remove . idb file. ALTER TABLE <table_name> DISCARD TABLESPACE.

How do I drop a tablespace?

Introduction to Oracle DROP TABLESPACE statement First, specify the name of the tablespace that you want to drop after the DROP TABLESPACE keywords. Second, use the INCLUDE CONTENTS to delete all contents of the tablespace. If the tablespace has any objects, you must use this option to remove the tablespace.


8 Answers

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.

like image 85
user3335616 Avatar answered Oct 05 '22 11:10

user3335616


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;
like image 27
Christoph Bitzner Avatar answered Oct 05 '22 11:10

Christoph Bitzner


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.

like image 20
test Avatar answered Oct 05 '22 12:10

test


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; 
like image 34
Noat Tran Avatar answered Oct 05 '22 11:10

Noat Tran


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.

like image 41
Aris Avatar answered Oct 05 '22 10:10

Aris


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.

like image 33
Robert D Avatar answered Oct 05 '22 10:10

Robert D


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.

like image 44
SAVe Avatar answered Oct 05 '22 10:10

SAVe


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

like image 38
Berk Can Avatar answered Oct 05 '22 12:10

Berk Can