Hibernate cannot create table hibernate_sequences

Question

Hibernate version:

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-core</artifactId>
    <version>5.2.4.Final</version>
</dependency>

ExportDB.java:

public class ExportDB {
    public static void main(String[] args) {
        ServiceRegistry serviceRegistry = new StandardServiceRegistryBuilder().configure().build();
        Metadata metadata = new MetadataSources(serviceRegistry).buildMetadata();
        SchemaExport schemaExport = new SchemaExport();
        schemaExport.create(EnumSet.of(TargetType.DATABASE), metadata);
    }
}

Run ExportDB.java:

2016-11-19 00:22:12,845 WARN [org.hibernate.orm.connections.pooling] - HHH10001002: Using Hibernate built-in connection pool (not for production use!)
Hibernate: drop table if exists hibernate_sequences
Hibernate: drop table if exists user
Hibernate: create table hibernate_sequences (sequence_name varchar(255) not null, sequence_next_hi_value bigint, primary key (sequence_name))
Hibernate: create table user (id bigint not null, balance decimal(20,4) default 0.00, createTime time, displayName varchar(64), password varchar(64), username varchar(64), primary key (id))
Hibernate: alter table user add constraint UK_7kuje5s4lbyq9qyv1r9ecm2it unique (username)

Database:

MariaDB [cms]> show tables;
+----------------+
| Tables_in_cms |
+----------------+
| investor       |
+----------------+
1 row in set (0.00 sec)

When I use printed SQL to create hibernate_sequences:

MariaDB [cms]> create table hibernate_sequences (sequence_name varchar(255) not null, next_val bigint, primary key (sequence_name));
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

How can I make ExportDB.java can create hibernate_sequences?

It appears that your variable `sequence_name` is too large to use as a primary key. Take a look at this question: http://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes 255 characters at 3 bytes per character is 765 bytes, plus a few extra bytes are required, so it goes over the 767 limit. — Enwired, Nov 18 '16 at 17:38

score 1 · Answer 1 · answered Nov 18 '16 at 18:32

1

You are using utf8mb4, correct? And you are using 5.6 or older?

Plan A: Upgrade to 5.7.

Plan B: Decrease 255 to 191 or less. (Did you really need 255??)

Plan C: Change to CHARACTER SET utf8 (assuming you don't need Emoji or Chinese)

Plan D: Normally a 'sequence' is something numeric. If that is the case, won't INT UNSIGNED or BIGINT UNSIGNED work?

answered Nov 18 '16 at 18:32

Rick James

135,179
13
127
222

Thank you, but how to change to utf8? You mean the whole database? – Alfred Huang Jan 30 '18 at 02:53
`ALTER TABLE ... CONVERT TO ...` for any table needing to change. More discussion: http://mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes – Rick James Jan 31 '18 at 01:42

score 1 · Answer 2 · answered Jan 30 '18 at 03:08

It is due to the utf8mb4 charset on the column definition in my case.

Too long varchar column cannot be used in primary_key.

So we can manually create the table and make it correct.

The following create table query do the trick.

create table hibernate_sequences (
   sequence_name varchar(255) CHARACTER SET utf8 not null ,
    next_val bigint,
    primary key (sequence_name)
) engine=MyISAM

Hibernate cannot create table hibernate_sequences

2 Answers2