Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
yes that worked. thank you. I was gun shy to do that as I was not sure if there were all kinds of referential integrity checks in other tables etc. – Steve Wasiura Feb 28 at 5:22
for future reference, is there any kind of constraint I could put on tables that are accessed from views, so this mistake doesn't happen again? i.e. prevent a column delete from a table if the table is used in a view? or is it better to control privileges to restrict the ability to ALTER and DROP on tables, which would force an error when you tried to do this bone headed move and then force you to think before ALTERing the structure? – Steve Wasiura Feb 28 at 5:36
There isn't an automatic way to keep people from breaking views by changing the underlying tables, but what happened here is an anomaly... ordinarily, all that should happen is ERROR 1356 (HY000): View 'test.broken_view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them ... at which point you (normally) can just drop the view and recreate it... where that process went wrong for you is not clear. – Michael - sqlbot Feb 28 at 16:14
Thanks Michael. I did get that error message, but I could not drop the view because it said it didn't exist. Anyhoo, it's fixed now, anomaly be damned. thanks – Steve Wasiura Feb 28 at 22:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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