Why is Spring creating this unique ID that's too long and causing DDL error?

Question

This error is new to me and I Have no idea why it's deciding to create an id thats too long. Below the error, you will find my model files and repository file.

2019-08-08 15:41:06.835  WARN 12180 --- [           main] o.h.t.s.i.ExceptionHandlerLoggedImpl     : GenerationTarget encountered exception accepting command : Error executing DDL "alter table recipes add constraint UK_ki12bp7g638chyap3hdmxe93f unique (recipe_name)" via JDBC Statement

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "alter table recipes add constraint UK_ki12bp7g638chyap3hdmxe93f unique (recipe_name)" via JDBC Statement
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67)
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applySqlString(SchemaCreatorImpl.java:440)
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applySqlStrings(SchemaCreatorImpl.java:424)
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.createFromMetadata(SchemaCreatorImpl.java:349)
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.performCreation(SchemaCreatorImpl.java:166)
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:135)
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:121)
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:155)
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:72)
    at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:310)
    at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:467)
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:939)
    at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:57)
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:390)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:377)
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:341)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1837)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1774)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:593)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:515)
    at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:320)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:318)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199)
    at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1105)
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:867)
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:549)
    at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:142)
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:775)
    at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:397)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:316)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1260)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1248)
    at com.potatospy.reciply.SpringBootApplication.main(SpringBootApplication.java:22)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at org.springframework.boot.maven.AbstractRunMojo$LaunchRunner.run(AbstractRunMojo.java:543)
    at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: java.sql.SQLSyntaxErrorException: Specified key was too long; max key length is 1000 bytes
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:782)
    at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:666)
    at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:95)
    at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54)
    ... 40 common frames omitted

Recipe

package com.potatospy.reciply.web.model.response;

import lombok.Getter;
import lombok.Setter;

import java.util.ArrayList;
import java.util.List;


public class Recipe {


    @Getter
    @Setter
    private Long id;

@Getter
@Setter
private int recipeId;

@Getter
@Setter
private String recipeName;

@Getter
@Setter
private List<Ingredient> ingredientList;


public Recipe() {
    this.ingredientList = new ArrayList<>();
}


@Override
public String toString() {
    return "Recipe{" +
            "id=" + id +
            ", recipeId=" + recipeId +
            ", recipeName='" + recipeName + '\'' +
            ", ingredientList=" + ingredientList +
            '}';
}

}

RecipeEntity

package com.potatospy.reciply.business.dao.entity;


    import lombok.Getter;
    import lombok.Setter;

    import javax.persistence.*;
    import java.io.Serializable;
    import java.util.List;

    // recipes table backing object

    @Entity(name="recipes")
    public class RecipeEntity  implements Serializable {
    @Getter
    @Setter
    @Id
    @GeneratedValue
    private Long id;    // Repository is expecting a Long but I believe Spring will do the conversion for me

    @Getter
    @Setter
    @Column(nullable=false, unique=true)
    private String recipeId;


    @Getter
    @Setter
    @Column(nullable=false, unique=true)
    private String recipeName;


    // Each Recipe has a List of Ingredients

    // Enable operations propagate to list of ingredients
    @OneToMany(mappedBy=/*"className"*/"recipeEntity", cascade = CascadeType.ALL)
    @Getter
    @Setter
    @Column(nullable=false) // Todo this cant be saved until it has this init'd
    private List<IngredientEntity> ingredientEntityList;






}

Ingredient

package com.potatospy.reciply.web.model.response;

import com.potatospy.reciply.web.model.Unit;
import lombok.Getter;
import lombok.Setter;



public class Ingredient {


    @Getter
    @Setter
    private Long id;

    @Getter
    @Setter
    private int ingredientId;

    @Getter
    @Setter
    private double quantityDouble;    // TODO There'll be conversion issues here, need fix

    @Getter
    @Setter
    private Unit unit;

    @Getter
    @Setter
    private String ingredientName;


    // Ingredient automatically converts quantity from Entity String to Model double
    public Ingredient(int ingredientId, double quantityString, Unit unit, String ingredientName) {
        this.ingredientId = ingredientId;
        this.quantityDouble = quantityString;
        this.unit = unit;
        this.ingredientName = ingredientName;
    }

    @Override
    public String toString() {
        return "Ingredient{" +
                "ingredientId='" + ingredientId + '\'' +
                ", quantity=" + quantityDouble +
                ", unit='" + unit + '\'' +
                ", ingredientName='" + ingredientName + '\'' +
                '}';
    }
}

IngredientEntity

package com.potatospy.reciply.business.dao.entity;


import lombok.Getter;
import lombok.Setter;

import javax.persistence.*;
import java.io.Serializable;

@Entity(name="ingredients")
public class IngredientEntity implements Serializable {


    // DB id
    @Id
    @GeneratedValue
    private long id;

    @Getter
    @Setter
    @Column(nullable=false, unique=true)
    private String ingredientId;

    @Getter
    @Setter
    @Column(nullable=false)
    private String quantity;            // TODO There'll be conversion issues here, need fix

    @Getter
    @Setter
    @Column(nullable=false)
    private String unit;    // Defined via Recipe by UnitConstants

    @Getter
    @Setter
    @Column(nullable=false)
    private String ingredientName;


    // Many ingredients to one recipe
    @ManyToOne
    @JoinColumn(name="recipe_id")
    @Getter
    @Setter
    private RecipeEntity recipeEntity;

}

Repository

package com.potatospy.reciply.business.dao.repository;


import com.potatospy.reciply.business.dao.entity.RecipeEntity;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface RecipeRepository extends CrudRepository<RecipeEntity, Long> {

    // JPA creates these methods for us
    RecipeEntity findByRecipeName(String recipeName);
    RecipeEntity findByRecipeId(String recipeId);
    //RecipeEntity findAll();         Already for me in CrudRepo
    RecipeEntity save(RecipeEntity recipeEntity);
}

If you want to set the name of a unique constraint, use the `UniqueConstraint` annotation (inside a `Table` annotation). BTW: If hibernate generates a too long constraint name, that could also be a sign that you haven't properly set your database dialect. — Mark Rotteveel, Aug 09 '19 at 09:25

score 0 · Accepted Answer · answered Aug 08 '19 at 19:05

0

The reason DDL is trying to apply a unique constraint is because...

    @Column(nullable=false, unique=true)
    private String recipeId;

I told it to.

To get rid of the constraint, check this other thread. First answer...

answered Aug 08 '19 at 19:05

chrips

4,996
5
24
48

Why is Spring creating this unique ID that's too long and causing DDL error?

1 Answers1