Issue with Trigger Causing Update Errors in Busy Node on Galera Cluster

MySQL & MariaDB Percona XtraDB Cluster 8.x
,
1

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.

2

It is always better to handle the trigger from within the application instead of having it in database.

What does the function do in the trigger?

SHOW CREATE FUNCTION func_resolve_scheme_from_account;

That can help understand why the trigger is being called.

3

@Yunus Here is the DDL for the function and the DDL for the Procedure that called inside from the function

Function:

CREATE DEFINER=`user@host` FUNCTION `func_resolve_scheme_from_account`(account_id INT) RETURNS int

READS SQL DATA

BEGIN

DECLARE scheme_id INTEGER DEFAULT 0;

CALL proc_get_scheme_from_account (account_id, scheme_id);

RETURN scheme_id;

END;

Stored Procedure:

CREATE DEFINER=user@host PROCEDURE proc_get_scheme_from_account(IN account_id INT, OUT scheme_id INT)
BEGIN
DECLARE cnt INTEGER DEFAULT 0;
SELECT count(*)
FROM generic_directory  – Changed table name for confidentiality
INNER JOIN account_table ON accountMemberId = generic_directoryId -- Changed table name for confidentiality
WHERE accountId = account_id
INTO cnt;

IF cnt = 0 THEN
    SET scheme_id = NULL;
ELSEIF cnt > 0 THEN
    SELECT `generic_directorySchemeId`  -- Changed column name for confidentiality
    FROM `generic_directory`  -- Changed table name for confidentiality
    INNER JOIN `account_table` ON `accountMemberId` = `generic_directoryId` -- Changed table name for confidentiality
    WHERE `accountId` = account_id
    INTO scheme_id;
END IF;

END;

I wonder why a update sql statement trigger any insert trigger.

4

Hi @Nazmul,

I reproduced this on a 3-node PXC 8.0.41 cluster using the schema, trigger, function, and procedure you shared. The root cause is a known MySQL server bug (Bug #80885).

When a BEFORE INSERT trigger fires, MySQL evaluates the row internally. If the INSERT omits a NOT NULL column without a DEFAULT (like your Taxes DECIMAL(10,2) NOT NULL), the server’s internal row validation state becomes stale. A subsequent UPDATE in the same connection then incorrectly re-validates against that stale state, producing ERROR 1048: Column 'Taxes' cannot be null even though the UPDATE itself sets a valid value. The trigger is not actually firing on the UPDATE; instead, leftover state from the INSERT’s trigger execution contaminates the UPDATE’s validation path.

One note: in my lab the bug reproduced consistently on both busy and idle nodes (10 out of 10 attempts on each). It is a server-level session bug, not specific to Galera replication or node load. You may be seeing it more often on busy nodes because that’s where most INSERT-then-UPDATE sequences happen in your application.

Workarounds (all verified on PXC 8.0.41):

The safest immediate fix is to add a DEFAULT to the Taxes column, which prevents the stale-state condition entirely:

ALTER TABLE financial MODIFY Taxes DECIMAL(10,2) NOT NULL DEFAULT 0.00;

Alternatively, include all NOT NULL columns explicitly in your INSERT statements (including Taxes), so MySQL never needs to apply implicit defaults during trigger execution.

Two other workarounds also work but are less practical: running FLUSH TABLES between the INSERT and UPDATE (clears the stale internal state, but has global performance impact), or adding an empty BEFORE UPDATE trigger (forces MySQL to re-evaluate the row properly, but adds hidden coupling).

Fix: Upgrade to PXC 8.0.42 or later. The upstream MySQL fix is included starting from that version. I confirmed the bug does not reproduce on PXC 8.0.45.

References:

5

Hi @anderson.nogueira
Thank you for testing the issue and sharing the bug information and solution.

Our financial table size is over 70 GB, and since MySQL 8 supports instant feature for setting a column default, could you please confirm if this functionality works the same way in a Percona Galera cluster? Specifically, can we set the default value without locking the table? Our PXC version is 8.0.36-28.1

image
image1212×623 54.5 KB

The command we are considering is:

ALTER TABLE financial MODIFY Taxes DECIMAL(10,2) NOT NULL DEFAULT 0.00, ALGORITHM=INSTANT;

Thank you for your assistance!

6

Yes, you can use the alter table to set default value in the table using INSTANT algorithm.

It would be best to test on the test instance before running on prod.
You can also consider using pt-online-schema-change.