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

org.h2.jdbc.JdbcSQLException: Cannot parse "TIMESTAMP" constant "${now}";

Tags:

timestamp

liquibase

h2

I'm struggling with this problem from a few days without really understanding the reason behind it. Working with Liquibase, PostgreSQL and H2DB (for testing). Liquibase is working fine when working with PostgreSQL but as soon as I need to run the tests, which are executed on H2DB, they fail because of parse exception with the ${now} property, just if it was not replaced by the effective value (at least I think so). This is my changeset for creating the table:

<?xml version="1.0" encoding="utf-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.5.xsd
                        http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

    <property name="now" value="now()" dbms="h2"/>

    <property name="now" value="current_timestamp" dbms="postgresql"/>

    <property name="floatType" value="float4" dbms="postgresql, h2"/>
    <property name="floatType" value="float" dbms="mysql, oracle, mssql"/>

    <!--
        Added the entity Container.
    -->
    <changeSet id="20180424154826-1" author="developer">
        <createTable tableName="container">
            <column name="id" type="bigint" autoIncrement="${autoIncrement}">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="name" type="varchar(255)">
                <constraints nullable="false" />
            </column>

            <column name="description" type="varchar(2000)">
                <constraints nullable="true" />
            </column>

            <column name="container_type" type="varchar(255)">
                <constraints nullable="true" />
            </column>

            <column name="created" type="timestamp">
                <constraints nullable="true" />
            </column>
        </createTable>
        <dropDefaultValue tableName="container" columnName="created" columnDataType="datetime"/>

    </changeSet>
</databaseChangeLog>

and this the changeset for populating it:

<?xml version="1.0" encoding="utf-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.5.xsd
                        http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

    <property name="now" value="now()" dbms="h2"/>

    <property name="now" value="current_timestamp" dbms="postgresql"/>

    <property name="floatType" value="float4" dbms="postgresql, h2"/>
    <property name="floatType" value="float" dbms="mysql, oracle, mssql"/>

    <changeSet id="20180425154950-1" author="developer">
        <loadData catalogName="container"
                  encoding="UTF-8"
                  file="config/liquibase/containers.csv"
                  schemaName="public"
                  separator=";"
                  quotchar="'"
                  tableName="container">
        </loadData>
    </changeSet>
</databaseChangeLog>

Here the CSV file used (working fine with PostgreSQL):

id;name;description;container_type;created
1;'Human';'Human container';HUMAN;${now}
2;'IT';'IT container';IT;${now}
3;'Physical';'Physical container';PHYSICAL;${now}
4;'Intangible';'Intangible container';INTANGIBLE;${now}

This is the full log of the exception:

2018-04-26 11:30:20.350 ERROR 17993 --- [           main] liquibase                                : classpath:config/liquibase/master.xml: config/liquibase/changelog/20180425154950_added_Containers_data.xml::20180425154950-1::developer: Change Set config/liquibase/changelog/20180425154950_added_Containers_data.xml::20180425154950-1::developer failed.  Error: Cannot parse "TIMESTAMP" constant "${now}"; SQL statement:
INSERT INTO public.container (id, name, description, container_type, created) VALUES ('1', 'Human', 'Human container', 'HUMAN', '${now}') -- ('1', 'Human', 'Human container', 'HUMAN', '${now}') [22007-197] [Failed SQL: INSERT INTO public.container (id, name, description, container_type, created) VALUES ('1', 'Human', 'Human container', 'HUMAN', '${now}')]
2018-04-26 11:30:20.352  WARN 17993 --- [           main] o.s.w.c.s.GenericWebApplicationContext   : Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'liquibase' defined in class path resource [eu/hermeneut/config/DatabaseConfiguration.class]: Invocation of init method failed; nested exception is liquibase.exception.MigrationFailedException: Migration failed for change set config/liquibase/changelog/20180425154950_added_Containers_data.xml::20180425154950-1::developer:
     Reason: liquibase.exception.DatabaseException: Cannot parse "TIMESTAMP" constant "${now}"; SQL statement:
INSERT INTO public.container (id, name, description, container_type, created) VALUES ('1', 'Human', 'Human container', 'HUMAN', '${now}') -- ('1', 'Human', 'Human container', 'HUMAN', '${now}') [22007-197] [Failed SQL: INSERT INTO public.container (id, name, description, container_type, created) VALUES ('1', 'Human', 'Human container', 'HUMAN', '${now}')]
2018-04-26 11:30:20.400 ERROR 17993 --- [           main] o.s.boot.SpringApplication               : Application startup failed

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'liquibase' defined in class path resource [eu/hermeneut/config/DatabaseConfiguration.class]: Invocation of init method failed; nested exception is liquibase.exception.MigrationFailedException: Migration failed for change set config/liquibase/changelog/20180425154950_added_Containers_data.xml::20180425154950-1::developer:
     Reason: liquibase.exception.DatabaseException: Cannot parse "TIMESTAMP" constant "${now}"; SQL statement:
INSERT INTO public.container (id, name, description, container_type, created) VALUES ('1', 'Human', 'Human container', 'HUMAN', '${now}') -- ('1', 'Human', 'Human container', 'HUMAN', '${now}') [22007-197] [Failed SQL: INSERT INTO public.container (id, name, description, container_type, created) VALUES ('1', 'Human', 'Human container', 'HUMAN', '${now}')]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1630)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:555)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:483)
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:312)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:308)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:297)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197)
    at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1080)
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:857)
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:543)
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:693)
    at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:360)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:303)
    at org.springframework.boot.test.context.SpringBootContextLoader.loadContext(SpringBootContextLoader.java:120)
    at org.springframework.test.context.cache.DefaultCacheAwareContextLoaderDelegate.loadContextInternal(DefaultCacheAwareContextLoaderDelegate.java:98)
    at org.springframework.test.context.cache.DefaultCacheAwareContextLoaderDelegate.loadContext(DefaultCacheAwareContextLoaderDelegate.java:116)
    at org.springframework.test.context.support.DefaultTestContext.getApplicationContext(DefaultTestContext.java:83)
    at org.springframework.test.context.web.ServletTestExecutionListener.setUpRequestContextIfNecessary(ServletTestExecutionListener.java:189)
    at org.springframework.test.context.web.ServletTestExecutionListener.prepareTestInstance(ServletTestExecutionListener.java:131)
    at org.springframework.test.context.TestContextManager.prepareTestInstance(TestContextManager.java:230)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.createTest(SpringJUnit4ClassRunner.java:228)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner$1.runReflectiveCall(SpringJUnit4ClassRunner.java:287)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.methodBlock(SpringJUnit4ClassRunner.java:289)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:247)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:94)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:191)
    at org.apache.maven.surefire.junit4.JUnit4Provider.execute(JUnit4Provider.java:283)
    at org.apache.maven.surefire.junit4.JUnit4Provider.executeWithRerun(JUnit4Provider.java:173)
    at org.apache.maven.surefire.junit4.JUnit4Provider.executeTestSet(JUnit4Provider.java:153)
    at org.apache.maven.surefire.junit4.JUnit4Provider.invoke(JUnit4Provider.java:128)
    at org.apache.maven.surefire.booter.ForkedBooter.invokeProviderInSameClassLoader(ForkedBooter.java:203)
    at org.apache.maven.surefire.booter.ForkedBooter.runSuitesInProcess(ForkedBooter.java:155)
    at org.apache.maven.surefire.booter.ForkedBooter.main(ForkedBooter.java:103)
Caused by: liquibase.exception.MigrationFailedException: Migration failed for change set config/liquibase/changelog/20180425154950_added_Containers_data.xml::20180425154950-1::developer:
     Reason: liquibase.exception.DatabaseException: Cannot parse "TIMESTAMP" constant "${now}"; SQL statement:
INSERT INTO public.container (id, name, description, container_type, created) VALUES ('1', 'Human', 'Human container', 'HUMAN', '${now}') -- ('1', 'Human', 'Human container', 'HUMAN', '${now}') [22007-197] [Failed SQL: INSERT INTO public.container (id, name, description, container_type, created) VALUES ('1', 'Human', 'Human container', 'HUMAN', '${now}')]
    at liquibase.changelog.ChangeSet.execute(ChangeSet.java:619)
    at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:51)
    at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:79)
    at liquibase.Liquibase.update(Liquibase.java:214)
    at liquibase.Liquibase.update(Liquibase.java:192)
    at liquibase.integration.spring.SpringLiquibase.performUpdate(SpringLiquibase.java:431)
    at liquibase.integration.spring.SpringLiquibase.afterPropertiesSet(SpringLiquibase.java:388)
    at io.github.jhipster.config.liquibase.AsyncSpringLiquibase.initDb(AsyncSpringLiquibase.java:94)
    at io.github.jhipster.config.liquibase.AsyncSpringLiquibase.afterPropertiesSet(AsyncSpringLiquibase.java:84)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1688)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1626)
    ... 43 common frames omitted
Caused by: liquibase.exception.DatabaseException: Cannot parse "TIMESTAMP" constant "${now}"; SQL statement:
INSERT INTO public.container (id, name, description, container_type, created) VALUES ('1', 'Human', 'Human container', 'HUMAN', '${now}') -- ('1', 'Human', 'Human container', 'HUMAN', '${now}') [22007-197] [Failed SQL: INSERT INTO public.container (id, name, description, container_type, created) VALUES ('1', 'Human', 'Human container', 'HUMAN', '${now}')]
    at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:309)
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:55)
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:113)
    at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1277)
    at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1259)
    at liquibase.changelog.ChangeSet.execute(ChangeSet.java:582)
    ... 53 common frames omitted
Caused by: org.h2.jdbc.JdbcSQLException: Cannot parse "TIMESTAMP" constant "${now}"; SQL statement:
INSERT INTO public.container (id, name, description, container_type, created) VALUES ('1', 'Human', 'Human container', 'HUMAN', '${now}') -- ('1', 'Human', 'Human container', 'HUMAN', '${now}') [22007-197]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:357)
    at org.h2.message.DbException.get(DbException.java:168)
    at org.h2.value.ValueTimestamp.parse(ValueTimestamp.java:147)
    at org.h2.value.Value.convertTo(Value.java:1071)
    at org.h2.table.Column.convert(Column.java:177)
    at org.h2.command.dml.Insert.insertRows(Insert.java:166)
    at org.h2.command.dml.Insert.update(Insert.java:134)
    at org.h2.command.CommandContainer.update(CommandContainer.java:102)
    at org.h2.command.Command.executeUpdate(Command.java:261)
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:233)
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:205)
    at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:95)
    at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
    at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:307)
    ... 58 common frames omitted
Caused by: java.lang.IllegalArgumentException: ${now}
    at org.h2.util.DateTimeUtils.parseDateValue(DateTimeUtils.java:345)
    at org.h2.util.DateTimeUtils.parseTimestamp(DateTimeUtils.java:460)
    at org.h2.value.ValueTimestamp.parse(ValueTimestamp.java:145)
    ... 69 common frames omitted
like image 424
1Z10 Avatar asked Apr 26 '18 09:04

1Z10

1 Answers

Even for h2 database, the in built function to get current timestamp is CURRENT_TIMESTAMP

Reference here -> http://www.h2database.com/html/functions.html#current_timestamp

So change this 

<property name="now" value="now()" dbms="h2"/>

to this

<property name="now" value="current_timestamp()" dbms="h2"/>
like image 99
Seetharamani Tmr Avatar answered Nov 07 '22 23:11

Seetharamani Tmr
Sign in to Comment

Related questions

set times: Operation not permitted
Inject build timestamp into vue-cli build output files to verify deployments with yarn
Cast date to timestamp in pgSQL
Java simple Timestamp to Date conversion
How to make a git rebase and keep the commit timestamp?
Timestamp object has no attribute dt
Mysql time stamp queries
Convert Windows Timestamp to date using PHP on a Linux Box
java.text.ParseException: Unparseable date: "Thu Jan 19 2012 08:00 PM"
Date.getTime() not including time?
qDebug Console output with Timestamp
convert string to date and time as am/pm format [duplicate]
How to include a timer in Bash Scripting?
Pass Datetime/Timestamp from PHP to Javascript by echo
How to convert millisecond time stamp to normal date in Python?
Python's fromtimestamp returns inconsistent results on different machines
Store date as unix timestamp or TIMESTAMP data type in MySQL?
MySql search time stamp based on timezone offset
JSON Web Token and the year 2038 bug

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!