mysql - GenerationTarget 遇到异常接受命令 : Error executing DDL: Standalone JPA app

Question

我正在编写一个简单的“Hello world”JPA Java 应用程序，在 Linux 上使用 MySQL。

我正在尝试自动配置模式，从 persist.xml 调用脚本。

当我尝试实例化我的 JPA EntityManagerFactory 时出现此错误:

WARN: GenerationTarget encountered exception accepting command : Error executing DDL "CREATE TABLE task (" via JDBC Statement
org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "CREATE TABLE task (" via JDBC Statement
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67)
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applySqlString(SchemaCreatorImpl.java:440)
...
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:782)
    at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:666)
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54)
    ... 15 more

我读过其他链接，我试过显式设置“MySQL57Dialect”(它甚至在我添加属性之前就自动检测到“MySQL57Dialect”)，我试过简化 SQL 脚本等等 - 所有无济于事。

版本信息:

Ubuntu 18.04.1 LTS
mysql  Ver 14.14 Distrib 5.7.24, for Linux (x86_64) using  EditLine wrapper
OpenJDK Runtime Environment (build 10.0.2+13-Ubuntu-1ubuntu0.18.04.4)

SQL 脚本(当前精简版:从 mysql CLI 运行正常):

CREATE TABLE task (
  id BIGINT NOT NULL auto_increment,
  summary VARCHAR(20) NOT NULL,
  priority VARCHAR(10) NOT NULL DEFAULT 'NORMAL', 
  status VARCHAR(10) NOT NULL DEFAULT 'PENDING',
  created_on TIMESTAMP DEFAULT current_timestamp,
  PRIMARY KEY(id)
);

持久性.xml:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1"
    xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
    <persistence-unit name="com.example.hellohibernate.jpa" transaction-type="RESOURCE_LOCAL">
        <class>com.example.hellohibernate.Task</class>      
        <class>com.example.hellohibernate.TaskUpdate</class>      
        <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.MySQL57Dialect" />
            <!-- Configuring The Database Connection Details -->
            <property name="javax.persistence.jdbc.driver" value="com.mysql.cj.jdbc.Driver" />
            <property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/test2" />
            <property name="javax.persistence.jdbc.user" value="test" />
            <property name="javax.persistence.jdbc.password" value="test123" /> 
            <!-- First time only,  create from schema: -->
            <property name="javax.persistence.schema-generation.database.action" value="create" />
            <property name="javax.persistence.schema-generation.create-source" value="script"/>
            <property name="javax.persistence.schema-generation.create-script-source" value="META-INF/mkdb.mysql.sql" />                 
        </properties>
    </persistence-unit>
</persistence>

控制台日志:

Jan 12, 2019 11:59:54 AM org.hibernate.dialect.Dialect <init>
INFO: HHH000412: Hibernate Core {5.4.0.Final}
...
INFO: HHH000400: Using dialect: org.hibernate.dialect.MySQL57Dialect
...
WARN: GenerationTarget encountered exception accepting command : Error executing DDL "create table task (" via JDBC Statement
org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "create table task (" via JDBC Statement
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67)
...
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:782)
    at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:666)
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54)
    ... 15 more

有什么建议吗？

Answer 1

我尝试验证 MySQL 语法，我尝试验证 MySQL 版本，我尝试显式设置 Hibernate MySql57Dialect，我尝试“简化”SQL 语法......没有任何效果。

我基本上尝试了这个链接中的所有内容(以及更多):

GenerationTarget encountered exception accepting command : Error executing DDL via JDBC Statement

然后 Bill Karwin 建议将每个陈述都放在 ONE LINE 上。

确实 有效。

Final - WORKING - 语法:

Java 应用程序:

public static final String PERSISTENCE_UNIT = "com.example.hellohibernate.jpa";

protected static EntityManagerFactory entityManagerFactory;

protected static EntityManagerFactory createEntityManagerFactory() {
    entityManagerFactory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT);
    ...

持久性.xml:

   <persistence-unit name="com.example.hellohibernate.jpa" transaction-type="RESOURCE_LOCAL">
        <class>com.example.hellohibernate.Task</class>      
        <class>com.example.hellohibernate.TaskUpdate</class>      
        <properties>
            <!-- Configuring The Database Connection Details -->
            <property name="javax.persistence.jdbc.driver" value="com.mysql.cj.jdbc.Driver" />
            <property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/test2" />
            <property name="javax.persistence.jdbc.user" value="test" />
            <property name="javax.persistence.jdbc.password" value="test123" /> 
            <!-- First time only,  create from schema: -->
            <property name="javax.persistence.schema-generation.database.action" value="create" />
            <property name="javax.persistence.schema-generation.create-source" value="script"/>
            <property name="javax.persistence.schema-generation.create-script-source" value="META-INF/mkdb.mysql.sql" />
            ...

mkdb.mysql.sql:

drop table if exists task_update;
drop table if exists task;

-- priority: LOW, NORMAL, HIGH
-- status: PENDING, INPROGRESS, COMPLETED, BLOCKED, REOPENED
create table task (  id int NOT NULL auto_increment,  summary varchar(20) NOT NULL,  priority varchar(10) NOT NULL default 'NORMAL',  status varchar(10) NOT NULL default 'PENDING',  date timestamp default current_timestamp,  PRIMARY KEY(id));

create table task_update (  id int NOT NULL auto_increment,  taskid int NOT NULL,  text varchar(255) NULL,  date timestamp default current_timestamp,  PRIMARY KEY(id),  FOREIGN KEY fk_task(taskid)  REFERENCES task(id));

注意事项:

• 注释和空行解析正常...但是使用 Hibernate/JPA 解析器 (org.hibernate.tool.schema.internal) 将语句拆分为多行似乎失败了。

• Bill - 再次感谢你。如果您想将它放在帖子中，我很乐意“接受”您的想法。否则，我想记录下我的发现。