"Index column size too large" Upgrading OJS 3.1 > 3.3 DB error on upgrade.php

[Nils]

Hi,

I want to upgrade ojs from 3.1.1-4 to 3.3.0-11. I am currently trying the upgrade on an LXC container with a copy of the system. When I run

php tools/upgrade.php upgrade

I get this result:

2022-09-17 09:56:10 [pre-install]
2022-09-17 09:56:10 [load: upgrade.xml]
2022-09-17 09:56:10 [version: 3.3.0.11]
2022-09-17 09:56:10 [code: Installer Installer::checkPhpVersion]
2022-09-17 09:56:10 [data: dbscripts/xml/upgrade/3.1.2_preupdate_user_author_names.xml]
2022-09-17 09:56:10 [code: Installer Installer::migrateSubmissionCoverImages]
2022-09-17 09:56:10 [data: dbscripts/xml/upgrade/3.2.0_preupdate_email_templates.xml]
2022-09-17 09:56:11 [data: dbscripts/xml/upgrade/3.2.0_preupdate_versioning_articleGalleySettings.xml (skipped)]
2022-09-17 09:56:11 [data: dbscripts/xml/upgrade/3.2.0_preupdate_versioning.xml]
2022-09-17 09:56:11 [data: dbscripts/xml/upgrade/3.2.0_preupdate_last_activity.xml]
2022-09-17 09:56:11 [schema: lib/pkp/xml/schema/common.xml]
ERROR: Upgrade failed: DB: SQLSTATE[HY000]: General error: 1709 Index column size too large. The maximum column size is 767 bytes. (SQL: ALTER TABLE email_templates_settings ADD  UNIQUE INDEX email_settings_pkey  (email_id, locale, setting_name))

I’ve noticed that email_templates_settings and a lot of other tables in the database where still MyISAM. In an attempt to solve the problem, I’ve converted all tables to InnoDB and made sure that innodb_file_format is Barracude and innodb_large_prefix is ON. That didn’t help.

Best regards

[Nils]

OK, the new database I’ve created for the test run had utf8mb4 as default collation. This table is created during the upgrade and since no collation is specified it will get the default one.

Creating an index from email_id, locale and setting_name:
bigint(20) + varchar(14) + varchar(255) will most definitely not work with mb4 strings.

I do wonder if setting_name really has to have a fixed length of 255. Even with normal width utf8 this will be a really huge index.