H2 and Oracle Compatiability issues

oracle maven-3 h2 flyway
john picture john · Nov 26, 2013 · Viewed 21.1k times · Source

I am having an issue executing the following script using Flyway and H2 database. I assume its a Oracle compatibility issue but I am using H2 with the correct mode. This script is used to construct my tables in production but I want to use an in-memory database for testing purposes. Setup and error below. I don't want rewrite the scripts for an in-memory database if thats possible.

Jdbc.properties

jdbc.driver=org.h2.Driver
jdbc.url=jdbc:h2:file:target/firmsoftDB;MODE=Oracle
jdbc.username=sa
jdbc.password=

Sql Script

CREATE TABLE USER_INFO
(
  USER_INFO_ID      NUMBER                      NOT NULL,
  USER_NAME         VARCHAR2(32 BYTE)           NOT NULL,
  EMAIL             VARCHAR2(320 BYTE)              NULL,
  LAST_NAME         VARCHAR2(32 BYTE)           NOT NULL,
  FIRST_NAME        VARCHAR2(32 BYTE)           NOT NULL,
  LAST_LOGIN        TIMESTAMP(6)                    NULL,
  USER_TYPE         VARCHAR2(32 BYTE)               NULL,
  USER_CN           VARCHAR2(32 BYTE)               NULL,
  SOURCE_DIRECTORY  VARCHAR2(15 BYTE)               NULL,
  PRIMARY_PHONE     VARCHAR2(32 BYTE)               NULL,
  ALT_PHONE         VARCHAR2(32 BYTE)               NULL,
  CREATED_BY        NUMBER                      NOT NULL,
  CREATED_DATE      TIMESTAMP(6)                NOT NULL,
  UPDATED_BY        NUMBER                      NOT NULL,
  UPDATED_DATE      TIMESTAMP(6)                NOT NULL
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

pom.xml

<profiles>
    <profile>
        <id>h2-test</id>
        <build>
            <plugins>
                <plugin>
                    <groupId>org.apache.maven.plugins</groupId>
                    <artifactId>maven-surefire-plugin</artifactId>
                    <configuration>
                        <systemPropertyVariables>
                            <flyway.locations>db/migration</flyway.locations>
                        </systemPropertyVariables>
                        <threadCount>1</threadCount>
                    </configuration>
                    <goals>
                        <goal>test</goal>
                    </goals>
                    <executions>
                    </executions>
                </plugin>
                <plugin>
                    <groupId>com.googlecode.flyway</groupId>
                    <artifactId>flyway-maven-plugin</artifactId>
                    <version>2.2.1</version>
                    <configuration>
                        <!-- <url>jdbc:hsqldb:file:target/firmsoftDB;sql.syntax_ora=true</url> -->
                        <!-- <url>jdbc:hsqldb:mem:firmsoftDB;sql.syntax_ora=true</url> -->
                        <url>jdbc:h2:file:target/firmsoftDB;MODE=Oracle</url>
                        <!-- <url>jdbc:derby:\temp\db\FAQ\db</url> -->
                    </configuration>
                </plugin>
            </plugins>
        </build>
    </profile>

Context

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:jdbc="http://www.springframework.org/schema/jdbc"
xsi:schemaLocation="
    http://www.springframework.org/schema/beans
    http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
    http://www.springframework.org/schema/context
    http://www.springframework.org/schema/context/spring-context-3.0.xsd
    http://www.springframework.org/schema/jdbc 
    http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd">

   <!--  Needed for system properties replacement -->
    <!-- Following will only work with spring 3     -->
<context:property-placeholder location="classpath*:jdbc.properties,classpath*:flyway.default.properties,classpath*:flyway.properties"
            ignore-resource-not-found="true"        
    system-properties-mode="OVERRIDE"/>

    <!-- flyway part -->
    <bean id="flyway" class="com.googlecode.flyway.core.Flyway" depends-on="dataSourceRef">
        <property name="dataSource" ref="dataSourceRef"/>
        <property name="locations" value="${flyway.locations}"/>
    </bean>

 <bean id="dataSourceRef" name="dataSource"
    class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">

            <property name="driverClassName"><value>${jdbc.driver}</value></property>
            <property name="url"><value>${jdbc.url}</value></property>
            <property name="username"><value>${jdbc.username}</value></property>
            <property name="password"><value>${jdbc.password}</value></property>
            <property name="maxActive" value="-1"/>

</bean>

StackTrace

[ERROR] Failed to execute goal com.googlecode.flyway:flyway-maven-plugin:2.2.1:migrate (default-cli) on project flyway-test: com.googlecode.flyway.core.api.FlywayException: Migration of schema "PUBLIC" to version 1 failed! Please restore backups and roll back database and code! Syntax error in SQL statement "CREATE TABLE USER_INFO
[ERROR] (
[ERROR] USER_INFO_ID      NUMBER                      NOT NULL,
[ERROR] USER_NAME         VARCHAR2(32 BYTE[*])           NOT NULL,
[ERROR] EMAIL             VARCHAR2(320 BYTE)              NULL,
[ERROR] LAST_NAME         VARCHAR2(32 BYTE)           NOT NULL,
[ERROR] FIRST_NAME        VARCHAR2(32 BYTE)           NOT NULL,
[ERROR] LAST_LOGIN        TIMESTAMP(6)                    NULL,
[ERROR] USER_TYPE         VARCHAR2(32 BYTE)               NULL,
[ERROR] USER_CN           VARCHAR2(32 BYTE)               NULL,
[ERROR] SOURCE_DIRECTORY  VARCHAR2(15 BYTE)               NULL,
[ERROR] PRIMARY_PHONE     VARCHAR2(32 BYTE)               NULL,
[ERROR] ALT_PHONE         VARCHAR2(32 BYTE)               NULL,
[ERROR] CREATED_BY        NUMBER                      NOT NULL,
[ERROR] CREATED_DATE      TIMESTAMP(6)                NOT NULL,
[ERROR] UPDATED_BY        NUMBER                      NOT NULL,
[ERROR] UPDATED_DATE      TIMESTAMP(6)                NOT NULL
[ERROR] )
[ERROR] LOGGING
[ERROR] NOCOMPRESS
[ERROR] NOCACHE
[ERROR] NOPARALLEL
[ERROR] MONITORING"; expected "K, M, G, CHAR, )"; SQL statement:
[ERROR] CREATE TABLE USER_INFO
[ERROR] (
[ERROR] USER_INFO_ID      NUMBER                      NOT NULL,
[ERROR] USER_NAME         VARCHAR2(32 BYTE)           NOT NULL,
[ERROR] EMAIL             VARCHAR2(320 BYTE)              NULL,
[ERROR] LAST_NAME         VARCHAR2(32 BYTE)           NOT NULL,
[ERROR] FIRST_NAME        VARCHAR2(32 BYTE)           NOT NULL,
[ERROR] LAST_LOGIN        TIMESTAMP(6)                    NULL,
[ERROR] USER_TYPE         VARCHAR2(32 BYTE)               NULL,
[ERROR] USER_CN           VARCHAR2(32 BYTE)               NULL,
[ERROR] SOURCE_DIRECTORY  VARCHAR2(15 BYTE)               NULL,
[ERROR] PRIMARY_PHONE     VARCHAR2(32 BYTE)               NULL,
[ERROR] ALT_PHONE         VARCHAR2(32 BYTE)               NULL,
[ERROR] CREATED_BY        NUMBER                      NOT NULL,
[ERROR] CREATED_DATE      TIMESTAMP(6)                NOT NULL,
[ERROR] UPDATED_BY        NUMBER                      NOT NULL,
[ERROR] UPDATED_DATE      TIMESTAMP(6)                NOT NULL
[ERROR] )
[ERROR] LOGGING
[ERROR] NOCOMPRESS
[ERROR] NOCACHE
[ERROR] NOPARALLEL
[ERROR] MONITORING [42001-174]
[ERROR] -> [Help 1]
org.apache.maven.lifecycle.LifecycleExecutionException: Failed to execute goal com.googlecode.flyway:flyway-maven-plugin:2.2.1:migrate (default-cli) on project flyway-test: com.googlecode.flyway.core.api.FlywayException: Migration of schema "PUBLIC" to version 1 failed! Please restore backups and roll back database and code!
    at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:217)
    at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:153)
    at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:145)
    at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject(LifecycleModuleBuilder.java:84)
    at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject(LifecycleModuleBuilder.java:59)
    at org.apache.maven.lifecycle.internal.LifecycleStarter.singleThreadedBuild(LifecycleStarter.java:183)
    at org.apache.maven.lifecycle.internal.LifecycleStarter.execute(LifecycleStarter.java:161)
    at org.apache.maven.DefaultMaven.doExecute(DefaultMaven.java:319)
    at org.apache.maven.DefaultMaven.execute(DefaultMaven.java:156)
    at org.apache.maven.cli.MavenCli.execute(MavenCli.java:537)
    at org.apache.maven.cli.MavenCli.doMain(MavenCli.java:196)
    at org.apache.maven.cli.MavenCli.main(MavenCli.java:141)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.codehaus.plexus.classworlds.launcher.Launcher.launchEnhanced(Launcher.java:290)
    at org.codehaus.plexus.classworlds.launcher.Launcher.launch(Launcher.java:230)
    at org.codehaus.plexus.classworlds.launcher.Launcher.mainWithExitCode(Launcher.java:409)
    at org.codehaus.plexus.classworlds.launcher.Launcher.main(Launcher.java:352)
Caused by: org.apache.maven.plugin.MojoExecutionException: com.googlecode.flyway.core.api.FlywayException: Migration of schema "PUBLIC" to version 1 failed! Please restore backups and roll back database and code!
    at com.googlecode.flyway.maven.AbstractFlywayMojo.execute(AbstractFlywayMojo.java:253)
    at org.apache.maven.plugin.DefaultBuildPluginManager.executeMojo(DefaultBuildPluginManager.java:101)
    at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:209)
    ... 19 more
Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "CREATE TABLE USER_INFO
(
  USER_INFO_ID      NUMBER                      NOT NULL,
  USER_NAME         VARCHAR2(32 BYTE[*])           NOT NULL,
  EMAIL             VARCHAR2(320 BYTE)              NULL,
  LAST_NAME         VARCHAR2(32 BYTE)           NOT NULL,
  FIRST_NAME        VARCHAR2(32 BYTE)           NOT NULL,
  LAST_LOGIN        TIMESTAMP(6)                    NULL,
  USER_TYPE         VARCHAR2(32 BYTE)               NULL,
  USER_CN           VARCHAR2(32 BYTE)               NULL,
  SOURCE_DIRECTORY  VARCHAR2(15 BYTE)               NULL,
  PRIMARY_PHONE     VARCHAR2(32 BYTE)               NULL,
  ALT_PHONE         VARCHAR2(32 BYTE)               NULL,
  CREATED_BY        NUMBER                      NOT NULL,
  CREATED_DATE      TIMESTAMP(6)                NOT NULL,
  UPDATED_BY        NUMBER                      NOT NULL,
  UPDATED_DATE      TIMESTAMP(6)                NOT NULL
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING"; expected "K, M, G, CHAR, )"; SQL statement:
CREATE TABLE USER_INFO
(
  USER_INFO_ID      NUMBER                      NOT NULL,
  USER_NAME         VARCHAR2(32 BYTE)           NOT NULL,
  EMAIL             VARCHAR2(320 BYTE)              NULL,
  LAST_NAME         VARCHAR2(32 BYTE)           NOT NULL,
  FIRST_NAME        VARCHAR2(32 BYTE)           NOT NULL,
  LAST_LOGIN        TIMESTAMP(6)                    NULL,
  USER_TYPE         VARCHAR2(32 BYTE)               NULL,
  USER_CN           VARCHAR2(32 BYTE)               NULL,
  SOURCE_DIRECTORY  VARCHAR2(15 BYTE)               NULL,
  PRIMARY_PHONE     VARCHAR2(32 BYTE)               NULL,
  ALT_PHONE         VARCHAR2(32 BYTE)               NULL,
  CREATED_BY        NUMBER                      NOT NULL,
  CREATED_DATE      TIMESTAMP(6)                NOT NULL,
  UPDATED_BY        NUMBER                      NOT NULL,
  UPDATED_DATE      TIMESTAMP(6)                NOT NULL
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING [42001-174]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:332)
    at org.h2.message.DbException.getSyntaxError(DbException.java:197)
    at org.h2.command.Parser.getSyntaxError(Parser.java:504)
    at org.h2.command.Parser.read(Parser.java:2867)
    at org.h2.command.Parser.parseColumnWithType(Parser.java:3788)
    at org.h2.command.Parser.parseColumnForTable(Parser.java:3603)
    at org.h2.command.Parser.parseCreateTable(Parser.java:5354)
    at org.h2.command.Parser.parseCreate(Parser.java:3890)
    at org.h2.command.Parser.parsePrepared(Parser.java:334)
    at org.h2.command.Parser.parse(Parser.java:289)
    at org.h2.command.Parser.parse(Parser.java:265)
    at org.h2.command.Parser.prepareCommand(Parser.java:226)
    at org.h2.engine.Session.prepareLocal(Session.java:437)
    at org.h2.engine.Session.prepareCommand(Session.java:380)
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1138)
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:168)
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:156)
    at com.googlecode.flyway.core.dbsupport.JdbcTemplate.executeStatement(JdbcTemplate.java:230)
    at com.googlecode.flyway.core.dbsupport.SqlScript.execute(SqlScript.java:89)
    at com.googlecode.flyway.core.resolver.sql.SqlMigrationExecutor.execute(SqlMigrationExecutor.java:72)
    at com.googlecode.flyway.core.command.DbMigrate$2.doInTransaction(DbMigrate.java:252)
    at com.googlecode.flyway.core.command.DbMigrate$2.doInTransaction(DbMigrate.java:250)
    at com.googlecode.flyway.core.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:56)
    at com.googlecode.flyway.core.command.DbMigrate.applyMigration(DbMigrate.java:250)
    at com.googlecode.flyway.core.command.DbMigrate.access$700(DbMigrate.java:47)
    at com.googlecode.flyway.core.command.DbMigrate$1.doInTransaction(DbMigrate.java:189)
    at com.googlecode.flyway.core.command.DbMigrate$1.doInTransaction(DbMigrate.java:138)
    at com.googlecode.flyway.core.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:56)
    at com.googlecode.flyway.core.command.DbMigrate.migrate(DbMigrate.java:137)
    at com.googlecode.flyway.core.Flyway$1.execute(Flyway.java:872)
    at com.googlecode.flyway.core.Flyway$1.execute(Flyway.java:819)
    at com.googlecode.flyway.core.Flyway.execute(Flyway.java:1200)
    at com.googlecode.flyway.core.Flyway.migrate(Flyway.java:819)
    at com.googlecode.flyway.maven.MigrateMojo.doExecuteWithMigrationConfig(MigrateMojo.java:159)
    at com.googlecode.flyway.maven.AbstractMigrationLoadingMojo.doExecute(AbstractMigrationLoadingMojo.java:151)
    at com.googlecode.flyway.maven.AbstractFlywayMojo.execute(AbstractFlywayMojo.java:251)
    ... 21 more
[ERROR]
[ERROR]
[ERROR] For more information about the errors and possible solutions, please read the following articles:
[ERROR] [Help 1] http://cwiki.apache.org/confluence/display/MAVEN/MojoExecutionException

Answer

sf_jeff picture sf_jeff · Jul 7, 2014

H2 doesn't recognize the syntax

VARCHAR2(32 BYTE)

Change this to 

VARCHAR2(32)

... and it will still work in oracle and will at least remove your first error in H2.

Related questions

Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

Read More
How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select * from sometable order by name limit 20,10 to get the 21st to the 30th rows (skip the …

Read More
ORA-12514 TNS:listener does not currently know of service requested in connect descriptor

We have an application running locally where we're experiencing the following error: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor I've tested the connection using TNSPing which resolved correctly and I tried SQLPlus to try …

Read More