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

DB2 truncate statement fails under Flyway 4.2.0 #1817

Closed
Rob34 opened this issue Nov 7, 2017 · 6 comments
Closed

DB2 truncate statement fails under Flyway 4.2.0 #1817

Rob34 opened this issue Nov 7, 2017 · 6 comments
Labels
Milestone

Comments

@Rob34
Copy link

@Rob34 Rob34 commented Nov 7, 2017

What version of Flyway are you using?

4.2.0

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)?

db2 10.5 fp6

What operating system are you using?

aix 7.1

What did you do?

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

Started the process of upgrading flyway from 2.3 to 4.2. However, the initial tests to clean and migrate existing scripts are failing when running truncate. The same scripts have been running successfully for a couple of years under 2.3.

The error indicates that the ddl is not being auto committed in 4.2.

What did you expect to see?

The truncate statement to execute successfully as it does in 2.3.

What did you see instead?

Migration fails at the truncate statement.

Attached is the snippet of debug out from both releases of flyway migrate.
Flyway_Issue.txt

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Nov 8, 2017

Please share the smallest possible SQL file which triggers this issue, so we can reproduce it.

@Rob34
Copy link
Author

@Rob34 Rob34 commented Nov 9, 2017

The attachment contains the contents of 3 files which shows each stage of the table set up. Flyway 4.2 fails at file 3 containing the truncate statement at the begging with the following error;

TRUNCATE TABLE "CTP_STATUS" IGNORE DELETE TRIGGERS IMMEDIATE
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0428N The SQL statement is only allowed as the first statement in a unit
of work. SQLSTATE=25001

I can replicate this using the db2 command line by setting the auto-commit option to off.

flyway_42_issue_example_ddl.txt

@Rob34
Copy link
Author

@Rob34 Rob34 commented Nov 9, 2017

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Nov 9, 2017

Did you set group to true in your Flyway config? If so, can you try again with false?

@Rob34
Copy link
Author

@Rob34 Rob34 commented Nov 9, 2017

It fails whether group is set to true or false. Out of interest I also tried mixed with both true and false, the result was the same.

@axelfontaine axelfontaine changed the title Original build script no longer executes under flyway command line version 4.2.0 DB2 truncate statement fails under Flyway 4.2.0 Nov 13, 2017
@axelfontaine axelfontaine added this to the Flyway 5.0.0 milestone Nov 13, 2017
axelfontaine added a commit to flyway/flywaydb.org that referenced this issue Nov 13, 2017
@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Nov 13, 2017

Fixed. Thanks for reporting and providing the sample SQL that was failing!

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.