AppSuite:7 10 Database Migration

This information is valid from 7.10.0

Database Migration with OX App Suite v7.10.0

OX App Suite v7.10.0 introduces significant changes regarding the underlying MySQL database system that require special attention in case of upgrades from former versions. Please read this paper carefully to ensure a smooth and clean upgrade process.

Change Overview

The most significant changes are:

• New version and configuration requirements.
• Most VARCHAR columns need to be migrated to utf8mb4 character encoding.
• A major rewrite of the calendar application requires full data migration.

We strongly recommend to thoroughly plan and test the upgrade procedure. To gain insights about update task runtimes and the expected load, our recommendation is to clone ConfigDB and the biggest UserDB and perform an isolated test upgrade that especially covers the calendar migration and character encoding changes. Update task durations can be significantly longer than with previous upgrades and the migrations might cause noticeable higher I/O load. Also some additional disk space is needed during and after the migrations.

Database System

MySQL Server is supported in versions 5.6 and 5.7 with recent patch levels only and MariaDB Server 10.1 and 10.2 respectively. Support for 5.6/10.1 exists for compatibility reasons and is transitional. We recommend upgrading to 5.7/10.2 as soon as possible. Any database system upgrade must happen before App Suite is upgraded to OX App Suite v7.10.0. Please follow the respective guides of your database vendor carefully.

Different App Suite and database system versions require different configurations of the DBMS, please follow My.cnf to have your database configured in a sane way. Especially the following items require some attention with the upgrade to App Suite 7.10.0 and upgrades of the DBMS itself:

• Supported SQL modes are only NO_ENGINE_SUBSTITUTION and NO_AUTO_CREATE_USER. Starting with App Suite 7.10.0, ONLY_FULL_GROUP_BY is also supported for MySQL 5.7. It is still not with older versions of MySQL or any MariaDB version! To review the current value, use SHOW GLOBAL VARIABLES WHERE Variable_name = 'sql_mode';.
• Ensure that character_set_server is set to utf8 for App Suite 7.8.x. Again the current global default value can be obtained via SHOW GLOBAL VARIABLES WHERE Variable_name = 'character_set_server';. With 7.10.0 being fully rolled out, this setting must then be changed to utf8mb4. If collation_server is configured explicitly, it must be set to a matching value according to the character set in either case!

Note that App Suite <= 7.8.4 did not support MySQL 5.7/MariaDB 10.2 so far. While we recommend it for 7.10.0, this leaves a lack of definition during the upgrade process. We consider running 7.8.4 on top of MySQL 5.7/MariaDB 10.2 a valid scenario as long as it is transitional during the upgrade phase. Please take our configuration recommendations seriously to mitigate potential user-facing issues as far as possible.

Upgrade Procedure

The upgrade to OX App Suite v7.10.0 can be performed like any other major upgrade before. However, the duration of blocking database update tasks for the mentioned charset and calendar migrations could conflict with customers’ availability demands. Therefore it is possible to decouple these special time- and resource-intensive tasks from the plain version upgrade. In this section a multi-step approach is described that performs the version upgrade before and independently from the migrations. Every step always results in a working system that is ready to serve user traffic. Some functional implications that affect user experience are outlined in the according subsections.

Important: Even though the first step leads to a basically working OX App Suite v7.10.0 environment, the subsequent steps are not optional but mandatory! Skipping the migrations will leave a few calendar features dysfunctional and can lead to issues with certain SQL queries that use explicit collations for searching and sorting. Running OX App Suite v7.10.0 in production without having all parts of the migration fulfilled is not supported – OX support will request you to complete the migration tasks when reporting issues that are not related to the migration itself.

Initial OX App Suite v7.10.0 Rollout

If not done so far, upgrade your MySQL installation to a version supported with OX App Suite v7.10.0 but configure it to be compatible with OX App Suite v7.8.x as described in the “Database System” section.

Despite the fact that the two special migrations for calendar and character sets are explicitly skipped, this section assumes that the “Rolling Upgrade with breaking Hazelcast upgrade” is applied as described in AppSuite:Running_a_cluster#Updating_a_Cluster. For the application server upgrade, the common guide from AppSuite:UpdatingOXPackages can be followed.

Prepare a dedicated App Suite middleware node that will be used to perform the database update tasks. The node must not be serving any user traffic and be prepared with

• OX App Suite v7.10.0 packages
• Configuration according to the user production nodes
• The Hazelcast rolling upgrade compatibility package (see AppSuite:Running_a_cluster#Rolling_Upgrade_with_breaking_Hazelcast_upgrade)
• Exclude the update tasks for both mentioned migrations (i.e. calendar and character encoding). See “Update Task Exclusion” for details.
• Execute update tasks according to your preferred strategy. More on this can be found at UpdateTasks.

By default (if using the "runallupdate" tool") update tasks operate on database schemas sequentially, one at a time. All users from all contexts of a given schema are logged out and locked out. Then, DB schema changes are executed. Finally, users are unlocked and able to login again. Schemas typically contain a few thousand users (if our recommended sizing is being followed) and thus executing update tasks means bunches of a few thousand users will be affected sequentially. You will not have a full downtime. For each update task execution process the following statements hold true:

• All users got service for nearly all the time (all the time but the time where their schema is upgraded)
• For each point in time, nearly all users got service (all but the ones from the currently updated schema)
• When update tasks are completed, all users will have been affected by one "logout" - "locked out" cycle

After complete and successful update task execution, roll out OX App Suite v7.10.0 to one node after another. After complete rollout, reconfigure MySQL if appropriate as described in the “Database System” section.

Update Task Exclusion

Add the following lines to /opt/open-xchange/etc/excludedupdatetasks.properties or remove the leading # character if already included, so that it contains these two lines:

# Character Encoding Migration
com.openexchange.groupware.update.excludedUpdateTasks=groupware.utf8mb4

# Calendar Migration
com.openexchange.chronos.storage.rdb.migration.ChronosStorageMigrationTask

#Update tasks depending on the 2 above
com.openexchange.chronos.storage.rdb.groupware.CalendarEventCorrectFilenamesTask
com.openexchange.chronos.storage.rdb.groupware.CalendarEventCorrectRangesTask
com.openexchange.chronos.storage.rdb.groupware.CalendarEventCorrectOrganizerSentByTask
com.openexchange.chronos.storage.rdb.migration.ChronosStoragePurgeLegacyDataTask
com.openexchange.pns.subscription.storage.groupware.PnsSubscriptionsUseUtf8mb4ForClientColumnTask
com.openexchange.groupware.update.tasks.AddChecksumColumnToAttachmentsTablesUpdateTask

The first line does not denote one dedicated update task, but a whole list of tasks. To make exclusion more convenient, the concept of update task namespaces has been introduced. All update tasks belonging to the character encoding migration are part of the groupware.utf8mb4 namespace. The denoted property takes care of excluding them all at once. You can list all according tasks with the /opt/open-xchange/sbin/listUpdateTaskNamespaces tool.

Character Encoding Migration

The default character encoding for Unicode (named character set by MySQL) of MySQL will become utf8mb4 in the near future. MariaDB on Debian Stretch (9) already has an according default configuration set when installing it from distribution packages. So far all VARCHAR columns are supposed to store at max. 3-byte UTF-8 characters due to the nature of MySQL’s utf8 character encoding. This leads to the fact that for example emojis cannot be saved as part of any App Suite entities. In a mixed-mode scenario (App Suite considers MySQL to operate in utf8mb4 mode due to the character_set_server setting, while columns are specified with utf8 encoding), this leads to issues whenever certain collations during SELECT statements are enforced. To avoid such issues generally and also increase user experience by finally allowing characters from the Unicode astral plane, Open-Xchange has decided to migrate existing data structures to the utf8mb4 character encoding.

This migration can be executed before or after the calendar migration, while it is recommend to execute it before.

The upgrade procedure is basically the same as above in terms executing update tasks, while the server software is already up to date and needs no further upgrades:

• Again prepare one dedicated node that doesn’t serve any user traffic
• Remove the according namespace property from excludedupdatetasks.properties again, but still keep the “ChronosStorageMigrationTask”. Afterwards restart the open-xchange daemon.
• Execute update tasks according to your preferred strategy.

Important: The update tasks require a lot of tables to be copied and re-created, leading to high I/O and especially sequential read and write operations. For every copied table the needed MySQL disk space doubles during the update task, so ensure enough free space before running the migration.

Calendar Data Migration

The new calendar stack in OX App Suite v7.10.0 comes along with a new data model using its very own tables in MySQL. To preserve users calendar data, an update task com.openexchange.chronos.storage.rdb.migration.ChronosStorageMigrationTask has been introduced, that reads all data from the old tables, applies transformations to match the new stack and writes it into the new tables. The approach and upgrade process is described in detail at https://documentation.open-xchange.com/7.10.0/middleware/components/calendar/data_migration.html. Please read that article carefully before continuing. Especially we want to emphasize again the recommendation to test the migration with a copy of your real data to exclude or determine any issues beforehand.

Before executing this update task, OX App Suite v7.10.0 uses the new calendar stack on top of the old database tables through a compatibility layer. As the old storage layout lacks certain functionality, not all features are functional in between the application upgrade and execution of the update task. Due to this fact, a few spots are affected where not all appointment data that the user interface allows to enter can be persisted. This includes:

• Reminders, where still only one notification prior the appointment start is possible
• Colors, that cannot be mapped to the previously used labels
• 4-byte UTF-8 characters (emojis) are not yet possible
• Secret appointments, that are still stored as private ones
• An appointment's end timezone can't be applied, if it's different from the start timezone

Operators of non-Galera MySQL setups - i.e. Master-Slave replication - can potentially speed up the migration by configuring com.openexchange.calendar.migration.intermediateCommits = false. Per default the migration is performed in batches that separately committed, as Galera does not cope well with large transactions. By changing the setting to false, a single transaction with batch-mode enabled is used.

Important: The migrating of calendar data actually leads to a duplication of that data. Also with OX App Suite v7.10.0 every new calendar data is written to both, the old and the new tables redundantly to preserve to ability to roll back to OX App Suite v7.8.4. However, only the parts can preserved that match the old data model. I.e. additional features like multiple reminders or subscriptions of external calendars (Google Calendar, SchedJoules) cannot be preserved during a rollback.

A repeated OX App Suite v7.10.0 upgrade after a former rollback to OX App Suite v7.8.4 requires to force re-execution of this update task!