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

SQL Server: ALTER DATABASE ... SET SINGLE_USER not supported #1426

Closed
jdoose opened this issue Sep 12, 2016 · 5 comments
Closed

SQL Server: ALTER DATABASE ... SET SINGLE_USER not supported #1426

jdoose opened this issue Sep 12, 2016 · 5 comments

Comments

@jdoose
Copy link

@jdoose jdoose commented Sep 12, 2016

What version of Flyway are you using?

4.0.3

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

Command-Line

What database are you using (type & version)?

MS SQL Server 2014

What operating system are you using?

Windows 10

What did you do?

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

Running a single sql migration file, containing the command

ALTER DATABASE FlyWayDemo
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
GO
What did you expect to see?

Flyway completing with success

What did you see instead?

Flyway completing with error:

Database: jdbc:jtds:sqlserver://localhost:14330/FlyWayDemo (Microsoft SQL Server 12.0)
Successfully validated 1 migration (execution time 00:00.010s)
SQLServer does not support setting the schema for the current session. Default schema NOT changed to dbo
Creating Metadata table: [dbo].[schema_version]
Current version of schema [dbo]: << Empty Schema >>
Migrating schema [dbo] to version 1.0 - 1 0
WARNING: DB: Nonqualified transactions are being rolled back. Estimated rollback completion: 0%. (SQL State: 01000 - Error Code: 5060)
WARNING: DB: Nonqualified transactions are being rolled back. Estimated rollback completion: 100%. (SQL State: 01000 - Error Code: 5060)
ERROR: Unable to commit transaction
java.sql.SQLException: Invalid state, the Connection object is closed.
        at net.sourceforge.jtds.jdbc.JtdsConnection.checkOpen(JtdsConnection.java:1744)
        at net.sourceforge.jtds.jdbc.JtdsConnection.commit(JtdsConnection.java:2149)
        at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:88)
        at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:173)
        at org.flywaydb.core.Flyway$1.execute(Flyway.java:959)
        at org.flywaydb.core.Flyway$1.execute(Flyway.java:917)
        at org.flywaydb.core.Flyway.execute(Flyway.java:1373)
        at org.flywaydb.core.Flyway.migrate(Flyway.java:917)
        at org.flywaydb.commandline.Main.executeOperation(Main.java:139)
        at org.flywaydb.commandline.Main.main(Main.java:102)
ERROR: Unable to restore autocommit to original value for connection
java.sql.SQLException: Invalid state, the Connection object is closed.
        at net.sourceforge.jtds.jdbc.JtdsConnection.checkOpen(JtdsConnection.java:1744)
        at net.sourceforge.jtds.jdbc.JtdsConnection.setAutoCommit(JtdsConnection.java:2276)
        at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:96)
        at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:173)
        at org.flywaydb.core.Flyway$1.execute(Flyway.java:959)
        at org.flywaydb.core.Flyway$1.execute(Flyway.java:917)
        at org.flywaydb.core.Flyway.execute(Flyway.java:1373)
        at org.flywaydb.core.Flyway.migrate(Flyway.java:917)
        at org.flywaydb.commandline.Main.executeOperation(Main.java:139)
        at org.flywaydb.commandline.Main.main(Main.java:102)
ERROR: Unable to check whether table [dbo].[schema_version] exists

My assumption:
Flyway holds the connection while executing the migration.
When migration sets database mode to single user the connection is dropped.

This is ok, I guess there is no way around it.
But the documentation should be accordingly.

@jdoose
Copy link
Author

@jdoose jdoose commented Sep 12, 2016

Or maybe Flyway is using more than one DB-Connection? That would lead to the same behaviour. In that case it would be a bug ;)
See also #1427

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Sep 14, 2016

Same comment for as for #1427

@brock-bouchard
Copy link

@brock-bouchard brock-bouchard commented Oct 5, 2017

In case this helps anyone:

I landed on this page trying to write a migration that uses ALTER DATABASE CURRENT SET_COMPATIBILITY_LEVEL = 120. I got around the problem ALTER DATABASE inside of a transaction by simply COMMITing the transaction first:

COMMIT
ALTER DATABASE CURRENT SET_COMPATIBILITY_LEVEL = 120

It seems to work for the happy path. To test the error path, I changed CURRENT to an invalid database name and flyway still failed without applying the migration.

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Nov 27, 2017

This has now been documented as a limitation (flyway-5.0.0 branch). Leaving this open until we come up with a proper fix.

@axelfontaine axelfontaine added this to the Flyway 5.1.0 milestone Nov 27, 2017
@axelfontaine axelfontaine changed the title Documentation: Limitation in SQL Server: ALTER DATABASE SINGLE_USER not supported SQL Server: ALTER DATABASE SINGLE_USER not supported Nov 27, 2017
@axelfontaine axelfontaine added the t: bug label Nov 27, 2017
@axelfontaine axelfontaine changed the title SQL Server: ALTER DATABASE SINGLE_USER not supported SQL Server: ALTER DATABASE ... SET SINGLE_USER not supported Nov 27, 2017
axelfontaine added a commit to flyway/flywaydb.org that referenced this issue Nov 30, 2017
@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Nov 30, 2017

We have found a way to fix this. Flyway now uses a single connection for SQL Server in combination with session-level application locks. On top of this we added automatic non-transactional statement detection to automatically run migrations containing ALTER DATABASE statements without a transaction.

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
3 participants
You can’t perform that action at this time.