This page applies to Appian Cloud only. It may not reflect the differences with Appian Government Cloud.
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. 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.
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 ->>.
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.
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. Making these changes well in advance of the upgrade to MariaDB will have no impact on MySQL.
->
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 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.
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.
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.
If you have any additional questions or concerns about the upgrade to MariaDB, please open a support case with Appian.