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 clean doesn't remove partition schemes and functions #2447

Closed
AigleeK opened this issue Jul 25, 2019 · 14 comments
Closed

Flyway clean doesn't remove partition schemes and functions #2447

AigleeK opened this issue Jul 25, 2019 · 14 comments

Comments

@AigleeK
Copy link

@AigleeK AigleeK commented Jul 25, 2019

Which version and edition of Flyway are you using?

Flyway 5 and Flyway 6 Beta

If this is not the latest version, can you reproduce the issue with the latest one as well?

(Many bugs are fixed in newer releases and upgrading will often resolve the issue)
Yes

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

Command-line

Which database are you using (type & version)?

SQL Server 2017

Which operating system are you using?

Windows 10

What did you do?

(Please include the content causing the issue, any relevant configuration settings, the SQL statement that failed (if relevant) and the command you ran.)
I partitioned a table in the DB and ran flyway clean so I could recreate my DB and recreate the partition

What did you expect to see?

Partition scheme and partition functions should not exist anymore in the DB

What did you see instead?

Partition scheme and partition functions were still in the DB. As a workaround I added the following code to afterClean.sql callback to remove them:

CREATE TABLE #Commands (Command VARCHAR(MAX));

INSERT #Commands
SELECT 
	'DROP PARTITION SCHEME ' + QUOTENAME(name) + '; '
FROM 
	sys.partition_schemes;

INSERT #Commands
SELECT 
	'DROP PARTITION FUNCTION ' + QUOTENAME(name) + '; '
FROM 
	sys.partition_functions;

DECLARE @Command VARCHAR(MAX);

DECLARE curCommand CURSOR FOR SELECT Command FROM #Commands;
OPEN curCommand;

FETCH NEXT FROM curCommand INTO @Command;

WHILE @@FETCH_STATUS =0
BEGIN
    EXEC(@Command);
    FETCH NEXT FROM curCommand INTO @Command;
END;

CLOSE curCommand;
DEALLOCATE curCommand;

DROP TABLE #Commands;
juliahayward added a commit to flyway/flywaydb.org that referenced this issue Jul 30, 2019
@juliahayward juliahayward added this to the Flyway 6.0.0 milestone Jul 30, 2019
@juliahayward
Copy link
Member

@juliahayward juliahayward commented Jul 30, 2019

Many thanks for the report - we have done a fix so with v6.0 you will not need this workround.

Loading

@AigleeK
Copy link
Author

@AigleeK AigleeK commented Nov 11, 2019

I just tried it again with Flyway 6.0.7 and the issue now is that it tries to clean the partition scheme before the rest of the cleanup so it fails because the partition scheme is being used. It should remove them after all tables are dropped. If it makes a difference, we have multiple schemas in our DB and changing the order of the schemas for cleanups doesn't make a difference in this issue.

ERROR:
Unable to clean schema [dbo]

SQL State : S0001
Error Code : 7717
Message : The partition scheme "PS_framework_log_raw_message_InsertTimestamp_DAY" is currently being used to partition one or more tables.

Loading

@juliahayward
Copy link
Member

@juliahayward juliahayward commented Nov 12, 2019

I can't reproduce this in a single schema. Do you have the case where the partition scheme exists in one schema, but is used to partition a table in another? (If so, that is a rather more complex fix)

Loading

@juliahayward juliahayward removed this from the Flyway 6.0.0 milestone Nov 12, 2019
@juliahayward juliahayward reopened this Nov 12, 2019
@AigleeK
Copy link
Author

@AigleeK AigleeK commented Nov 19, 2019

Partition functions and schemes do not get created on schemas, they exists on the storage area of SQL Server. The table is the only one on a schema

--Create a schema
create schema Test;
GO

--Create a table on the schema
create table Test.Test_Table (id int not null, create_date datetime not null);
GO

--Insert some data (not really needed)
insert into Test.Test_Table (id, create_date) values (1, '2019-11-05 00:00:00.000'), (2, '2019-11-06 00:00:00.000');
GO

--Create the partition function and scheme
CREATE PARTITION FUNCTION PF_test_test_table_create_date_DAY(datetime) AS RANGE RIGHT FOR VALUES ('2019-11-05 00:00:00.000','2019-11-06 00:00:00.000');
CREATE PARTITION SCHEME PS_test_test_table_create_date_DAY AS PARTITION PF_test_test_table_create_date_DAY ALL TO ([PRIMARY]);
GO

--Assing the partition scheme to the table
ALTER TABLE Test.Test_Table ADD CONSTRAINT PK_Test_Table PRIMARY KEY CLUSTERED (id, create_date)  ON PS_test_test_table_create_date_DAY (create_date);

--Run flyway clean on the schema. It will fail

Loading

@juliahayward
Copy link
Member

@juliahayward juliahayward commented Nov 21, 2019

With the above example, what is flyway.schemas set to in your configuration?

Loading

@AigleeK
Copy link
Author

@AigleeK AigleeK commented Nov 21, 2019

We have it set to dbo, Test
Since the flyway table gets created in the first schema of the list and we dont want it in the application schemas. If I change the order to Test, dbo it does work (hadn't tried until you asked), so the only thing I can think of right now is maybe creating a schema for the history table, but we already have sites in production that have used it so we could cause issues by now trying to move the table.

Loading

@juliahayward
Copy link
Member

@juliahayward juliahayward commented Nov 21, 2019

OK, I understand - it cleans dbo first, which tries to drop the partition objects, but they depend on Test, rather than waiting for all schemas to be cleaned.

Loading

@juliahayward
Copy link
Member

@juliahayward juliahayward commented Nov 26, 2019

A partial solution - in 6.1.0 we're splitting the configuration so that you can specify schemas as before, and all schemas are cleaned in the order of the list, but also defaultSchema which is where the history table will live. This should allow your use case by specifying:

  • schemas=Test,dbo
  • defaultSchema=dbo

Let us know whether this solves your problem!

Loading

@AigleeK
Copy link
Author

@AigleeK AigleeK commented Nov 26, 2019

Yes!! that would for sure help! :D Thanks

Loading

@alextercete alextercete added this to the Flyway 6.x milestone Dec 16, 2019
@MikielAgutu MikielAgutu removed this from the Flyway 6.x milestone Jan 31, 2020
@MikielAgutu MikielAgutu added this to the On the Radar milestone Jan 31, 2020
dohrayme pushed a commit to dohrayme/flyway that referenced this issue Feb 3, 2020
@AigleeK
Copy link
Author

@AigleeK AigleeK commented Feb 25, 2020

I was just trying to "new" default schema functionality to see if we could remove the workaround we have for partitioning, but we found we still have a problem. When I tested the solution, I did it with dbo and one more schema but with more than those 2 schemas we still have issues, so we tried a DryRun and got the following:

DROP TABLE [Test].[Test2].[Test_Table]
GO
DROP PARTITION SCHEME [PS_test_test_table_create_date_DAY]
GO
DROP PARTITION FUNCTION [PF_test_test_table_create_date_DAY]
GO
DROP TABLE [Test].[Test].[junk_table]
GO
DROP PARTITION SCHEME [PS_test_test_table_create_date_DAY]
GO
DROP PARTITION FUNCTION [PF_test_test_table_create_date_DAY]
GO
DROP TABLE [Test].[dbo].[another_junk_table]
GO
DROP PARTITION SCHEME [PS_test_test_table_create_date_DAY]
GO
DROP PARTITION FUNCTION [PF_test_test_table_create_date_DAY]

Basically flyway clean is trying to drop non-schema objects after each and every single schema, which creates this issue. It should delete non-schema objects after all schemas have been cleaned.

Thanks

Loading

@juliahayward
Copy link
Member

@juliahayward juliahayward commented Feb 26, 2020

If I've understood correctly the problem is only with clean dry runs? The problem appears to be that having scripted the clean for one schema, the partition functions (that would have been dropped in an actual clean) are still there and are re-scripted.

I believe the right solution is a structural change to Flyway to add at least one clean() method to Database - possibly a pre-schemas clean and post-schemas clean to handle both orders of dependencies - rather than "is this a dry run?" flags seeping in everywhere. Hopefully this will be in in time for 6.3 and it might resolve other issues as a bonus...

Loading

@juliahayward
Copy link
Member

@juliahayward juliahayward commented Feb 26, 2020

Actually, this opens up a question - if you only select schema Test to clean, should database level objects be cleaned up at all (that is, should they be treated as part of dbo anyway)? In the longer term, should we provide an option to toggle the cleaning behaviour?

Loading

@AigleeK
Copy link
Author

@AigleeK AigleeK commented Feb 26, 2020

The problem is with clean in general, not just dry runs, it's just easy to see the issue with dry runs :)

I agree with having the clean method for other database objects either at the end or configurable.

I also find interesting the issue of cleaning the objects that are not part of a schema or not. I feel it should in this particular case, but I could see a case where people don't want that, so a toggle sounds like a great idea.

For now we still left the workaround to remove partitioning in the beforeClean callback, but thought I would add my comment here since we thought that maybe moving dbo to the end of the list would fix this issue.

Loading

@MikielAgutu MikielAgutu removed this from the On the Radar milestone Mar 13, 2020
@MikielAgutu MikielAgutu added this to the Flyway 6.3.1 milestone Mar 13, 2020
@juliahayward
Copy link
Member

@juliahayward juliahayward commented Mar 13, 2020

Added to 6.3, both a pre-schemas and post-schemas database level cleaning method. This only affects partition functions and schemes in SQL Server at the moment.

Loading

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
4 participants