PHP and database problems when trying to upgrade

[simonle]

I am trying to upgrade from v4.5.4.2 to v4.7.12, but two issues are stopping me.

I'm currently running PHP version 7.4.30 and when I try to upgrade to PHP 8.1 to be able to perform the upgrade, my community and ACP eventually stops working.

Also, once I run on PHP 8.1 (or 8.0) I get "database problems" that I can't seem to fix. My ACP says I need to run these queries:

UPDATE `core_output_cache` SET `cache_expire`=0 WHERE `cache_expire` IS NULL;

ALTER TABLE `core_output_cache` DROP INDEX `cache_expire`, CHANGE COLUMN `cache_expire` `cache_expire` INT NOT NULL DEFAULT 0 COMMENT 'Unix timestamp of when the cache expires', ADD KEY `cache_expire` (`cache_expire`);

UPDATE `blog_entry_categories` SET `entry_category_blog_id`=0 WHERE `entry_category_blog_id` IS NULL;

ALTER TABLE `blog_entry_categories` CHANGE COLUMN `entry_category_blog_id` `entry_category_blog_id` INT NOT NULL DEFAULT 0 ;

But it doesn't help if I try to fix them automatically or run them manually. I still get a "database problems" message when I try to upgrade.

What should I do? I am using the automatic installation process in my ACP. Should I upgrade from v4.5.4.2 to v4.7.12 manually instead? 

[Miss_B]

9 minutes ago, simonle said:

Should I upgrade from v4.5.4.2 to v4.7.12 manually instead? 

It's worth a try. Before you begin the upgrade, it is better to disable all third party plugins/apps/themes imo to avoid php incompatibility errors afterwards. 

Edited September 4, 2023 by Miss_B

[Marc]

WIth the mysql issue, its because you are on an older release, where there was a bug that has since been resolved. So you would need to run manually as you mentioned

[simonle]

Alright, thanks!

I'm doing a manual upgrade, but now I get this error message:

Specified key was too long; max key length is 1000 bytes

ALTER TABLE `core_attachments_map` ADD KEY `map_lookup` (`location_key`(250),`id1`,`id2`)

What does this mean?

[simonle]

18 minutes ago, simonle said:

Alright, thanks!

I'm doing a manual upgrade, but now I get this error message:

Specified key was too long; max key length is 1000 bytes

ALTER TABLE `core_attachments_map` ADD KEY `map_lookup` (`location_key`(250),`id1`,`id2`)

What does this mean?

Okay, so it seems my core_attachments_map table was in MyISAM. I converted it to InnoDB (see code below) and the upgrade process could continue.

ALTER TABLE `core_attachments_map` ENGINE = InnoDB

Edit: And my upgrade is now complete! 🥳

Edited September 4, 2023 by simonle

[Miss_B]

Gald to see that you were able to upgrade your forum successfully.