Cannot create new table in mySQL 3

[lupidol]

Hi everyone,
This is the code to create a new table:
[pre]CREATE TABLE melClans (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
KEY (id)
);[/pre]]
This is an error message I get in response to creating table with the above code:
[pre]#1813 - Tablespace for table '`skullcrashingwords`.`melclans`' exists. Please DISCARD the tablespace before IMPORT[/pre]
Since table already exists I type:
[pre]use skullcrashingwords;
select * from melClans;[/pre]
But I get the following error:
[pre]#1146 - Table 'skullcrashingwords.melclans' doesn't exist[/pre]
Any idea why I'm recieving these contradictory errors ?
Thanks

 

[Chris Miller]

With

use skullcrashingwords;

You change the database.

Well, melClans still can exist in the database you were befoer switching to skullcrashingwords. and so it's not contradictory.

Chriss

 

[mikrom]

You probably try to create a table which was once already created and then dropped
Does your database version support CREATE OR REPLACE ? When yes then try to create the table with

CREATE OR REPLACE TABLE melClans (
  id INT NOT NULL AUTO_INCREMENT, 
  name VARCHAR(255) NOT NULL,
  KEY (id)
);

Maybe it helps...

 

[lupidol]

Thanks,
With all the complications I went through I dropped the database and created new one without foreign keys that too was part of complication and I stuck to innoDB engine which was another source of troubles.
Thanks

 

[mikrom]

Maybe for the next time if the error occurs:
There is DROP TABLESPACE statement, maybe it could help to discard the junk files:

https://dev.mysql.com/doc/refman/5.7/en/drop-tablespace.html

If the statement does not work, then it is possible to locate the directory where the table-space files are stored, and delete them - see:

https://stackoverflow.com/questions...ists-please-discard-the-tablespace-before-imp

 

[Chris Miller]

It's not a good idea to avoid foreign keys because they pose a problem to drop a table.
If you absolutely want to drop a table that has foreign keys to others, of course, you first drop the relationships due to foreign keys first.

But if you drop a table just to create it with a different structure and will have the same foreign key relationships with the other tables, that points out you should use ALTER TABLE to add or drop columns or change data types, instead of using a pair of drop/create statements.

In the long-term life of a database, your structural updates will be based on ALTER statements more than anything else. You'll also create new tables, but it's actually DROP that has no meaning in the long run. Even if you rearrange data into a new structure then you'd first create that new structure, move data to it and then turn key columns to normal to cut old relationships and then finally drop tables you don't want anymore.


Chriss

 

[lupidol]

Thanks a lot . Your aid was very enlightening !