My Site Is Borked!

[Jordan Sharples]

I tried to upgrade the site: https://forums.wscc.mb.ca. It said there were a lot of database errors that needed fixing  . . . so I clicked "fix" and very shortly the site would only display:

 

An error occurred (500 Error)

We're sorry, but a temporary technical error has occurred which means we cannot display this site right now. 

Incorrect key file for table 'core_sys_lang_words'; try to repair it

You can try again by clicking the button below, or try again later. 

Try again

 

The "Try again" is not an active link, I'm at a loss of where to start.

 

Can you help?

[Randy Calvert]

It sounds like you have corruption in your mySQL database,  You might want to use a tool like phpMyAdmin to check and repair those tables.  

[Jordan Sharples]

Thank you Randy! I've already looked at the table in question (and others) using phpMyAdmin, and it confirms that I've got corruptions . . . evidently in more than one table. But I don't know where to start to repair.

[Randy Calvert]

It’s something your host should be able to help you fix. However if you have access to phpMyAdmin, you can sometimes do this yourself. Check out:

https://www.siteground.com/tutorials/phpmyadmin/repair-optimize-database/

[Jordan Sharples]

I'll get my host to fix this. phpmyadmin only reports that the table is corrupt and won't fix it.

[Randy Calvert]

Ok. Good luck. 🙂 

[Jordan Sharples]

OK! So now my site database has been restored, and the forums are working. https://forums.wscc.mb.ca/

I still need to update the software but approach it with caution.

1. So I downloaded a working version of the database.
2. I initiate the update and it tells me there are problems with the database that need to be fixed. The query list is HUGE! 764 lines of sql queries!!!!!
3. I try some of them manually:
   1. UPDATE `core_modules` SET `sys_module_title`='' WHERE `sys_module_title` IS NULL;
      1. result: nothing
   2. UPDATE `core_modules` SET `sys_module_application`='' WHERE `sys_module_application` IS NULL;
      1. result: nothing
   3. UPDATE `core_modules` SET `sys_module_key`='' WHERE `sys_module_key` IS NULL;
      1. result: nothing
   4. CREATE TABLE `core_modules_new` LIKE `core_modules`;
      1. result: created new table
   5. ALTER TABLE `core_modules_new` CHANGE COLUMN `sys_module_title` `sys_module_title` VARCHAR (32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' , DROP INDEX `identifier`, DROP INDEX `sys_module_application`, CHANGE COLUMN `sys_module_application` `sys_module_application` VARCHAR (32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' , ADD UNIQUE KEY `identifier` (`sys_module_application`,`sys_module_key`,`sys_module_area`), ADD KEY `sys_module_application` (`sys_module_application`), DROP INDEX `sys_module_key`, CHANGE COLUMN `sys_module_key` `sys_module_key` VARCHAR (32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' , ADD KEY `sys_module_key` (`sys_module_key`), CHANGE COLUMN `sys_module_area` `sys_module_area` VARCHAR (32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT 'front' , CHANGE COLUMN `sys_module_default_controller` `sys_module_default_controller` VARCHAR (32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL ;
      1. result: the table 'core_modules_new' is now trashed:
         1. #1034 - Incorrect key file for table 'core_modules_new'; try to repair it
         2. it's not repairable
   6. The next steps were to drop the original table 'core_modules_new' and to change the name of the new table to that of the old one . . . obviously I didn't do that.

So now I know why my system got borked . . . but I don't know what to do about it!

 

 

 

fix-queries.sql

[Jordan Sharples]

Short version: can’t upgrade, suggested database fixes results in corrupted tables. Details above. 

[Jordan Sharples]

In the process of updating our forum software, I applied the recommended database ‘fix’ of 764 sql queries. It trashed my database. A backup of the database got my site back running.

One of the problematic queries was, after creating a new table, altering it:

1. CREATE TABLE `core_modules_new` LIKE `core_modules`;
   1. result: created new table
2. ALTER TABLE `core_modules_new` CHANGE COLUMN `sys_module_title` `sys_module_title` VARCHAR (32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' , DROP INDEX `identifier`, DROP INDEX `sys_module_application`, CHANGE COLUMN `sys_module_application` `sys_module_application` VARCHAR (32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' , ADD UNIQUE KEY `identifier` (`sys_module_application`,`sys_module_key`,`sys_module_area`), ADD KEY `sys_module_application` (`sys_module_application`), DROP INDEX `sys_module_key`, CHANGE COLUMN `sys_module_key` `sys_module_key` VARCHAR (32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' , ADD KEY `sys_module_key` (`sys_module_key`), CHANGE COLUMN `sys_module_area` `sys_module_area` VARCHAR (32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT 'front' , CHANGE COLUMN `sys_module_default_controller` `sys_module_default_controller` VARCHAR (32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL ;

the result was an un repairable table:

#1034 - Incorrect key file for table 'core_modules_new'; try to repair it

what gives?

[david.hathaway@gmail.com]

Well, it'll be interesting to see if they come up with a fix or at least a fixed upgrade path as I just trashed my database as well with that "upgrade".  The article linked above would be fine, except no amount of phpmyadmin love via optimize or repair is working. Thankfully I do have a backup but...no update path.

[Jordan Sharples]

I’ve got a support ticket in with my service provider, I’ll post if I get a solution. 

[david.hathaway@gmail.com]

8 hours ago, Jordan Sharples said:

I’ve got a support ticket in with my service provider, I’ll post if I get a solution. 

That'd be great, thank you, pretty frustrated at the moment with this.

[Marc Stridgen]

Ensure you change all your tables to INNODB, and they are using dynamic row format. It may well be this causing issues

[david.hathaway@gmail.com]

FYI, changing all tables to innodb didn't help at all.

[Jim M]

14 minutes ago, david.hathaway@gmail.com said:

FYI, changing all tables to innodb didn't help at all.

Did you also convert all to dynamic row format?

[david.hathaway@gmail.com]

They were all converted to that as well. 

[Jordan Sharples]

I’ve not yet heard back from my host about this, but found this might point to a solution.

http://stackoverflow.com/questions/2428738/ddg#4075770

 

[ThunderBlue]

On 4/11/2023 at 1:53 AM, Jordan Sharples said:

OK! So now my site database has been restored, and the forums are working. https://forums.wscc.mb.ca/

Hi. Have you tried this way?

• Restore previous and working backup.
• Open administration and disable all plugins, applications and themes you have added.
• Rename all innodb tables, and use dynamic row format.
• Go to client area and download the latest version of the forum.
• Send with ftp the files and folders to your space, making sure to overwrite all files and folders. This is a manual update. See this guide to learn more
• Open the browser and proceed with upgrade from your-site/admin/upgrade
• If you can log into admin when finished, clear all the cache using the support option in the upper right corner

 

 

Edited April 13, 2023 by ThunderBlue

[david.hathaway@gmail.com]

Ah the misery continues 😉  What has happened to the file linked here:

 

• Some or all of the columns in your database are not using the utf8_unicode_ci collation (ibf_blog_blogs.blog_settings is utf8mb3_unicode_ci). Download the UTF8 Database Converter

[david.hathaway@gmail.com]

Well, I am nothing if not persistent in trying to get this upgraded, what "should" this table be, anyone got an sql command to create it?

Table 'revkites_revkites.ibf_core_widget_areas' doesn't exist
/home/revkites/public_html/forum/applications/core/setup/upg_107400/queries.json - query #2
UPDATE `ibf_core_widget_areas` `core_widget_areas`  SET `widgets`=? WHERE app='core' AND ( controller IN ( 'login', 'register' ) ) 

/home/revkites/public_html/forum/applications/core/setup/upg_107400/queries.json - query #2

[david.hathaway@gmail.com]

As a followup, this board is now upgraded and seemingly working properly, however, gotta say, after 12 years of maintaining this board, this was easily the most painful upgrade ever.

[Jordan Sharples]

David: I’d love to know how you did it!

[david.hathaway@gmail.com]

Gads, I should have taken notes but a few key things, make sure the entire db is converted to innodb and all tables and columns are utf8mb4_unicode_ci (i had a ton that were mb3, likely due to the age of the forum). I used the manual approach, but still had to refer to the sql backup of the old forum to create a handful of tables that the update claimed were missing. It still took 5 attempts at a manual upgrade as it would time out, but it would pick up where it left off. I also had to restore the uploads folder as some part of the upgrade wiped out 10 years of user photos. Backups are your friend.

[Jordan Sharples]

I can't upgrade!?!?

• I've updated all the tables to INNODB
• all tables and columns now are utf8mb4_unicode_ci (they were already)
• I've manually ran the huge list of sql queries that the update process recommends (ran it in batches of 20 - 30 lines at a time, noting any errors, most frequently:
  • Warning: #3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
  • Warning: #3778 'utf8mb3_unicode_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
• I've also run the database fixes using the "Fix Automatically" button
• I click on "Check Again" and it returns the same 764 line sql query list as before!!!
• The forum is still running  . . .  so at least the update process hasn't borked like before
• but I can't progress beyond: "There are some problems with your database which need to be fixed before you can start the upgrade.

[Jim M]

Have you tried to perform a manual upgrade? I don't remember the exact version but there was a bug recently which caused something similar to what you're experiencing and the only way forward is a manual upgrade. Please be sure to perform a full database and file backup prior.