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

More mssql sp calls cause non-tran statement error. #2542

Closed
seb-urbaniak opened this issue Oct 23, 2019 · 6 comments

Comments

@seb-urbaniak
Copy link

@seb-urbaniak seb-urbaniak commented Oct 23, 2019

Which version and edition of Flyway are you using?

v6.0.7

Which client are you using? (Command-line, Java API, Maven plugin, Gradle plugin)

command-line

Which database are you using (type & version)?

MSSQL 14.0.x

Which operating system are you using?

Win10 x64

What did you do?

Added a new Stored Proc that accesses MSSQL Replication routines.

What did you expect to see?

For Flyway to automatically detect special MSSQL keywords, and allow non-tran, for that given R script.

What did you see instead?

"ERROR: Detected both transactional and non-transactional statements within the same migration (even though mixed is false)."
its one , some or all of these that is causing this:

EXEC sp_addarticle
EXEC sp_droparticle
EXEC sp_articlefilter
EXEC sp_addsubscription
EXEC sp_dropsubscription

[
If i am not mistaken there already exists a regex/list inside the code (or at least there used to be) that does special handling of some keywords such as these.
See this prev item for ref, it might help in some way: https://github.com//issues/2175
]

@seb-urbaniak

This comment has been minimized.

Copy link
Author

@seb-urbaniak seb-urbaniak commented Oct 25, 2019

I guess I was thinking about this module & block of code:
SQLServerParser.java

    protected Boolean detectCanExecuteInTransaction(String simplifiedStatement, List<Token> keywords) {
        String current = keywords.get(keywords.size() - 1).getText();
        if ("BACKUP".equals(current) || "RESTORE".equals(current) || "RECONFIGURE".equals(current)) {
            return false;
        }

        if (keywords.size() < 2) {
            return null;
        }

        String previous = keywords.get(keywords.size() - 2).getText();
        // #2175: The procedure 'sp_addsubscription' cannot be executed within a transaction.
        // #2298: The procedures 'sp_serveroption' and 'sp_droplinkedsrvlogin' cannot be executed within a transaction.
        // These procedures is only present in SQL Server. Not on Azure nor in PDW.
        if ("EXEC".equals(previous) && (
                "SP_ADDSUBSCRIPTION".equals(current) ||
                        "SP_DROPLINKEDSRVLOGIN".equals(current) ||
                        "SP_SERVEROPTION".equals(current))) {
            return false;
        }

        // (CREATE|DROP|ALTER) (DATABASE|FULLTEXT (INDEX|CATALOG))
        if (("CREATE".equals(previous) || "ALTER".equals(previous) || "DROP".equals(previous))
                && ("DATABASE".equals(current) || "FULLTEXT".equals(current))) {
            return false;
        }

        return null;
    }
@juliahayward

This comment has been minimized.

Copy link
Member

@juliahayward juliahayward commented Oct 29, 2019

The parser ought to indicate which one is the problem in this case - are you able to share the full debug output from Flyway? (Or mail it to us?)

@seb-urbaniak

This comment has been minimized.

Copy link
Author

@seb-urbaniak seb-urbaniak commented Oct 29, 2019

I isolated them 1 by 1 in a new script and looks like:
EXEC sp_dropsubscription
from the list above, is the only problem child.

@seb-urbaniak

This comment has been minimized.

Copy link
Author

@seb-urbaniak seb-urbaniak commented Oct 29, 2019

... however while figuring the above out, i have run into a yet different problem with non-tran operation containing scripts in Mixed mode. I will make a new entry for that.

@juliahayward

This comment has been minimized.

Copy link
Member

@juliahayward juliahayward commented Oct 30, 2019

Thanks - our solution to #2546 will be rather more complete, but I can certainly add sp_dropsubscription and some others that I demonstrated were also not transactionable.

@juliahayward

This comment has been minimized.

Copy link
Member

@juliahayward juliahayward commented Oct 30, 2019

Fix for this example coming in 6.0.8; for the more complete solution #2546 we are aiming at 6.1.

juliahayward added a commit that referenced this issue Oct 30, 2019
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
2 participants
You can’t perform that action at this time.