What Is and How to Remove Tablespace Error from My Database

mysqldump problems with restore error: 'Please DISCARD the tablespace before IMPORT'

Sounds like you have a tablename.ibd but no tablename.frm.

To check:

1. cd to your mysql data directory then the database name.

   cd /var/lib/mysql/database_name

2. Search for the table name that is giving the error.

   ls tablename.*

   You should see two files:

   
   tablename.ibd
   tablename.frm
   

   But I'm guessing you don't and only see

   tablename.ibd

To fix you have a few options:

1. Add the follow to mysqldump, which will cause the database to be dropped, cleaning up data directory, before restore.

   --add-drop-database

2. Copy the tablename.frm from prod over to dev and then issue a delete table statement.

Also:

• No need to use net_buffer_length=5000 when you're dumping to a file on localhost.
• Other backup solutions - Percona Xtrabackup

Oracle SQL DROP CREATE TABLESPACE error

My guess would be that the file is already part of the database, but part of a tablespace named something other than temp.

What do you get from the following query:

select tablespace_name from dba_data_files where file_name = 'C:/Oracle/oradata/orcl/temp.dbf'
union all
select tablespace_name from dba_temp_files where file_name = 'C:/Oracle/oradata/orcl/temp.dbf';

ORACLE Database | remove tablespace with missing datafile

You can follow the steps given here in this Oracle forum:

Follow the below steps : -

1) Shutdown abort

2) sqlplus sys/xxx as sysdba

3) Alter database mount

4) alter database datafile '' offline drop;

5) Alter database open

MYSQL Error at restoring database: Please DISCARD the tablespace before IMPORT

I deleted the database using the following:
cd data; sudo rm -rf COMMERCE_DB_622;

However, the important thing is always in the beginning use the regular remove SQL:
DROP DATABASE COMMERCE_DB_622

If the "DROP DATABASE" SQL results in error then one can remove the data using the remove directory command.

mysqldump add TABLESPACE definition that fails during import

After some research and tests, this is what my understanding is.

When databases are created with tablespaces, mysqldump does not include the tablespace definitions. There is no option with mysqldump to ignore tablespace references. So, I had two options after taking dump.
1. Edit the dump manually and remove the references to tablespace.
2. Create the tablespace before importing the dump.

I had a luxury to import into a server that was running as container. So, mysql listed all the tablespaces that were not available (After such error reported, I could throw away the mysql container and start another one - to feel that the server is fresh). Since I knew all the tablespaces, I went with the second option and also thought that editing the dump created by tool (mysqldump) is not a good idea.

Used the following statement to create tablespace

create tablespace <tablespace-name> add datafile 'tablespace-name.ibd'

This approach worked for me.

---

Related Topics