Cannot upgrade automatically due to "There are some problems with your database."

[MikeDVB]

Hello!

When attempting to run an automatic update I am seeing this error:

Quote

 

There are some problems with your database. Normally it is safe to try to fix these problems automatically however if your community is large, you may want to run the necessary queries manually. If so, the queries to run are:

Some of the SQL is like follows:

UPDATE `ipb_core_modules` SET `sys_module_title`='' WHERE `sys_module_title` IS NULL;
UPDATE `ipb_core_modules` SET `sys_module_application`='' WHERE `sys_module_application` IS NULL;
UPDATE `ipb_core_modules` SET `sys_module_key`='' WHERE `sys_module_key` IS NULL;
CREATE TABLE `ipb_core_modules_new` LIKE `ipb_core_modules`;
ALTER TABLE `ipb_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 ;
INSERT IGNORE INTO `ipb_core_modules_new` SELECT * FROM `ipb_core_modules`;
DROP TABLE `ipb_core_modules`;
RENAME TABLE `ipb_core_modules_new` TO `ipb_core_modules`;

There are ~754 queries that need run.

When I click "Fix Automatically" it just comes back to the "Database Check - There are some problems with your database which need to be fixed before you can start the upgrade." page.

I have manually processed all ~754 queries on the database and the upgrade tool is still saying that the database needs 'fixed'.

If I manually download and upload the forum files over the existing ones the upgrade succeeds without issues.

We are running MariaDB 10.6.11 and PHP 8.1.13.  The forum works great, manual updates work great, but automatic upgrades seem to be broken and I'm not sure why.

Edited December 9, 2022 by MikeDVB

[Marc Stridgen]

If this is happening on the next upgrade, please let us know. It sounds likely this was an issue in the version you were upgrading from, and the upgrader itself would correct any database issues. Therefore its unlikely you will see this on the next upgrade.

[MikeDVB]

On 12/9/2022 at 3:30 AM, Marc Stridgen said:

If this is happening on the next upgrade, please let us know. It sounds likely this was an issue in the version you were upgrading from, and the upgrader itself would correct any database issues. Therefore its unlikely you will see this on the next upgrade.

When we were upgrading from 4.7.3 to 4.7.4 we had this issue.  I ran the automatic "Fix this" in the upgrader and it didn't detect anything different.

I ran the SQL queries manually on the database, and the upgrade process still detected the exact same issues.

I performed a manual update from 4.7.3 to 4.7.4 - and that went without any issues.  The forum works great.

Now that 4.7.5 is out - the same exact issue exists.  Again - the "Fix this" during the upgrader does not fix it nor does running all of the queries manually.

It seems to me that the updater is detecting problems where none exist and not that the "Fix this" tool is broken.  If it were the "Fix this" tool not working - me running the queries on the database manually would solve it.  Being that it doesn't matter how I "fix" it - the issue is detected - I supsect it's the updater itself.

All of this is to say this is not the first time we've seen this.  This is two consecutive updates where this issue has occurred.

[Marc Stridgen]

Could I please confirm you have nothing showing within the support area under the mysql section that needs addressing, other than those queries?

[MikeDVB]

14 hours ago, Marc Stridgen said:

Could I please confirm you have nothing showing within the support area under the mysql section that needs addressing, other than those queries?

It was reporting we were on UTFMB3 instead of UTFMB4.

I corrected this - and the update works as expected.

Why the upgrader is not detecting this and then suggesting the fix from UTFMB3 to UTFMB4 [as there is a fix offered in the forum itself] before performing the overall 'check and repair' seems strange.

Once converting to UTFMB4 the upgrade works fine.

So I imagine you can reproduce this.

Install 4.7.4.  Convert it to UTFMB3 [just to reproduce the issue].  Then run the updater - you'll get a long list of queries to run and the auto-fixer won't fix anything.

When you convert to UTFMB4 - it all 'just works'.

Should probably check for UTFMB3/non-UTFMB4 before the generalized check as that generalized check appears to be a false positive based upon the collation/character set being different.

[Marc Stridgen]

Actually the config setting tells the software what collation your site is using. So if that was set to show its using utf8mb4, then thats what it would think its using, and likely will have been at some point. Have you moved host at some point perhaps?

[MikeDVB]

On 12/27/2022 at 11:25 AM, Marc Stridgen said:

Actually the config setting tells the software what collation your site is using. So if that was set to show its using utf8mb4, then thats what it would think its using, and likely will have been at some point. Have you moved host at some point perhaps?

We _are_ the host - we run this on a dedicated server for the purpose.

It was on a really old version of 3.X which we moved to 4.X.

I suspect this issue would only happen in that kind of situation and isn't common [as such, you aren't aware of it / haven't seen it before].

Were you unable to reproduce the issue?

[Marc Stridgen]

Im unable to recreate this, except in cases where configuration files are incorrectly set, no. As its resolved in your case there, we have little else required to investigate in this case

[Graeme S.]

I do get the same issue when trying to upgrade from 4.5.4.2 to 4.7.14.
Doesnt matter if I run these queries manually or not, it stays the same.

 

[teraßyte]

Don't use the upgrade script in ACP. Try going manually to /admin/upgrade instead.