Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

H2 and Oracle Compatiability issues

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
like image 389
john Avatar asked Nov 26 '13 14:11

john


People also ask

Why H2 database is not used in production?

Mainly, H2 database can be configured to run as inmemory database, which means that data will not persist on the disk. Because of embedded database it is not used for production development, but mostly used for development and testing.

Is H2 database case sensitive?

Text comparison in MySQL is case insensitive by default, while in H2 it is case sensitive (as in most other databases). H2 does support case insensitive text comparison, but it needs to be set separately, using SET IGNORECASE TRUE.

Is H2 database thread safe?

1 Answer. Save this answer. Show activity on this post. I still don't have a satisfying answer for this but I can only conclude that H2's in-memory database is not thread safe, whereas the disk database is thread safe.

Is H2 a good database?

H2 is a terrific database engine. After we solved a problem not related to H2, we switched from PostgreSQL and MySQL (both still supported) to H2. H2 became our primary database choice.


2 Answers

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.

like image 92
sf_jeff Avatar answered Oct 20 '22 15:10

sf_jeff


Short Answer

Oracle is a huge piece of software, and you can't expect a small embedded database to be 100% compatible.

Long Answer

Compatibility Modes

As documented, for certain features, this database can emulate the behavior of specific databases. However, only a small subset of the differences between databases are implemented in this way. This list is documented.

Compatibility

As documented, H2 is (up to a certain point) compatible to other databases such as HSQLDB, MySQL and PostgreSQL. There are certain areas where H2 is incompatible.

All database engines behave a little bit different. Where possible, H2 supports the ANSI SQL standard, and tries to be compatible to other databases. There are still differences however.

like image 37
Thomas Mueller Avatar answered Oct 20 '22 15:10

Thomas Mueller