CODEWITHSUNDEEP
javamysqlspringhibernatejpa
oschlueter
oschlueter

Reputation: 2698

JPA Unique Index creation in MySQL silently fails

My POJO has an attribute name which is supposed to be unique, hence I annotated it with @Column(unique = true). The length defaults to 255 and I'm using MySQL 5.5 with the database collation set to utf8mb4_bin. I know that I can't specify a unique constraint on the column, however I'm wondering why there's no warning/error in my log file regarding the failed index creation. MySQL responds to a manual attempt of index creation with "#1071 - Specified key was too long; max key length is 767 bytes" so at least some kind of hint should be visible, shouldn't it?

Is there a way I can treat/catch these kinds of errors at runtime? E.g. logging the error message so the developer is aware that something went wrong.

I'm using spring-boot-starter-parent as maven-parent 1.4.2.RELEASE for my project and included spring-boot-starter-data-jpa to add all relevant dependencies. This includes spring-data-jpa-1.10.5.RELEASE and hibernate-core-5.0.11.

Upvotes: 0

Views: 769

Answers (1)

Romeo Sheshi
Romeo Sheshi

Reputation: 931

The error is due to

InnoDB has a maximum index length of 767 bytes, so for utf8 or utf8mb4 columns, you can index a maximum of 255 or 191 characters, respectively. If you currently have utf8 columns with indexes longer than 191 characters, you will need to index a smaller number of characters. https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-conversion.html

you ara using a field with to 255 and the max for utf8mb4_bin is 191 so you have the error that the index is not being created o if it was a warning it will have create the index with the max value to 191.

Upvotes: 1

Related Questions