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

Exception: Incomplete statement at line 1 col 1: DROP TABLE IF EXISTS #2748

Closed
sergey-selivanov opened this issue Mar 30, 2020 · 5 comments
Closed
Assignees
Milestone

Comments

@sergey-selivanov
Copy link

sergey-selivanov commented Mar 30, 2020

Which version and edition of Flyway are you using?

gradle plugin 6.3.2

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)

latest version, 6.3.2

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

gradle plugin

Which database are you using (type & version)?

DB2 11.5.0, latest jdbc driver 'com.ibm.db2:jcc:11.5.0.0'

Which operating system are you using?

Windows 8.1

What did you do?

There's exception when I try to apply sql script which starts right with DROP TABLE IF EXISTS:

drop table if exists Customer;
What did you expect to see?

correct migration.

What did you see instead?
> Task :db:flywayClean
> Task :db:flywayMigrate FAILED

FAILURE: Build failed with an exception.

* What went wrong:
Execution failed for task ':db:flywayMigrate'.
> Error occurred while executing flywayMigrate
  Unable to parse statement in D:\git\learn-ibmcloud\db\sql-migrations\V1.0.0.0__create-tables.sql at line 1 col 1: Incomplete statement at line 1 col 1: drop table if exists Customer;
  
  create table Customer (
      id int not null primary key,
.....
City, ST 12345');

	at org.flywaydb.core.internal.parser.Parser.getNextStatement(Parser.java:287)
	at org.flywaydb.core.internal.parser.Parser.access$000(Parser.java:41)
	at org.flywaydb.core.internal.parser.Parser$ParserSqlStatementIterator.<init>(Parser.java:690)
	at org.flywaydb.core.internal.parser.Parser.parse(Parser.java:109)
	at org.flywaydb.core.internal.sqlscript.ParserSqlScript.parse(ParserSqlScript.java:79)
	at org.flywaydb.core.internal.sqlscript.ParserSqlScript.validate(ParserSqlScript.java:127)
	at org.flywaydb.core.internal.sqlscript.ParserSqlScript.executeInTransaction(ParserSqlScript.java:196)
.....
	at org.flywaydb.gradle.task.AbstractFlywayTask.runTask(AbstractFlywayTask.java:546)
	... 95 more
Caused by: org.flywaydb.core.api.FlywayException: Incomplete statement at line 1 col 1: drop table if exists Customer;

create table Customer (
    id int not null primary key,
.....
City, ST 12345');

	at org.flywaydb.core.internal.parser.Parser.getNextStatement(Parser.java:223)
	... 121 more
@Lyeeedar
Copy link
Contributor

Lyeeedar commented Mar 31, 2020

Could you share the entire script that failed to parse (With sensitive information removed of course)?

@sergey-selivanov
Copy link
Author

sergey-selivanov commented Mar 31, 2020

Sure, please see full script here:

https://github.com/sergey-selivanov/learn-ibmcloud/blob/develop/db/sql-migrations/V1.0.0.0__create-tables.sql

and full run log with stack traces:

https://github.com/sergey-selivanov/learn-ibmcloud/blob/develop/log.txt

the whole project with Gradle scripts if needed:
https://github.com/sergey-selivanov/learn-ibmcloud/tree/develop

Will keep log.txt for you until issue will be resolved. Note 'develop' branch.

Looks like it doesn't matter whether 'drop table is exists' is on 1st line or column or there are spaces and/or blank lines before and after this statement, still gives an error.

@Lyeeedar Lyeeedar self-assigned this Mar 31, 2020
@Lyeeedar
Copy link
Contributor

Lyeeedar commented Mar 31, 2020

After a bit of googling it looks like DB2 doesn't support the DROP ... IF EXISTS statement. https://dba.stackexchange.com/a/9536

@sergey-selivanov
Copy link
Author

sergey-selivanov commented Mar 31, 2020

That answer is from 2011. IBM docs say that 'DROP TABLE IF EXISTS' is supported both on IBM Cloud and standalone 11.5 server (which I'm trying both):
https://www.ibm.com/support/knowledgecenter/SS6NHC/com.ibm.swg.im.dashdb.sql.ref.doc/doc/r0000945.html
https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000945.html?pos=2

'DROP TABLE IF EXISTS' definitely works for me at 11.5 server via db2 command line and through jdbc connection using SQuirrelSQL, and looks like not supported on my IBM Cloud instance though doc says it should. My full script referenced above applies to 11.5 without errors, and via Flyway equally fails both on Cloud and on local 11.5 server with the same exception.
Anyway, I see that exception is at flyway parser code, does that happens before anything is sent to db2, or is it reply from server?

at org.flywaydb.core.internal.parser.Parser.getNextStatement(Parser.java:223)

IBM Cloud Db2 replies with following on manual 'DROP TABLE IF EXISTS':

Error: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=exists;drop table if ;NPS_DOUBLE_DOT, DRIVER=4.26.14
SQLState:  42601
ErrorCode: -104

or via web interface:

Error message
An unexpected token "exists" was found following "drop table if ".  Expected tokens may include:  "NPS_DOUBLE_DOT".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.25.1301

not something like 'Unable to parse statement in ...' or 'Incomplete statement at line 1 col 1'

@sergey-selivanov
Copy link
Author

sergey-selivanov commented Mar 31, 2020

From jdbc metadata, Db2 in my cloud instance reports version 11.1, according to docs DB2 11.1 does not support 'drop table if exists' yet

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

3 participants