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

Nested calls to routines containing non-tran operations don't get detected in mixed mode. #2546

Closed
seb-urbaniak opened this issue Oct 29, 2019 · 4 comments

Comments

@seb-urbaniak
Copy link

@seb-urbaniak seb-urbaniak commented Oct 29, 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 new script sql\Migrations\V11.0__SomethingSomething.sql
which contains a stored procedure call to: MyLittleSPthatTouchesReplication
That SP has this statement in it : EXEC sp_addarticle ...blah...
Tried to Migrate with Mixed mode = TRUE.

What did you expect to see?

Flyway (if it were able to traverse nested calls) would detect that in Mixed mode that V script should be marked as [non-transactional].

What did you see instead?

V script was not marked as [non-transactional], and it errors out because sp_addarticle is not allowed to be called within TRAN wrapper that Flyway creates.


I realize this is a very complex problem, and it will probably take you quite some hoop jumping to resolve. As a temporary workaround we fake inject a NON-TRAN operation into the V script just to get things to run. Not ideal - unless you have a better idea for us.

@alextercete

This comment has been minimized.

Copy link
Member

@alextercete alextercete commented Oct 30, 2019

This is related to #2478.

As you suggested, this is a very complex problem full of edge cases, so we intend to provide a mechanism to force a script [not] to run within a transaction. We haven't settled on anything specific, but it's likely to involve adding a comment to the top of the script, for example:

-- flyway transactional: true
@alextercete

This comment has been minimized.

Copy link
Member

@alextercete alextercete commented Nov 15, 2019

For the record, we decided to go with slightly different approach: you'll need to create a migration configuration file such as:

V1__My_migration.sql.conf:

executeInTransaction=true
@seb-urbaniak

This comment has been minimized.

Copy link
Author

@seb-urbaniak seb-urbaniak commented Nov 15, 2019

@alextercete Let me make sure I understand your/new approach right, for every V script that I want executed in this mode, one has to make a conf file, with the same name ? and in the usual conf folder ? and Flyway will automatically know to use that conf ? .... and now the possible deal breaker: What if it is one of the callback scripts that currently explodes in my collection ? (do i just make a conf file with the matching callback script name ?)

@alextercete

This comment has been minimized.

Copy link
Member

@alextercete alextercete commented Nov 18, 2019

@seb-urbaniak That's very close to how it's been implemented, except that .conf files live next to the script they're modifying (and not inside the conf folder). For example:

sql
├── beforeMigrate.sql
├── beforeMigrate.sql.conf
├── beforeMigrate__No_configuration.sql
├── V1__My_migration.sql
├── V1__My_migration.sql.conf
└── V2__Migration_with_no_configuration.sql

As the example suggests, this approach would work for callbacks as well: just create a .conf for the script in question and it will override executeInTransaction to the value you set.

alextercete added a commit that referenced this issue Nov 26, 2019
Related to #2478 and #2546.
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.