3

I'm writing a simple "Hello world" JPA Java app, using MySQL on Linux.

I'm trying to auto-configure the schema, invoking a script from persist.xml.

I'm getting this error when I try to instantiate my 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

I've read other links, I've tried explicitly setting "MySQL57Dialect" (it auto-detected "MySQL57Dialect" even before I added the property), I've tried simplifying the SQL script, etc. etc. - all to no avail.

VERSION INFO:

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 Script (current, stripped down version: works OK from 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)
);

persistence.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>

Console log:

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

Any suggestions?

paulsm4
  • 114,292
  • 17
  • 138
  • 190
  • 3
    It looks like it's executing only the first line of your DDL, up to the first `(` character. I don't use JPA or Hibernate, so I can't suggest a fix. But what happens if you format your CREATE TABLE so the full statement is on one line of text? – Bill Karwin Jan 12 '19 at 21:03
  • 1
    Wow - darn fine question. It *WORKED*! – paulsm4 Jan 12 '19 at 22:27

1 Answers1

1

I tried verifying the MySQL syntax, I tried verifying the MySQL version, I tried explicitly setting the Hibernate MySql57Dialect, I tried "simplifying" the SQL syntax ... nothing worked.

I basically tried everything in this link (and much more):

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

And then Bill Karwin suggested putting each statement on exactly ONE LINE.

That DID work.

Final - WORKING - syntax:

Java app:

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

protected static EntityManagerFactory entityManagerFactory;

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

persistence.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));

NOTES:

  • Comments and blank lines parse OK ... but splitting a statement into multiple lines seems to fail with the Hibernate/JPA parser (org.hibernate.tool.schema.internal).

  • Bill - thank you again. I'd be happy to "accept" your idea if you want to put it in a post. Otherwise, I wanted to document what I found.

paulsm4
  • 114,292
  • 17
  • 138
  • 190