ipb_topic_views in use

[rev.dennis]

Was having a hell of time with any SQL repair or check showing:

An error occured with the SQL server:

mySQL query error: ALTER TABLE ipb_topic_views ADD INDEX views_tid (views_tid)

So I opened a support ticket with Invision Power which replied

If you are trying to fix indexes, there is little point if you are upgrading to 4.1 to be honest as the database structure and all indexes are changed in any case

.

I replied with:

Thanks, it appears an issue is the table ipb_topic_views is locked in use and I can't seem to find a way to repair it.

What I tried was...

CLI

mysql> use thezahco_hosangit-comDB;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> REPAIR TABLE ipb_topic_views;

+-----------------------------------------+--------+----------+---------------------------------------------------------------------------+

| Table | Op | Msg_type | Msg_text |

+-----------------------------------------+--------+----------+---------------------------------------------------------------------------+

| thezahco_hosangit-comDB.ipb_topic_views | repair | Error | Can't find file: 'ipb_topic_views' (errno: 2 - No such file or directory) |

| thezahco_hosangit-comDB.ipb_topic_views | repair | status | Operation failed |

+-----------------------------------------+--------+----------+---------------------------------------------------------------------------+

2 rows in set (0.00 sec)

They replied with simply stating

Please contact your hosting provider for further assistance with MySQL issues as we are unable to assist with these issues.

My reply (kinda pissed they just threw it over the wall to have someone else deal with it:

Since hosting won't be able to help anymore than you can. Could you tell me if upgrading to 4.x will fix the issue? It's obviously a corrupt table and I'm looking for a way to fix the table which hosting won't have a clue on. I have started mysql in safe mode, tried to repair it and its obviously an issue with the table being corrupt.

There final reply was

You would need to resolve the issue before youc an upgrade unfortunately. You could drop the table and recreate this if you are happy to lose views of topics?

So the mission started to figure this out. So what eventually worked was

First... force a dump on the table

mysqldump -f thezahco_hosangit-comDB ipb_topic_views

This created ipb_topic_views.sql

I also ran the following command, thinking I could just drop the table now

mysql
use thezahco_hosangit-comDB;
SET FOREIGN_KEY_CHECKS=0; DROP TABLE ip_topic_views; SET FOREIGN_KEY_CHECKS=1;
quit;

Inside the ACP of IPB I was now able to fix the issue with Database Tools when prompted if they wanted to try and repair it (it worked)