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 -

Query -

(
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;


Solution :-

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]# ll

After 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!

Was this answer helpful?

 Print this Article

Also Read

How to Re-arrange accounts in WHM?

Please go through the following steps on How to Re-arrange accounts in WHM 1. Go to WHM and...

How to Install cPanel on CentOS 6 or 7?

How to install cPanel on CentOS 6 or 7.  Please note cPanel does not support any other operating...

Error while accessing site with special characters. Update collation for database.

I was getting the error while acccessing the http://yourdomainname/collat.php as below -Error...

How to install Let's Encrypt free SSL plugin on cPanel server?

Let's Encrypt plugin allows automated SSL certificate for those account which do not have valid...

Fatal error: Call to undefined function utf8_substr()

While accessing Opencart site, I was getting error as 'Fatal error: Call to undefined function...

Powered by WHMCompleteSolution