Upgrade from 22.04.2 to 22.10 failing with column character encoding mismatch

[Jasminl]

Upgrade from Mahara 22.04.2 to 22.10 fails during ensure_upgrade_sanity() because some columns have a different default character encoding. Version 22.04 is currently running in production without issues. This is our production database that has been migrated from 19.04 -> 20.04.0 -> 21.04.1 -> 22.04.2 in the last few years.

We did not manually change character encodings on the database or tables. We followed the normal upgrade procedure from version to version. Did a prior upgrade silently failed (maybe trying to migrate the indexes of some tables), leaving us with a mix and match of utf8/utf8mb4 tables and columns?

Did a mysqldump fo the current database schema, some tables are in utf8:

CREATE TABLE `config` (
  `field` varchar(255) NOT NULL,
  `value` longtext,
  PRIMARY KEY (`field`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

while others are in utf8mb4:

CREATE TABLE `module_submissions` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `groupid` bigint(10) NOT NULL,
  `ownertype` varchar(20) COLLATE utf8mb4_bin NOT NULL,
  `ownerid` bigint(10) NOT NULL,
  `portfolioelementtype` varchar(20) COLLATE utf8mb4_bin NOT NULL,
  `portfolioelementid` bigint(10) NOT NULL,
  `portfolioelementtitle` text COLLATE utf8mb4_bin NOT NULL,
  `submissiondate` datetime DEFAULT NULL,
  `status` tinyint(2) NOT NULL,
  `taskid` bigint(10) DEFAULT NULL,
  `tasktitle` text COLLATE utf8mb4_bin,
  `exportarchiveid` bigint(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `modusubm_exp_uix` (`exportarchiveid`),
  KEY `modusubm_gro_ix` (`groupid`),
  KEY `modusubm_ownown_ix` (`ownertype`,`ownerid`),
  KEY `modusubm_tas_ix` (`taskid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

The database was created years ago (prior to the move to utf8mb4) with this command:
create database mahara character set UTF8;

But now shows:
MariaDB [mahara]> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8mb4 | utf8mb4_bin |
+--------------------------+----------------------+

We also filed another bug (#2002038) for the fresh install of 22.10 in a fully utf8mb4 compliant database.

[Kristina Hoeppner (kris-hoeppner)]

Bug #1912492 contains instructions on how to change your collation on an existing database.

[Jasminl (jasminlevesque)]

I confirm that the instructions in Bug #1912492 unlocked our migration process. It could be helpful for people encountering this issue if this process/howto was part of the official wiki or documentation, maybe here: https://wiki.mahara.org/wiki/System_Administrator%27s_Guide/Upgrading_Mahara#Troubleshooting ?

There could aslo be a note suggesting to check the "innodb_large_prefix" settings.

The issue can be marked as solved.

Thank you Kristina! :)

[Kristina Hoeppner (kris-hoeppner)]

Great to read that your issues were resolved, Jasminl.