ERROR :Tablespace for table exist. Please DISCARD the tablespace before IMPORT. – Knowledgebase

ERROR :Tablespace for table exist. Please DISCARD the tablespace before IMPORT. – Knowledgebase

May 18, 2020 by bumkailashkumar

ERROR :Tablespace for table exist. Please DISCARD the tablespace before IMPORT.

While creating the table from cPanel >> PHPMyAdmin>> SQL, I was getting below error –

ERROR 1050 (42S01): Table ‘`database_name`.`table_name`’ already exists
ERROR 1813 (HY000) at line 25: Tablespace for table ‘ ‘`database_name`.`table_name`’ exists. Please DISCARD the tablespace before IMPORT.

Below was the query which I was trying to fire while creating the table –

(

id int(10) unsigned NOT NULL AUTO_INCREMENT,
ticket_id int(10) unsigned NOT NULL DEFAULT 0,
admin_id int(10) unsigned NOT NULL DEFAULT 0,
created_at timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
updated_at timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
PRIMARY KEY (id),
UNIQUE KEY admin_ticket_unique (ticket_id,admin_id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

1. Drop TABLESPACE – ALTER TABLE `tablename`DROP TABLESPACE;

(Note – Make sure to replace the `tablename` withthe name of the table which you’re getting in error.)

Give a try t create the table now. It will work!

2. After dropping table, if you’re still getting the error as – Error in query (1146): Table ‘tabelname’ doesn’t exist

Then, it’s time to fire the SQL commands –

1. Login to your server.

2. cd /var/lib/mysql/database_name

3. [[email protected]_name database_name]# llAfter listing (that is after firing ‘ll’) command, if you’ll get output as below –

[[email protected]_name database_name]# ll
-rw-rw—- 1 mysql mysql 65536 Nov 29 21:59 table_name.ibd

If the table name in ‘table_name.idb’ file is same as the table name in your error then please rename the ‘table_name.idb’ file.

[[email protected]_name database_name]# mv bk_table_name.idb table_name.idb

‘bk_table_name.idb’ is the renamed idb file and ‘table_name.idb’ is the existing idb file which you’d renamed.

Try to create the table now and table is created!Issue resolved!