0

I've a springboot app, configured using yaml configuration. When it is configured for PgSql it works but when I try to configure it for oracle and I run the applications, I got the following error:

liquibase.exception.DatabaseException: Token SQL92 non supportato nella posizione: 417 [Failed SQL: 
CREATE TABLE MVOLPINI.desk_user (
id NUMBER(38, 0) GENERATED BY DEFAULT AS IDENTITY NOT NULL, 
login VARCHAR2(50) NOT NULL, 
password_hash VARCHAR2(60), 
first_name VARCHAR2(50), 
last_name VARCHAR2(50), 
email VARCHAR2(100), 
image_url VARCHAR2(256), 
activated NUMBER(1) NOT NULL, 
activation_key VARCHAR2(20), 
reset_key VARCHAR2(20), 
reset_date TIMESTAMP, created_by VARCHAR2(50) NOT NULL, 
created_date TIMESTAMP DEFAULT ${now} NOT NULL, 
last_modified_by VARCHAR2(50), last_modified_date TIMESTAMP, 
tenant_id NUMBER(38, 0), 
CONSTRAINT PK_DESK_USER PRIMARY KEY (id), 
UNIQUE (login), 
UNIQUE (email))
]

It looks like hibernate is trying to pass a wrong default value ({now}) when it comes to create the column created date. This the entity:

@MappedSuperclass
@Audited
@EntityListeners(AuditingEntityListener.class)
public abstract class AbstractAuditingEntity implements Serializable {

    private static final long serialVersionUID = 1L;

    @CreatedBy
    @Column(name = "created_by", nullable = false, length = 50, updatable =false)
    @JsonIgnore
    private String createdBy;

    @CreatedDate
    @Column(name = "created_date", nullable = false, updatable=false)
    @JsonIgnore
    private Instant createdDate = Instant.now();

    @LastModifiedBy
    @Column(name = "last_modified_by", length = 50)
    @JsonIgnore
    private String lastModifiedBy;

    @LastModifiedDate
    @Column(name = "last_modified_date")
    @JsonIgnore
    private Instant lastModifiedDate = Instant.now();

I couldn't find anywhere in the code the token {now} that cause the problem. Thinking that it is generated by the annotation @CreatedDate I tried to substitute it with other annotations such as @Temporal and changing Instant type with Date but I got the same error. Could anyone help me?

This is the yaml configuration:

 logging:
    level:
        ROOT: INFO
        it.dedagroup.todo: INFO
        io.github.jhipster: INFO

spring:
    devtools:
        restart:
            enabled: false
        livereload:
            enabled: false 
    datasource:
        type: com.zaxxer.hikari.HikariDataSource
        url: jdbc:oracle:thin:@172.17.34.20:1521:db12Test
        username: mvolpini
        password: password1
        driver-class-name: oracle.jdbc.driver.OracleDriver
    jpa:
        database-platform: org.hibernate.dialect.Oracle12cDialect
        database: ORACLE
        show-sql: false
        properties:
            hibernate.id.new_generator_mappings: true
            hibernate.cache.use_second_level_cache: false
            hibernate.cache.use_query_cache: false
            hibernate.generate_statistics: false
    mail:
        host: localhost
        port: 25
        username:
        password:
    thymeleaf:
        cache: true

liquibase:
    contexts: prodOracle

# ===================================================================
# To enable SSL, generate a certificate using:
# keytool -genkey -alias DeskBackend -storetype PKCS12 -keyalg RSA -keysize 2048 -keystore keystore.p12 -validity 3650
#
# You can also use Let's Encrypt:
# https://maximilian-boehm.com/hp2121/Create-a-Java-Keystore-JKS-from-Let-s-Encrypt-Certificates.htm
#
# Then, modify the server.ssl properties so your "server" configuration looks like:
#
# server:
#    port: 8443
#    ssl:
#        key-store: keystore.p12
#        key-store-password: <your-password>
#        keyStoreType: PKCS12
#        keyAlias: DeskBackend
# ===================================================================
server:
    port: 8180
    compression:
        enabled: true
        mime-types: text/html,text/xml,text/plain,text/css, application/javascript, application/json
        min-response-size: 1024

# ===================================================================
# JHipster specific properties
#
# Full reference is available at: http://www.jhipster.tech/common-application-properties/
# ===================================================================

jhipster:
    http:
        version: V_1_1 # To use HTTP/2 you will need SSL support (see above the "server.ssl" configuration)
    cache: # Cache configuration
        ehcache: # Ehcache configuration
            time-to-live-seconds: 3600 # By default objects stay 1 hour in the cache
            max-entries: 1000 # Number of objects in each cache entry
    # CORS is only enabled by default with the "dev" profile, so BrowserSync can access the API
    cors:
        allowed-origins: "*"
        allowed-methods: "*"
        allowed-headers: "*"
        exposed-headers: "Authorization"
        allow-credentials: true
        max-age: 1800
    security:
        authentication:
            jwt:
                secret: d593dbdfe07821e0f92f45cdef8bdccba69757ff
                # Token is valid 24 hours
                #token-validity-in-seconds: 86400
                #token-validity-in-seconds-for-remember-me: 2592000
                # Token is valid 1 year
                token-validity-in-seconds: 946080000
                token-validity-in-seconds-for-remember-me: 946080000
    mail: # specific JHipster mail property, for standard properties see MailProperties
        from: oracle@localhost
        base-url: http://127.0.0.1:8080
    metrics: # DropWizard Metrics configuration, used by MetricsConfiguration
        jmx.enabled: true
        graphite: # Use the "graphite" Maven profile to have the Graphite dependencies
            enabled: false
            host: localhost
            port: 2003
            prefix: DeskBackend
        prometheus: # Use the "prometheus" Maven profile to have the Prometheus dependencies
            enabled: false
            endpoint: /prometheusMetrics
        logs: # Reports Dropwizard metrics in the logs
            enabled: false
            report-frequency: 60 # in seconds
    logging:
        logstash: # Forward logs to logstash over a socket, used by LoggingConfiguration
            enabled: false
            host: localhost
            port: 5000
            queue-size: 512


# ===================================================================
# Application specific properties
# Add your own application properties here, see the ApplicationProperties class
# to have type-safe configuration, like in the JHipsterProperties above
#
# More documentation is available at:
# http://www.jhipster.tech/common-application-properties/
# ===================================================================

application:

And this is the configuration concerning liquibase:

    @Bean
    public SpringLiquibase liquibase(@Qualifier("taskExecutor") TaskExecutor taskExecutor,
            DataSource dataSource, LiquibaseProperties liquibaseProperties) {

        // Use liquibase.integration.spring.SpringLiquibase if you don't want Liquibase to start asynchronously
        SpringLiquibase liquibase = new AsyncSpringLiquibase(taskExecutor, env);
        liquibase.setDataSource(dataSource);
        liquibase.setChangeLog("classpath:config/liquibase/master.xml");
        liquibase.setContexts(liquibaseProperties.getContexts());
        liquibase.setDefaultSchema(liquibaseProperties.getDefaultSchema());
        liquibase.setDropFirst(liquibaseProperties.isDropFirst());
        if (env.acceptsProfiles(JHipsterConstants.SPRING_PROFILE_NO_LIQUIBASE)) {
            liquibase.setShouldRun(false);
        } else {
            liquibase.setShouldRun(liquibaseProperties.isEnabled());
            log.debug("Configuring Liquibase");
        }
        return liquibase;
    }
  • what if you add and not substitute @Temporal(TIMESTAMP)? – Aris2World Jun 27 '18 at 08:08
  • I got the same error.... – Marco Volpini Jun 27 '18 at 08:16
  • try to add to the annotation Column this: columnDefinition="TIMESTAMP DEFAULT CURRENT_TIMESTAMP" insertable=false. you should also remove the initialization of createdDate because you need the timestamp of the db not that of the jvm. See https://blog.octo.com/en/audit-with-jpa-creation-and-update-date/ – Aris2World Jun 27 '18 at 08:21
  • I'm continuing to have the same error... – Marco Volpini Jun 27 '18 at 08:36
  • Sorry Marco but I've noted that the exception is generated by liquibase. Have you the yaml configuration of the tables? Try to watch here https://stackoverflow.com/questions/23550232/liquibase-how-to-set-the-default-value-of-a-date-column-to-be-now-in-utc-form – Aris2World Jun 27 '18 at 14:57
  • Thank you for your help Aris! I add the yaml configuration, I'm cheking the link you added – Marco Volpini Jun 28 '18 at 06:55
  • Good! Let me know if you have found the solution. – Aris2World Jun 28 '18 at 11:38
  • Sorry for the delay, but that problem passed into the background for the last days. In any case I solved it: since I didn't know liquibase I was thinking that tables were created starting from the classes annotated with @Entity while there were xml files with the definition of all tables. Indeed the problem was that the date column default value was specified using pgsql dialect and not the oracle's one. Thank you very much Aris! – Marco Volpini Jul 03 '18 at 12:35
  • Very good @Marco! I suggest you to post your own answer and accept it as best answer. This could be helpful for someone else who incurs in the same problem and it's a good practice for stackoverflow. – Aris2World Jul 03 '18 at 13:53

0 Answers0