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


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. [root@serverhost_name database_name]# llAfter listing (that is after firing ‘ll’) command, if you’ll get output as below


[root@serverhost_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.

[root@serverhost_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!

Also Read

Powered by WHMCompleteSolution

Leave a Comment

Your email address will not be published. Required fields are marked *

Open chat