Can't start the app after generating a project with MSSQL : Cannot insert explicit value when IDENTITY_INSERT is set to OFF

[souyahia]

Overview of the issue

After generating a project with MSSQL, and starting the app with IntelliJ, we get the following error :

t.j.c.liquibase.AsyncSpringLiquibase     : Liquibase could not start correctly, your database is NOT ready: liquibase.exception.MigrationFailedException: Migration failed for change set config/liquibase/changelog/00000000000000_initial_schema.xml::00000000000001::jhipster:
     Reason: liquibase.exception.DatabaseException: java.sql.BatchUpdateException: Cannot insert explicit value for identity column in table 'jhi_user' when IDENTITY_INSERT is set to OFF.

Even though in the 00000000000000_initial_schema.xml we have the following properties :

        <ext:loadData
                  file="config/liquibase/data/user.csv"
                  separator=";"
                  identityInsertEnabled="true"
                  tableName="jhi_user">
            <column name="activated" type="boolean"/>
            <column name="created_date" type="timestamp"/>
        </ext:loadData>

With the identityInsertEnabled, set to true (generated like this by default), we still can't start the app properly because liquibase can't manage to load the custom data in the CSV.

Motivation for or Use Case

Liquibase does not work properly right after generating the project, and we did not add any custom code.

Reproduce the error

Using Java 15, generating a project with MSSQL (with the JHipster configuration given below), and then running the project with IntelliJ.

JHipster Version(s)

JHipster 7.0.1

JHipster configuration

{
  "generator-jhipster": {
    "entitySuffix": "Entity",
    "dtoSuffix": "JhiDto",
    "blueprints": [],
    "otherModules": [],
    "applicationType": "monolith",
    "baseName": "myApp",
    "jhipsterVersion": "7.0.1",
    "skipClient": false,
    "skipServer": false,
    "skipUserManagement": false,
    "skipCheckLengthOfIdentifier": false,
    "skipFakeData": false,
    "jhiPrefix": "jhi",
    "testFrameworks": ["cypress", "gatling", "cucumber"],
    "pages": [],
    "creationTimestamp": 1621254883770,
    "serviceDiscoveryType": false,
    "reactive": true,
    "authenticationType": "jwt",
    "packageName": "my.app",
    "serverPort": "8080",
    "cacheProvider": "no",
    "enableHibernateCache": false,
    "databaseType": "sql",
    "devDatabaseType": "mssql",
    "prodDatabaseType": "mssql",
    "buildTool": "maven",
    "serverSideOptions": ["enableSwaggerCodegen:true"],
    "websocket": false,
    "searchEngine": false,
    "messageBroker": false,
    "enableSwaggerCodegen": true,
    "clientFramework": "angularX",
    "withAdminUi": true,
    "clientTheme": "none",
    "enableTranslation": true,
    "nativeLanguage": "fr",
    "packageFolder": "my/app",
    "jwtSecretKey": "My JWT Secret Key",
    "clientPackageManager": "npm",
    "clientThemeVariant": "",
    "languages": ["fr", "en"]
  }
}

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

We do not have the generated .jhipster directory since it is still a blank project.

Browsers and Operating System

Ubuntu 20.04

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

[souyahia]

After adding the following SQL lines around the user.csv loadData XML tag :

        <sql dbms="mssql">
            SET IDENTITY_INSERT jhi_user ON;
        </sql>
        <ext:loadData
            file="config/liquibase/data/user.csv"
            separator=";"
            identityInsertEnabled="true"
            tableName="jhi_user">
            <column name="activated" type="boolean"/>
            <column name="created_date" type="timestamp"/>
        </ext:loadData>
        <sql dbms="mssql">
            SET IDENTITY_INSERT jhi_user OFF;
        </sql>

The Liquibase migrations work fine and the app can start. But we now can't log in with the admin account because the server can't establish connection with the database. We get the following error in the logs :

org.springframework.transaction.CannotCreateTransactionException: Could not open R2DBC Connection for transaction; nested exception is java.net.UnknownHostException: failed to resolve 'localhost:1433;database=myApp' after 9 queries

I can log into the database through IntelliJ without any issues, so it seems like there's an issue with the R2DBC driver. Is it even possible to use webflux with MSSQL ?

I don't know if it is still related to the same issue, or if I should open a new one.

[mraible]

Is it even possible to use webflux with MSSQL ?

It looks like there is an R2DBC driver for MSSQL, so it should be possible.

https://github.com/r2dbc/r2dbc-mssql

I tried generating an app with your .yo-rc.json and then searched its pom.xml for r2dbc-mssql. It does exist.

<dependency>
  <groupId>io.r2dbc</groupId>
  <artifactId>r2dbc-mssql</artifactId>
</dependency>

Next, I tried to start it up by starting MSSQL in Docker.

docker-compose -f src/main/docker/mssql.yml up

Then, I started the app with ./mvnw. I got a number of compilation errors on startup, which is different from your error.

[INFO] Compiling 66 source files to /Users/mraible/mssql/target/classes
[INFO] -------------------------------------------------------------
[ERROR] COMPILATION ERROR :
[INFO] -------------------------------------------------------------
[ERROR] /Users/mraible/mssql/src/main/java/my/app/repository/UserRepository.java:[34,57] cannot find symbol
  symbol: class User
[ERROR] /Users/mraible/mssql/src/main/java/my/app/repository/UserRepository.java:[65,57] cannot find symbol
  symbol: class User
[ERROR] /Users/mraible/mssql/src/main/java/my/app/service/UserService.java:[355,17] cannot find symbol
  symbol:   class User
  location: class my.app.service.UserService

Renaming my.app.domain.UserEntity to my.app.domain.User gets me a bit further.

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 
'tokenProvider' defined in file [/Users/mraible/mssql/target/classes/my/app/security/jwt/TokenProvider.class]: 
Bean instantiation via constructor failed; nested exception is org.springframework.beans.BeanInstantiationException: 
Failed to instantiate [my.app.security.jwt.TokenProvider]: Constructor threw exception; 
nested exception is io.jsonwebtoken.io.DecodingException: Illegal base64 character: ' '

I updated the base64-secret secret's value in application-dev.yml and tried again. This time, it started but I'm unable to log in.

org.springframework.transaction.CannotCreateTransactionException: Could not open R2DBC Connection for transaction;
 nested exception is java.net.UnknownHostException: failed to resolve 'localhost:1433;database=myApp' after 2 queries
    at org.springframework.r2dbc.connection.R2dbcTransactionManager.lambda$null$5(R2dbcTransactionManager.java:226)
    Suppressed: reactor.core.publisher.FluxOnAssembly$OnAssemblyException:

[souyahia]

Thanks for answering !

Yes, I forgot to mention that after specifying an entity suffix (in our case, we wanted to add "Entity" at the end of the entity class names generated by JHipster), the generator forgot to add the suffix in some parts of the code. Instead of renaming UserEntity to User we updated the imports and the symbol names in the files that we causing the issues.

[souyahia]

We managed to get the connection with R2DBC working. In the application-dev.yml, we changed the r2dbc service from :

r2dbc:
    url: r2dbc:sqlserver://localhost:1433;database=myApp
    username: SA
    password: yourStrong(!)Password

To :

r2dbc:
    url: r2dbc:mssql://localhost:1433/myApp
    username: SA
    password: yourStrong(!)Password

Essentially switching sqlserver protocol with mssql, changing the way the database is specified (/myApp instead of ;database=myApp).

After these changes as well as adding the entity suffix where the generator forgot to add them, and adding the SQL tags in the Liquibase initialSchema, we managed to get the app to work properly ! 🎉

[mraible]

That's great to hear! I'll put a bug bounty on this to get it fixed. Please create a PR if you feel like it.

[souyahia]

Also, last thing I forgot to mention, the integration tests were not working properly after the generation, the migrations were not executed in the H2 database for the tests. To fix this, we changed the changeSet XML tag in the initial schema from :

    <changeSet id="00000000000001" author="jhipster" dbms="mssql">

to :

    <changeSet id="00000000000001" author="jhipster">

And then the IT worked just fine.