Hibernate dialect "Incorrect string value" when using UUID as @Id

[danielbrutti]

Overview of the issue

When creating a entity to use UUID as @id column, the entity will fail on save with a "Incorrect string value" exception.

JDL

entity Area {
    id UUID,
    name String required,
}

Entity JSON

{
  "name": "Area",
  "fields": [
    {
      "fieldName": "id",
      "fieldType": "UUID"
    },
...

Area.java

    @Id
    @GeneratedValue
    private UUID id;

Motivation for or Use Case

It is not possible to save entities when using UUID to save new entities, even having utf8mb4 as character encoding in MySQL server.

Reproduce the error

• Create a microservice from a JDL definition with an entity using UUID for id column
• Try to save

Related issues

#13258

Suggest a Fix

Define the id column with the type of BINARY(16) in MySQL or it's equivalent in other SQL implementations. In my case I replaced this line on master.xml

<property name="uuidType" value="binary(16)" dbms="h2, mysql, mariadb"/>

An alternative, that I didn't tested, seems to be add @Type(type="uuid-char") annotation to use VARCHAR(36)

JHipster Version(s)

7.0.0

JHipster configuration

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


<details>
<summary>.yo-rc.json file</summary>
<pre>
{
  "generator-jhipster": {
    "blueprints": [],
    "otherModules": [],
    "applicationType": "microservice",
    "baseName": "areas",
    "jhipsterVersion": "7.0.0",
    "skipClient": true,
    "skipUserManagement": true,
    "skipServer": false,
    "skipCheckLengthOfIdentifier": false,
    "skipFakeData": true,
    "jhiPrefix": "gowo",
    "entitySuffix": "",
    "dtoSuffix": "DTO",
    "testFrameworks": ["cypress", "gatling", "cucumber"],
    "pages": [],
    "creationTimestamp": 1617147373401,
    "serviceDiscoveryType": "eureka",
    "reactive": false,
    "authenticationType": "oauth2",
    "packageName": "com.gowo.areas.service",
    "serverPort": "8081",
    "cacheProvider": "hazelcast",
    "enableHibernateCache": true,
    "databaseType": "sql",
    "devDatabaseType": "mysql",
    "prodDatabaseType": "mysql",
    "buildTool": "maven",
    "serverSideOptions": ["searchEngine:elasticsearch", "enableSwaggerCodegen:true"],
    "websocket": false,
    "searchEngine": "elasticsearch",
    "messageBroker": false,
    "enableSwaggerCodegen": true,
    "enableTranslation": true,
    "nativeLanguage": "es",
    "packageFolder": "com/gowo/areas/service",
    "jwtSecretKey": "YourJWTSecretKeyWasReplacedByThisMeaninglessTextByTheJHipsterInfoCommandForObviousSecurityReasons",
    "languages": ["es"],
    "clientFramework": "angularX",
    "clientPackageManager": "npm",
    "clientTheme": "none",
    "clientThemeVariant": "",
    "withAdminUi": true,
    "entities": ["Position", "Area"],
    "appFolder": "areas"
  }
}

</pre>
</details>


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

<details>
<summary>JDL entity definitions</summary>

<pre>
entity Position {
  id UUID
  name String required
  description String
  icon ImageBlob
}
entity Area {
  id UUID
  name String required
  description String
  owner GowoUserType
  ownerId UUID
  ownerName String
}
enum GowoUserType {
  GOWO,
  PARTNER,
  CLIENT,
  GOWER
}

relationship OneToMany {
  Area{positions} to Position{area(name) required}
  Area{children} to Area{parent(name)}
}

dto Position, Area with mapstruct
paginate Position, Area with pagination
service Position, Area with serviceClass
search Position, Area with elasticsearch
microservice Position, Area with areas
filter Position, Area
clientRootFolder Position, Area with areas

</pre>
</details>


##### **Environment and Tools**

openjdk version "11.0.10" 2021-01-19
OpenJDK Runtime Environment (build 11.0.10+9-Ubuntu-0ubuntu1.20.04)
OpenJDK 64-Bit Server VM (build 11.0.10+9-Ubuntu-0ubuntu1.20.04, mixed mode, sharing)

git version 2.25.1

node: v14.16.0

npm: 7.8.0

Docker version 20.10.5, build 55c4c88

docker-compose version 1.28.6, build 5db8d86f


No change to package.json was detected. No package manager install will be executed.

Browsers and Operating System

Linux Mint 20

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

[mshima]

Non id UUID fields uses @Type(type="uuid-char"), but BINARY(16) seems a better alternative.
Since having UUID fields is not useful for non id fields, I think we can use BINARY(16) approach.
@danielbrutti can you provide a PR with the fix?

[Tcharl]

@mshima UUID is useful in a microservice stack, where you want to reference another microservice's object id.
Anyway, your suggestion concerning the type is always relevant :-)

[danielbrutti]

Hello, sorry I didn't have time to work on this, will try to do the PR soon.

Regarding how to solve it, in my project I decided to go with @Type(type="uuid-char") in that way the UUID remains a string in the database and is more simple if you later want to query/update. Using BINARY(16) the field content is not simple to read and if you want to do an update based on UUID makes things difficult.

So, I think @Type(type="uuid-char") is the best solution. @mshima what do you hink?

[mshima]

I don’t usually use UUID and I use Postgres so I don’t have a preference.
Binary should save space and is faster, but the database should be really huge to make a difference.

@Type(type="uuid-char") is the current implementation and you have created the bug report.

Go for it.

[gzsombor]

The more annoying thing with the current UUID support is that the app currently assumes, that the ID is generated by database(or hibernate) - so if the entity does not have an ID, it is not persisted yet, and if it has, it came from the database. So in a microservice environment, the clients couldn't create an ID by itself.

[mshima]

@gzsombor it probably won’t be hard to implement. See #14108.
Most of the implementation is already there.

entity Foo {
  @Id 
  @AutoGenerate(false)
  id UUID
}

[gzsombor]

Yes, it's wont be hard, but we need to change at least:

• the EntityController to check/not check if ID is present
• the EntityControllerIT's test cases
• the Entity to add a Persistable interface, and a couple of methods

boolean isNew() { return !isPersisted; } 
@PostPersist 
@PostLoad
void setPersisted() { isPersisted = true; }

So Spring Data could know if it should call entityManager.persist(...) for the new entities or entityManager.merge(...) for the updates.

[Tcharl]

I usually do not mix technical id (database key) and functional id (used to be referenced by this id from anywhere else). So I systematically have one ID field and one uuid for uService reference. however, if you think it's a good idea...

[github-actions]

This issue is stale because it has been open 30 days with no activity.
Our core developers tend to be more verbose on denying. If there is no negative comment, possibly this feature will be accepted.
We are accepting PRs 😃.
Comment or this will be closed in 7 days

[OmarHawk]

With my already merged pull request (see link above) this should also be covered, since I also adjusted it for the non-reactive stack...

So with the latest jhipster version (starting 7.8.0), it is supposed to be fixed.