mahara-contributors team mailing list archive

[Kristina Hoeppner <2002041@xxxxxxxxxxxxxxxxxx>]

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

** Changed in: mahara
       Status: New => Incomplete

-- 
You received this bug notification because you are a member of Mahara
Contributors, which is subscribed to Mahara.
Matching subscriptions: mahara-contributors
https://bugs.launchpad.net/bugs/2002041

Title:
  Upgrade from 22.04.2 to 22.10 failing with column character encoding
  mismatch

Status in Mahara:
  Incomplete

Bug description:
  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.

To manage notifications about this bug go to:
https://bugs.launchpad.net/mahara/+bug/2002041/+subscriptions