Impossible to Upgrade Website

[coert_g]

Been struggling to update my IBP install for ages now and none of the support so far has been able to address the problem.

When I try to do an automatic upgrade, I get an error about the PHP version needing to update. But when I update the PHP version, the entire site crashes and returns 500 errors on every page including the admin.

To try and get around this, I tried to do a manual upgrade by changing to PHP 8.1 and then copying the new files to the server. However, after doing so if I go to domain.com/admin/upgrade - the upgrade process starts and then immediately returns another 500 error and cannot proceed.

I've looked at the error logs and prior to the manual update attempt there were references to the theme.php, however, no new errors are being logged during these crashes.

I've tried just about everything and cannot get our site to upgrade, we're now massively behind in updates because of this.

[Jim M]

We would need the exact server error log entry which the 500 Internal Server Error is creating. Otherwise, we would not be able to provide further information on how to proceed.

[coert_g]

9 minutes ago, Jim M said:

We would need the exact server error log entry which the 500 Internal Server Error is creating. Otherwise, we would not be able to provide further information on how to proceed.

Please advise how I can get this error to you. I've looked at the error log and the error isn't being logged in the file.

[Jim M]

2 minutes ago, coert_g said:

Please advise how I can get this error to you. I've looked at the error log and the error isn't being logged in the file.

You would need to check your server's error log. If the error isn't logged, please contact your hosting provider for assistance.

[coert_g]

2 hours ago, Jim M said:

You would need to check your server's error log. If the error isn't logged, please contact your hosting provider for assistance.

I managed to eventually get past the 500 errors in the installation process, but now I'm stuck in a loop where it's asking me to make manual changes to the MYSQL.

I've gone through the process of running every line as required. I've triple-checked to make sure I didn't miss any. But when I click the "I have run the queries manually and confirm they were run successfully" button, the installer refreshes and shows the same manual commands to run.

[Jim M]

Have you ran them on the correct database? That would indicate the upgrader is not seeing what you've done so likely not the correct database.

[coert_g]

5 minutes ago, Jim M said:

Have you ran them on the correct database? That would indicate the upgrader is not seeing what you've done so likely not the correct database.

Thanks for following up. Yup, we've only got one database, and several of the queries successfully made changes to the tables they're targeting. Some weren't able to be run through Adminer (Using MariaDB) due to timeout problems but were run via SSH.

[Jim M]

I would suggest running all via SSH and confirming that these changes actually were performed.

Are you also running the database in UTF8mb4?

[coert_g]

12 minutes ago, Jim M said:

I would suggest running all via SSH and confirming that these changes actually were performed.

Are you also running the database in UTF8mb4?

I ran all queries through SSH now, but same problem. The database is in utf8mb3 though, not utf8mb4.

Would you recommend that I update the tables to utf8mb4?

[Jim M]

9 minutes ago, coert_g said:

Would you recommend that I update the tables to utf8mb4?

You'll want to utilize the converter in the ACP -> Support. If you do not have access, you'll need to restore your backup taken prior to upgrading.

[teraßyte]

@Jim M Not having a utf8mb4 database is causing more and more issues recently, especially with the new utf8mb3 alias.

I suggest changing the warning (orange) on the ACP support page to an error (red) to make it stand out more. 🙄

[Jim M]

1 minute ago, teraßyte said:

@Jim M Not having a utf8mb4 database is causing more and more issues recently, especially with the new utf8mb3 alias.

I suggest changing the warning (orange) on the ACP support page to an error (red) to make it stand out more. 🙄

There are some talks internally on how we can better handle this.

[coert_g]

On 8/23/2024 at 5:23 PM, Jim M said:

You'll want to utilize the converter in the ACP -> Support. If you do not have access, you'll need to restore your backup taken prior to upgrading.

Unfortunately, using the conversion tool doesn't seem to work for our database. I got the following error when trying to convert:

 

Quote

4C171/4 The database could not be converted. Contact your hosting provider or system administrator for assistance.Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

I reached out to our hosts and they gave me the following response:

 

Quote

There's no system-wide setting on our end that we can adjust to correct this. The row size limit is built into the database storage engine and cannot be changed.

The solution would be to modify your database structure as the error message suggests, ie by converting the data type of one or more large TEXT fields from TEXT to BLOB.

"Blob" storage means that the field data is stored outside of the tablespace and instead is linked to the field via a small pointer value, thus allowing the BLOB fields to use only a few bytes in the tablespace regardless of the amount of data stored. This is how that change gets you under the row size limit.

We can help you make whatever changes are needed but we don't have any particular expertise with your Invision Power CMS software and are not able to identify exactly which fields for which tables would need to be modified.

Since this is a commercial product, my recommendation is that you first bring this issue to the attention of the software vendor. This is important because if their data conversion tool is trying to modify data in a way that is inherently incompatible with the database storage engine that their software uses (ie by exceeding the row size limit) then that could be an indication of a serious flaw in the conversion tool.

If their advice is to proceed with modifying the database structure to convert TEXT fields to BLOB fields and they aren't able to support their customers by doing that themselves, then please find out exactly which fields on which tables would need to be modified. We'll then be able to assist you with the necessary changes.

Another possible solution might be to migrate your database from MariaDB to PostgreSQL, but again that's something for which the software vendor would need to provide guidance.

Can you please advise?

[Marc]

Check your database is using INNODB rather than myISAM and also that its using dynamic row type rather than compact

[coert_g]

4 hours ago, Marc said:

Check your database is using INNODB rather than myISAM and also that its using dynamic row type rather than compact

I've checked the database settings and all row formats are Dynamic, and they're all using the InnoDB engine.

[Marc]

Do you have database access you can provide within your client area so I can take a quick look, as the above would indicate otherwise

[coert_g]

11 minutes ago, Marc said:

Do you have database access you can provide within your client area so I can take a quick look, as the above would indicate otherwise

Please let me know how to share the database logins with you. In the client area, I only see options for FTP and Admin logins, I'm connecting with MariaDB using Adminer. Shall I drop you a PM? Or am I missing something in the client area?

[Marc]

Add temporary details to the notes in that area. You can remove them of course once Im done taking a look

[coert_g]

Details are shared in the notes of the client area.

Edited September 5 by coert_g

[Marc]

Cant see anything obvious on there. Its a very very old release you are on at present unfortunately. Just looking at profile fields there, please check through those and ensure that you only have ones you need. I know thats one area that may break it

[coert_g]

1 minute ago, Marc said:

Cant see anything obvious on there. Its a very very old release you are on at present unfortunately. Just looking at profile fields there, please check through those and ensure that you only have ones you need. I know thats one area that may break it

Yeah, we've been trying to update for years now, but there's always an issue like this in the process so we've fallen far behind in the updates.

I appreciate your help though...

When I look at an overview of the DB tables, I see that some of the tables have been converted correctly, and it seems to stop at the table cms_custom_database_5 - do you mind taking a look there and seeing if there's anything that stands out. I'm assuming that's the table that's having the problem if the converter is running alphabetically down the tables.

Would I be correct in assuming that it's likely one of the VARCHAR columns within that table? 

It's really imperative we get through this update process 🙃

[Marc]

You could try the following. Please ensure you have done a FULL backup, and you are ready to revert if needed.

Add the following to your conf_global.php in the 

'sql_utf8mb4' => true,

example

<?php

$INFO = array (
  'sql_host' => 'localhost',
  'sql_database' => 'stuff',
  'sql_user' => 'stuff',
  'sql_port' => NULL,
  'sql_socket' => NULL,
  'sql_tbl_prefix' => NULL,
  'sql_utf8mb4' => true,
  'board_start' => 1677243260,
  'installed' => true,
  'base_url' => 'http://location',
  'guest_group' => 2,
  'member_group' => 3,
  'admin_group' => 4,
);

 

Then try to do the upgrade to the latest release, without doing the UTF8mb4 update