AppSuite:7 10 Database Migration
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.
The most significant changes are:
- New version and configuration requirements.
- Most VARCHAR columns need to 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.
MySQL Server is supported in versions 5.6 and 5.7 with recent patch levels only and MariaDB Server 10.0 and 10.2 respectively. Support for 5.6/10.0 exists for compatibility reasons and is transitional. We recommend upgrading to 5.7 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.
When running MySQL in version 5.7, the required configuration differs between OX App Suite v7.10.0 and former versions. As long as older App Suite versions are serving user traffic, the following configuration options must be applied:
sql_modesetting if it is contained there . By default this is the case for MySQL 5.7. but not MariaDB in any version. If
sql_modeis not set in
my.cnfso far, obtain the default via
SHOW GLOBAL VARIABLES WHERE Variable_name = 'sql_mode';, remove
ONLY_FULL_GROUP_BYfrom it and set the final result as value in
- Ensure that
character_set_serveris set to
utf8. Again the current global default value can be obtained via
SHOW GLOBAL VARIABLES WHERE Variable_name = 'character_set_server';.
After the upgrade to OX App Suite v7.10.0, these settings need to be adjusted again to fulfil the new requirements:
sql_modesetting or remove the setting as a whole again to fall back to the internal default.
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! 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 needed and 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 http://oxpedia.org/wiki/index.php?title=AppSuite:Running_a_cluster#Updating_a_Cluster. For the application server upgrade, the common guide from http://oxpedia.org/wiki/index.php?title=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 http://oxpedia.org/wiki/index.php?title=AppSuite:Running_a_cluster#Rolling_Upgrade_with_breaking_Hazelcast_upgrade)
- Exclude the update tasks for both mentioned migrations. See “Update Task Exclusion” for details.
- Execute update tasks according to your preferred strategy. More on this can be found at http://oxpedia.org/wiki/index.php?title=UpdateTasks.
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
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
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.propertiesagain, 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!