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