Random FK Constraint issues on MariaDB

[ohager]

VERSION: 3.8.0

It may happen randomly that insertion errors appear (reported once a while)

org.jooq.exception.DataAccessException: SQL [insert into `block` (`id`, `version`, `timestamp`, `previous_block_id`, `total_amount`, `total_fee`, `total_fee_cash_back`, `total_fee_burnt`, `payload_length`, `generator_public_key`, `previous_block_hash`, `cumulative_difficulty`, `base_target`, `height`, `generation_signature`, `block_signature`, `payload_hash`, `generator_id`, `nonce`, `ats`) values (3658542890957534042, 4, 306624049, -8593194476844238940, 1525000000, 160200000, 0, 160200000, 72, X'8826b7c9dd68d28b65e8e8f814238909c809adbdc5e5e58b837cb734632d7c5b', X'a4b7d9e926d7be88021471ad7e5454e29c703cce8d59793ec48f894dfa10af20', X'0881ae3da821e30df4', 6070375815265423852, 1271377, X'b252b7e7ed1143cafa021010785863df0bc110a2a33685d03b9f6cbc3ff06ffb', X'24e830a6b9d13747f76c103459fcf464965dd0c5489033f3306dbb50a11d830b60c50ed3689ea40d6e6d85b97f8b857f9f0f28c1dea4412cf88ca529a65fc849', X'e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855', -6154617539399287288, 22900000018369928, X'137c77dce40a40ba845c50213db058851c52177767c794f3e22dbf5a56bf426447a56576dfb9e28320faff6c20b789e10319e3bdff90b38850cc067a0f1c3e853dac69775d7d9fbe')]; (conn=200844) Cannot add or update a child row: a foreign key constraint fails (`brs_master`.`block`, CONSTRAINT `constraint_3c` FOREIGN KEY (`previous_block_id`) REFERENCES `block` (`id`) ON DELETE CASCADE)
    at org.jooq_3.15.7.MARIADB.debug(Unknown Source)
    at org.jooq.impl.Tools.translate(Tools.java:2997)
    at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:639)
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:354)
    at org.jooq.impl.AbstractDelegatingQuery.execute(AbstractDelegatingQuery.java:111)
    at brs.db.sql.SqlBlockDb.saveBlock(SqlBlockDb.java:130)
    at brs.db.sql.SqlBlockchainStore.lambda$addBlock$15(SqlBlockchainStore.java:295)
    at brs.db.sql.Db.useDSLContext(Db.java:236)
    at brs.db.sql.SqlBlockchainStore.addBlock(SqlBlockchainStore.java:294)
    at brs.BlockchainProcessorImpl.addBlock(BlockchainProcessorImpl.java:845)
    at brs.BlockchainProcessorImpl.pushBlock(BlockchainProcessorImpl.java:1051)
    at brs.BlockchainProcessorImpl.lambda$new$5(BlockchainProcessorImpl.java:641)
    at brs.util.ThreadPool.lambda$start$0(ThreadPool.java:104)
    at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
    at java.base/java.util.concurrent.FutureTask.runAndReset(Unknown Source)
    at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.base/java.lang.Thread.run(Unknown Source)

Observation:

This looks like a threading issue, where the insertion order is not guaranteed leading to FK constraint issues. Iirc, FK checks are disabled exactly to this reason. Maybe with the MariaDB version update something has change regarding the FK configuration. The FKs are introduced to take advantage of cascaded deletion on popOffs. Cascaded deletion can be emulated with Triggers instead of FK checks, like it is done on SQLite.