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?
This appears to be a bug that is already fixed in the 10.11.8 release that came out last week.
The problem per the commit message is:
Problem was too tight condition on ha_commit_trans to not
allow non transactional storage engines participate 2pc
in Galera case. This is required because transaction
using e.g. procedures might read mysql.proc table inside
a tra[n]saction and these tables use at the moment Aria
storage engine that does not support 2pc.
Fixed by allowing read only transactions to storage
engines that do not support two phase commit to participate
2pc transaction. ...