Mysql – View dropped, now shows as table “in use”, can’t drop

corruptionmyisamMySQLview

I have a view on a MySQL/MyISAM setup, then the underlying table had a column dropped, and made that view invalid.

I tried dropping the view so I could recreate it, then MySQL said it was a table (not a view) and marked it "in use".

Now I can't drop nor repair that table because it says it is not a table.

I've googled for hours trying find a way to fix this 🙁

I do have root access on the MySQL server running Ubuntu. I stopped MySQL and tried myisamchk, but no luck.

I can see a .frm file for the view in the db data folder.

Do I need to delete that file to make the corrupt item go away,
or do I somehow delete the table from the db schema?

I have a db backup from last night, but would rather fix this 1 issue than restore from backup and lose today's changes.

Best Answer

You should be able to simply rename that file to just about anything that doesn't end in .frm and then SHOW TABLES. It should be gone. If it's still there, FLUSH TABLES, then SHOW TABLES again.

Related Solutions

Mysql – Repairing Myisam Table when there was no additional Disk space & Table corrupted

If you cannot free up or add any disk space, you could copy the table_data.* files (MYD,MYI,frm,etc) to another machine with plenty of free disk, run the repair there with myisamchk, then copy the files back to the original machine. If the server needs to stay running, do a FLUSH TABLES WITH READ LOCK table_data before copying the data to the other server and UNLOCK TABLES after you copy the data back. You will need to keep the FLUSH TABLES ... session running for this duration.

So, it would look like this

server1:

# keep this session running for the duration of the repair!
mysql> FLUSH TABLES WITH READ LOCK table_data;
/var/lib/mysql# rsync -aP table_date.* server2:/somedir/

server2:

# make a backup
/somedir# tar -czvf table_data_backup.tgz table_date.*
# run the repair
/somedir# myisamchk -r table_data
# copy the files back
/somedir# rsync -aP table_date.* server1:/var/lib/mysql/

server1:

# make sure the permissions are correct on /var/lib/mysql/table_date.*
mysql> UNLOCK TABLES;

Remember, do not close the FLUSH TABLES WITH READ LOCK session.

You may want to give myisamchk additional memory for the following parameters to speed it up: --key_buffer_size --sort_buffer_size --read_buffer_size --write_buffer_size

Next, convert your table to InnoDB. There's almost no good reason to use MyISAM these days.

Mysql – How to restore thesql DB from only thei files

No, it's not possible - you need the MYD file, too. The MYI file is only the indexes and does not contain any data. It is like trying to restore the contents of the book High Performance MySQL from only the index pages.

Best Answer

Related Solutions

Mysql – Repairing Myisam Table when there was no additional Disk space & Table corrupted

Mysql – How to restore thesql DB from only thei files

Related Question