Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6, 10.11, 11.4, 11.8, 12.2
-
None
Description
In the test case below, INSERT on the table works (inserts a row) despite the error which the trigger causes. I'm not sure what would be the right behavior here.
On one hand, if we flat down the trigger logic, then the sequence
Transaction start => valid INSERT => savepoint => erroneous statement => commit
is expected to keep the inserted row.
On the other hand, since savepoint and the erroneous statement are under the trigger hood, the INSERT, while valid itself, ends up with an error, so it would be reasonable to expect it to be rolled back as an atomic operation.
Either way, the result should be consistent with the binary log, and it currently isn't.
While INSERT works and the row is kept, it isn't written into the binary log, so it's not executed on the slave, which creates a discrepancy between master and slave and further inevitable replication failure. The UPDATE statement in the test case, while not necessary to demonstrate the basic issue described above, shows how it leads to a replication abort.
--source include/have_innodb.inc
|
--source include/have_binlog_format_row.inc
|
--source include/master-slave.inc
|
|
|
CREATE TABLE t (a INT) ENGINE=InnoDB; |
--delimiter $
|
CREATE TRIGGER tr AFTER INSERT ON t FOR EACH ROW BEGIN SAVEPOINT A; DELETE FROM non_existing; END $ |
--delimiter ;
|
START TRANSACTION; |
--error ER_NO_SUCH_TABLE
|
INSERT INTO t VALUES (1); |
COMMIT; |
|
|
SELECT * FROM t; |
UPDATE t SET a = 2; |
|
|
--sync_slave_with_master
|
|
|
--connection master
|
DROP TABLE t; |
--source include/rpl_end.inc |
|
main 5ed3668890390060526434b92271756392d77869 |
SELECT * FROM t; |
a
|
1
|
UPDATE t SET a = 2; |
master-bin.000001 329 Gtid 1 371 GTID 0-1-1
|
master-bin.000001 371 Query 1 483 use `test`; CREATE TABLE t (a INT) ENGINE=InnoDB |
master-bin.000001 483 Gtid 1 525 GTID 0-1-2
|
master-bin.000001 525 Query 1 745 use `test`; CREATE DEFINER=`root`@`localhost` TRIGGER tr AFTER INSERT ON t FOR EACH ROW BEGIN SAVEPOINT A; DELETE FROM non_existing; END |
master-bin.000001 745 Gtid 1 787 BEGIN GTID 0-1-3 |
master-bin.000001 787 Annotate_rows 1 0 UPDATE t SET a = 2 |
master-bin.000001 828 Table_map 1 0 table_id: 34 (test.t)
|
master-bin.000001 872 Update_rows_v1 1 0 table_id: 34 flags: STMT_END_F
|
master-bin.000001 916 Xid 1 947 COMMIT /* xid=147 */ |
Last_Errno 1032
|
Last_Error Could not execute Update_rows_v1 event on table test.t; Can't find record in 't', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log master-bin.000001, |