Appian Cloud Database MariaDB Upgrade Guide

Introduction

This page provides information about Appian's move from MySQL to MariaDB as the Cloud-provided database. It explains the process Appian will go through to upgrade your database from MySQL to MariaDB and outlines any actions you might have before the upgrade.

Summary of the MariaDB upgrade process

With the Appian 20.3 release, MariaDB will be the default database for the data source provided out-of-the-box with Appian Cloud sites. All new Appian Cloud sites starting with Appian 20.3 will be provisioned with MariaDB as the data source. Existing Appian Cloud sites will be upgraded to MariaDB via a phased approached described in the upgrade timeline section.

Appian's move from MySQL to MariaDB as the Cloud database will be completely transparent to most existing Appian Cloud customers. Most customer applications and database objects (stored procedures, views, and triggers) will continue to work in MariaDB without any changes required. Customers will continue to use phpMyAdmin as the interface to access the database.

A small number of Appian Cloud customers that have minor compatibility issues captured in the logs will need to take actions to resolve the issues. This information is outlined in Actions for customers before MariaDB upgrade section. Taking these actions is not required in order for a site to be upgraded to 20.3, but will be required to switch from MySQL to MariaDB. Customer sites can upgrade to Appian 20.3 without switching to MariaDB.

The switch to MariaDB does not have any impact on the databases supported by Appian. Appian's move from MySQL to MariaDB as the Cloud database also has no impact for self-managed customers. This information is only applicable for Cloud customers.

Upgrade timeline

Appian Cloud sites for existing customers will be converted to MariaDB in a phased approach as described below:

  • During the upgrade to Appian 20.3 (Group 1): Appian Support will notify these customers through a support case. This group will include a subset of customers where Appian has confirmed that their applications have no known incompatibilities.
  • At the customer’s request (Group 2): Anytime after their site has been upgraded to Appian 20.3, a customer can request the upgrade to MariaDB through a separate support case. This will require a standard maintenance window.
  • During the upgrade to Appian 20.4 (Group 3): All remaining customer sites will be converted to MariaDB during their Appian 20.4 upgrade.

Upgrade to MariaDB for existing customers

MariaDB compatibility with MySQL

MariaDB was created by one of the founders of MySQL. In 2010, the open-source MySQL project was forked to create MariaDB. Thus, MariaDB is an evolution of MySQL and is similar to MySQL in most aspects. In fact, MariaDB guarantees an "in-place" upgrade to it from MySQL. There is no data migration required during the upgrade. MySQL and MariaDB use the same InnoDB engine. Thus, the same data files are accessed by both databases. Barring some incompatible syntax mentioned in MariaDB differences with MySQL, all the database scripts written for MySQL in the Appian environment will work with MariaDB.

For Appian environments, the following will remain the same with MariaDB:

  • phpMyAdmin will continue to be the user interface for Appian Cloud database access.
  • Customers will continue to see and use the Appian schema for the business data source in Appian Cloud.
  • The same technology will be used to provide High Availability of the database (applicable for Premier and Premier Plus Customers).
  • Stored procedures, views, triggers, and other database objects will continue to work with MariaDB.

MariaDB differences with MySQL

There are some incompatibilities between MySQL and MariaDB. The entire list can be seen in MariaDB documents. We have identified the following compatibility issues that are applicable to the Cloud database provided by Appian:

  1. Usage of JSON in the database
    • JSON columns
      • MariaDB supports JSON column type as an alias. Inside the table, the data is actually stored as LONGTEXT. This should not cause any issues in the applications, since MariaDB supports the same JSON functions as MySQL on a column defined as LONGTEXT.
    • JSON operators
      • MariaDB does not support the JSON operators like -> and ->>.
  2. Reserved keywords
    • OVER, ROWS, and RECURSIVE are reserved keywords in MariaDB. They are not reserved in MySQL 5.7. Any usage of these reserved keywords without backticks is not allowed in MariaDB.
  3. Incompatible functions
    • There are certain functions that are supported in MySQL 5.7 but not in MariaDB. The entire list is available in MariaDB documents.

Appian has collected metrics to determine which customers have any of the above incompatibilities in their Appian environment and has made this information available to customers in their log files, described in the following sections.

Actions for customers before MariaDB upgrade

Customers can prepare for their MariaDB upgrade by taking the following actions:

  1. Review the Database Incompatibility Check Log.
  2. Review the Query Database Smart Service Incompatibility Check Log.
  3. Premier and Premier Plus customers leveraging the Enhanced Data Pipeline feature should check for any potential incompatibilities with MariaDB. Appian does not have the ability to check for incompatible database scripts in those systems. Thus, we recommend customers evaluate any compatibility issues that might exist in the external systems.
  4. Customers using custom plug-ins that rely on specific database functionality or contain code that specifically looks for MySQL database in Appian Cloud should evaluate the plug-ins before upgrading to MariaDB. Customers can check these plug-ins in advance or can sequence their site upgrades to allow for testing of the plug-ins before upgrading their production environments.
  5. Customers using the Flyway functionality from the Deployment Automation Manager plugin should update to the latest version of the plugin prior to upgrading to MariaDB.

Database Incompatibility Check Log and Query Database Smart Service Incompatibility Check Log are the two files that have been added in <APPIAN_HOME>/logs directory to inform customers about any incompatible objects that exist in their environment. These log files are generated in all the supported Appian Cloud versions. For most customers, these logs will not contain any actionable data, since most customers do not have any incompatible objects. Customers that have these logs populated with information about incompatible objects are advised to make the changes to the objects in preparation for the upgrade to MariaDB with Appian 20.4. Making these changes well in advance of the upgrade to MariaDB will have no impact on MySQL.

Customers that have unresolved incompatibilities recorded in these logs will be contacted by Appian Support prior to being upgraded to MariaDB.

Database Incompatibility Check Log

mariadb-incompatibility-report.log in <APPIAN_HOME>/logs directory contains information about MariaDB incompatible objects in database scripts like views, stored procedures, and functions. The log file records incompatible objects on every restart of Appian Cloud site and every 7 days after that.

If there are no compatibility issues in the environment, the content of the log looks like below and you can ignore the rest of the information in this section of the upgrade guide.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
------------------------------------
-- MariaDB Incompatibility Report --
------------------------------------

-- Incompatible Functions --

-- JSON Incompatibilities --

-- Reserved Keywords Incompatibilities --

-- Partitioned Table Incompatibilities --
No partitioned tables found.

-- Inaccessible Tables --

If there are compatibility issues in the environment, the content of the log looks similar to the following:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
------------------------------------
-- MariaDB Incompatibility Report --
------------------------------------

-- Incompatible Functions --
Function RANDOM_BYTES is used in procedure c
Function RELEASE_ALL_LOCKS is used in procedure c
Function RANDOM_BYTES is used in procedure rand_bytes

-- JSON Incompatibilities --
JSON column j1 found in table b
JSON column j2 found in table b
JSON column j found in table c
JSON Operator -> is used in procedure b
JSON Operator -> is used in procedure c
JSON Operator ->> is used in procedure c

-- Reserved Keywords Incompatibilities --
Reserved Keyword ROWS is used as a parameter name in routine c
Reserved Keyword OVER is used in procedure c
Reserved Keyword RECURSIVE is used in procedure c

-- Partitioned Table Incompatibilities --
Partitioned table using the HASH partitioning method is used in Appian.partition_hash
Partitioned table using the LIST partitioning method is used in Appian.partition_list
Partitioned table using the RANGE partitioning method is used in Appian.partition_range
Partitioned table using the RANGE partitioning method is used in Appian.partition_range_sub_hash

-- Inaccessible Tables --
The following table exists but is currently inaccessible in your MySQL instance: 'Appian.a'
The following table exists but is currently inaccessible in your MySQL instance: 'Appian.b'
The following table exists but is currently inaccessible in your MySQL instance: 'Appian.c'
The following table exists but is currently inaccessible in your MySQL instance: 'Appian.d'

Customer action required

The following incompatibilities mentioned in the log require customer action to remediate before moving to MariaDB:

  1. Incompatible Functions: If you have lines in the log that indicate you are using functions that are not supported in MariaDB, you need to evaluate the usage of the functions and replace them with equivalent code. With the metric collection process, we found that no Appian customer uses MariaDB incompatible functions in their database scripts in their production environment.
  2. JSON Incompatibilities - JSON Operators: If you have lines in the log alluding to the usage of JSON operators, you need to update those database objects to use JSON functions instead of JSON operators. To resolve the incompatibility, replace all occurrences of -> with JSON_EXTRACT(...) and ->> with JSON_UNQUOTE(JSON_EXTRACT(...)).
  3. Reserved Keyword Incompatibilities: If you have lines in the log alluding to the usage of keywords that are reserved in MariaDB, you need to evaluate those database objects. To resolve the incompatibility, use backticks with the keywords if you need to use the keywords in your database scripts. Alternatively, don't use these keywords - replace them with another name.
No customer action required

The following incompatibilities mentioned in the log are taken care of automatically by Appian during the upgrade to MariaDB. These incompatibilities are captured in the log for your informational purposes only.

  1. JSON Incompatibilities - JSON Columns: If you have JSON columns in your tables, they will be automatically migrated to LONGTEXT type in MariaDB during the upgrade to MariaDB. A JSON constraint check will be added on the column, and the behavior of this column will be like JSON columns in MySQL. Your data store entities will continue to work without any changes.
  2. Partitioned Table Incompatibilities: Tables that are partitioned in MySQL need to be unpartitioned and repartitioned in MariaDB. Appian does this automatically during the upgrade to MariaDB.
  3. Inaccessible Tables: This section lists some tables in your database that are in an inaccessible state and are not queryable in the database. These tables are likely not used by your applications since they are inaccessible and they likely do not contain any data. If you have inaccessible tables in your database that seem important to you, please open a support case with Appian to investigate these further.

Query Database Smart Service Incompatibility Check Log

query_node_mariadb_incompatibility.log in <APPIAN_HOME>/logs directory contains the information about MariaDB incompatible objects in the SQL statements contained in the Query Database Smart Service in your Process Models. If you have no data in this log file, you do not have any compatibility issues in the smart service and you can ignore the rest of this section in the upgrade guide. The system checks the smart service and generates data in the log file every 7 days.

Depending on your Appian hotfix version, you might have the older version or the newer version of this log in your environment. The older version of this log only printed the problematic SQL statement contained in your smart service, along with the corresponding incompatibility. It did not contain the process model name the smart service belonged to and it did not exclude a smart service that was not actually connected to the Appian Cloud database. It also did not remove the old entries after checking the data every 7 days.

We have since introduced a new version of this log. The new version captures the incompatibility only if your smart service is configured to connect to the Appian Cloud database. It also tells you the process model name for the problematic smart service. The log rewrites the entire file every 7 days. The new version of the log will be available once you upgrade to Appian 20.3 or if you are on one of the following or later hotfixes of Appian Cloud: '20.2.91.0', '20.1.153.0', '19.4.202.0', '19.3.255.0', '19.2.317.0'

If you do not have any incompatibilities in the Query Database Smart Service, the log will contain no data, irrespective of whether you have the new version or the old version of the log. If you have incompatibilities, the new version of the log looks similar to the following:

1
2
3
4
5
2020-08-19 22:42:40,536 [Appian Timer - 2] INFO  Process Model PM_AppianDS: Found usage of OVER in Query Database Node SQL - select 'bad' as over
2020-08-19 22:42:40,537 [Appian Timer - 2] INFO  Process Model PM_AppianDS: Found usage of RANDOM_BYTES in Query Database Node SQL - select TO_BASE64(RaNdOm_ByTeS(2)) as name
2020-08-19 22:42:40,537 [Appian Timer - 2] INFO  Process Model PM_AppianDS: Found usage of -> in Query Database Node SQL - SELECT j->'$.name' as name FROM `t`
2020-08-19 22:42:40,537 [Appian Timer - 2] INFO  Process Model PM_Another_AppianDS: Found usage of RECURSIVE in Query Database Node SQL - select 'bad' as recursive

Any information in the above log will require customer action for resolution before MariaDB upgrade. The same steps as mentioned in Customer Action Required section are applicable to remediate the compatibility issues.

Conversion process for Appian customers

The conversion from MySQL to MariaDB is controlled by a site property set by Appian Support. The site property triggers the conversion process during a site restart. This conversion process can be coupled with an Appian version upgrade.

During the conversion process, the environment will be checked for any incompatibilities.

  • If any incompatibility is detected, the conversion process will abort and the database will remain on MySQL. If this conversion was included as part of an Appian upgrade, the Appian version upgrade will proceed as normal.
  • If no incompatibilities are detected, the conversion process will proceed. Once complete, the database will again be validated for any schema anomalies. In the event that there are schema anomalies detected after the conversion process completes, the site will be reverted back to its state at the beginning of the maintenance window (including Appian version level) and a support case will be opened to investigate the anomalies.

As noted in the summary section, some customer sites will be scheduled for this conversion as part of their Appian 20.3 upgrade. This will exclude any customer sites where incompatibilities have already been detected and recorded to the log files for the customer to take action on.

Customers sites that aren’t converted to MariaDB as part of the Appian 20.3 upgrade can either request to complete this conversion through a support case (following their 20.3 upgrade) or they will be converted during their Appian 20.4 upgrade.

Customers that have a preference for how their conversion is handled (with Appian 20.3 upgrade, after Appian 20.3 upgrade, or with Appian 20.4 upgrade) can register their preference by creating a support case or emailing support@appian.com.

MariaDB upgrade without resolving compatibility issues

It is important to note that the pure database upgrade from MySQL to MariaDB is not automatically blocked due to the incompatible objects. The incompatible objects, in reality, only affect MariaDB at runtime, when those particular objects are being used by your site. Appian has purposely introduced checks to ensure that the site does not upgrade to MariaDB in case of compatibility issues, so that your production environments are not affected at runtime.

If your compatibility issues are in a non-production environment and you do not intend to resolve those issues (or if there are false positives), you can open a support case with Appian and inform us to ignore the issues for your specific sites. Appian will be able to do so and convert your database to MariaDB regardless of the existence of incompatible objects. You can still resolve the compatibility issues after the upgrade.

MariaDB security compliance

MariaDB is subject to the same standard of security that MySQL enjoys today, and many of the same controls are ported from MySQL to MariaDB. This includes baseline hardening against industry-standard benchmarks from the Center for Internet Security, controlled authentication processes and mechanisms, and full audit logging.

For regulated environments (e.g. FedRAMP, PCI, etc.), Appian Cloud sites will be converted as they complete steps specific to those frameworks.

Additional concerns

If you have any additional questions or concerns about the upgrade to MariaDB, please open a support case with Appian.

Open in Github

On This Page

FEEDBACK