Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Duplicate
-
None
-
None
-
AWS RDS
-
Not for Release Notes
Description
After upgrading MariaDB on multiple staging servers (from 10.6.18 to 10.6.21), all running on Amazon RDS, we started experiencing errors when performing `INSERT ... SELECT` operations into a table that has a `BEFORE INSERT` trigger. Note: the trigger in this case does nothing (it just uses `LEAVE`).
The table has a `UNIQUE` constraint to prevent duplicated values. After the upgrade, we’re getting a duplicate key error where the values involved are `0-0` — this means the inserted rows seem to be incorrectly defaulting to zeroes, as if the `SELECT` result is being ignored or discarded, and the trigger interferes with value propagation.
This may be related to MDEV-36271(https://jira.mariadb.org/browse/MDEV-36271), but the behavior is slightly different and simpler to reproduce, which is why I’m opening a separate ticket. Feel free to close it if you think it’s a duplicate.
The error :
Error Code: 1062. Duplicate entry '0-0' for key 'Unique_target_permission' |
How to reproduce:
/* Create table for the test */
|
CREATE DATABASE IF NOT EXISTS TEST_TRIGGER_BREAKS_INSERT_SELECT_10_6_21; |
|
|
USE TEST_TRIGGER_BREAKS_INSERT_SELECT_10_6_21; |
|
|
DROP TABLE IF EXISTS TEST_TRIGGER_BREAKS_INSERT_SELECT_10_6_21.`Test`; |
CREATE TABLE TEST_TRIGGER_BREAKS_INSERT_SELECT_10_6_21.`Test` ( |
`ID` bigint(20) NOT NULL AUTO_INCREMENT, |
`Target_id` bigint(20) NOT NULL, |
`Permission_code` bigint(20) NOT NULL, |
`Created_dtm` datetime NOT NULL, |
`Updated_dtm` datetime NOT NULL, |
PRIMARY KEY (`ID`), |
UNIQUE KEY `Unique_target_permission` (`Target_id`,`Permission_code`) |
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; |
|
|
/* Add trigger which does nothing */
|
DELIMITER $$
|
DROP TRIGGER IF EXISTS `TEST_TRIGGER_BREAKS_INSERT_SELECT_10_6_21`.trigger_test_insert_that_does_nothing $$ |
CREATE TRIGGER trigger_test_insert_that_does_nothing BEFORE INSERT ON TEST_TRIGGER_BREAKS_INSERT_SELECT_10_6_21.`Test` |
FOR EACH ROW |
trigger_loop : BEGIN |
IF (NEW.`ID` = 0) THEN |
LEAVE trigger_loop;
|
END IF; |
END $$ |
DELIMITER ;
|
|
|
/* Insert test data */
|
INSERT INTO `TEST_TRIGGER_BREAKS_INSERT_SELECT_10_6_21`.`Test`(`Target_id`, `Permission_code`, Created_dtm, Updated_dtm) |
VALUES (1, 56, now(), now()), |
(1, 25, now(), now()),
|
(1, 15, now(), now());
|
|
|
/* Test Insert...Select */
|
INSERT INTO `TEST_TRIGGER_BREAKS_INSERT_SELECT_10_6_21`.`Test`(`Target_id`, `Permission_code`, Created_dtm, Updated_dtm) |
SELECT 2 AS 'Target_id', Permission_code, NOW(), NOW() |
FROM TEST_TRIGGER_BREAKS_INSERT_SELECT_10_6_21.`Test` |
WHERE Target_id = 1; |
|
|
/* Version: 10.6.21 -> Getting Error Code: 1062. Duplicate entry '0-0' for key 'Unique_target_permission' */ |
Expected behavior:
Rows should be inserted with the new Target_id = 2, and duplicate key violations should not occur.
❌ Actual behavior (only in 10.6.21):
The trigger causes inserted values to default to 0s, violating the UNIQUE constraint on (Target_id, Permission_code).
❌ Fails in 10.6.21:
✅ Works fine in 10.6.18:
Additional test: data inspection via trigger log
I just ran an additional test: I removed the `UNIQUE` constraint and modified the `BEFORE INSERT` trigger to insert each incoming row into a log table, in order to inspect the actual data being inserted.
The result confirms that:
- The *first row* inserted via `INSERT ... SELECT` contains the expected values.
- All *subsequent rows* have all columns set to *NULL*.
Additional test 2: Test agains 10.6.22
In the 10.6.22 version it works corretly.
Attachments
Issue Links
- duplicates
-
-
- Closed
-
- relates to
-
-
- Open
-