re_core_search_index Table Crashes Every Saturday (SQL qstn)

[Aramaech]

This issue has been plaguing our forum for about 8 months at this point.  It's driving me, the rest of staff, and many of our members to the brink of madness with the amount of failed attempts to fix it.  
Every Saturday morning at exactly 4:13 am the search index table crashes.  Hopefully someone who understands MySQL will be able to help here.

IPS tech support has been very patient and supportive, but ultimately determined that the software is not capable of corrupting the database, and that it must be an issue with the host.  Possibly their backup procedure.  

The host's tech support (bluehost), has been completely ineffective, and very recently STOPPED doing trouble tickets... -_-  They are unable to run SQL queries, will not examine the database, and have advised me to restore to backup as a last resort pretty much every time.  This seems to be their default answer.  Since the bug has been recurring for about 8 months, restoring isn't really an option.  To boot, I believe restoring to backup may have caused the problem in the first place.  Or more so, restoring to 1 week backup and then restoring to current backup possibly before the first restore process had completed.  I don't know, that's just a last guess.  This bug has a freakin Odessy's worth of attempts to diagnose and solve it between myself, other staff and tech support.

No 3rd party plugins, no ckeditor buttons, no rss feeds, no calendar.  Forum only.  Conclusion:  There Must be some corrupted rows of data in the database.  Likely from a botched restore process.
So finally we arrive at the current state, and question.  

1) the table "re_core_search_index" must be attempting to reference the following tables tables:
    ~ "re_forums_posts"
    ~ "re_core_members_status_updates"
    ~ "re_core_members_status_replies"

2) These tables all contain rows where the member_id value is 0.  
    ~ Given that the forum creators member_id = 1, and it auto increments from there, might these rows be corrupted?
    ~ *** Should these rows be deleted? ***
  

Note:  Attempting to Optimize the re_core_search_index table causes it to crash.  This can be used to trigger the bug manually.
Pics:  First 2 pics are of rows with member_id = 0.  Last pic is of the error returned by Optimizing the re_core_search_index table.

 

[Numbered]

Hello. I am not good DBA, but not sure anybody can help you with clear answer without some deep checks.

So first question - how you repair this table in older time? You truncate it, or just restore data from backup? Anyway - better way for fix that - full drop this table and re-create it. Than - restore backuped database or (works for this table only!) - you can just re-create it and run 'Re-create search index in ACP' - it will run background working of re-creating search index. But it must be guarantee correct.

Problems with member_id=0 may be got if you have wrong type if member_id row. bigint(20) - currently right type and size (so if some you member has id bigger then old int(8) - you can get error/corrupt db or member_id as zero.

Last part (if points above is not fix problem) - you can try to set this table engine to innodb. By default engine of this table is myisam because it has a lot of fast write operations. Much more than reads. But if your community is not write/post fast - you can switch engine to innodb and get possible more stability (and on backup time too).

Hope this help you

[Aramaech]

First of all thank you.

First question, In the past I've truncated the table and rebuilt it.  The problem, I think, is not with the "re_core_search_index" table itself, but one of the tables that its built out of.  Namely either the posts or status tables.  Rebuilding the search index only fixes it temporarily. 

In the past setting the table to InnoDB format DID fix the problem.  It worked great for a few months after doing that.  Problem is, in recent months, IPS automatically converted the table back to MyISAM format. Now, trying to change it Back to InnoDB produces an error.  "Max key length too long".  So apparently InnoDB is no longer an option.  

It seems like the only remaining option is to go into the tables that the "re_core_search_index" is built out of and look for corrupted rows of data.  ( Again, namely the topics, and status tables).  So that's what I'm trying to do here.  

I'm wondering if a row of data in the Topics table that has a userid = 0 would constitute a corrupted row, that would cause problems in the search index table no matter how many times I recreate it, because every time I do, it just pulls that corrupted up row and causes the same problem.

So basically, can I delete those rows without causing problems?

[bfarber]

The table should be converted back to InnoDB to resolve the issue it sounds like. We do absolutely support InnoDB, however if you are using MyISAM now the max key length is 1000 while it is only 767 with InnoDB, so you will first need to adjust indexes.

Here is what I would do:

1. Truncate the core_search_index table in phpmyadmin
2. Drop all of the indexes on the table, except for the primary key, in phpmyadmin
3. Convert table to InnoDB
4. Log in to AdminCP and run the support tool, which will tell you about the missing indexes on the table and allow you to recreate them
5. Rebuild the search index as previously mentioned

I do not believe that these rows in the other database tables are "corrupted" or are causing your search index table to corrupt. I do not believe that removing them will resolve the problem you are facing, or are indeed a problem necessarily.

[Aramaech]

Your reply is Very much appreciated.  This thing has been haunting us for about a year now.  However, unfortunately, we tried truncating the search index table quite a while ago, and it didn't end up working.  And you're right about those rows not being corrupted.  It turned out to be something with the table itself.  

About 2 weeks ago I hired a freelancer with a few decades of database management experience, and he and I have been working at the problem.  His initial diagnosis was that the table sounded like it had a bad pointer, so he suggested recreating the table from the original tables structure.  CREATE TABLE ___ LIKE ___ query in MySQL.  That wound up producing a version of the table that could actually be converted to InnoDB, which solved the problem.  However before converting it, I did wait for one Saturday to pass with it in MyISAM format just to see if the the fix was total, or just a band-aid.  The bug did end up happening, even with the new table, so whatever the problem is, it's deeper than recreating the table.

But for now it's happily in InnoDB mode, which is working just fine, but he and I are still trying to diagnose the ACTUAL cause of the thing.  The main problem with it at this point is that our site is on a cloud server, so we don't have a dedicated server he can have root access to in order to use all his normal tricks, so we're kind of just doing the workaround thing for the moment.  His pending suggesting at the moment is to try downloading a backup of the entire database, and restoring to it, just to flush out any table errors.  So we'll see how that goes.  If we make anymore useful headway with it tho, I'll post it here just for reference sake

Again, thank you very much for the reply.  That was actually one of the first things our staffs coder suggested too