Spring boot/Hibernate fails creating table

Question

I am getting this warn when I try to start my spring boot application:

2018-09-30 07:34:23.097  INFO 59360 --- [           main] org.hibernate.dialect.Dialect            : HHH000400: Using dialect: org.hibernate.dialect.MySQL5Dialect
Hibernate: create table social_link (author_username varchar(255) not null, type varchar(255) not null, value varchar(255), primary key (author_username, type)) engine=MyISAM
2018-09-30 07:34:24.067  WARN 59360 --- [           main] o.h.t.s.i.ExceptionHandlerLoggedImpl     : GenerationTarget encountered exception accepting command : Error executing DDL via JDBC Statement

Strange thing is that I can execute SQL statement via the SQL workbench without a problem and everything works fine after that. Here is the entity that is responsible for that table:

@Entity
public class SocialLink {

    @EmbeddedId
    private SocialLinkKeyEmbeddable id = new SocialLinkKeyEmbeddable();
    private String value;
    @ManyToOne
    @MapsId("author_username")
    @JoinColumns({
            @JoinColumn(name="author_username", referencedColumnName="author_username")
    })
    private Author author;

    //Getters and setters
}

@Embeddable
public class SocialLinkKeyEmbeddable implements Serializable {

    @Column(name = "author_username")
    private String author_username;
    private String type;

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    public String getAuthor_username() {
        return author_username;
    }

    public void setAuthor_username(String author_username) {
        this.author_username = author_username;
    }

    @Override
    public int hashCode() {
        return super.hashCode();
    }

    @Override
    public boolean equals(Object obj) {
        return super.equals(obj);
    }
}

public interface SocialLinkRepository extends CrudRepository<SocialLink, SocialLinkKeyEmbeddable> {
}

Answer 1

The problem was with the length of the key. MySQL was pretending it is larger than 1000 bytes. It seems its a common problem with MyISAM storage engine. In order to fix it I added:

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL55Dialect

to my applicaiton.properties and now the problem is fixed. Was using this post as reference #1071 - Specified key was too long; max key length is 1000 bytes

Answer 2

I had a similar problem caused by spring.jpa.database-platform property set to MySQL5Dialect.

Setting the property to MySQL8Dialect or removing it altogether (which enables auto-configuration) solved the problem.

Answer 3

I added a column length to my ID field to prevent the varchar(255) default.

@Entity
public class MyEntity {

    @Id
    @Column(length = 64)
    private String id;

...
}

Answer 4

For those who are only using Hibernate (not Spring), edit the SQL dialect property from hibernate.cfg.xml file, located in src/main/resources folder.

<property name="dialect">org.hibernate.dialect.MySQL55Dialect</property>

Or remove the dialect property altogether.

Answer 5

Error-Caused by: java.sql.SQLSyntaxErrorException: Specified key was too long; max key length is 1000 bytes

operation perform- @Id private String userName;

Solution

step1-Go to mysql database--> step2-Go to schema setting -->step3-change Default charset to latin1--> step 4-Run on server your spring mvc program--> step 5- Finally Your table is created with @Id on String

Answer 6

I had the same issue. I resolved it by assigning char set to my database on creation:

CREATE DATABASE mydb DEFAULT CHARACTER SET utf8mb3;