Liquibase insert failing with Table not found in H2

Liquibase Development
1

Hello,
I am using liquibase 4.3.3 to insert data to H2 DB using yaml changeset. But, when I try to start the server I get table not found while inserting even though the table exists. But, if I use the generated insert statement and disable liquibase and use spring.sql.init.data-locations to specify the data file having insert SQL statement, this works. Can you please help me out with this weird problem?

Regards,
Deepak

2

Hi @DEEPAK

Could you please help us with more details?

  1. Does this only happens when you use YAML changeset? and works with XML or SQL changeset?
  2. Could you paste the error log here? it will get more clarity on the exact issue.
  3. Please share the YAML changeset file with us.

Also, please help me understand what do you mean by this:

What is generated insert statement here?

Thanks,
Rakhi Agrawal

3

Hello @rakhi,

Please find below the answers to your question

  1. Does this only happens when you use YAML changeset? and works with XML or SQL changeset?
    This happens irrespective of what kind of changeset it is. I tried with SQL, yaml and XML and all had the same issue.
  2. Could you paste the error log here? it will get more clarity on the exact issue.
    Please find below
2021-08-25 13:08:27.265  INFO 62336 --- [           main] liquibase.lockservice                    : Successfully acquired change log lock
2021-08-25 13:08:27.506  INFO 62336 --- [           main] liquibase.changelog                      : Creating database history table with name: PUBLIC.COM_SAP_GRC_RISK_ASSESSMENT_DATABASECHANGELOG
2021-08-25 13:08:27.509  INFO 62336 --- [           main] liquibase.changelog                      : Reading from PUBLIC.COM_SAP_GRC_RISK_ASSESSMENT_DATABASECHANGELOG
2021-08-25 13:08:27.545  INFO 62336 --- [           main] liquibase.changelog                      : Change set liquibase/data/test-insert.xml::Analysis-1::Test failed, but failOnError was false.  Error: Table "COM_TEST_LIQUIBASE_ANALYSIS" not found; SQL statement:
INSERT INTO PUBLIC.COM_TEST_LIQUIBASE_ANALYSIS (ID, name, description, sortOrder, parent_ID, owner, removed) VALUES ('9926ee2b-a22d-4a49-9a46-073e20dcdc9d', 'Test Liquibase', 'Test', 0, '9926ee2b-a22d-4a49-9a46-073e20dcdc9d', 'Test', FALSE) [42102-200] [Failed SQL: (42102) INSERT INTO PUBLIC.COM_TEST_LIQUIBASE_ANALYSIS (ID, name, description, sortOrder, parent_ID, owner, removed) VALUES ('9926ee2b-a22d-4a49-9a46-073e20dcdc9d', 'Test Liquibase', 'Test', 0, '9926ee2b-a22d-4a49-9a46-073e20dcdc9d', 'Test', FALSE)]
2021-08-25 13:08:27.547  INFO 62336 --- [           main] liquibase.lockservice                    : Successfully released change log lock

  1. Please share the YAML changeset file with us.
    My changelog.yaml

    image
    image706×117 11.4 KB

    My changeset

    image
    image2136×862 71.6 KB

The generated statement is the insert statement generated by liquibase as you can also see in the logs which I have posted. So, If i take this and directly go to h2-console and try to run then it works.

4

Hello,
Any update on this?

5

I’ll preface by saying that I’m not familiar with H2, but here are a couple of observations…

I see “PUBLIC” schema prefix on the table name in the Liquibase log. Are you running that exact statement in the h2-console.

Is the userid that you are using to run the insert in the h2-console the same as the userid that Liquibase runs as?

6

HI @daryldoak
I tried the exact insert statement with PUBLIC in h2-console and it worked. The username is coming from the spring boot data source and I too use the same to login to h2-console and I am pretty much sure even liquibase uses the same username.