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

MySQL: Reset SQL_SAFE_UPDATES and FOREIGN_KEY_CHECKS after each migration #2341

Closed
jfly opened this issue Apr 1, 2019 · 4 comments

Comments

Projects
None yet
2 participants
@jfly
Copy link

commented Apr 1, 2019

(Sorry if this isn't the right place for feature requests.)

We use Flyway 3.0 for running migrations against our MySQL database. Sometimes our migrations leave the database connection/session in a weird state (such as turning on safe mode). We have noticed that if flyway runs multiple migrations, it runs them all in the same connection/session, and weird state from a past migration can cause future migrations to fail. However, if we run those migrations in separate invocations of flyway, then everything is fine, because flyway creates a fresh connection to the database.

#1250 is a very similar issue, which was fixed in 7aec656 by having flyway track the original value of ANSI_NULLS and then to restore the value after each migration. That fix feels pretty specific to me. If it were instead possible to run each migration in its own fresh connection, then every session specific setting would get reset.

@axelfontaine

This comment has been minimized.

Copy link
Contributor

commented Apr 8, 2019

What is your exact use case? Is it just SQL_SAFE_UPDATES or are there more? And if yes what are they?

Regarding having a separate connection per migration:

Short story: it's not as trivial as it seems

Long story: For certain databases like SQL Server this wouldn't be possible as certain commands expect no other connection to be active. That being said I am not aware of any similar restrictions for MySQL. But even if we could manage that, we would still have to deal with connection pools intercepting the Connection.close() method...

@jfly

This comment has been minimized.

Copy link
Author

commented Apr 8, 2019

SQL_SAFE_UPDATES is one setting I know we've been bit by before. I'm not sure if there are others. I will reach out to the team and report back if there are others.

Re: SQL Server, I don't think I see why creating a new connection for each migration would cause simultaneous connections?

@jfly

This comment has been minimized.

Copy link
Author

commented Apr 8, 2019

Update: Other settings we have played with include FOREIGN_KEY_CHECKS and AUTOCOMMIT

@axelfontaine axelfontaine changed the title Is it possible to force flyway to use a new connection for each migration? MySQL: Reset SQL_SAFE_UPDATES and FOREIGN_KEY_CHECKS after each migration Apr 17, 2019

@axelfontaine axelfontaine added this to the Flyway 6.0.0 milestone Apr 17, 2019

axelfontaine pushed a commit to flyway/flywaydb.org that referenced this issue Apr 17, 2019

@axelfontaine

This comment has been minimized.

Copy link
Contributor

commented Apr 17, 2019

Autocommit was already being reset. Flyway now also resets SQL_SAFE_UPDATES and FOREIGN_KEY_CHECKS. Fixed.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.