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.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 http://oxpedia.org/wiki/index.php?title=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_AUTO_CREATE_USER. Starting with App Suite 7.10.0,
ONLY_FULL_GROUP_BYis 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_serveris set to
utf8for 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
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.
- 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.
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.