I'm trying out Flyway with a simple example, and somehow fell into an error I don't understand. This is just a "hello world" type project.
This migration script fails as posted here, but succeeds if I either remove the ";" at the end, or change the column default to anything but 'N'. Lowercase 'n' also fails.
I've used a hex editor to confirm everything is ascii, and can successfully run the migration script in Toad and SQuirreL. It only fails when going through Flyway.
I've also used the debugger to capture the SQL string within Flyway before it gets executed, and it looks fine there as well. Copying it from the debugger to Toad or SQuirreL also succeeds.
It's got to be something simple that I'm missing, but I can't see what it is. Any ideas?
Here's my only migration script:
CREATE TABLE APP_USER
(
USER_ID INTEGER NOT NULL,
USER_CONFIRMED CHAR(1) DEFAULT 'N'
);
and the code:
import javax.sql.DataSource;
import oracle.jdbc.pool.OracleDataSource;
import org.flywaydb.core.Flyway;
public class FlywayTask {
public static void main(String[] args) {
FlywayTask flywayTask = new FlywayTask();
try {
flywayTask.run();
} catch (Exception e) {
e.printStackTrace();
}
}
public void run() {
System.out.println("FlywayTask Running!");
// Create Flyway instance w/ a datasource
Flyway flyway = new Flyway();
flyway.setDataSource(buildDS());
// Delete the WHOLE SCHEMA!! DANGER!!
flyway.clean();
// Start the migration
flyway.migrate();
}
public DataSource buildDS() {
...
return ods;
}
}
Running this against Oracle 10g or 11g results in this error:
FlywayTask Running!
Jun 12, 2015 10:38:40 AM org.flywaydb.core.internal.util.VersionPrinter printVersion
INFO: Flyway 3.2 by Boxfuse
Jun 12, 2015 10:38:41 AM org.flywaydb.core.internal.dbsupport.DbSupportFactory createDbSupport
INFO: Database: jdbc:oracle:thin:@(DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=scan-dev-db.dev.probuild.com )(PORT=1525)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME=NTRADEV.dev.probuild.com))) (Oracle 11.2)
Jun 12, 2015 10:38:42 AM org.flywaydb.core.internal.command.DbClean cleanSchema
INFO: Cleaned schema "TOM1" (execution time 00:00.686s)
Jun 12, 2015 10:38:42 AM org.flywaydb.core.internal.command.DbValidate validate
INFO: Validated 1 migration (execution time 00:00.016s)
Jun 12, 2015 10:38:42 AM org.flywaydb.core.internal.metadatatable.MetaDataTableImpl createIfNotExists
INFO: Creating Metadata table: "TOM1"."schema_version"
Jun 12, 2015 10:38:43 AM org.flywaydb.core.internal.command.DbMigrate migrate
INFO: Current version of schema "TOM1": << Empty Schema >>
Jun 12, 2015 10:38:43 AM org.flywaydb.core.internal.command.DbMigrate applyMigration
INFO: Migrating schema "TOM1" to version 1 - Tables
Jun 12, 2015 10:38:43 AM org.flywaydb.core.internal.command.DbMigrate applyMigration
SEVERE: Migration of schema "TOM1" to version 1 failed! Please restore backups and roll back database and code!
org.flywaydb.core.internal.dbsupport.FlywaySqlScriptException:
Migration V1__Tables.sql failed
-------------------------------
SQL State : 22019
Error Code : 911
Message : ORA-00911: invalid character
Location : db/migration/V1__Tables.sql (C:\Workspaces\luna_64\flyway-spike\target\classes\db\migration\V1__Tables.sql)
Line : 3
Statement : CREATE TABLE APP_USER
(
USER_ID INTEGER NOT NULL,
USER_CONFIRMED CHAR(1) DEFAULT 'N'
);
at org.flywaydb.core.internal.dbsupport.SqlScript.execute(SqlScript.java:117)
at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.execute(SqlMigrationExecutor.java:71)
at org.flywaydb.core.internal.command.DbMigrate$5.doInTransaction(DbMigrate.java:284)
at org.flywaydb.core.internal.command.DbMigrate$5.doInTransaction(DbMigrate.java:282)
at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:72)
at org.flywaydb.core.internal.command.DbMigrate.applyMigration(DbMigrate.java:282)
at org.flywaydb.core.internal.command.DbMigrate.access$800(DbMigrate.java:46)
at org.flywaydb.core.internal.command.DbMigrate$2.doInTransaction(DbMigrate.java:207)
at org.flywaydb.core.internal.command.DbMigrate$2.doInTransaction(DbMigrate.java:156)
at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:72)
at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:156)
at org.flywaydb.core.Flyway$1.execute(Flyway.java:1059)
at org.flywaydb.core.Flyway$1.execute(Flyway.java:1006)
at org.flywaydb.core.Flyway.execute(Flyway.java:1418)
at org.flywaydb.core.Flyway.migrate(Flyway.java:1006)
at com.probuild.spike.flyway.FlywayTask.run(FlywayTask.java:34)
at com.probuild.spike.flyway.FlywayTask.main(FlywayTask.java:16)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00911: invalid character
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:999)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1315)
at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1890)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1855)
at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:304)
at org.flywaydb.core.internal.dbsupport.JdbcTemplate.executeStatement(JdbcTemplate.java:238)
at org.flywaydb.core.internal.dbsupport.SqlScript.execute(SqlScript.java:114)
... 16 more
It is know bug of Flyway. It is fixed already, but it will be released in Flyway 4.0. Take a look at issue 1080