message -"could not read a hi value - you need to populate the table: hibernate_sequence"

Question

My problem is as follows: When i create POST request in "Postman" app. This is what i try to POST

  {"name": "John Doe", "email":"[email protected]", "city": "London"}

I am getting the following error:

{
"timestamp": "2018-11-19T20:16:00.486+0000",
"status": 500,
"error": "Internal Server Error",
"message": "could not read a hi value - you need to populate the table: hibernate_sequence; nested exception is org.hibernate.id.IdentifierGenerationException: could not read a hi value - you need to populate the table: hibernate_sequence",
"path": "/api/ver01/product"
}

I was looking for answer in search box but none of them helped me. So i think that the problem is in sql code but I am not sure. Whole project is written in intelliJ IDE.

This is my Product class.

package com.hubertkulas.webstore.store.archetype;

import com.fasterxml.jackson.annotation.JsonFormat;
import com.fasterxml.jackson.annotation.JsonIgnoreProperties;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import java.math.BigDecimal;
import java.sql.Date;

@Entity
@JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
public class Product {

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

private boolean contact;
private String email;
private String category;
private String name;
private String city;

private String model;
private BigDecimal price;

@JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "MM-dd-yyyy")
private Date date;



public String getName() {
    return name;
}

public void setName(String name) {
    this.name = name;
}

public String getEmail() {
    return email;
}

public void setEmail(String email) {
    this.email = email;
}

public String getCity() {
    return city;
}

public void setCity(String city) {
    this.city = city;
}

public String getCategory() {
    return category;
}

public void setCategory(String category) {
    this.category = category;
}

public String getModel() {
    return model;
}

public void setModel(String model) {
    this.model = model;
}

public BigDecimal getPrice() {
    return price;
}

public void setPrice(BigDecimal price) {
    this.price = price;
}

public Date getDate() {
    return date;
}

public void setDate(Date date) {
    this.date = date;
}

public boolean isContact() {
    return contact;
}

public void setContact(boolean contact) {
    this.contact = contact;
}

public Long getId() {
    return id;
}

// setter for id because Jackson will use it
public void setId(Long id) {
    this.id = id;
}
}

This is my ProductController class

package com.hubertkulas.webstore.store.controllers;
import com.hubertkulas.webstore.store.archetype.Product;
import com.hubertkulas.webstore.store.jparepository.ProductRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("api/ver01/product")
public class ProductController {

//injecting ProductRepository when ProductController is called
@Autowired
private ProductRepository productRepository;

@GetMapping
public List<Product> list() {
    //finds all of the records and returns it
   return productRepository.findAll();
}

@PostMapping
@ResponseStatus(HttpStatus.OK)
public void create(@RequestBody Product product){
    productRepository.save(product);
}



@GetMapping("/{id}")
public Product get(@PathVariable("id") long id){
    // return specific record with added id
    return productRepository.getOne(id);
}

}

This is my ProductRepository Interface

package com.hubertkulas.webstore.store.jparepository;

import com.hubertkulas.webstore.store.archetype.Product;
import org.springframework.data.jpa.repository.JpaRepository;

//Using Jpa for CRUD operations
public interface ProductRepository extends JpaRepository<Product, Long> {
}

And this is my database

CREATE TABLE
product
(
    id BIGINT NOT NULL,
    contact BOOLEAN NOT NULL,
    email VARCHAR,
    category VARCHAR,
    name VARCHAR,
    city VARCHAR,
    date DATETIME,
    price NUMERIC,
    model VARCHAR,
    PRIMARY KEY (id)
);

CREATE TABLE
hibernate_sequence
(
    next_val BIGINT
);

INSERT INTO product (id, contact, email, category, name, city, date, price)
VALUES (1, 1, '[email protected]', 'Electronics', 'Abraham Westbrom', 'New 
York', 4419619200000, '3250');
INSERT INTO product (id, contact, email, category, name, city, date, price)
VALUES (2, 1, '[email protected]', 'Electronics', 'Udon Hon', 'London', 
4419619200000, '799');
INSERT INTO product (id, contact, email, category, name, city, date, price)
VALUES (3, 0, '[email protected]', 'Software', 'Mateusz Sinus', 
'Warsaw', 4419619200000, '10000');

INSERT INTO hibernate_sequence (next_val) VALUES (4);

Solution 1

Your hibernate_sequence table is wrong.

See 2.6.10. Using identifier table:

create table hibernate_sequences(
    sequence_name VARCHAR NOT NULL,
    next_val INTEGER NOT NULL
)

Solution 2

If you are creating a schema using spring boot for local database and jpa is configured to do a auto create-drop, ideally you wont be facing this situation.

spring.jpa.hibernate.ddl-auto=create-drop

But in staging/production you want to handle your schema definition (DDL) separately so hibernate_sequence needs to have an initial value and 0 should suffice for start. It tells the program library from which number to start the auto-generation id.

spring.jpa.hibernate.ddl-auto=validate

INSERT INTO <schema_name>.hibernate_sequence (next_val) VALUES (0);

The above one works for MYSQL

Solution 3

You can add

spring:
jpa:hibernate:ddl-auto: create-drop 

in your application.yml file or application.properties file Do this only when you have truncated table or else you can add INSERT INTO <schema_name>.hibernate_sequence (next_val) VALUES (1);

Solution 4

drop the table and re-create with next_val as primary key

View more solutions

Comments

[argoth]

Please take a look in this post: confluence.atlassian.com/confkb/… Maybe it can help you.

[HZK]

I have added 'NOT NULL' to my 'next_val BIGINT'. It solved this problem thank you very much.

[Dinesh Shekhawat]

Using the INSERT command only fixed it for me. Thanks!