Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

External Stored Procedure in Trigger does not behave as expected #7737

Open
jycor opened this issue Apr 12, 2024 · 0 comments
Open

External Stored Procedure in Trigger does not behave as expected #7737

jycor opened this issue Apr 12, 2024 · 0 comments
Labels
bug Something isn't working correctness We don't return the same result as MySQL sql Issue with SQL

Comments

@jycor
Copy link
Contributor

jycor commented Apr 12, 2024

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.

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

@jycor jycor added bug Something isn't working sql Issue with SQL correctness We don't return the same result as MySQL labels Apr 12, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working correctness We don't return the same result as MySQL sql Issue with SQL
Projects
None yet
Development

No branches or pull requests

1 participant