Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
12.3.3
-
None
-
Linux x86_64, MariaDB.org Ubuntu 24.04 binary distribution
-
Not for Release Notes
Description
A multi-row InnoDB INSERT is not statement-atomic when an AFTER INSERT trigger creates a savepoint and then raises an error. After the statement fails, one inserted row remains visible in the active transaction.
This occurs when a trigger creates a savepoint during a multi-row statement and a later trigger operation raises an error.
Steps to reproduce
The following was reproduced on MariaDB 12.3.3.
- Save this script as case.sql:
DROP DATABASE IF EXISTS trigger_atomicity; |
CREATE DATABASE trigger_atomicity; |
USE trigger_atomicity; |
|
|
CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; |
|
|
DELIMITER //
|
CREATE TRIGGER after_insert |
AFTER INSERT ON t1 |
FOR EACH ROW |
BEGIN
|
SAVEPOINT row_sp;
|
DELETE FROM missing_table; |
END// |
|
|
CREATE PROCEDURE run_case() |
BEGIN
|
DECLARE v_error BOOL DEFAULT FALSE; |
DECLARE v_errno INT DEFAULT 0; |
DECLARE v_message TEXT DEFAULT ''; |
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION |
BEGIN |
SET v_error=TRUE; |
GET DIAGNOSTICS CONDITION 1
|
v_errno=MYSQL_ERRNO, v_message=MESSAGE_TEXT;
|
END; |
|
|
START TRANSACTION; |
INSERT INTO t1 VALUES (1),(2); |
SELECT 'result' AS marker, |
v_error AS statement_failed, |
v_errno AS error_number, |
v_message AS error_message, |
(SELECT COUNT(*) FROM t1) AS rows_after_failed_statement; |
ROLLBACK; |
END// |
DELIMITER ;
|
|
|
CALL run_case();
|
DROP DATABASE trigger_atomicity; |
- Run the script in a MariaDB client:
mariadb < case.sql |
Actual result
The trigger's reference to the absent table raises error 1146, but the count inside the same transaction is 1:
marker statement_failed error_number error_message rows_after_failed_statement
|
result 1 1146 Table 'trigger_atomicity.missing_table' doesn't exist 1
|
Expected result
The error should roll back every change made by the failed INSERT statement. The transaction may remain active for the handler, but rows_after_failed_statement must be 0. A count of 1 shows that the trigger-created savepoint displaced the statement's rollback boundary and allowed a partial change from the failed statement to remain visible. The explicit ROLLBACK at the end prevents that partial change from being committed by the reproducer.
Attachments
Issue Links
- duplicates
-
-
- Open
-