Details
-
Bug
-
Status: Needs Feedback (View Workflow)
-
Major
-
Resolution: Unresolved
-
24.02.7
-
None
-
None
-
Galera cluster, MariaDB 11.4, Maxscale 24.02.7-ubi, Docker, PHP 8.4 (php-fpm)
Description
I have a table partitioned by quarters (_part column, SMALLINT).
CREATE TABLE `receipts` ( |
`guid` varchar(50) NOT NULL, |
`idp8` varchar(50) NOT NULL, |
`ac` int(11) NOT NULL, |
`idA` int(11) NOT NULL, |
`k` int(11) NOT NULL, |
`c` decimal(8,2) NOT NULL, |
`s` decimal(8,2) DEFAULT NULL, |
`koef` smallint(5) unsigned NOT NULL DEFAULT 0, |
`_user_id` int(10) unsigned NOT NULL DEFAULT 0 INVISIBLE, |
`_part` smallint(6) NOT NULL DEFAULT 0 INVISIBLE, |
PRIMARY KEY (`_part`,`guid`,`idp8`) USING BTREE, |
KEY `guid` (`guid`) USING BTREE |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
PARTITION BY RANGE (`_part`) |
(PARTITION `old` VALUES LESS THAN (20251) ENGINE = InnoDB, |
PARTITION `y2025q1` VALUES LESS THAN (20252) ENGINE = InnoDB, |
PARTITION `y2025q2` VALUES LESS THAN (20253) ENGINE = InnoDB, |
PARTITION `y2025q3` VALUES LESS THAN (20254) ENGINE = InnoDB, |
PARTITION `y2025q4` VALUES LESS THAN (20261) ENGINE = InnoDB) |
The table contains ~60–70 million records per quarter.
I have traffic from several thousand clients that update this table (each request contains ~1,000 records). The average load is about 1.5 requests per second.
Sometimes I get a strange error:
app.WARNING: Transaction failed, retrying... {"attempt":1,"error":"SQLSTATE[42S02]: Base table or view not found: 1146 Table 'atlas_prod._temp_receipts_95786b906eb043fd' doesn't exist","at":"/var/www/html/src/Domain/Service/ReceiptService.php:140","conn_ids":{"start":1673523,"after_truncate":1672995,"after_begin_tx":1681555,"after_insert":1681555,"after_update_koef":1681555,"after_update_receipts":1681555,"catch_connection_id":1681555},"temp_tables":["_temp_receipts_95786b906eb043fd"]}
|
It ALWAYS occurs on this request:
$inserted = $this->pdo->exec("INSERT INTO receipts ($all_fields) |
SELECT $select_fields FROM $temp_table t |
LEFT JOIN receipts r ON t.guid=r.guid AND t.idp8=r.idp8 AND t._part=r._part
|
WHERE r.guid IS NULL");
|
The project is deployed on two identical servers running in Docker. Both servers use MaxScale 24.02.7-ubi.
The error starts to appear as soon as traffic slightly increases on either node (for example, when a new active client with a single IP address appears and the load balancer consistently routes this client to one node).
The PHP code that reproduces the problem is attached.