Using MariaDB version 10.11.6
I’ve enabled log_bin for replication purposes. However since it’s been enabled we sometimes get the following error when committing a transaction: “Transactional commit not supported by involved engine(s)”.
We use InnoDB as the storage engine for all of our tables so it’s none of our user defined tables that are causing this issue. Instead it seems like it is a system table that is causing this, and I’ve tracked down the queries that are causing it. Every once in a awhile before calling an SP this query gets called first:
SELECT param_list, returns
FROM mysql.proc
WHERE db = 'DB_NAME'
AND name = 'PROC_NAME';
After some reading it sounds like this happens if the SP isn’t in the metadata cache and it needs to grab some information before executing the SP. This is causing the error because mysql.proc’s storage engine is Aria which is different from InnoDB leading to the error. I’m wondering how I’m supposed to get around this error? I’m assuming it isn’t a good idea to manually update the mysql.proc engine to InnoDB as that is usually a no no to update those tables in any way. Surely there is a way to have bin_log enabled, tables using InnoDB, and be able to use transactions right? Is there a way to ensure that entire table is always in the metadata cache so those queries don’t get randomly called?