Liquibase Fails on Integration Tests

[jamescrdyer]

Overview of the issue

When running the integration tests on an unmodified MSSQL backend project, the JHI_USER table creation throws an error when running the tests.

Reproduce the error

Fresh monolithic JHipster project with MS SQL database. Then run mvn -Pprod verify. Problem does not occur when run with MySQL configuration.

Error from the integration test: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'liquibase' defined in class path resource [ca/icas/portal/config/LiquibaseConfiguration.class]: Invocation of init method failed; nested exception is liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for change set config/liquibase/changelog/00000000000000_initial_schema.xml::00000000000001::jhipster: Reason: liquibase.exception.DatabaseException: Syntax error in SQL statement "IF[*] EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'jhi_user' AND TABLE_SCHEMA = 'PUBLIC' AND COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1) SET IDENTITY_INSERT PUBLIC.JHI_USER ON"; SQL statement: IF EXISTS(select TABLE_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'jhi_user' and TABLE_SCHEMA = 'PUBLIC' and COLUMNPROPERTY(object_id(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1) SET IDENTITY_INSERT PUBLIC.jhi_user ON [42000-200] [Failed SQL: (42000) IF EXISTS(select TABLE_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'jhi_user' and TABLE_SCHEMA = 'PUBLIC' and COLUMNPROPERTY(object_id(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1) SET IDENTITY_INSERT PUBLIC.jhi_user ON]

Related issues

Did not find any

Suggest a Fix

Not sure. Change the liquibase 00000000000000_initial_schema.xml file somehow to avoid this issue.

JHipster Version(s)

JHipster version: 7.3.1

JHipster configuration

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

.yo-rc.json file

{
  "generator-jhipster": {
    "applicationType": "monolith",
    "authenticationType": "jwt",
    "baseName": "portal",
    "blueprints": [],
    "buildTool": "maven",
    "cacheProvider": "ehcache",
    "clientFramework": "angularX",
    "clientPackageManager": "npm",
    "clientTheme": "none",
    "clientThemeVariant": "",
    "creationTimestamp": 1651861838522,
    "cypressCoverage": true,
    "databaseType": "sql",
    "devDatabaseType": "mssql",
    "devServerPort": 4200,
    "dtoSuffix": "DTO",
    "enableGradleEnterprise": false,
    "enableHibernateCache": true,
    "enableSwaggerCodegen": false,
    "enableTranslation": true,
    "entitySuffix": "",
    "jhiPrefix": "jhi",
    "jhipsterVersion": "7.8.1",
    "jwtSecretKey": "YourJWTSecretKeyWasReplacedByThisMeaninglessTextByTheJHipsterInfoCommandForObviousSecurityReasons",
    "languages": ["en", "fr"],
    "messageBroker": false,
    "nativeLanguage": "en",
    "otherModules": [],
    "packageName": "ca.icas.portal",
    "pages": [],
    "prodDatabaseType": "mssql",
    "reactive": false,
    "searchEngine": false,
    "serverPort": "8080",
    "serverSideOptions": ["websocket:spring-websocket"],
    "serviceDiscoveryType": "no",
    "skipCheckLengthOfIdentifier": false,
    "skipFakeData": false,
    "skipUserManagement": false,
    "testFrameworks": ["cypress"],
    "websocket": "spring-websocket",
    "withAdminUi": true
  }
}

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

JDL entity definitions

Environment and Tools

java version "17.0.2" 2022-01-18 LTS Java(TM) SE Runtime Environment (build 17.0.2+8-LTS-86) Java HotSpot(TM) 64-Bit Server VM (build 17.0.2+8-LTS-86, mixed mode, sharing)

git version 2.35.1.windows.2

node: v16.13.2

npm: 8.5.5

Docker version 20.10.10, build b485636

docker-compose version 1.29.2, build 5becea4c

No change to package.json was detected. No package manager install will be executed. Congratulations, JHipster execution is complete!

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

No entities generated

Browsers and Operating System

Windows 10 Pro OS Build: 19044.1526

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

[DanielFran]

This has been fixed with https://github.com/jhipster/generator-jhipster/pull/16612 with v.7.5.0 Can you update the application and confirm it is OK?

I will close it for now. You can reopen it if it is still happening with the latest version.

[jamescrdyer]

Thanks for the response @DanielFran. I have generated using JHipster 7.8.1, and am still getting the same liquibase failure on the integration tests.

[DanielFran]

Thanks @jamescrdyer for the confirmation. This seems duplicated with https://github.com/jhipster/generator-jhipster/issues/17449

Can you confirm that removing this dependency everything is OK?

        <dependency>
            <groupId>org.liquibase.ext</groupId>
            <artifactId>liquibase-mssql</artifactId>
            <version>${liquibase.version}</version>
        </dependency>

[jamescrdyer]

Yes, I agree duplicate. Should I move conversation over there?

Dropping that dependency did not resolve the error.

[DanielFran]

Yes better to migrate there. Duplicate with https://github.com/jhipster/generator-jhipster/issues/17449