Problemas de compatibilidad con H2 y Oracle
Tengo un problema al ejecutar el siguiente script utilizando la base de datos de Flyway y H2. Asumo que es un problema de compatibilidad con Oracle, pero estoy usando H2 con el modo correcto. Esta secuencia de comandos se utiliza para construir mis tablas en producción, pero quiero usar una base de datos en memoria para realizar pruebas. Configuración y error a continuación. No quiero volver a escribir los scripts para una base de datos en memoria si es posible.
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>
Contexto
<?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>
Seguimiento de pila
[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