Skip to main content
wenh0002
March 22, 2024
Question

MariaDB System-Versioned Tables for historical data

  • March 22, 2024
  • 10 replies
  • 0 views

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. 

    10 replies

    mathieud0001
    March 23, 2024

    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.

    wenh0002
    wenh0002Author
    March 25, 2024

    [mention:f5e81b8502fa4e77ad12697686a4fc56:e9ed411860ed4f2ba0265705b8793d05] 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. 

    [mention:108ad6994e36420e9acc9f4aebf3f032:e9ed411860ed4f2ba0265705b8793d05] Thoughts? 

    mathieud0001
    March 25, 2024

    [mention:40f4159b47044d88922f133822fd33ae:e9ed411860ed4f2ba0265705b8793d05] I would assume that it is not currently possible since support for dumping the history was only added in 10.11