Cross-database transactions error message with pipeline

Help Connectors and Pipelines
1

Hi,

I face the following issue: I created several procedures (hereafter: “sub-procedures”) to update various tables. I gathered those in one main procedure in which I call them all and I loaded data from a pipeline into that main procedure. While I can run manually without any issue each of the sub-procedures, when I start my pipeline, I keep on getting the same error message:

“Unhandled exception
Type: ER_MEMSQL_FEATURE_LOCKDOWN (1706)
Message: Feature ‘Cross-database transactions’ is not supported by MemSQL.”

However, the problematic sub-procedure only uses one DB.

Would you have any idea on how I can run those sub-procedures individually without error but not as part of a pipeline?

Please let me know if you need more information/clarification on my problem.

Thanks a lot in advance.

2

Hi @sim.masq! Welcome to the forum.

Are you certain your top-level procedure is only updating one database?

Is your code using multi-statement transactions explicitly (begin/begin work, and commit)?

1 Like
3

Thanks @hanson.

Some of the procedures within the top-level procedure do indeed insert into another database. Nevertheless, in the error message I get, the Callstack mentions a procedure which uses only one DB.

I am not sure about your second question. My top-level procedure is built as follows:

CREATE OR REPLACE PROCEDURE DB_1.TopLevelProcedure(query query(START_PIPELINE text CHARACTER SET utf8 COLLATE utf8_general_ci NULL)) RETURNS void AS
DECLARE
BEGIN

call DB_1.Procedure1(); - - This one inserts into a table in DB_2
call DB_1.Procedure2(); - - This one updates a table in DB_1 -> I get the error message for that one.

call DB_1.Procedure3();
…

END;

4

@sim.masq,

The entire top-level stored procedure is wrapped in a single transaction internally by SingleStore. So since the code inside it indirectly updates DB_2 and DB_1, it is trying to do a multi-database transaction. That is not allowed. It’s the source of the error message.

I’d recommend putting everything in one database if you can.

5

Well I understand, but one thing I did not mention is that we have 3 instances (Dev, Acc and Prod). The same top-level stored procedure works fine in two of them (Acc and Prod) and actually it is in the Dev one that I face the issue.

So I’m a bit confused: how come I am able to update cross-DB in two instances but not in the other?

6

All I can think of is that cross-DB updates inside the top level SP are only happening in Dev. Maybe there’s conditional logic making that happen? It’s should error if you really attempt a cross-DB update in the body of the stored proc called by pipelines.