Database version error & Search index missing following server issue

[willerby]

Evening all...
Whilst away from home on vacation my bluehost support fixed a database connection error for me but I'm not entirely sure what they have done. Since they implemented something, I no longer have a custom search index (which generates errors for every member trying to search) and every post from a member generates an error message stating:
Note: It appears that your database may require an upgrade. Your forum's files are currently at version SMF 2.0.18, while your database is at version 2.0.2. The above error might possibly go away if you execute the latest version of upgrade.php.
When trying to recreate a custom search index from the Admin options I get the following error:
Tablespace for table '`xxx_Forum`.`xxx_log_search_words`' exists. Please DISCARD the tablespace before IMPORT.
File: /home/xxx/public_html/forum/Sources/ManageSearch.php
Line: 617

Could you advise on best place to start to 1) fix the posting error and 2) resinstate the search function / index?
Any help appreciated
Thanks

[Aleksi "Lex" Kilpinen]

Okay, not to alarm you - It seems to me like BlueHost literally broke your database. That error is usually caused by a situation where a table is incompletely removed for some reason.
Are you able to access your database through phpmyadmin or similar tool? Can you check if that table exists in the database?
Also, what exactly does the SMF Search settings currently say about the index, "Already created" or "Doesn't currently exist"?

[willerby]

Thanks for replying Lex. That's what I thought. phpMyAdmin is accessible and working fine, however, that table doesn't exist in the listings.

SMF originally said that the table was "Already created" but showed it as 0kb. Having reverted to searching with No Index - slow but it works - it now says "Doesn't currently exist". This has also stopped the database error appearing whilst posting but I'm unsure what was meant by SMF's original warning as above. Have they installed an incorrect database version or is this error message just a consequence of the search index not being present?
I still can't recreate a search index as the table seems to be there is some form but is not showing. I am unsure how to DISCARD any remaining tablespace - or even what that means

[Aleksi "Lex" Kilpinen]

You can completely ignore the DB version warning, that's a red herring. If the table doesn't exist in the phpmyadmin list, and SMF says the index doesn't exist - then if you try to recreate a new index now, and it fails with the same error "Please DISCARD the tablespace before IMPORT." you will most likely need to contact your host again, and ask them to investigate the issue. Most likely, there is still a leftover .ibd for the table that was otherwise removed cold.
The file should be named xxx_log_search_words.ibd and with any luck, simply removing it and restarting the DB may fix it - but I would be cautious, no telling if something else was damaged too.

[willerby]

Ok, thanks. These things always happen when you are away from home and have a limited mobile internet connection. I'll get in touch with them and see what they can screw up this time.
Could you advise what the likely cause was? It appears I have a new MySQL server so I'm assuming that they tried to copy everytging across (to fix the original connection problem) and that table screwed up. Is that a likely scenario for what I am seeing?

Many thanks

[Aleksi "Lex" Kilpinen]

Couldn't say for sure with the info available, all I can say is that it seems like the table was incompletely removed, or incompletely moved, for some reason and the remnants of it is now haunting your database server not allowing you to create a new table with the same name.
It's possible that there was a hardware failure that caused your original connection problems, if that indeed was what led them to moving the database to another server completely - And if that happened, then the hardware failure might have caused the problem too, and not the actual move operation.

[willerby]

Thanks Lex.

[Aleksi "Lex" Kilpinen]

No problems, hope you get it sorted

[willerby]

After a week of requesting that Bluehost support remove this ibd file they have finally informed me that they have 'moved it away'. Unfortunately nothing has chnaged.
When viewing the Admin>Forum>Search>Search method page it states that:
Space used to index messages in the database:
    41236 KB
The forum is operating fine with settings as follows:

Search index                     No index
Fulltext index
    Index: doesn't currently exist [create a fulltext index]
Custom index
    Index: doesn't currently exist [create custom index]


However, if I try and click [create custom index] the following error appears:


Tablespace for table '`xxx_Forum`.`xxx_log_search_words`' exists. Please DISCARD the tablespace before IMPORT.
File: /home/xxx/public_html/forum/Sources/ManageSearch.php
Line: 617


Does the space used to index messages or the error message suggest that the old table is still getting in the way of being able to recreate a search index? (or that the host has not done what was asked?)

Any other suggestions?

Many thanks




 

[Aleksi "Lex" Kilpinen]

Give your host that exact error message, exactly as you see it, and tell them that is what they need to fix, and what they did so far did not resolve it. It is a DB error, not a script error.

There is a possible workaround as well, if you are willing to go to some lengths.
If you would be able to rename all those tables that do exist now, with new prefixes and then change your SMF settings to use that new prefix, the problem would likely be "gone". As in no longer in the way. But I would aim for a proper solution instead myself, and if you try this make sure you have 10 working backups before you even think of starting.

[shawnb61]

What happens when you try to repair the log_search_* tables within phpmyadmin?

[willerby]

The table `xxx_log_search_words` doesn't appear when viewing the database on PHPMyAdmin -

I have tables for `xxx_log_search_messages` `xxx_log_search_results` `xxx_log_search_subjects` `xxx_log_search_topics`.

Not sure how to repair a table I can't see listed?

[willerby]

Here is the latest response from my host...
Thank you for getting back to us. My name is Richie and I am the administrator looking over your case today. I appreciate your patience while we looked into your most recent request. I truly regret your recent experience as well as our delay in reaching back out to you.

It appears that your MySQL server went offline due to innodb database corruption on June 26th.

2021-06-26 00:00:02 InnoDB: Assertion failure in thread 46948423829248 in file trx0purge.cc line 713

InnoDB: Failing assertion: purge_sys->iter.trx_no <= purge_sys->rseg->last_trx_no

InnoDB: We intentionally generate a memory trap.

InnoDB: Submit a detailed bug report to http://bugs.mysql.com.

InnoDB: If you get repeated assertion failures or crashes, even

InnoDB: immediately after the mysqld startup, there may be

InnoDB: corruption in the InnoDB tablespace. Please refer to

InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html

InnoDB: about forcing recovery.

My colleague, Jim, renamed some MySQL tablespace files due to this corruption. This allowed him to bring your MySQL service back online. You can see the commands that he ran below:

   

2021-06-26 11:47:33 cd /var/lib/mysql/

2021-06-26 11:47:38 tail -n 50 server.XXX.com.err

2021-06-26 11:47:52 mv ibdata1 ibdata1.old

2021-06-26 11:48:03 mv ib_logfile0 ib_logfile0.old

2021-06-26 11:48:10 mv ib_logfile1 ib_logfile1.old

2021-06-26 11:48:20 service mysql start

My other colleague, Greg, reverted the changes on July 5th:

   

2021-07-05 16:58:42 cd /var/lib/mysql/

2021-07-05 16:59:24 mkdir newibfiles

2021-07-05 16:59:31 service mysql stop

2021-07-05 16:59:46 mv ibdata1 newibfiles/

2021-07-05 17:00:05 mv ib_logfile0 newibfiles/

2021-07-05 17:00:14 mv ib_logfile1 newibfiles/

2021-07-05 17:00:26 mv ibdata1{.old,}

2021-07-05 17:00:38 mv ib_logfile0{.old,}

2021-07-05 17:00:43 mv ib_logfile1{.old,}

2021-07-05 17:01:01 /scripts/restartsrv_mysql

2021-07-05 17:02:36 vi /etc/my.cnf

2021-07-05 17:02:50 /scripts/restartsrv_mysql

When he brought back the original ibdata and ib_log files, Greg was unable to get your MySQL service started again. He then added a line to your /etc/my.cnf which did allow MySQL to start correctly. The line that was added can be seen below:

   

innodb_force_recovery = 2

This is a safe configuration and does allow for the service to run properly even in the event of database corruption. Connor, then, as per your request renamed the XXX_log_search_words.ibd file so that it would no longer be in use, regrettably without any effective change. I sincerely apologize for this. You can see the commands he ran below:

   

2021-07-08 10:06:04 cd /var/lib/mysql/

2021-07-08 10:06:05 ls -lah

2021-07-08 10:06:42 find ./ -type f -iname 'XXX_log_search_words.ibd'

2021-07-08 10:06:50 cd ./XXX_Forum/

2021-07-08 10:06:51 ls -lah

2021-07-08 10:07:31 mv XXX_log_search_words.ibd{,.bak}

2021-07-08 10:07:35 service mysql restart

These were all the changes that my colleagues made during the troubleshooting of your MySQL service. If you can get this information to SMF and see what their technical team and developers say, please feel free to bring that information to us so that we can investigate further.

In the meantime, do not hesitate to reach back out to us if you have any additional questions or concerns.  Please also feel free to rate my service today by taking the survey mentioned at the bottom of the email.

Thank you,
Richie J.
Escalated Support

[Aleksi "Lex" Kilpinen]

I'm sort of surprised that they turned it back to us, though I do appreciate them actually sharing the steps they've taken.
I'm not an expert with MySQL myself, but as I understand this issue - this is purely a MySQL ( InnoDB ) issue.

I would however think at this point, maybe the best way to go would probably be to

1) Put SMF in maintenance mode 2 ( Untouchable ) through editing settings.php
2) Make backups of everything for yourself as a precaution.
3) Dump the current database contents to a file, create a new database with a different name, import the exported data to the new database
4) Reconfigure SMF to use the new DB and take it out of maintenance mode, using repair_settings.php, and check that everything works as it should
5) Remove the old broken database.

P.S. I am under the impression, that if you need to start MySQL with innodb_force_recovery the database is likely already permanently corrupt and needs to be rebuilt.