Help needed with DISCARD TABLESPACE

[Mike Feher]

Greetings,

I am a new user to this forum so please bear with me. I started using MySQL several months ago at home with Navicat Premium for MySQL. I ran into my first bona fide MySQL problem that I'm at a loss to fix, but which I'm sure someone will have some advice on.

My problem is as follows: Somehow, while setting up a database last night, I did something that caused me to run into the "tablespace already in use" issue (or whatever it's called). I looked into it briefly since it was late, and I kind of got the idea that if I just flushed the tablespace on the offending tables (see example below), the problem should resolve itself. Here are the particulars:

1. I am assuming I'm using the InnoDB engine, though I'm not familiar with it enough to know.

2. I am running (from memory) MySQL 5.6.12 on iMac OS X 10.7.

3. I am using Navicat's table designer to lay out and design the table structures and relationships between them, then syncing back to the database.

4. Somehow, during an operation, I lost more than one table, which had both a structure defined, and actual data in it.

5. I researched the problem based on the error message I was getting and the websites were telling me to use a command at the terminal in mysql as follows:

mysql > ALTER TABLE tblGoals DISCARD TABLESPACE;

This never ever worked for me.

6. When I refreshed the table list in Navicat, the offending tables were gone. The layout designer still showed them, of course.

7. When I went into the MySQL prompt and did "SHOW TABLES;" it gave me the same list of tables as Navicat had - my "corrupt" tables were no longer there. This tells me that when I tried to do the ALTER TABLE command, it
simply couldn't, because the table no longer existed.

8. I tried a data migration feature in Navicat where you can essentially copy a database to a new database and give it a different name, but that didn't solve the problem.

9. I shut down Navicat and restarted it. The problem remained.

10. I tried to go in the MySQL prompt and restart the server, but I don't know the command(s) for that, so that didn't fix anything.

11. I restarted my machine and restarted Navicat, and the problem remained.

12. I recreated the table not using the table designer but just the Design Table menu option (it's basically the same interface as the visual designer, where you can input the field names, types, sizes, etc.) and that didn't work.

Basically, the "tablespace" seems to be the name of the database". When I am doing the sync function through the designer feature, the wizard walks you through a bunch of steps where at the end, it shows you the SQL commands it's about to run against the database. You can choose to execute any, all, or none of the commands in the list, but you can't edit them.

So, it seems to me like the database needs to be "purged" in some fashion to remove the tablespace usage from the offending tables. It's almost like I can't reuse the same table names, and I can't perform the necessary ALTER TABLE command because the table doesn't exist, and yet, I need the tablespace to go away. I have no idea what a tablespace is, by the way, but it seems akin to Java's packages or C++/C# namespaces. Somehow I accidentally got the database name to act as the tablespace for one or more tables and this has messed me up.

So I guess the bottom line is, the problem in the original database 'travels' with any new database, and I do not want to have to start all over again in a fresh databse.

Questions:

1. Do I basically have to recreate all of the tables and their respective data in a totally new database within my same connection? (Navicat groups all databases under a connection, so I just store all my databases under that one, and it's worked just fine that way for me ever since I started.)

2. Is there some other form of "force purge" command or action I can take to tell the database to forget about my tablespace for good and let me do what I want?

Thank you for reading.

Mike