hibernate 和使用数据库索引会在 CockroachDB 上创建 SQL 语法错误

Question

以下使用带有 Spring Boot、Hibernate、JpaRepository、CockroachDB 和 Kotlin 的数据库索引的最小示例无法重新启动。

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

|------------------------------------------|
|           |  PostgresSQL  |  CockroachDB |
|-----------+---------------+--------------|
| no index  |  OK           |  OK          |
| index     |  OK           |  ERROR       |
|------------------------------------------|

但是对于 CockroachDB，它会以 SQL 语法错误结尾(请参阅本问题末尾的日志)。

这是重现问题的代码。
./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.Index
import javax.persistence.Column

import org.springframework.web.bind.annotation.RestController
import org.springframework.data.jpa.repository.JpaRepository

interface ThingRepository : JpaRepository<Thing, Long> {
}

@RestController
class ThingController(private val repository: ThingRepository) {
}

@Entity
@Table(indexes = [Index(name="value", columnList="value")])
data class Thing (
    @Id
    @Column(name="id")
    var id: Long,
    @Column(name="value")
    var value: String
)

@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
# wait until started
# then ctrl+c to stop

现在让我们看一下数据库中生成的表:

./cockroach-v1.1.6.linux-amd64/cockroach sql --insecure -e "SHOW COLUMNS FROM things_db.thing;"

输出:

# Server version: CockroachDB CCL v1.1.6 (linux amd64, built 2018/03/12 17:58:05, go1.8.3) (same version as client)
# Cluster ID: 2f85d639-a096-4ebc-a478-216a4e7e3a14
+-------+-------------+-------+---------+---------------------------+
| Field |    Type     | Null  | Default |          Indices          |
+-------+-------------+-------+---------+---------------------------+
| id    | BIGINT      | false | NULL    | {"primary","value_index"} |
| value | STRING(255) | true  | NULL    | {"value_index"}           |
+-------+-------------+-------+---------+---------------------------+
(2 rows)

然后

./cockroach-v1.1.6.linux-amd64/cockroach sql --insecure -e "SHOW INDEXES FROM things_db.thing;"

输出

# Server version: CockroachDB CCL v1.1.6 (linux amd64, built 2018/03/12 17:58:05, go1.8.3) (same version as client)
# Cluster ID: 2f85d639-a096-4ebc-a478-216a4e7e3a14
+-------+-------------+--------+-----+--------+-----------+---------+----------+
| Table |    Name     | Unique | Seq | Column | Direction | Storing | Implicit |
+-------+-------------+--------+-----+--------+-----------+---------+----------+
| thing | primary     | true   |   1 | id     | ASC       | false   | false    |
| thing | value_index | false  |   1 | value  | ASC       | false   | false    |
| thing | value_index | false  |   2 | id     | ASC       | false   | true     |
+-------+-------------+--------+-----+--------+-----------+---------+----------+
(3 rows)

然而运行

gradle bootRun

第二次不起作用。应用程序在启动时崩溃。

这是 the log of the second (failing) run 的摘录:

2018-03-18 10:57:06.419 ERROR 28412 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: syntax error at or near "."
  Detail: source SQL:
SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM,   ct.relname AS TABLE_NAME, NOT i.indisunique AS NON_UNIQUE,   NULL AS INDEX_QUALIFIER, ci.relname AS INDEX_NAME,   CASE i.indisclustered     WHEN true THEN 1    ELSE CASE am.amname       WHEN 'hash' THEN 2      ELSE 3    END   END AS TYPE,   (i.keys).n AS ORDINAL_POSITION,   trim(both '"' from pg_catalog.pg_get_indexdef(ci.oid, (i.keys).n, false)) AS COLUMN_NAME,   CASE am.amcanorder     WHEN true THEN CASE i.indoption[(i.keys).n - 1] & 1       WHEN 1 THEN 'D'       ELSE 'A'     END     ELSE NULL   END AS ASC_OR_DESC,   ci.reltuples AS CARDINALITY,   ci.relpages AS PAGES,   pg_catalog.pg_get_expr(i.indpred, i.indrelid) AS FILTER_CONDITION FROM pg_catalog.pg_class ct   JOIN pg_catalog.pg_namespace n ON (ct.relnamespace = n.oid)   JOIN (SELECT i.indexrelid, i.indrelid, i.indoption,           i.indisunique, i.indisclustered, i.indpred,           i.indexprs,           information_schema._pg_expandarray(i.indkey) AS keys         FROM pg_catalog.pg_index i) i     ON (ct.oid = i.indrelid)   JOIN pg_catalog.pg_class ci ON (ci.oid = i.indexrelid)   JOIN pg_catalog.pg_am am ON (ci.relam = am.oid) WHERE true  AND n.nspname = 'things_db' AND ct.relname = 'thing' ORDER BY NON_UNIQUE, TYPE, INDEX_NAME, ORDINAL_POSITION
                                                                                                                                                                                                                                                                                                               ^
2018-03-18 10:57:06.420  WARN 28412 --- [           main] ConfigServletWebServerApplicationContext : Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory
2018-03-18 10:57:06.420  INFO 28412 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2018-03-18 10:57:06.421  INFO 28412 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.
2018-03-18 10:57:06.422  INFO 28412 --- [           main] o.apache.catalina.core.StandardService   : Stopping service [Tomcat]
2018-03-18 10:57:06.432  INFO 28412 --- [           main] ConditionEvaluationReportLoggingListener :

Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled.
2018-03-18 10:57:06.433 ERROR 28412 --- [           main] o.s.boot.SpringApplication               : Application run failed

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1710) ~[spring-beans-5.0.4.RELEASE.jar:5.0.4.RELEASE]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:583) ~[spring-beans-5.0.4.RELEASE.jar:5.0.4.RELEASE]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:502) ~[spring-beans-5.0.4.RELEASE.jar:5.0.4.RELEASE]
        // truncated to fit post
        at things.ThingServiceKt.main(ThingService.kt:40) [main/:na]
Caused by: javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory
        at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.persistenceException(EntityManagerFactoryBuilderImpl.java:970) ~[hibernate-core-5.2.14.Final.jar:5.2.14.Final]
        at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:895) ~[hibernate-core-5.2.14.Final.jar:5.2.14.Final]
        at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:57) ~[spring-orm-5.0.4.RELEASE.jar:5.0.4.RELEASE]
        at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365) ~[spring-orm-5.0.4.RELEASE.jar:5.0.4.RELEASE]
        at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:388) ~[spring-orm-5.0.4.RELEASE.jar:5.0.4.RELEASE]
        at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:377) ~[spring-orm-5.0.4.RELEASE.jar:5.0.4.RELEASE]
        at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:341) ~[spring-orm-5.0.4.RELEASE.jar:5.0.4.RELEASE]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1769) ~[spring-beans-5.0.4.RELEASE.jar:5.0.4.RELEASE]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1706) ~[spring-beans-5.0.4.RELEASE.jar:5.0.4.RELEASE]
        ... 16 common frames omitted
Caused by: org.hibernate.exception.SQLGrammarException: Error accessing index information: things_db.thing
        at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106) ~[hibernate-core-5.2.14.Final.jar:5.2.14.Final]
        at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) ~[hibernate-core-5.2.14.Final.jar:5.2.14.Final]
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111) ~[hibernate-core-5.2.14.Final.jar:5.2.14.Final]
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97) ~[hibernate-core-5.2.14.Final.jar:5.2.14.Final]
        at org.hibernate.tool.schema.extract.internal.InformationExtractorJdbcDatabaseMetaDataImpl.convertSQLException(InformationExtractorJdbcDatabaseMetaDataImpl.java:98) ~[hibernate-core-5.2.14.Final.jar:5.2.14.Final]
        // truncated to fit post
        at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:892) ~[hibernate-core-5.2.14.Final.jar:5.2.14.Final]
        ... 23 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "."
  Detail: source SQL:
SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM,   ct.relname AS TABLE_NAME, NOT i.indisunique AS NON_UNIQUE,   NULL AS INDEX_QUALIFIER, ci.relname AS INDEX_NAME,   CASE i.indisclustered     WHEN true THEN 1    ELSE CASE am.amname       WHEN 'hash' THEN 2      ELSE 3    END   END AS TYPE,   (i.keys).n AS ORDINAL_POSITION,   trim(both '"' from pg_catalog.pg_get_indexdef(ci.oid, (i.keys).n, false)) AS COLUMN_NAME,   CASE am.amcanorder     WHEN true THEN CASE i.indoption[(i.keys).n - 1] & 1       WHEN 1 THEN 'D'       ELSE 'A'     END     ELSE NULL   END AS ASC_OR_DESC,   ci.reltuples AS CARDINALITY,   ci.relpages AS PAGES,   pg_catalog.pg_get_expr(i.indpred, i.indrelid) AS FILTER_CONDITION FROM pg_catalog.pg_class ct   JOIN pg_catalog.pg_namespace n ON (ct.relnamespace = n.oid)   JOIN (SELECT i.indexrelid, i.indrelid, i.indoption,           i.indisunique, i.indisclustered, i.indpred,           i.indexprs,           information_schema._pg_expandarray(i.indkey) AS keys         FROM pg_catalog.pg_index i) i     ON (ct.oid = i.indrelid)   JOIN pg_catalog.pg_class ci ON (ci.oid = i.indexrelid)   JOIN pg_catalog.pg_am am ON (ci.relam = am.oid) WHERE true  AND n.nspname = 'things_db' AND ct.relname = 'thing' ORDER BY NON_UNIQUE, TYPE, INDEX_NAME, ORDINAL_POSITION
                                                                                                                                                                                                                                                                                                               ^
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2422) ~[postgresql-42.2.1.jar:42.2.1]
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2167) ~[postgresql-42.2.1.jar:42.2.1]
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306) ~[postgresql-42.2.1.jar:42.2.1]
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441) ~[postgresql-42.2.1.jar:42.2.1]
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365) ~[postgresql-42.2.1.jar:42.2.1]
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:307) ~[postgresql-42.2.1.jar:42.2.1]
        at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:293) ~[postgresql-42.2.1.jar:42.2.1]
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:270) ~[postgresql-42.2.1.jar:42.2.1]
        at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:224) ~[postgresql-42.2.1.jar:42.2.1]
        at org.postgresql.jdbc.PgDatabaseMetaData.getIndexInfo(PgDatabaseMetaData.java:2334) ~[postgresql-42.2.1.jar:42.2.1]
        at org.hibernate.tool.schema.extract.internal.InformationExtractorJdbcDatabaseMetaDataImpl.getIndexes(InformationExtractorJdbcDatabaseMetaDataImpl.java:719) ~[hibernate-core-5.2.14.Final.jar:5.2.14.Final]
        ... 35 common frames omitted

关于什么可能导致这个问题或如何解决它的任何想法？

Answer 1

来自 https://github.com/cockroachdb/cockroach/issues/24010 的交叉发布:

您正在点击 #16971 ，这在 CockroachDB 1.1 或即将推出的 2.0 中尚未修复。

此问题的解决方法是避免尝试通过 JDBC 创建或修改模式，而是自己执行模式管理或使用 Flyway 等外部工具。

一个积极进取的 Java 专家可能会创建一种方言，避免使用 _pg_expandarray 特殊记录语法——这正是 CockroachDB 正在绊倒的地方。这是查询的一部分，看起来像 (i.keys).n - 这样做是从位于 i.keys 中的记录类型中请求命名字段“n” . CockroachDB 还不支持记录类型，因此很麻烦。