3

Since I had some problems with my database, I tried to drop the database. When that didn't work, I removed my database folder in C:/xampp/mysql/data. I used SHOW TABLES and it said no tables in my database.

Now the problem is, first time importing my database dump file, I got the following problem:

 ERROR 1050 (42S01) at line 2990: Table '`db`.`tbl`' already exists

I tried removing the table with the following query:

DROP TABLE tbl;

This gives the following error:

ERROR 1051 (42S02): Unknown table 'db.tbl'

However, I tried to create it with the following query:

CREATE TABLE tbl (id int NOT NULL PRIMARY KEY);

MySQL gives the following error:

ERROR 1813 (HY000): Tablespace for table '`db`.`tbl`' exists. Please DISCARD the tablespace before IMPORT.

I tried looking it up but I can't figure it out. Removing the .idb file causes the same problem eventually, I can't run ALTER TABLE tbl DISCARD TABLESPACE; because it'll give the error the table db.tbl doesn't exist.

Is there a way to fix this and what is the cause of it? I got these errors out of nowhere and it's frustrating

Joshua Bakker
  • 2,288
  • 3
  • 30
  • 63
  • By default databases are now created as INNODB. These databases place parts of each table in the `C:/xampp/mysql/data/db` folder, **but also parts in** `C:/xampp/mysql/data/ibdata1` You have not yet removed the parts in `ibdata1` – RiggsFolly Aug 05 '19 at 11:19
  • Okay so how do I remove them from that? – Joshua Bakker Aug 05 '19 at 11:21
  • [Read and inwardly digest](https://dba.stackexchange.com/questions/107608/cannot-delete-db-table) – RiggsFolly Aug 05 '19 at 11:25
  • check this one mate, looks like it'll give the solution. https://stackoverflow.com/questions/15694168/error-tablespace-for-table-xxx-exists-please-discard-the-tablespace-before-imp – Subash Aug 05 '19 at 11:36
  • @RiggsFolly I tried removing all databases except for mysql and performance_schema and remove ibdata1 but then it doesn't start MySQL up for me anymore – Joshua Bakker Aug 05 '19 at 12:13
  • https://stackoverflow.com/questions/8911115/how-to-recover-recreate-mysqls-default-mysql-database – RiggsFolly Aug 05 '19 at 14:34
  • @RiggsFolly Thanks though I was able to use the backup folder as data folder and it worked again – Joshua Bakker Aug 05 '19 at 14:47

0 Answers0