1

So I had a bunch of csv files in my /var/lib/mysql/imports directory for importing into mysql tables I had created. To clean up the folder I deleted all the files in the directory (incorrectly thinking it only contained .csv files). The only problem was that the folder also stored table data from my imports database. Now that I realized my error I was just going to recreate the database I did delete and re-import from the csv file. However I cannot create the table again because mysql must have references to the table. How can I remove this table completely so that I can simply recreate it and re-import the information?

Edit:

mysql> DROP TABLE imports.ncaa_passing_statistics;
ERROR 1051 (42S02): Unknown table 'ncaa_passing_statistics'
mysql> SELECT * FROM imports.ncaa_passing_statistics;
ERROR 1146 (42S02): Table 'imports.ncaa_passing_statistics' doesn't exist
mysql> CREATE TABLE imports.ncaa_passing_statistics (id INT);
ERROR 1050 (42S01): Table '`imports`.`ncaa_passing_statistics`' already exists
mysql> 
  • Can you tell if those were MyISAM or InnoDB tables? I suppose InnoDB, so they have been deleted from the filesystem but not from InnoDB's internal dictionary. – jynus Apr 3 '15 at 19:07
  • They are InnoDB – moopasta Apr 3 '15 at 19:11
1

Shut down mysql. What files do you see? Restart mysql; are things better now?

One possible fix is to CREATE a new table (with a different name), copy the .frm to somewhere else, drop the table, shutdown, copy the .frm back as the problematical table name, restart.

  • So I shutdown by doing... service mysql stop and service mysql start ... not sure if that's correct. I checked for file changes inbetween those and after and none of the .frm files i removed showed up again... or anything other than the csv files currently in the folder. – moopasta Apr 3 '15 at 18:23
  • I added some mysql information to my original post perhaps that'll clear things up a little? – moopasta Apr 3 '15 at 18:55
  • It is the fix, it is documented here: dev.mysql.com/doc/refman/5.6/en/… – jynus Apr 3 '15 at 19:18
  • Tried that and named it the same thing as the table in question and doesn't seem to work for me. It simply says... ERROR 1051 (42S02): Unknown table 'ncaa_passing_statistics' – moopasta Apr 3 '15 at 19:39
  • @daveomcd could you paste literally every single command (sql and OS) that you did before getting that? – jynus Apr 4 '15 at 0:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.