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.
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.
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. 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 a 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.
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:
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:
-> and ->>.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.
Customers can prepare for their MariaDB upgrade by taking the following actions:
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.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;
It is also strongly recommended that you upgrade your lower environments first and perform testing with the same amount of data present in your higher environments. Doing so can help identify any problems related to timeout issues early. If you still encounter timeout issues after the upgrade to MariaDB, please open a case with Appian Support.
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. 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.
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'
The following incompatibilities mentioned in the log require customer action to remediate before moving to MariaDB:
-> with JSON_EXTRACT(...) and ->> with JSON_UNQUOTE(JSON_EXTRACT(...)).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.
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.
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:
execute_stored_procedure_trace.csv file from <APPIAN_HOME>/logs/perflogs to identify the stored procedures executed by the function.If you need additional help refactoring your stored procedures, you can open a case with Appian Support.
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.
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.
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. 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 higher 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 lower environments, the updates can be deployed to the higher environments after they are upgraded to MariaDB.
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.
If you have any additional questions or concerns about the upgrade to MariaDB, please open a support case with Appian.
On This Page
