search

jhipster / generator-jhipster

JHipster is a development platform to quickly generate, develop, & deploy modern web applications & microservice architectures.
https://www.jhipster.tech
Apache License 2.0
21.53k stars 4.02k forks source link

Liquibase fails to populate PostgreSQL database with blob data #19940

Closed pavel-shareiko closed 9 months ago

pavel-shareiko commented 2 years ago
Overview of the issue

After upgrading from JHipster v7.7.0 to v7.9.3, fake data containing blobs stopped working.

Previously, the generator itself created fake data for blob fields, now csv files are empty. If you fill the CSV manually, then at startup, the Liquibase cannot start the database and throws an error. The bug is relevant even for a newly generated application.

Rolling back the Liquibase version from 4.15 to 4.6.1 solves the issue, but this solution does not suit us.

At startup, we have the following stack trace:

Liquibase could not start correctly, your database is NOT ready: liquibase.exception.MigrationFailedException: Migration failed for changeset config/liquibase/changelog/20221004083439_added_entitySaleTicket.xml::20221004083439-1-data::jhipster: Reason: liquibase.exception.DatabaseException: org.postgresql.util.PSQLException: ERROR: column "ticket" is of type bytea but expression is of type bigint Hint: You will need to rewrite or cast the expression. Position: 101

liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for changeset config/liquibase/changelog/20221004083439_added_entity_SaleTicket.xml::20221004083439-1-data::jhipster: Reason: liquibase.exception.DatabaseException: org.postgresql.util.PSQLException: ERROR: column "ticket" is of type bytea but expression is of type bigint Hint: You will need to rewrite or cast the expression. Position: 101 at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:126) at liquibase.Liquibase.lambda$null$0(Liquibase.java:263) at liquibase.Scope.lambda$child$0(Scope.java:180) at liquibase.Scope.child(Scope.java:189) at liquibase.Scope.child(Scope.java:179) at liquibase.Scope.child(Scope.java:158) at liquibase.Scope.child(Scope.java:243) at liquibase.Liquibase.lambda$update$1(Liquibase.java:262) at liquibase.Scope.lambda$child$0(Scope.java:180) at liquibase.Scope.child(Scope.java:189) at liquibase.Scope.child(Scope.java:179) at liquibase.Scope.child(Scope.java:158) at liquibase.Liquibase.runInScope(Liquibase.java:2414) at liquibase.Liquibase.update(Liquibase.java:209) at liquibase.Liquibase.update(Liquibase.java:195) at liquibase.integration.spring.SpringLiquibase.performUpdate(SpringLiquibase.java:314) at liquibase.integration.spring.SpringLiquibase.afterPropertiesSet(SpringLiquibase.java:269) at org.springframework.boot.autoconfigure.liquibase.DataSourceClosingSpringLiquibase.afterPropertiesSet(DataSourceClosingSpringLiquibase.java:46) at tech.jhipster.config.liquibase.AsyncSpringLiquibase.initDb(AsyncSpringLiquibase.java:118) at tech.jhipster.config.liquibase.AsyncSpringLiquibase.lambda$afterPropertiesSet$0(AsyncSpringLiquibase.java:93) at tech.jhipster.async.ExceptionHandlingAsyncTaskExecutor.lambda$createWrappedRunnable$1(ExceptionHandlingAsyncTaskExecutor.java:79) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) at java.base/java.lang.Thread.run(Thread.java:829) Caused by: liquibase.exception.MigrationFailedException: Migration failed for changeset config/liquibase/changelog/20221004083439_added_entity_SaleTicket.xml::20221004083439-1-data::jhipster: Reason: liquibase.exception.DatabaseException: org.postgresql.util.PSQLException: ERROR: column "ticket" is of type bytea but expression is of type bigint Hint: You will need to rewrite or cast the expression. Position: 101 at liquibase.changelog.ChangeSet.execute(ChangeSet.java:708) at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:56) at liquibase.changelog.ChangeLogIterator$2.lambda$null$0(ChangeLogIterator.java:113) at liquibase.Scope.lambda$child$0(Scope.java:180) at liquibase.Scope.child(Scope.java:189) at liquibase.Scope.child(Scope.java:179) at liquibase.Scope.child(Scope.java:158) at liquibase.changelog.ChangeLogIterator$2.lambda$run$1(ChangeLogIterator.java:112) at liquibase.Scope.lambda$child$0(Scope.java:180) at liquibase.Scope.child(Scope.java:189) at liquibase.Scope.child(Scope.java:179) at liquibase.Scope.child(Scope.java:158) at liquibase.Scope.child(Scope.java:243) at liquibase.changelog.ChangeLogIterator$2.run(ChangeLogIterator.java:93) at liquibase.Scope.lambda$child$0(Scope.java:180) at liquibase.Scope.child(Scope.java:189) at liquibase.Scope.child(Scope.java:179) at liquibase.Scope.child(Scope.java:158) at liquibase.Scope.child(Scope.java:243) at liquibase.Scope.child(Scope.java:247) at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:65) ... 23 common frames omitted Caused by: liquibase.exception.DatabaseException: org.postgresql.util.PSQLException: ERROR: column "ticket" is of type bytea but expression is of type bigint Hint: You will need to rewrite or cast the expression. Position: 101 at liquibase.statement.ExecutablePreparedStatementBase.execute(ExecutablePreparedStatementBase.java:110) at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:151) at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1299) at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1281) at liquibase.changelog.ChangeSet.execute(ChangeSet.java:673) ... 43 common frames omitted Caused by: org.postgresql.util.PSQLException: ERROR: column "ticket" is of type bytea but expression is of type bigint Hint: You will need to rewrite or cast the expression. Position: 101 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408) at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:167) at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:156) at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44) at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java) at liquibase.statement.ExecutablePreparedStatementBase.executePreparedStatement(ExecutablePreparedStatementBase.java:166) at liquibase.statement.ExecutablePreparedStatementBase.execute(ExecutablePreparedStatementBase.java:108) ... 47 common frames omitted

Motivation for or Use Case

You are unable to start application with predefined data containing images or any blob data. It slows down development a lot, as soon as you have to manually fill the data every time application starts.

Reproduce the error

1) Generate the application 2) Select PostgreSQL as your database 3) Generate entity and add the field of Blob type 4) Go to src/main/resources/config/liquibase/fake-data and add to the generated CSV file line containing reference to some blob file and its content type. (for instance, ../fake-data/blob/hipster.png;image/png). As a result, you should get a CSV file looking like this: id;ticket;ticket_content_type 1;../fake-data/blob/hipster.png;image/png 5) Start your application.

Related issues

Didn't find any. Probably problem appeared after #18071 issue. There are some comments related to populating issues with MariaDB, maybe its can help somehow. Can be related to this liquibase/liquibase#2519 liquibase/liquibase#2595 liquibase/liquibase#2923

Suggest a Fix

No idea

JHipster Version(s)

v7.9.3

JHipster configuration
JHipster Version(s)
def@0.0.1-SNAPSHOT /home/pavel/Desktop/default-jhi
└── generator-jhipster@7.9.3
JHipster configuration, a .yo-rc.json file generated in the root folder
.yo-rc.json file 
{
  "generator-jhipster": {
    "applicationIndex": 0,
    "applicationType": "monolith",
    "authenticationType": "jwt",
    "baseName": "def",
    "blueprints": [],
    "buildTool": "gradle",
    "cacheProvider": "ehcache",
    "clientFramework": "angularX",
    "clientPackageManager": "npm",
    "clientTheme": "none",
    "clientThemeVariant": "",
    "creationTimestamp": 1664872374379,
    "databaseType": "sql",
    "devDatabaseType": "postgresql",
    "devServerPort": 4200,
    "dtoSuffix": "DTO",
    "enableGradleEnterprise": false,
    "enableHibernateCache": true,
    "enableSwaggerCodegen": false,
    "enableTranslation": true,
    "entities": ["SaleTicket"],
    "entitySuffix": "",
    "gradleEnterpriseHost": "",
    "jhiPrefix": "jhi",
    "jhipsterVersion": "7.9.3",
    "jwtSecretKey": "YourJWTSecretKeyWasReplacedByThisMeaninglessTextByTheJHipsterInfoCommandForObviousSecurityReasons",
    "languages": ["en", "al"],
    "lastLiquibaseTimestamp": 1664872479000,
    "messageBroker": false,
    "microfrontend": false,
    "microfrontends": [],
    "nativeLanguage": "en",
    "otherModules": [],
    "packageFolder": "com/mycompany/myapp",
    "packageName": "com.mycompany.myapp",
    "pages": [],
    "prodDatabaseType": "postgresql",
    "reactive": false,
    "searchEngine": false,
    "serverPort": "8080",
    "serverSideOptions": [],
    "serviceDiscoveryType": "no",
    "skipCheckLengthOfIdentifier": false,
    "skipClient": false,
    "skipFakeData": false,
    "skipUserManagement": false,
    "testFrameworks": [],
    "websocket": false,
    "withAdminUi": true
  }
}
JDL for the Entity configuration(s) entityName.json files generated in the .jhipster directory
JDL entity definitions 
entity SaleTicket {
  ticket ImageBlob
}
dto SaleTicket with mapstruct
paginate SaleTicket with pagination
service SaleTicket with serviceClass
filter SaleTicket
Environment and Tools

openjdk version "11.0.13" 2021-10-19 LTS OpenJDK Runtime Environment Zulu11.52+13-CA (build 11.0.13+8-LTS) OpenJDK 64-Bit Server VM Zulu11.52+13-CA (build 11.0.13+8-LTS, mixed mode)

git version 2.34.1

node: v16.14.0

npm: 8.3.1

Docker version 20.10.18, build b40c2f6

docker-compose version 1.29.2, build unknown

PostgreSQL is running in docker. Image used: postgres:11 CSV file contents used for testing:

id;ticket;ticket_content_type 1;../fake-data/blob/hipster.png;image/png

Browsers and Operating System

OS: Ubuntu 22.04

pavel-shareiko commented 1 year ago

https://github.com/liquibase/liquibase/pull/3381#pullrequestreview-1167513569

Ebsan commented 1 year ago

Having the same issue. It looks like liquibase released v4.18.0 with the fix. Will jhipster update the dependency to liquibase too?

Nvm, I see that liquibase has been updated in jhipster's master branch. This should be fixed with the next release of Jhipster.

jsm174 commented 1 year ago

I tried to manually bump liquibase to 4.18.0 and it locks up during this step. Maybe there is some other dependency needed, but just commenting incase anyone else tries.

copperschnack commented 1 year ago

@jsm174 manual bumping does seem to work for Liquibase v4.19.0

jsm174 commented 1 year ago

@copperschnack - Awesome, I will give it a try!

jsm174 commented 1 year ago

So I finally had a chance to give this a try. Still locks up at update_002_02.xml

2023-01-27 14:32:09.446 DEBUG 1 --- [           main] c.u.i.config.WebConfigurer               : Registering CORS filter
2023-01-27 14:32:09.564  INFO 1 --- [           main] c.u.i.config.WebConfigurer               : Web application configuration, using profiles: prod
2023-01-27 14:32:09.566  INFO 1 --- [           main] c.u.i.config.WebConfigurer               : Web application fully configured
2023-01-27 14:32:10.568 DEBUG 1 --- [           main] c.u.i.config.AsyncConfiguration          : Creating Async Task Executor
2023-01-27 14:32:10.666 DEBUG 1 --- [           main] c.u.i.config.LiquibaseConfiguration      : Configuring Liquibase
Running Changeset: config/liquibase/changelog/00000000000000_initial_schema.xml::00000000000000::jhipster
Running Changeset: config/liquibase/changelog/00000000000000_initial_schema.xml::00000000000001::jhipster
.
.
.
Running Changeset: config/liquibase/changelog/update_001/update_001.xml::update_001::jmillard
Running Changeset: config/liquibase/changelog/update_002/update_002_01.xml::update_002_01::jmillard
Running Changeset: config/liquibase/changelog/update_002/update_002_02.xml::update_002_02::jmillard

All update_002_2.xml does it:

    <changeSet id="update_002_02" author="jmillard">

        <update tableName="design">
            <column name="image_thumb" valueBlobFile="images/5e4a0fe0-11fa-461d-a740-bfdbda998c83/7e99e6fa-103b-444a-ab65-3124e2ea508d_thumb.jpg"/>
            <column name="image_thumb_content_type" value="image/jpeg"/>

            <column name="image" valueBlobFile="images/5e4a0fe0-11fa-461d-a740-bfdbda998c83/7e99e6fa-103b-444a-ab65-3124e2ea508d.png"/>
            <column name="image_content_type" value="image/png"/>

            <column name="label_icon" valueBlobFile="images/5e4a0fe0-11fa-461d-a740-bfdbda998c83/7e99e6fa-103b-444a-ab65-3124e2ea508d_label.png"/>
            <column name="label_icon_content_type" value="image/png"/>

            <where>id = '6e99e6fa-103b-444a-ab65-3124e2ea508d'</where> 
        </update>
copperschnack commented 1 year ago

@jsm174 - Did you try inspecting your DB server's locking information to check what is causing the waiting issue?

github-actions[bot] commented 9 months ago

This issue is stale because it has been open for too long without any activity. Due to the moving nature of jhipster generated application, bugs can become invalid. If this issue still applies please comment otherwise it will be closed in 7 days