Appian Cloud Database MariaDB Upgrade Guide

This page applies to Appian Cloud only. It may not reflect the differences with Appian Government Cloud.

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.

Context for the upgrade to MariaDB

  1. Appian Cloud currently deploys MySQL 5.7 as the database for customer sites. MySQL 8 is the newest version of MySQL. Our analysis showed that customer databases will need more changes to upgrade to MySQL 8 than to MariaDB, which was forked from MySQL 5.5. Thus, upgrading to MariaDB is more convenient for customers than upgrading to MySQL 8.
  2. MySQL 5.7 is not the latest major version of MySQL and will eventually be unsupported. Moving to the latest version of MariaDB will allow the Appian Cloud database to receive the latest features and timely security updates for the foreseeable future.
  3. With MariaDB, Appian Cloud customers will get important features like database encryption with AWS Key Management Service integration. MariaDB also provides other benefits like better binary log compression to save disk space, frequent security updates and better open source community support.
  4. Appian plans to implement some heavily requested Cloud database features with MariaDB as the underlying database. These features will be shipped in the short-term future and not be available with MySQL 5.7 on Appian Cloud.

Summary of the MariaDB upgrade process

MariaDB is now the default database for the data source provided out-of-the-box with Appian Cloud sites. All new Appian Cloud sites will be provisioned with MariaDB as the data source. Existing Appian Cloud sites will be upgraded to MariaDB via a phased approach described in the upgrade timeline section. MariaDB is only available for Appian Cloud sites on version 20.3 or later.

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 compatibility issues captured in the logs will need to take actions to resolve the issues. Related information and other steps for MariaDB upgrade are outlined in steps for MariaDB upgrade section. Taking these actions is not required in order for a site to be upgraded to any new version of Appian, but will be required to switch from MySQL to MariaDB. Customer sites can upgrade to newer Appian versions without switching to MariaDB. However, all the features related to the Appian Cloud database will be shipped with MariaDB in the future versions of Appian. Thus, all the Appian Cloud sites will be upgraded to MariaDB in due course and any database compatibility issues must be resolved.

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

  • If none of your Appian Cloud environments have been converted to MariaDB, a support case will be created to coordinate the scheduling of your conversion to MariaDB. The deadline to complete the conversion to MariaDB is September 30, 2021. However, it is recommended to switch to MariaDB soon in order to take advantage of upcoming features with the Appian Cloud database.
  • If any of your Appian Cloud environments have already been converted to MariaDB, your remaining sites will continue to be converted as planned.

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. 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 Advanced or Enterprise Support 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.
  4. Function return types
    • Certain stored (built-in) functions return a different type in MySQL and MariaDB, resulting in a changed column type in views that may use these functions.
  5. Stricter syntax
    • MariaDB is stricter with syntax in database objects like user-defined stored procedures and user-defined function. The same objects that compile in MySQL might not compile in MariaDB due syntactic errors. Minor changes might be required to correct the syntax.

Steps for MariaDB upgrade

It is recommended that customers take the following steps for the MariaDB upgrade. A downloadable version of this checklist is available on Appian Community:

Review compatibility logs

1. Review the Database Incompatibility Check Log.

2. Review the Query Database Smart Service Incompatibility Check Log.

Review plugins

3. The Execute Stored Procedure Plug-in includes a function that allows you to execute stored procedures for your applications. This executestoredprocedure function does not support database writes or modifications. If you use this function to modify data in your database, you may see data modification errors upon upgrading to MariaDB. Refer to Execute Stored Procedure Function section below for more information and details about what to do before upgrading. Additionally, customers should upgrade to the latest version of this plug-in before the upgrade to MariaDB.

4. 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.

5. 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.

Other checks

6. Advanced or Enterprise Support customers leveraging the Enhanced Data Pipeline feature should check for any potential incompatibilities with MariaDB in SQL statements run by the database clients external to Appian. 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.

7. Ensure that all the database tables have a primary key or an index defined. This is especially important for tables that store a large amount of data. MariaDB contains enhancements and changes to the way queries are optimized. Queries on tables without an index might perform sufficiently well with MySQL but may experience timeouts with MariaDB. Thus, you should ensure that tables have the appropriate keys defined. The following query can be run in phpMyAdmin to find tables that are missing a primary or unique key:

1
2
3
4
5
6
7
8
9
10
11
12
13
select t.table_schema as database_name,
       t.table_name
from information_schema.tables t
left join information_schema.table_constraints cons
          on t.table_schema = cons.table_schema
          and t.table_name = cons.table_name
          and (cons.constraint_type = 'PRIMARY KEY' OR cons.constraint_type = 'UNIQUE')
where cons.constraint_type is null
      and t.table_schema not in('mysql', 'information_schema',
                                  'performance_schema', 'sys','AppianPrimary','AppianAnywherePrimary')
      and t.table_type = 'BASE TABLE'
order by t.table_schema,
         t.table_name;

8. Views that use TEMPTABLE as the algorithm might not perform optimally on MariaDB. The alternative is to use UNDEFINED or MERGE as the algorithm. You can find views that use TEMPTABLE algorithm by running the follow query in phpMyAdmin:

1
2
3
select table_name, algorithm FROM information_schema.views
where table_schema not in('mysql', 'information_schema', 'performance_schema', 'sys','AppianPrimary','AppianAnywherePrimary')
and algorithm = 'TEMPTABLE';

Upgrade and test on non-production environments

9. Update your Appian Support case to upgrade your non-production environments to MariaDB. After the upgrade, you can confirm that you are now running MariaDB by executing SHOW VARIABLES LIKE '%version% in phpMyAdmin.

10. Review the column type conversion log once the environment is successfully upgraded.

11. It is strongly recommended that you perform testing on non-production environments with the same amount of data present in your production environments. Doing so can help identify any problems related to timeout and other issues early. Ensure that your stored procedures run without issues and your most common and complex queries are performant. If you still encounter timeout or other issues after the upgrade to MariaDB, please open a case with Appian Support to further debug the issue.

Upgrade production environment(s)

12. Once all the checks pass on the non-production environments, you can update your Appian Support case to upgrade your production environments to MariaDB. After the upgrade, you can confirm that you are now running MariaDB by executing SHOW VARIABLES LIKE '%version% in phpMyAdmin.

Database Incompatibility Check Log

The 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. Making these changes well in advance of the upgrade to MariaDB will have no impact on MySQL.

  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.
  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. The reserved keyword needs to be replaced with a different 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

The query_node_mariadb_incompatibility.log in <APPIAN_HOME>/logs directory contains information about MariaDB incompatible objects in the SQL statements contained in the Query Database Smart Service in your Process Models, if the queries are against the Appian Cloud database. The system checks the smart service and rewrites data in the log file every 7 days.

If you have no data in this log file, you do not have any compatibility issues in the smart service. If you have compatibility issues, 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.

MariaDB Column Type Conversion Log

The mariadb-column-type-conversions.log in the <APPIAN_HOME>/logs directory captures the names of the database columns whose types changed after your database upgrade from MySQL to MariaDB. This log is only available for review after the upgrade to MariaDB and exists for informational purposes. All of the columns listed in the log file are expected to be in your SQL views. The data in these columns is typically produced by a SQL function that returns different types in MySQL and MariaDB. For example, a bigint column type in a MySQL view might change to int in MariaDB.

Note that if any column types have changed in your database after upgrading to MariaDB, the next time that you publish your data store the verification process may fail. The column type changes are not expected to cause any issue while querying the views at run-time, even if some types are different. Thus, it is not expected to affect your production environments. The failure will only occur during the publishing of the data store. To address this failure and update your data store, you can either cast the database columns to the appropriate type, or update the XSDs. Once your data store is updated and verified in the non-production environments, the updates can be deployed to the production environments after they are upgraded to MariaDB.

Execute Stored Procedure Function

The Execute Stored Procedure Plug-in includes a function that allows you to execute stored procedures for your applications. This executestoredprocedure function does not support database writes or modifications. To prevent modifications, the plug-in relies on the database reporting that an update has occurred. MySQL has several scenarios where it should report a SQL statement as an update to the database, but it does not. MariaDB is stricter in enforcing this. As a result, after upgrading to MariaDB the executestoredprocedure plug-in function may prevent the execution of previously working stored procedures that modify data.

The following table outlines the keywords that may cause this issue:

Action/Statement Prohibited by Plug-in? Blocked by MySQL? Blocked by MariaDB?
Insert Yes No Yes
Update Yes No Yes
Delete Yes No Yes
Truncate Yes No Yes
Create temporary table Yes No Yes
Select…. into When the input target is not an input or output variable No Yes

If you use the above keywords in your stored procedures that are called by the executestoredprocedure function, you will need to refactor your stored procedures for them to work with MariaDB. You can take the following steps to identify the potentially problematic stored procedures:

  1. Identify the stored procedures that are run by the plug-in function by searching for the expression "executestoredprocedure" in your design environment. You can also leverage the execute_stored_procedure_trace.csv file from <APPIAN_HOME>/logs/perflogs to identify the stored procedures executed by the function.
  2. Once you identify the stored procedures, export the database schema with the stored procedures from phpMyAdmin.
  3. Review the stored procedures to identify those that modify data, and make the appropriate changes to them so that they no longer do so.

If you need additional help refactoring your stored procedures, you can open a case with Appian Support.

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.

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.

MariaDB is available for Appian Cloud FedRAMP, HITRUST, and PCI-DSS customers.

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 Built: Thu, Feb 23, 2023 (02:59:22 PM)

On This Page

FEEDBACK