H2和Oracle兼容性问题 [英] H2 and Oracle Compatiability issues
本文介绍了H2和Oracle兼容性问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我在使用Flyway和H2数据库执行以下脚本时遇到问题.我认为这是一个Oracle兼容性问题，但我正在以正确的模式使用H2.该脚本用于在生产环境中构造我的表，但是我想使用内存数据库进行测试.下面的设置和错误.如果可能的话，我不想重写内存数据库的脚本.
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.properties
jdbc.driver=org.h2.Driver
jdbc.url=jdbc:h2:file:target/firmsoftDB;MODE=Oracle
jdbc.username=sa
jdbc.password=
Sql脚本
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>
上下文
<?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
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
推荐答案
简短回答
Oracle是一款庞大的软件，您不能指望小型嵌入式数据库会100％兼容.
Short Answer
Oracle is a huge piece of software, and you can't expect a small embedded database to be 100% compatible.
兼容模式
如所述，对于某些功能，该数据库可以模拟特定数据库的行为 .但是，这种方式仅实现了数据库之间差异的一小部分.此列表已记录.
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.
兼容性
如前所述， H2与(到特定点)兼容到其他数据库，例如HSQLDB，MySQL和PostgreSQL.在某些地区，H2不相容.
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.
所有数据库引擎的行为都有些不同.在可能的情况下，H2支持ANSI SQL标准，并尝试与其他数据库兼容.但是仍然存在差异.
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.
这篇关于H2和Oracle兼容性问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
