XF 1.5 Trouble with corrupt tables

[Derek Zeanah]

TL/DR: is it possible to truncate or just recreate xf_search? Issues with that table are keeping me from getting my site back up.

So, I was out of town, and my power company decided to replace my power pole. I was down for hours, and when I eventually brought everything back up I ended up running into a bug in my version of MariaDB that wouldn't dig through the transaction log and fix things. I ended up taking the shortcut of renaming ib_logfile and ib_logfile1, and things looked OK. At the time it made more sense than reverting to the previous night's snapshot.

Now a couple of nights later my server went offline, and it turns out MariaDB was crashing. I ran a scan and found:

• xf_edit_history is missing one entry in the index index_edit_user_id
• xf_ip is missing an entry on ip_log_date
• xf_thread_read has a couple of issues
• xf_search is just...wrong.

This post is really about xf_search.

When I try and do a mysqldump MySQL dies on xf_search_row 38565. Always on row 38565. Whether I try and dump that one table or the entire database. When I try and create a second table LIKE xf_search and issue

INSERT new_xf_search SELECT * FROM xf_search;

I get this error:

error 2013 (hy000): lost connection to mysql server during query local machine

That happens via SSH, or on the console of the virtual machine.

The indexes listed above are marked as corrupt, and I'd like to simply rebuild the indexes and get my forum back online. I'm struggling though, likely because it's now 3AM here.

MySQL Dumps fail on the xf_search table. I'd run a TRUNCATE on xf_search but all the directions I find when googling are on xf_search_index, not xf_search. Creating a new table and copying the contents across as I saw on this page fails with the error shown above.

Is there a simple way to just rebuild the index on each table? Is it possible to just TRUNCATE the xf_search table? As an interesting data point, the mysqldump of xf_search by itself was 971M before mysql suicided on reaching row 38565. That can't be right....

 

[AndyB]

is it possible to truncate or just recreate xf_search?

Yes you can truncate the xf_search table.

 

[Derek Zeanah]

Thanks, Andy.

Crossing my fingers and wading back in...

 

[Derek Zeanah]

That worked for xf_search, and I OPTIMIZEd xf_edit_history and xf_ip.

When I try to OPTIMIZE xf_thread_read, however, I get this again:

MariaDB [xxx]> OPTIMIZE TABLE xf_thread_read; ERROR 2013 (HY000): Lost connection to MySQL server during query MariaDB [xxx]>

Is this another one I can simply truncate, and (I guess) the entire board will look like new posts to users? Or is this one where I should just drop the table and import it again? That's one that mysqldump actually dumped successfully, and it's only 5.5M which seems reasonable size-wise.

 

[Derek Zeanah]

Replying to myself here, in case someone else runs into this issue.

Instead of waiting on a reply I ran the following to recreate the xf_thread_read table, which completed successfully and the board popped back up.
CREATE TABLE new_xf_thread_read LIKE xf_thread_read; INSERT new_xf_thread_read SELECT * FROM xf_thread_read; DROP TABLE xf_thread_read; CREATE TABLE xf_thread_read like new_xf_thread_read; INSERT xf_thread_read SELECT * FROM new_xf_thread_read; DROP TABLE new_xf_thread_read;

I appreciate the help Andy. You rock.