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 does not reset the current schema between migrations #1108

Closed
JGMM opened this issue Oct 19, 2015 · 1 comment
Closed

Flyway does not reset the current schema between migrations #1108

JGMM opened this issue Oct 19, 2015 · 1 comment

Comments

@JGMM
Copy link

@JGMM JGMM commented Oct 19, 2015

If a migration includes a statement like:

alter session set current_schema = <schema>;

... the current schema of the database connection is not reset by flyway before subsequent migrations are executed, potentially leading to unintended consequences, such as creating tables in the wrong schema.

See the end of this description for example migrations.

Note that the end result of these two sets of commands would produce different results:

@rem Re-use db connection for migration 3.
flyway init
flyway migrate 

vs:

@rem Use a new db connection for migration 3, masking the issue.
flyway init
flyway migrate -target=2
flyway migrate

This issue was initially found on an Oracle 11g database, using flyway versions 3.0 and 3.2.1.

Example migrations

V2

-- Change the default schema for this migration.

alter session set current_schema = schema_B;

-- This table should be created in schema B, due to the change in default schema.

create table schema_B_test
( X number
);

-- (Imagine a large amount of other statements that refer to schema_B here, none of which use an explicit schema name)

-- Verify the schema_B table has been created in the correct schema.

insert into schema_B.schema_B_test (X,Y) values (5,6);

V3

-- This table should be created in the default schema, even if run in the same batch as migration V2.

create table schema_A_test
( X number
);

-- Verify the schema_A table has been created in the correct schema.
-- Under flyway 3.0 and 3.2.1:
-- * This passes when V2 and V3 are executed in two different sessions.
-- * This fails  when V3 is executed in the same session as V2.

insert into schema_A.schema_A_test (X) values (5); 
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