We are experiencing an issue with a trigger in our MySQL Galera Cluster. We have a table, referred to as financial, defined as follows:
CREATE TABLE `financial` (
`TransactionId` int unsigned NOT NULL AUTO_INCREMENT,
`Type` tinyint unsigned NOT NULL,
`RecipientId` int unsigned NOT NULL,
`RecipientSchemeId` int unsigned DEFAULT NULL,
`PayerId` int unsigned NOT NULL,
`PayerSchemeId` int unsigned DEFAULT NULL,
`TransactionTimestamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`Amount` decimal(10,2) NOT NULL,
`Fees` decimal(10,2) NOT NULL,
`Taxes` decimal(10,2) NOT NULL,
`Status` enum('Pending','Completed','Failed','Cancelled') COLLATE utf8mb3_bin NOT NULL DEFAULT 'Pending',
`StatusUpdated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`ExternalStatus` enum('Pending','Completed','Failed','Cancelled') COLLATE utf8mb3_bin NOT NULL DEFAULT 'Pending',
`ExternalStatusUpdated` datetime NOT NULL,
`BatchId` int unsigned DEFAULT NULL,
`InvoiceReference` int unsigned DEFAULT NULL,
PRIMARY KEY (`TransactionId`),
KEY `TransactionTimestamp` (`TransactionTimestamp`),
KEY `PayerSchemeId` (`PayerSchemeId`),
KEY `RecipientSchemeId` (`RecipientSchemeId`),
KEY `idx_batchid` (`BatchId`),
KEY `idx_invoice` (`InvoiceReference`),
KEY `idx_payerstatus` (`PayerId`, `Status`, `Amount`),
KEY `idx_recipientstatus` (`RecipientId`, `Status`, `Amount`),
KEY `idx_payerdate` (`PayerId`, `Status`, `TransactionTimestamp`),
KEY `idx_recipientdate` (`RecipientId`, `Status`, `TransactionTimestamp`),
KEY `idx_statusdate` (`Status`, `Type`, `TransactionTimestamp`),
KEY `idx_recipientfeestatus` (`RecipientId`, `ExternalStatus`, `Fees`)
) ENGINE=InnoDB AUTO_INCREMENT=1885796210 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin;
In addition, we have a trigger, named trigger_resolv_before_insert, set to execute before an insert operation. Its purpose is to automatically resolve and set values for PayerSchemeId and RecipientSchemeId based on other input values from the same row:
CREATE DEFINER=`root`@`localhost` TRIGGER `trigger_resolv_before_insert`
BEFORE INSERT ON `financial`
FOR EACH ROW
BEGIN
SET NEW.PayerSchemeId = func_resolve_scheme_from_account(NEW.PayerId);
SET NEW.RecipientSchemeId = func_resolve_scheme_from_account(NEW.RecipientId);
END;
However, when we perform an update on the Taxes column for a specific row with the following command:
UPDATE financial_transaction SET Taxes = 0.05 WHERE TransactionId = 21000065445;
we encounter the following error on one of our nodes, identified as the busy node:
ERROR 1048 (23000): Column ‘Taxes’ cannot be null
Interestingly, this issue occur only busy node where we have traffic and does not occur on our less busy nodes where we don’t have traffic. When we drop the trigger, the update operation works flawlessly on the busy node, indicating that the trigger might be causing the problem.
Can you please help to identify the issue why update statement triggering insert trigger.
Thanks.