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

Unable to Clean DB2 LUW using Modules #2832

Closed
monmallonga opened this issue May 27, 2020 · 2 comments
Closed

Unable to Clean DB2 LUW using Modules #2832

monmallonga opened this issue May 27, 2020 · 2 comments

Comments

@monmallonga
Copy link

@monmallonga monmallonga commented May 27, 2020

Which version and edition of Flyway are you using?

6.4.2

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

Gradle Plugin (Gradle version 5.6.1)

Which database are you using (type & version)?

IBM DB2 LUW 11.1 (Express C)

Which operating system are you using?

Windows 10 Pro

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 created a custom task flywayCleanTestDB (Note: Also happens to the default database, I just can not use the flywayClean command since I'am currently using the database for my dev work)

I ran the flywayCleanTestDB to refresh my database.
Currently deployed in the database are modules. We are currently extensively using Modules to group procedures/functions/types together.

-- gradle task

task flywayCleanTestDB(type: org.flywaydb.gradle.task.FlywayCleanTask) {
    url = 'jdbc:db2://TESTDB:50000/QA'
 	user = <USER>
	password = <PW>
    schemas = ['TFS']
}

-- Sample Module

SET CURRENT SCHEMA TFS@
CREATE MODULE TEST_MODULE@
ALTER MODULE TEST_MODULE 
PUBLISH PROCEDURE SP ()
	DYNAMIC RESULT SETS 1
P1: BEGIN
	-- #######################################################################
	-- # Returns all tables
	-- #######################################################################
	-- Declare cursor
	DECLARE cursor1 CURSOR WITH RETURN for
		SELECT NAME FROM SYSIBM.SYSTABLES 
			ORDER BY NAME;

	-- Cursor left open for client application
	OPEN cursor1;
END P1@
What did you expect to see?

I expected that the database would be cleaned.

What did you see instead?

Task :flywayCleanTestDB FAILED

FAILURE: Build failed with an exception.

  • What went wrong:
Execution failed for task ':flywayCleanTestDB'.
> Error occurred while executing flywayCleanTestDB

  Unable to clean schema "TFS"
  ----------------------------
  SQL State  : 42704
  Error Code : -204
  Message    : TFS.SQL200527150647939

  TFS.SQL200527150647939

* Try:
Run with --info or --debug option to get more log output. Run with --scan to get full insights.

* Exception is:
org.gradle.api.tasks.TaskExecutionException: Execution failed for task ':flywayCleanTestDB'.
        at org.gradle.api.internal.tasks.execution.ExecuteActionsTaskExecuter$3.accept(ExecuteActionsTaskExecuter.java:166)
        at org.gradle.api.internal.tasks.execution.ExecuteActionsTaskExecuter$3.accept(ExecuteActionsTaskExecuter.java:163)
        at org.gradle.internal.Try$Failure.ifSuccessfulOrElse(Try.java:191)
        at org.gradle.api.internal.tasks.execution.ExecuteActionsTaskExecuter.execute(ExecuteActionsTaskExecuter.java:156)
        at org.gradle.api.internal.tasks.execution.ValidatingTaskExecuter.execute(ValidatingTaskExecuter.java:62)
        at org.gradle.api.internal.tasks.execution.SkipEmptySourceFilesTaskExecuter.execute(SkipEmptySourceFilesTaskExecuter.java:108)
        at org.gradle.api.internal.tasks.execution.ResolveBeforeExecutionOutputsTaskExecuter.execute(ResolveBeforeExecutionOutputsTaskExecuter.java:67)
        at org.gradle.api.internal.tasks.execution.ResolveAfterPreviousExecutionStateTaskExecuter.execute(ResolveAfterPreviousExecutionStateTaskExecuter.java:46)
        at org.gradle.api.internal.tasks.execution.CleanupStaleOutputsExecuter.execute(CleanupStaleOutputsExecuter.java:94)
        at org.gradle.api.internal.tasks.execution.FinalizePropertiesTaskExecuter.execute(FinalizePropertiesTaskExecuter.java:46)
        at org.gradle.api.internal.tasks.execution.ResolveTaskExecutionModeExecuter.execute(ResolveTaskExecutionModeExecuter.java:95)
        at org.gradle.api.internal.tasks.execution.SkipTaskWithNoActionsExecuter.execute(SkipTaskWithNoActionsExecuter.java:57)
        at org.gradle.api.internal.tasks.execution.SkipOnlyIfTaskExecuter.execute(SkipOnlyIfTaskExecuter.java:56)
        at org.gradle.api.internal.tasks.execution.CatchExceptionTaskExecuter.execute(CatchExceptionTaskExecuter.java:36)
        at org.gradle.api.internal.tasks.execution.EventFiringTaskExecuter$1.executeTask(EventFiringTaskExecuter.java:77)
        at org.gradle.api.internal.tasks.execution.EventFiringTaskExecuter$1.call(EventFiringTaskExecuter.java:55)
        at org.gradle.api.internal.tasks.execution.EventFiringTaskExecuter$1.call(EventFiringTaskExecuter.java:52)
        at org.gradle.internal.operations.DefaultBuildOperationExecutor$CallableBuildOperationWorker.execute(DefaultBuildOperationExecutor.java:416)
        at org.gradle.internal.operations.DefaultBuildOperationExecutor$CallableBuildOperationWorker.execute(DefaultBuildOperationExecutor.java:406)
        at org.gradle.internal.operations.DefaultBuildOperationExecutor$1.execute(DefaultBuildOperationExecutor.java:165)
        at org.gradle.internal.operations.DefaultBuildOperationExecutor.execute(DefaultBuildOperationExecutor.java:250)
        at org.gradle.internal.operations.DefaultBuildOperationExecutor.execute(DefaultBuildOperationExecutor.java:158)
        at org.gradle.internal.operations.DefaultBuildOperationExecutor.call(DefaultBuildOperationExecutor.java:102)
        at org.gradle.internal.operations.DelegatingBuildOperationExecutor.call(DelegatingBuildOperationExecutor.java:36)
        at org.gradle.api.internal.tasks.execution.EventFiringTaskExecuter.execute(EventFiringTaskExecuter.java:52)
        at org.gradle.execution.plan.LocalTaskNodeExecutor.execute(LocalTaskNodeExecutor.java:43)
        at org.gradle.execution.taskgraph.DefaultTaskExecutionGraph$InvokeNodeExecutorsAction.execute(DefaultTaskExecutionGraph.java:355)
        at org.gradle.execution.taskgraph.DefaultTaskExecutionGraph$InvokeNodeExecutorsAction.execute(DefaultTaskExecutionGraph.java:343)
        at org.gradle.execution.taskgraph.DefaultTaskExecutionGraph$BuildOperationAwareExecutionAction.execute(DefaultTaskExecutionGraph.java:336)
        at org.gradle.execution.taskgraph.DefaultTaskExecutionGraph$BuildOperationAwareExecutionAction.execute(DefaultTaskExecutionGraph.java:322)
        at org.gradle.execution.plan.DefaultPlanExecutor$ExecutorWorker$1.execute(DefaultPlanExecutor.java:134)
        at org.gradle.execution.plan.DefaultPlanExecutor$ExecutorWorker$1.execute(DefaultPlanExecutor.java:129)
        at org.gradle.execution.plan.DefaultPlanExecutor$ExecutorWorker.execute(DefaultPlanExecutor.java:202)
        at org.gradle.execution.plan.DefaultPlanExecutor$ExecutorWorker.executeNextNode(DefaultPlanExecutor.java:193)
        at org.gradle.execution.plan.DefaultPlanExecutor$ExecutorWorker.run(DefaultPlanExecutor.java:129)
        at org.gradle.internal.concurrent.ExecutorPolicy$CatchAndRecordFailures.onExecute(ExecutorPolicy.java:64)
        at org.gradle.internal.concurrent.ManagedExecutorImpl$1.run(ManagedExecutorImpl.java:48)
        at org.gradle.internal.concurrent.ThreadFactoryImpl$ManagedThreadRunnable.run(ThreadFactoryImpl.java:56)
Caused by: org.flywaydb.core.api.FlywayException: Error occurred while executing flywayCleanTestDB

Unable to clean schema "TFS"
----------------------------
SQL State  : 42704
Error Code : -204
Message    : TFS.SQL200527150647939

TFS.SQL200527150647939
        at org.flywaydb.gradle.task.AbstractFlywayTask.runTask(AbstractFlywayTask.java:550)
        at org.gradle.internal.reflect.JavaMethod.invoke(JavaMethod.java:103)
        at org.gradle.api.internal.project.taskfactory.StandardTaskAction.doExecute(StandardTaskAction.java:49)
        at org.gradle.api.internal.project.taskfactory.StandardTaskAction.execute(StandardTaskAction.java:42)
        at org.gradle.api.internal.project.taskfactory.StandardTaskAction.execute(StandardTaskAction.java:28)
        at org.gradle.api.internal.AbstractTask$TaskActionWrapper.execute(AbstractTask.java:717)
        at org.gradle.api.internal.AbstractTask$TaskActionWrapper.execute(AbstractTask.java:684)
        at org.gradle.api.internal.tasks.execution.ExecuteActionsTaskExecuter$5.run(ExecuteActionsTaskExecuter.java:476)
        at org.gradle.internal.operations.DefaultBuildOperationExecutor$RunnableBuildOperationWorker.execute(DefaultBuildOperationExecutor.java:402)
        at org.gradle.internal.operations.DefaultBuildOperationExecutor$RunnableBuildOperationWorker.execute(DefaultBuildOperationExecutor.java:394)
        at org.gradle.internal.operations.DefaultBuildOperationExecutor$1.execute(DefaultBuildOperationExecutor.java:165)
        at org.gradle.internal.operations.DefaultBuildOperationExecutor.execute(DefaultBuildOperationExecutor.java:250)
        at org.gradle.internal.operations.DefaultBuildOperationExecutor.execute(DefaultBuildOperationExecutor.java:158)
        at org.gradle.internal.operations.DefaultBuildOperationExecutor.run(DefaultBuildOperationExecutor.java:92)
        at org.gradle.internal.operations.DelegatingBuildOperationExecutor.run(DelegatingBuildOperationExecutor.java:31)
        at org.gradle.api.internal.tasks.execution.ExecuteActionsTaskExecuter.executeAction(ExecuteActionsTaskExecuter.java:461)
        at org.gradle.api.internal.tasks.execution.ExecuteActionsTaskExecuter.executeActions(ExecuteActionsTaskExecuter.java:444)
        at org.gradle.api.internal.tasks.execution.ExecuteActionsTaskExecuter.access$200(ExecuteActionsTaskExecuter.java:93)
        at org.gradle.api.internal.tasks.execution.ExecuteActionsTaskExecuter$TaskExecution.execute(ExecuteActionsTaskExecuter.java:237)
        at org.gradle.internal.execution.steps.ExecuteStep.lambda$execute$1(ExecuteStep.java:33)
        at org.gradle.internal.execution.steps.ExecuteStep.execute(ExecuteStep.java:33)
        at org.gradle.internal.execution.steps.ExecuteStep.execute(ExecuteStep.java:26)
        at org.gradle.internal.execution.steps.CleanupOutputsStep.execute(CleanupOutputsStep.java:58)
        at org.gradle.internal.execution.steps.CleanupOutputsStep.execute(CleanupOutputsStep.java:35)
        at org.gradle.internal.execution.steps.ResolveInputChangesStep.execute(ResolveInputChangesStep.java:48)
        at org.gradle.internal.execution.steps.ResolveInputChangesStep.execute(ResolveInputChangesStep.java:33)
        at org.gradle.internal.execution.steps.CancelExecutionStep.execute(CancelExecutionStep.java:39)
        at org.gradle.internal.execution.steps.TimeoutStep.executeWithoutTimeout(TimeoutStep.java:73)
        at org.gradle.internal.execution.steps.TimeoutStep.execute(TimeoutStep.java:54)
        at org.gradle.internal.execution.steps.CatchExceptionStep.execute(CatchExceptionStep.java:35)
        at org.gradle.internal.execution.steps.CreateOutputsStep.execute(CreateOutputsStep.java:51)
        at org.gradle.internal.execution.steps.SnapshotOutputsStep.execute(SnapshotOutputsStep.java:45)
        at org.gradle.internal.execution.steps.SnapshotOutputsStep.execute(SnapshotOutputsStep.java:31)
        at org.gradle.internal.execution.steps.CacheStep.executeWithoutCache(CacheStep.java:208)
        at org.gradle.internal.execution.steps.CacheStep.execute(CacheStep.java:70)
        at org.gradle.internal.execution.steps.CacheStep.execute(CacheStep.java:45)
        at org.gradle.internal.execution.steps.BroadcastChangingOutputsStep.execute(BroadcastChangingOutputsStep.java:49)
        at org.gradle.internal.execution.steps.StoreSnapshotsStep.execute(StoreSnapshotsStep.java:43)
        at org.gradle.internal.execution.steps.StoreSnapshotsStep.execute(StoreSnapshotsStep.java:32)
        at org.gradle.internal.execution.steps.RecordOutputsStep.execute(RecordOutputsStep.java:38)
        at org.gradle.internal.execution.steps.RecordOutputsStep.execute(RecordOutputsStep.java:24)
        at org.gradle.internal.execution.steps.SkipUpToDateStep.executeBecause(SkipUpToDateStep.java:96)
        at org.gradle.internal.execution.steps.SkipUpToDateStep.lambda$execute$0(SkipUpToDateStep.java:89)
        at org.gradle.internal.execution.steps.SkipUpToDateStep.execute(SkipUpToDateStep.java:54)
        at org.gradle.internal.execution.steps.SkipUpToDateStep.execute(SkipUpToDateStep.java:38)
        at org.gradle.internal.execution.steps.ResolveChangesStep.execute(ResolveChangesStep.java:76)
        at org.gradle.internal.execution.steps.ResolveChangesStep.execute(ResolveChangesStep.java:37)
        at org.gradle.internal.execution.steps.legacy.MarkSnapshottingInputsFinishedStep.execute(MarkSnapshottingInputsFinishedStep.java:36)
        at org.gradle.internal.execution.steps.legacy.MarkSnapshottingInputsFinishedStep.execute(MarkSnapshottingInputsFinishedStep.java:26)
        at org.gradle.internal.execution.steps.ResolveCachingStateStep.execute(ResolveCachingStateStep.java:90)
        at org.gradle.internal.execution.steps.ResolveCachingStateStep.execute(ResolveCachingStateStep.java:48)
        at org.gradle.internal.execution.steps.CaptureStateBeforeExecutionStep.execute(CaptureStateBeforeExecutionStep.java:69)
        at org.gradle.internal.execution.steps.CaptureStateBeforeExecutionStep.execute(CaptureStateBeforeExecutionStep.java:47)
        at org.gradle.internal.execution.impl.DefaultWorkExecutor.execute(DefaultWorkExecutor.java:33)
        at org.gradle.api.internal.tasks.execution.ExecuteActionsTaskExecuter.execute(ExecuteActionsTaskExecuter.java:140)
        ... 34 more
Caused by: org.flywaydb.core.internal.exception.FlywaySqlException:
Unable to clean schema "TFS"
----------------------------
SQL State  : 42704
Error Code : -204
Message    : TFS.SQL200527150647939

        at org.flywaydb.core.internal.database.base.Schema.clean(Schema.java:156)
        at org.flywaydb.core.internal.command.DbClean$5.call(DbClean.java:240)
        at org.flywaydb.core.internal.command.DbClean$5.call(DbClean.java:237)
        at org.flywaydb.core.internal.jdbc.TransactionalExecutionTemplate.execute(TransactionalExecutionTemplate.java:66)
        at org.flywaydb.core.internal.command.DbClean.cleanSchema(DbClean.java:237)
        at org.flywaydb.core.internal.command.DbClean.clean(DbClean.java:121)
        at org.flywaydb.core.Flyway.doClean(Flyway.java:291)
        at org.flywaydb.core.Flyway.access$300(Flyway.java:74)
        at org.flywaydb.core.Flyway$3.execute(Flyway.java:309)
        at org.flywaydb.core.Flyway$3.execute(Flyway.java:302)
        at org.flywaydb.core.Flyway.execute(Flyway.java:530)
        at org.flywaydb.core.Flyway.clean(Flyway.java:302)
        at org.flywaydb.gradle.task.FlywayCleanTask.run(FlywayCleanTask.java:28)
        at org.flywaydb.gradle.task.AbstractFlywayTask.runTask(AbstractFlywayTask.java:546)
        ... 88 more
Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: TFS.SQL200527150647939
        at com.ibm.db2.jcc.am.b6.a(b6.java:810)
        at com.ibm.db2.jcc.am.b6.a(b6.java:66)
        at com.ibm.db2.jcc.am.b6.a(b6.java:140)
        at com.ibm.db2.jcc.am.k3.b(k3.java:2471)
        at com.ibm.db2.jcc.am.k3.c(k3.java:2452)
        at com.ibm.db2.jcc.t4.ab.n(ab.java:914)
        at com.ibm.db2.jcc.t4.ab.a(ab.java:120)
        at com.ibm.db2.jcc.t4.p.a(p.java:50)
        at com.ibm.db2.jcc.t4.aw.b(aw.java:220)
        at com.ibm.db2.jcc.am.k4.bm(k4.java:3599)
        at com.ibm.db2.jcc.am.k4.a(k4.java:4644)
        at com.ibm.db2.jcc.am.k4.b(k4.java:4182)
        at com.ibm.db2.jcc.am.k4.bg(k4.java:2880)
        at com.ibm.db2.jcc.am.k4.execute(k4.java:2855)
        at org.flywaydb.core.internal.jdbc.JdbcTemplate.execute(JdbcTemplate.java:224)
        at org.flywaydb.core.internal.database.db2.DB2Schema.doClean(DB2Schema.java:128)
        at org.flywaydb.core.internal.database.base.Schema.clean(Schema.java:154)
        ... 101 more

* Get more help at https://help.gradle.org

BUILD FAILED in 18s
1 actionable task: 1 executed

I tried testing if the Clean Task is working by dropping the Module manually and deploying a plain stored procedure instead, I was able to execute the task successfully.

I hope this DB2 feature can be supported, since we use this a lot and I want to use flyway Migration for our continuous integration tasks.

@MikielAgutu
Copy link
Contributor

@MikielAgutu MikielAgutu commented May 28, 2020

Fix will go out in the next release.

rgautomatedbuild pushed a commit that referenced this issue Jun 2, 2020
Lyeeedar added a commit that referenced this issue Jun 3, 2020
@monmallonga
Copy link
Author

@monmallonga monmallonga commented Jun 11, 2020

Hi I Just tested version 6.4.4, the issue still persists. It seems flyway assumes the members of the modules are standalone stored procedures and would try to drop them as they would a stored procedure causing the DB2 to return a -204 error.

I think the problem is the query to drop stored procedures also retrieves the procedure members of modules.

    private List<String> generateDropStatementsForProcedures() throws SQLException {
        String dropProcGenQuery =
                "select SPECIFICNAME from SYSCAT.ROUTINES where ROUTINETYPE='P' and ROUTINESCHEMA = '" + name + "'";
        return buildDropStatements("DROP SPECIFIC PROCEDURE", dropProcGenQuery);
    }

You can maybe try to add
and ROUTINEMODULENAME IS NULL

If you want to drop module members, syntax should be

ALTER MODULE <MODULE NAME> DROP PROCEDURE <PROC NAME>'

Can we re-open this issue or should i just create a new one.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants