dolthub / dolthub/dolt

External Stored Procedure in Trigger does not behave as expected

Open
#7,737 0 comments 0 reactions 1 assignee Claimed by @zachmu View on GitHub
bug correctness sql
Dominant language
Go
Stars
24.4k
Forks
873
Avg merge
1d 5h
Merged PRs (30d)
108

Description

Including a call statement inside of a trigger causes inserts (and probably other queries) to do nothing.
There's a skipped tests for this here: https://github.com/dolthub/dolt/pull/7734/files#diff-0f403f04aebada5ed7b3bf9f153a5fc0d26a9535583a90ba37e13c87724501beR271

However, the repro below actually reveals an error, that doesn't show up in the enginetests.

```sql
tmp/main> create table t1 (i int);
tmp/main*> create table t2 (i int);
tmp/main*> delimiter //
tmp/main*> create trigger trig before insert on t1 for each row
-> begin
-> insert into t2 values (new.i);
-> call dolt_branch(concat('branch', new.i));
-> end;
-> //
tmp/main*> delimiter ;
tmp/main*> insert into t1 values (1), (2);
Query OK, 2 rows affected (0.00 sec)
time="2024-04-12T11:31:09-07:00" level=error msg="Unexpected error when calling ReleaseSavepoint during triggerRollbackIter.Close()" connectionDb=tmp error="SAVEPOINT __go_mysql_server_starting_savepoint__ does not exist"
tmp/main*> select * from t1;
+---+
| i |
+---+
| 2 |
| 1 |
+---+
2 rows in set (0.00 sec)

tmp/main*> select * from t2;
+---+
| i |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

tmp/main*> select * from dolt_branches;
+---------+----------------------------------+------------------+------------------------+---------------------+----------------------------+--------+--------+
| name | hash | latest_committer | latest_committer_email | latest_commit_date | latest_commit_message | remote | branch |
+---------+----------------------------------+------------------+------------------------+---------------------+----------------------------+--------+--------+
| branch1 | tg3uapau9h5vtibu7onf8dvj4n346n0p | James | james@dolthub.com | 2024-04-12 18:31:06 | Initialize data repository | | |
| branch2 | tg3uapau9h5vtibu7onf8dvj4n346n0p | James | james@dolthub.com | 2024-04-12 18:31:06 | Initialize data repository | | |
| main | tg3uapau9h5vtibu7onf8dvj4n346n0p | James | james@dolthub.com | 2024-04-12 18:31:06 | Initialize data repository | | |
+---------+----------------------------------+------------------+------------------------+---------------------+----------------------------+--------+--------+
3 rows in set (0.00 sec)

```

`t1` and `dolt_branches` has all the rows we expect, but `t2` is missing `2`, because of the `call dolt_branch` in between.

It may be that the procedure is external or that `dolt_branch` messes with transactions somehow(?), but local stored procedures seem to work: https://github.com/dolthub/go-mysql-server/pull/2446/files#diff-7374b89c573163e52e3d783ff5208641cfb62a663d826065eb92bad979bfb398R2406

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.