0

I have an existing MariaDB table that I want to keep a history of changes to using the built-in system versioning functionality. I'm using MariaDB 10.5 but I have also tested everything in this question on 10.11 (the version this system will move to in the next 6-12 months).

I also want to store the history separately, for the following reasons:

  • Each row is edited on average 10 times, so the history dataset will eventually be 10x the size of the current dataset.
  • All interactive queries (~95% of all queries) will be run against the current dataset, so I want to keep that as small as possible.
  • The queries that need access to the history dataset run in overnight batch jobs where response times are less of an issue (e.g. taking 1s to return instead of 0.1s is acceptable).

However, if I try to do this on an existing table with the following query:

ALTER TABLE t ADD SYSTEM VERSIONING PARTITION BY SYSTEM_TIME;

this fails with:

SQL Error [1217] [23000]: (conn=5) Cannot delete or update a parent row: a foreign key constraint fails

This happens even if I turn off foreign key checking (SET FOREIGN_KEY_CHECKS=0) before running the query.

The explanation I have found is on the foreign keys page of the MariaDB documentation:

Partitioned tables cannot contain foreign keys, and cannot be referenced by a foreign key.

Does this mean that it is impossible to have system versioning with the history saved separately if a table contains, or is referenced by, a foreign key? That seems to be the result of my investigations but it also seems odd given that most relational databases contain foreign keys and the MariaDB documentation has a whole section on keeping history in a separate partition - presumably because it's a common use case.

Is there any way round this in MariaDB, or do I have to either keep the history with the current data (which will increase the dataset by a factor of 10)? The other option is to create a separate t_history table and have the application keep track of history, but that doesn't capture changes made outside of the application, e.g. if a developer manually runs an UPDATE query.

4
  • 1
    This is a task MDEV-19191. Can you show the performance difference you'll get from a current partition (compare to a current table) compared to the combined system versioned table with your current data and include those results on MDEV-19191? Performance motivations for engineers are pretty good (though there's a bit of a bug backlog to get through first).
    – danblack
    Commented Apr 6, 2023 at 2:54
  • I'm not sure how I would show the performance difference without adding system versioning and waiting for history to be collected? That's a fairly risky thing to do in production without knowing what the consequences will be (I could try randomly updating records in a test environment but I'm not sure if that would reflect real user data).
    – pwaring
    Commented Apr 6, 2023 at 7:57
  • You could create two replicas off production. one with SYSTEM VERSIONING, one without FK, but with with SYSTEM VERSIONING PARTITION BY TIME and measure query time on all three instances. If successful you could promote one replica to primary if the experiment works out.
    – danblack
    Commented Apr 6, 2023 at 21:57
  • I'm unlikely to set up replication just for this - it's an enormous amount of work and used to break repeatedly (this system used to use replication and load balancing and we had to switch it off).
    – pwaring
    Commented Apr 8, 2023 at 8:29

1 Answer 1

1

The answer to this is that the behaviour I want isn't available at the moment, as per the following bug:

https://jira.mariadb.org/browse/MDEV-19191

There are two other bugs that are blocking the resolution of 19191, one of which is Stalled, so it may be some time before this functionality is available.

1
  • The two other bugs, one is a broader task of FK enablement, and the other is a related history in a different table task. Neither are blocking its implementation. Please watch/vote on outstanding issues you want fixed. It helps give them a user priority.
    – danblack
    Commented Apr 7, 2023 at 22:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.