Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

H2 and Oracle Compatiability issues

Tags:

oracle

maven-3

h2

flyway

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
Sign in to Comment

Related questions

Selecting all columns and constant value from Oracle table
Fail to convert to internal representation
How to drop Oracle LOB
No Entity Framework provider found for the ADO.NET provider with invariant name 'Oracle.ManagedDataAccess.Client'
ORA-01843: not a valid month : TO_DATE('12-JUN-02','DD-MON-YY')
Missing artifact com.oracle:ojdbc14:jar:10.2.0.1.0
Retrieving identity of most recent insert in Oracle DB 12c
Oracle debugging techniques [closed]
Oracle Unique Constraint with Expression
How to run sql scripts from a pl sql procedure
How to group by each day in PL/SQL?
Oracle Database. Insert a copy of a row to the same table (Duplicate key error message)
Oracle Julian day of year
Oracle: Update a datarow by adding to existing value
what is the difference between left join and left outer join? [duplicate]
Oracle Syntax for Creating Database Link Owned by Another User
Oracle "SQL Error: Missing IN or OUT parameter at index:: 1"
How to get a list of months between 2 given dates using a query?
Does Oracle's "date'[yyyy-mm-dd]'" literal always use the yyyy-mm-dd pattern?
Manually forward a sequence - oracle sql

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!