Generator-jhipster: New project (6.5.1) w/ postgresql, liquibase:diff won't find FixedPostgreSQL95Dialect and liquibase:diff changelog output fails to execute

[nuba]

Overview of the issue

In a clean, new, project, using postgresql, liquibase won't find the custom jhipster postgresql driver dialect, and switching that driver results in a liquibase:diff output that fails to run.

This is partially similar to #10885, created earlier today.

Motivation for or Use Case

I expect a newly created project to output no changes with liquibase:diff, and also expect the custom jhipster postgresql dialect driver to be found.

Reproduce the error

I created a repository each step in a commit: https://github.com/nuba/bug-report-jhipster-liquibase

Error messages are

[ERROR] Failed to execute goal org.liquibase:liquibase-maven-plugin:3.6.3:diff (default-cli) on project jhipster: Error setting up or running Liquibase: liquibase.exception.UnexpectedLiquibaseException: liquibase.exception.DatabaseException: java.lang.ClassNotFoundException: io.github.jhipster.domain.util.FixedPostgreSQL95Dialect -> [Help 1]

And

[ERROR] Failed to execute goal org.liquibase:liquibase-maven-plugin:3.6.3:update (default-cli) on project jhipster: Error setting up or running Liquibase: Migration failed for change set config/liquibase/changelog/20191205215152_changelog.xml::1575582718315-1::nuba (generated):
[ERROR]      Reason: liquibase.exception.DatabaseException: ERROR: constraint "uc_jhi_useremail_col" of relation "jhi_user" does not exist [Failed SQL: ALTER TABLE jhi_user DROP CONSTRAINT UC_JHI_USEREMAIL_COL]
[ERROR] -> [Help 1]

Here is the table definition in postgres

bugreport=# \d jhi_user;
                              Table "public.jhi_user"
       Column       |            Type             | Collation | Nullable | Default 
--------------------+-----------------------------+-----------+----------+---------
 id                 | bigint                      |           | not null | 
 login              | character varying(50)       |           | not null | 
 password_hash      | character varying(60)       |           | not null | 
 first_name         | character varying(50)       |           |          | 
 last_name          | character varying(50)       |           |          | 
 email              | character varying(191)      |           |          | 
 image_url          | character varying(256)      |           |          | 
 activated          | boolean                     |           | not null | 
 lang_key           | character varying(10)       |           |          | 
 activation_key     | character varying(20)       |           |          | 
 reset_key          | character varying(20)       |           |          | 
 created_by         | character varying(50)       |           | not null | 
 created_date       | timestamp without time zone |           |          | 
 reset_date         | timestamp without time zone |           |          | 
 last_modified_by   | character varying(50)       |           |          | 
 last_modified_date | timestamp without time zone |           |          | 
Indexes:
    "jhi_user_pkey" PRIMARY KEY, btree (id)
    "ux_user_email" UNIQUE CONSTRAINT, btree (email)
    "ux_user_login" UNIQUE CONSTRAINT, btree (login)
Referenced by:
    TABLE "jhi_user_authority" CONSTRAINT "fk_user_id" FOREIGN KEY (user_id) REFERENCES jhi_user(id)

##### **Related issues** Yes, #10885 mentions the diff output, but not the driver issue. ##### **JHipster Version(s)** 6.5.1 ##### **JHipster configuration** INFO! Using JHipster version installed locally in current project's node_modules INFO! Executing jhipster:info INFO! Options: from-cli: true ##### **JHipster Version(s)**

[email protected] /home/nuba/IdeaProjects/bug-reports/jhipster-liquibase
└── [email protected] 

##### **JHipster configuration, a `.yo-rc.json` file generated in the root folder**

.yo-rc.json file

{
  "generator-jhipster": {
    "promptValues": {
      "packageName": "bug.report"
    },
    "jhipsterVersion": "6.5.1",
    "applicationType": "monolith",
    "baseName": "jhipster",
    "packageName": "bug.report",
    "packageFolder": "bug/report",
    "serverPort": "8080",
    "authenticationType": "jwt",
    "cacheProvider": "no",
    "websocket": false,
    "databaseType": "sql",
    "devDatabaseType": "postgresql",
    "prodDatabaseType": "postgresql",
    "searchEngine": false,
    "messageBroker": false,
    "serviceDiscoveryType": false,
    "buildTool": "maven",
    "enableSwaggerCodegen": false,
    "jwtSecretKey": "bXktc2VjcmV0LXRva2VuLXRvLWNoYW5nZS1pbi1wcm9kdWN0aW9uLWFuZC10by1rZWVwLWluLWEtc2VjdXJlLXBsYWNl",
    "embeddableLaunchScript": false,
    "useSass": true,
    "clientPackageManager": "npm",
    "skipClient": true,
    "testFrameworks": [],
    "jhiPrefix": "jhi",
    "entitySuffix": "",
    "dtoSuffix": "DTO",
    "otherModules": [],
    "enableTranslation": false,
    "blueprints": []
  }
}

JDL for the Entity configuration(s) entityName.json files generated in the .jhipster directory

JDL entity definitions

Environment and Tools

openjdk version "11.0.5-ea" 2019-10-15
OpenJDK Runtime Environment (build 11.0.5-ea+10-post-Ubuntu-0ubuntu1)
OpenJDK 64-Bit Server VM (build 11.0.5-ea+10-post-Ubuntu-0ubuntu1, mixed mode, sharing)

git version 2.20.1

node: v12.13.1

npm: 6.13.1

yeoman: 3.1.1

Docker version 19.03.2, build 6a30dfca03

INFO! Congratulations, JHipster execution is complete!

Entity configuration(s) entityName.json files generated in the .jhipster directory

No entity has been created. The app had just been generated.

Browsers and Operating System

Ubuntu 19.10

• [x] Checking this box is mandatory (this is just to show you read everything)

[nuba]

Solved the driver issue by adding io.github.jhipster as a dependency for liquibase inside the appropriate pom.xml section, under project > build > pluginManagement > plugins > plugin

                        <dependency>
                            <groupId>io.github.jhipster</groupId>
                            <artifactId>jhipster-framework</artifactId>
                            <version>${jhipster-dependencies.version}</version>
                        </dependency>

[nuba]

Liquibase was going for the wrong constraint name. I decided to name them explicitly, as shown below, and the problem went away.

/**
 * A user.
 */
@Entity
@Table(name = "jhi_user", uniqueConstraints = {
    @UniqueConstraint(name = "ux_user_login", columnNames = "login"),
    @UniqueConstraint(name = "ux_user_email", columnNames = "email"),
})
public class User extends AbstractAuditingEntity implements Serializable {

And it looks like the liquibase community is fine with it dropping and creating the same constraint again: https://stackoverflow.com/questions/42033408/liquibase-always-generating-changesets-for-creating-dropping-index

Even though some might consider it a noop, for me, if it's not a bug, then it's just bad practice. This may be ok in a dev environment, but, when deploying, we do run these changelogs in production, where tables can be huge and index rebuilding expensive.

[nuba]

Just pushed the fixes above to my repo, each step in a separated commit, hope it helps whoever's triaging this issue and also those running into the same problem.

https://github.com/nuba/bug-report-jhipster-liquibase/commits/master

Thanks guys for jhipster! :)

[pmverma]

I can confirm the driver's issue.
This is a regression coming after #10736 where default PG dialect was replaced with custom dialect hence needing to import <artifactId>jhipster-framework</artifactId> in plugin dependencies to load the related class file.

@nuba Can you do a PR as you have mentioned in https://github.com/jhipster/generator-jhipster/issues/10887#issuecomment-562383926?

[atomfrede]

Is this related to https://github.com/jhipster/generator-jhipster/issues/10885?

[pmverma]

@atomfrede Actually there are two issues here.
One being PG dialect while another is the SQL constraint.

[nuba]

@pmverma I didn't close the issue from the commit because liquibase is still trying to drop constraints that don't exist, and that's still unaddressed, so far.

Would you like me to add the unique constraints annotations, too? But I'm not so sure it won't break things, as jhipster supports many databases, and they're a finicky bunch, aren't they? huh... anyways, let me know!

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<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-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.6.xsd">
    <changeSet author="nuba (generated)" id="1575651897755-1">
        <dropUniqueConstraint constraintName="UC_JHI_USEREMAIL_COL" tableName="jhi_user"/>
        <addUniqueConstraint columnNames="email" constraintName="UC_JHI_USEREMAIL_COL" tableName="jhi_user"/>
    </changeSet>
    <changeSet author="nuba (generated)" id="1575651897755-2">
        <dropUniqueConstraint constraintName="UC_JHI_USERLOGIN_COL" tableName="jhi_user"/>
        <addUniqueConstraint columnNames="login" constraintName="UC_JHI_USERLOGIN_COL" tableName="jhi_user"/>
    </changeSet>
</databaseChangeLog>

[pmverma]

Thanks @nuba, before adding your mentioned constraints, we need to check the following liquibase column definition.
May be liquibase has changes the way it checks/loads the definition files.

          <column name="login" type="varchar(50)">
                <constraints unique="true" nullable="false" uniqueConstraintName="ux_user_login"/>
            </column>
            <%_ if (authenticationType !== 'oauth2') { _%>
            <column name="password_hash" type="varchar(60)"/>
            <%_ } _%>
            <column name="first_name" type="varchar(50)"/>
            <column name="last_name" type="varchar(50)"/>
            <column name="email" type="varchar(191)">
                <constraints unique="true" nullable="true" uniqueConstraintName="ux_user_email"/>
            </column>

vs

    <changeSet author="nuba (generated)" id="1575651897755-1">
        <addUniqueConstraint columnNames="email" constraintName="UC_JHI_USEREMAIL_COL" tableName="jhi_user"/>
    </changeSet>
    <changeSet author="nuba (generated)" id="1575651897755-2">
        <addUniqueConstraint columnNames="login" constraintName="UC_JHI_USERLOGIN_COL" tableName="jhi_user"/>
    </changeSet>

I also see there is a difference between constraint names.

[nuba]

As a side note:

Have you considered the idea of generating the classes, only? And have liquibase flex its muscles to generate the initial changelogs? It's something that could be done right after the app is generated.

• User points to his blank state database of choice.
• Run liquibase:diff in dev or prod against that database, that's the bulk of the initial changelog.
• Update. Then, in text context, run liquibase:diff again. That'll give you jhi_date_time_wrapper for DateTimeWrapper.

Sequences, however, have to be manually written :/ as liquibase-hibernate won't honor the initialValue and allocationSize properties of @SequenceGenerator. Apparently, that's a long standing issue.

[pmverma]

I am not in favor of generating definition files from DB as

• It involves many steps including some manual, as you have outlined
• We can not have separate files for each entity which is easier to check and see
• We want to know in-prior what is going to be updated in the production database ( of course we can use dev db to check but above points still remain to consider)

[pascalgrimaud]

Adding a bounty on this, as it requires some time and analyze to find a good solution

[amatosg]

Solved the driver issue by adding io.github.jhipster as a dependency for liquibase inside the appropriate pom.xml section, under project > build > pluginManagement > plugins > plugin

                        <dependency>
                            <groupId>io.github.jhipster</groupId>
                            <artifactId>jhipster-framework</artifactId>
                            <version>${jhipster-dependencies.version}</version>
                        </dependency>

I just tried and this did not solved the ClassNotFoundException issue, still getting

[ERROR] Failed to execute goal org.liquibase:liquibase-maven-plugin:3.6.3:diff (default-cli) on project facturapp: Error setting up or running Liquibase: liquibase.exception.UnexpectedLiquibaseException: liquibase.exception.DatabaseException: java.lang.ClassNotFoundException: io.github.jhipster.domain.util.FixedPostgreSQL95Dialect

[backnotprop]

Hey all, this is a legitimate issue. Is there a suggested solution or version rollback?

[pmverma]

Hi @nuba , Would it be possible for you to follow this issue?
We also have a bug bounty on this issue 😄

[pmverma]

@nuba Can you confirm this issue with version 6.8.0?
Please see more info at https://github.com/jhipster/generator-jhipster/issues/11467#issuecomment-598571309

[DanielFran]

@pascalgrimaud This has been fixed with https://github.com/jhipster/generator-jhipster/pull/11386
Can we close this?

[DanielFran]

@pascalgrimaud Bug bounty claimed https://opencollective.com/generator-jhipster/expenses/19297

[pascalgrimaud]

@DanielFran : approved