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

One connection is used for all migrations causes an issue with session params redefined #1250

Closed
ayratn opened this issue Mar 22, 2016 · 3 comments

Comments

@ayratn
Copy link

@ayratn ayratn commented Mar 22, 2016

What version of Flyway are you using?

3.1

What database are you using (type & version)?

MSSQL 2008

What operating system are you using?

Windows 7

What did you do?

(Please include the content causing the issue, any relevant configuration settings, and the command you ran)

We have script1.sql and script2.sql and perform migrate:

  1. At once putting two scripts together
  2. Separately (two Flyway runs)
    The output is different.

We migrate scripts using Flyway API from Java.

Explanation:
Recently we found out the issue that our scripts have different output (comparing databases with Red Gate Compare tool) depending on whether we apply all of them at once or whether first pack is applied after another (that may happen between releases). The core reason was that one script changed some session value like: SET ANSI_NULL OFF while not setting it back.
Why we had different output is because ANSI_NULL is set to ON by default whenever new db connection is opened.

Need to say I understand the gains behind opening connection only once (it may speed up things).
In our case we develop lots of scripts in a single release and it's really hard to track such session values changes in a single script.

It's ok to have current behavior but would be nice to have connection management configurable: so we have an option to have connections to be opened/closed for everything migration.

What did you expect to see?

Result is the same no matter how often we run Flyway.
(Some way to reset session values between migrations. One way to handle it by connections to be recreated.)

As we migrate scripts using Flyway API from Java then probably we should use Flyway Callbacks somehow to handle this case and reset session values by ourselves. Please advise.

What did you see instead?

One connection is opened and is used for all migrations. Some migrations affect other migrations
(No way to control the connections between migrations applied.)

@axelfontaine axelfontaine added the t: bug label Mar 22, 2016
@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Mar 22, 2016

Thanks. Your best bet at the moment is indeed to use a beforeEachMigrate callback to ensure the state of the connection is properly reset. Alternatively you can also make sure individual migrations don't have side effects.

I am not sure yet, whether it makes sense to fix this or not as Flyway is often used with connection pools. Also people use the callbacks to achieve exactly the opposite: set the one connection to a well-known state.

All in all this is a behavior change that would probably have to wait until at least the next major release.

@ayratn
Copy link
Author

@ayratn ayratn commented Mar 22, 2016

Firs of all thank you for quick response and detailed explanation.

Alternatively you can also make sure individual migrations don't have side effects.

Could you please explain.
Do you mean it should be done manually during script review process or can it be automated?
Initially I had an idea to use afterEachMigrate to validate the side effect there but seems it's too late to react there - we can't stop migrate process in the middle using callback mechanism, right?
We can't do so in beforeEachMigrate either, please confirm.

We are also searching for the best solution and in our case seems beforeEachMigrate callback suits the best where I thought we needed to create connection.prepareteStatement(...) to call sql script that set some values to default. But seems you have better idea.

I am not sure yet, whether it makes sense to fix this or not as Flyway is often used with connection pools.

That's why first thought was about moving it to parameters so default behavior would stay the same for back compatibility.

@axelfontaine axelfontaine added this to the Flyway 5.1.0 milestone Nov 26, 2017
axelfontaine added a commit to flyway/flywaydb.org that referenced this issue Dec 18, 2017
@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Dec 18, 2017

This has now been fixed for ANSI_NULLS. Please open a new issue if you are affected by additional parameters as well, and include those in the issue.

dohrayme pushed a commit to dohrayme/flyway that referenced this issue Feb 3, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants
You can’t perform that action at this time.