hibernate - hibernate 并将@GeneratedValue与CockroachDB一起使用会导致SQLGrammarException

以下最小示例将生成的ID与Spring Boot，Hibernate，JpaRepository，CockroachDB和Kotlin结合使用，生成org.hibernate.exception.SQLGrammarException。

我也用PostgresSQL而不是CockroachDB进行了测试，这很好。

|------------------------------------------------|
|                 |  PostgresSQL  |  CockroachDB |
|-----------------+---------------+--------------|
| no generated id |  OK           |  OK          |
| generated id    |  OK           |  ERROR       |
|------------------------------------------------|

这是重现该问题的代码。
./src/main/kotlin/ThingService.kt:

package things

import org.springframework.boot.autoconfigure.SpringBootApplication
import org.springframework.boot.runApplication

import javax.persistence.Entity
import javax.persistence.Id
import javax.persistence.Table
import javax.persistence.Column
import javax.persistence.GeneratedValue

import org.springframework.web.bind.annotation.RestController
import org.springframework.web.bind.annotation.PutMapping
import org.springframework.web.bind.annotation.RequestBody

import org.springframework.data.jpa.repository.JpaRepository

import com.fasterxml.jackson.annotation.JsonIgnore

interface ThingRepository : JpaRepository<Thing, Long> {
}

@RestController
class ThingController(private val repository: ThingRepository) {
    @PutMapping("/thing/")
    fun save(@RequestBody t:Thing): Thing
            = repository.save(t)
}

@Entity
data class Thing (
    @Column(name="value")
    var value: String,
    @Id
    @GeneratedValue
    @Column(name="id")
    @JsonIgnore
    var id: Long = -1
)

@SpringBootApplication
class Application {
}

fun main(args: Array<String>) {
    runApplication<Application>(*args)
}

./src/main/resources/application.properties:

server.port=8082

spring.datasource.url=jdbc:postgresql://localhost:26257/things_db?sslmode=disable
spring.datasource.username=root
spring.datasource.password=123

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

spring.jpa.hibernate.ddl-auto=update

./build.gradle.kts:

import org.jetbrains.kotlin.gradle.tasks.KotlinCompile

plugins {
    val kotlinVersion = "1.2.20"
    id("org.springframework.boot") version "2.0.0.RELEASE"
    id("org.jetbrains.kotlin.jvm") version kotlinVersion
    id("org.jetbrains.kotlin.plugin.spring") version kotlinVersion
    id("org.jetbrains.kotlin.plugin.jpa") version kotlinVersion
    id("io.spring.dependency-management") version "1.0.4.RELEASE"
}

version = "1.0.0-SNAPSHOT"

tasks.withType<KotlinCompile> {
    kotlinOptions {
        jvmTarget = "1.8"
        freeCompilerArgs = listOf("-Xjsr305=strict")
    }
}

val test by tasks.getting(Test::class) {
    useJUnitPlatform()
}

repositories {
    mavenCentral()
}

dependencies {
    compile("org.springframework.boot:spring-boot-starter-web")
    compile("org.springframework.boot:spring-boot-starter-data-jpa")
    compile("org.jetbrains.kotlin:kotlin-stdlib-jdk8")
    compile("org.jetbrains.kotlin:kotlin-reflect")
    compile("org.hibernate:hibernate-core")
    compile("org.springframework.retry:spring-retry:1.2.2.RELEASE")
    compile("org.postgresql:postgresql")
    compile("org.json:json:20180130")
    testCompile("org.springframework.boot:spring-boot-starter-test") {
        exclude(module = "junit")
    }
    testImplementation("org.junit.jupiter:junit-jupiter-api")
    testRuntimeOnly("org.junit.jupiter:junit-jupiter-engine")
}

下面是重现该问题的步骤。

下载并初始化CockroachDB:

# download
wget -qO- https://binaries.cockroachdb.com/cockroach-v1.1.6.linux-amd64.tgz | tar xvz

# start
./cockroach-v1.1.6.linux-amd64/cockroach start --insecure
# leave terminal open in background

# init
./cockroach-v1.1.6.linux-amd64/cockroach sql --insecure -e "CREATE USER root WITH PASSWORD '123';"
./cockroach-v1.1.6.linux-amd64/cockroach sql --insecure -e "CREATE DATABASE things_db;"
./cockroach-v1.1.6.linux-amd64/cockroach sql --insecure -e "GRANT ALL ON DATABASE things_db TO root;"

运行数据服务:

gradle bootRun

现在尝试将东西放入服务中:

curl -w "\n" -H 'Content-Type: application/json' -X PUT -d '{"value":"foo", "id":123}' http://localhost:8082/thing/

响应为:

{"timestamp":"2018-03-19T13:05:12.856+0000","status":500,"error":"Internal Server Error","message":"could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet","path":"/thing/"}

但是，当使用PostgreSQL instread的CockroachDB时，结果很好

{"value":"foo","id":123}

是否知道导致问题的原因或使用CockroachDB时如何避免该问题？

以下是完整的日志:

CockroachDB:https://gist.github.com/Dobiasd/4e64eea5457e6eea5bd627ed3c5b780c

Spring :https://gist.github.com/Dobiasd/27615d2b0308724e1202f1a2a8e2eafb

最佳答案

Spring日志具有以下条目:

Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "sequence"

Detail: source SQL:
  create sequence hibernate_sequence start 1 increment 1

CockroachDB 1.x不支持序列，但是在2.0中它们是still in beta。测试版的状态确实意味着它不适合生产使用，但是如果您只是在使用CockroachDB，那可能会很好。

CockroachDB 2.0中的序列遵循postgres语法和行为，但cycle(尚未实现)除外。这里使用的语句不使用它，仅使用START 1 INCREMENT 1。

您可以在CockroachDB docs中找到有关序列的更多详细信息。