5.1.0 onwards not supporting SQL single-line comment starting with "--" but not followed by a whitespace

vxavictor513 commented 6 years ago

Which version and edition of Flyway are you using?

Flyway Community Edition, version 5.1.4

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

Java API

Which database are you using (type & version)?

MySQL 5.7

Which operating system are you using?

Mac OS

What did you do?

I upgraded from Flyway 5.0.7 to 5.1.4 for my Spring Boot application, then tried to run a unit test but it failed at DB migration step.

My Flyway script contains a single-line comment starting with "--" but not followed by a whitespace.

--Hello
CREATE TABLE `products` (
  `product_code` VARCHAR(10) NOT NULL,
  `product_type` VARCHAR(1) NOT NULL,
  PRIMARY KEY (`product_code`));

What did you expect to see?

DB Migration to be successful, as it was in 5.0.7.

What did you see instead?

Caused by: org.flywaydb.core.internal.sqlscript.FlywaySqlScriptException: 
Migration V1__create_products.sql failed
----------------------------------------
SQL State  : 42000
Error Code : 1064
Message    : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '--Hello
CREATE TABLE `products` (
  `product_code` VARCHAR(10) NOT NULL,
  `prod' at line 1
Location   : db/migration/V1__create_products.sql (/Users/wai_loon/IdeaProjects/deposits/target/classes/db/migration/V1__create_products.sql)
Line       : 1
Statement  : --Hello
CREATE TABLE `products` (
  `product_code` VARCHAR(10) NOT NULL,
  `product_type` VARCHAR(1) NOT NULL,
  PRIMARY KEY (`product_code`))

    at org.flywaydb.core.internal.database.ExecutableSqlScript.handleException(ExecutableSqlScript.java:331)
    at org.flywaydb.core.internal.database.ExecutableSqlScript.executeStatement(ExecutableSqlScript.java:301)
    at org.flywaydb.core.internal.database.ExecutableSqlScript.execute(ExecutableSqlScript.java:243)
    at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.execute(SqlMigrationExecutor.java:91)
    at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:352)
    ... 59 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '--Hello
CREATE TABLE `products` (
  `product_code` VARCHAR(10) NOT NULL,
  `prod' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.Util.getInstance(Util.java:408)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3976)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3912)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2482)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2440)
    at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:845)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:745)
    at org.flywaydb.core.internal.util.jdbc.JdbcTemplate.executeStatement(JdbcTemplate.java:244)
    at org.flywaydb.core.internal.database.StandardSqlStatement.execute(StandardSqlStatement.java:44)
    at org.flywaydb.core.internal.database.ExecutableSqlScript.executeStatement(ExecutableSqlScript.java:280)
    ... 62 more

axelfontaine commented 6 years ago

While this seems like a bug, the syntax you used was never valid in the first place. See https://dev.mysql.com/doc/refman/5.7/en/comments.html and https://dev.mysql.com/doc/refman/5.7/en/ansi-diff-comments.html

This invalid syntax worked by chance in the past as Flyway skipped comments between statements. Starting with Flyway 5.1 this is no longer the case in order to preserve header comments on stored procedures among other things.

This is a bug in your scripts and not a Flyway defect.

pssoft7 commented 6 years ago

"This syntax differs slightly from standard SQL comment syntax, as discussed in Section 1.8.2.4, “'--' as the Start of a Comment”."

With this in mind, can we have option to allow '--' without space?

flyway / flyway