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

flyway schema version table creation fails after SQL callback execution #1893

Closed
aebaugh opened this issue Jan 11, 2018 · 1 comment
Closed

flyway schema version table creation fails after SQL callback execution #1893

aebaugh opened this issue Jan 11, 2018 · 1 comment

Comments

@aebaugh
Copy link

@aebaugh aebaugh commented Jan 11, 2018

Flyway 5.0.5 Community command line (behavior also observed in 4.2.0)
Command-line client
Microsoft SQL Server 13.0 (AWS RDS Enterprise SQL Server)
Tested using Flyway 4.1.2, 4.2.0, 5.0.5 using jtds 1.3.1
repros running from OSX, Linux (assume platform independent)

We have a script that performs various tasks, but the problem can be reproduced using a script with one statement to switch database in a beforeMigrate.sql:

-- Switch to using master database
use master

Expected result, with flyway 4.1.2

$ flyway -url=jdbc:jtds:sqlserver://databaseurl:1433/tempdb -schemas=flyway -user=user -password=password -locations=filesystem:./migrations migrate
Flyway 4.1.2 by Boxfuse

Database: jdbc:jtds:sqlserver://databaseurl:1433/tempdb (Microsoft SQL Server 13.0)
SQLServer does not support setting the schema for the current session. Default schema NOT changed to flyway
Successfully validated 0 migrations (execution time 00:00.403s)
Executing SQL callback: beforeMigrate
WARNING: DB: Changed database context to 'master'. (SQL State: 01000 - Error Code: 5701)
Creating Metadata table: [flyway].[schema_version]
Current version of schema [flyway]: << Empty Schema >>
Schema [flyway] is up to date. No migration necessary.

Result with 5.0.5 (and 4.2.0)

$ flyway -url=jdbc:jtds:sqlserver://databaseurl:1433/tempdb -schemas=flyway -user=user -password=password -group=false -table=schema_version -locations=filesystem:./migrations  migrate
Flyway Community Edition 5.0.5 by Boxfuse

Database: jdbc:jtds:sqlserver://databaseurl:1433/tempdb (Microsoft SQL Server 13.0)
SQLServer does not support setting the schema for the current session. Default schema NOT changed to flyway
Successfully validated 0 migrations (execution time 00:00.634s)
Executing SQL callback: beforeMigrate
WARNING: DB: Changed database context to 'master'. (SQL State: 01000 - Error Code: 5701)
Creating Schema History table: [tempdb].[flyway].[schema_version]
ERROR: 
Script failed
-------------
SQL State  : 42000
Error Code : 262
Message    : CREATE TABLE permission denied in database 'master'.
Line       : 17
Statement  : CREATE TABLE [flyway].[schema_version] (
    [installed_rank] INT NOT NULL,
    [version] NVARCHAR(50),
    [description] NVARCHAR(200),
    [type] NVARCHAR(20) NOT NULL,
    [script] NVARCHAR(1000) NOT NULL,
    [checksum] INT,
    [installed_by] NVARCHAR(100) NOT NULL,
    [installed_on] DATETIME NOT NULL DEFAULT GETDATE(),
    [execution_time] INT NOT NULL,
    [success] BIT NOT NULL
);
ALTER TABLE [flyway].[schema_version] ADD CONSTRAINT [schema_version_pk] PRIMARY KEY ([installed_rank]);

CREATE INDEX [schema_version_s_idx] ON [flyway].[schema_version] ([success]);

Appears the activities within the callback affect ability to access intended flyway schema and version table. Using user databases other than master returns similar but different errors, for example:

Error Code : 2760
Message    : The specified schema name "flyway" either does not exist or you do not have permission to use it.

This may be related to running within a transaction scope #1707 ? Note I can manually run the above schema_version CREATE statements if I qualify them with the database to use as indicated in the log output message, such as:

CREATE TABLE [tempdb].[flyway].[schema_version]
...
axelfontaine added a commit to flyway/flywaydb.org that referenced this issue Jan 16, 2018
@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Jan 16, 2018

Thanks for the detailed analysis. This was a great help in fixing this.

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.