Appian recommends using history tables + triggers for storing historical data. Why not use MariaDB's OOTB feature? https://mariadb.com/kb/en/system-versioned-tables/
This approach requires very little configuration for each table.
ALTER TABLE t ADD SYSTEM VERSIONING;
I tested this out in our Appian Cloud instance and the only downside I've found so far is that ALTER operations on versioned tables may need to be run manually in combination with
SET @@system_versioning_alter_history = 1;
The solution may be to change the global config (set global system_versioning_alter_history = 1), but it requires SUPER privileges.
So, what's the catch? This approach seems to be the simplest solution for storing historical data.
Discussion posts and replies are publicly visible
Well the most obvious downside to me is that the historical data is not easily accessible via Appian.
Here is the other (rather big) limitation IMO. Appian Cloud only supports MariaDB 10.6 as of Appian 24.1 AFAIK.
Mathieu Drouin That's right. You would need to create a view to make the versioned data available to Appian.
Re: the dump, do you know whether Appian Cloud uses that capability for their backups? If it's done at the server level, I don't believe this limitation has any impact.
miranda.barr Thoughts?
Wen Huynh I would assume that it is not currently possible since support for dumping the history was only added in 10.11
I met with Appian support who confirmed that this approach is feasible. There are other customers using this approach as well.
Very interesting. But how does it work exactly? Are they using an on-prem database?
Will you have one record for consulting the data, and one sp for the historical one?
We use both on-prem and cloud. As long as it's MariaDB, you can use this feature. See my original post for documentation link. The code and examples are in that link. If on Cloud, you'll need to request support to update the global setting.
It is the same table but only accessible using a special clause. See the documentation link in my original post for details and examples.
SELECT * FROM t FOR SYSTEM_TIME ALL;
You can configure the historical data to be stored on a different partition if you prefer.
But how do they deal with the backups issue in the cloud? Doc says that versioning backup is only available in 10.11. AFAIK, Appian Cloud only supports 10.6.
According to our Appian support contact, they do not back up the database separately. They back up at the server level.