Monday, September 26, 2022

[FIXED] GenerationTarget encountered exception accepting command : Error executing DDL engine=MyISAM" via JDBC Statement

September 26, 2022 csv, hibernate, java, maven, xml

Issue

I have a really weird error with Hibernate, which appeared only today, since yesterday everything was working and I wasn't make any changes in the config files. It seems like java wants to execute defined columns and export them to the database but then the Error executing DDL engine=MyISAM" via JDBC Statement arises.

I've read that error can arise because of the MySQLDialect instead of MySQL5Dialect in hibernate.cfg.xml but that's not an issue since I use MySQL5Dialect from the beginning.

I operate on a local database to which I am connected.

hibernate.cfg.xml

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD//EN"
        "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
    <session-factory>
        <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
        <property     name="hibernate.connection.url">jdbc:mysql://localhost:3306/users</property>
        <property name="hibernate.connection.username">root</property>
        <property name="hibernate.connection.password">root</property>
    <property name="hibernate.dialect">org.hibernate.dialect.MySQL5Dialect</property>
        <property name="show_sql">true</property>
        <property name="format_sql">true</property>
        <property name="hbm2ddl.auto">update </property> 

        <mapping class="UserData"/> 
    </session-factory>
</hibernate-configuration>

UserData.java

import javax.persistence.*;

@Table
@Entity(name="users")

public class UserData {



    @Id 
    @GeneratedValue(strategy = GenerationType.IDENTITY)

    @Column
    private String firstName;

    @Column
    private String lastName;

    @Column
    private int telephoneNo;

    @Column
    private String email;

    @Column
    private String pickUp;

    @Column
    private String dropOff;

    public UserData(){}


    public UserData(String firstName, String lastName, int telephoneNo, String email, String pickUp, String dropOff){
        this.firstName = firstName;
        this.lastName = lastName;
        this.telephoneNo = telephoneNo;
        this.email = email;
        this.pickUp = pickUp;
        this.dropOff = dropOff;
    }



    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public int getTelephoneNo() {
        return telephoneNo;
    }

    public void setTelephoneNo(int telephoneNo) {
        this.telephoneNo = telephoneNo;
    }

    public String getEmail() {
        return email;
    }

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

    public String getPickUp() {
        return pickUp;
    }

    public void setPickUp(String pickUp) {
        this.pickUp = pickUp;
    }

    public String getDropOff() {
        return dropOff;
    }

    public void setDropOff(String dropOff) {
        this.dropOff = dropOff;
    }
}

sample.csv

1,Reanna,Colepio,159031625,[email protected],London,Glasgow

HibernateConfig.java

import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;



public class HibernateConfig {
    private static final SessionFactory sessionFactory = buildSessionFactory();

    private static SessionFactory buildSessionFactory(){

        try {
            return new Configuration().configure().buildSessionFactory();
        }
        catch (Throwable ex){
            System.err.println("Initial SessionFactory creation failed." + ex);
            throw new ExceptionInInitializerError(ex);
        }
    }

    public static SessionFactory getSessionFactory(){
        return sessionFactory;
    }

    public static void shutdown(){
        getSessionFactory().close();
    }
}

ManageData.java

import org.hibernate.Session;
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;

import java.util.Scanner;

public class ManageData {

    public static void readFile() {
        String line = "";

        Session session = HibernateConfig.getSessionFactory().openSession();

        try (BufferedReader br = new BufferedReader(new FileReader("/Users/ggabrychowicz/IdeaProjects/Bus Managing System/src/main/java/sample.csv"))){

            while ((line = br.readLine()) != null){
                String[] tempArr = line.split(",");

                UserData userData = new UserData();


                userData.setFirstName(tempArr[1]);
                userData.setLastName(tempArr[2]);
                userData.setTelephoneNo(Integer.parseInt(tempArr[3]));
                userData.setEmail(tempArr[4]);
                userData.setPickUp(tempArr[5]);
                userData.setDropOff(tempArr[6]);

                session.beginTransaction();
                session.save(userData);
                session.getTransaction().commit();

            }
            session.close();
        }

        catch (IOException e) {
            if (session!=null){
                session.getTransaction().rollback();
            }
            e.printStackTrace();
        }
    }
}

stacktrace:

Mar 25, 2020 11:20:43 AM org.hibernate.Version logVersion
INFO: HHH000412: Hibernate Core {5.4.10.Final}
Mar 25, 2020 11:20:43 AM org.hibernate.annotations.common.reflection.java.JavaReflectionManager <clinit>
INFO: HCANN000001: Hibernate Commons Annotations {5.1.0.Final}
Mar 25, 2020 11:20:43 AM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl configure
WARN: HHH10001002: Using Hibernate built-in connection pool (not for production use!)
Mar 25, 2020 11:20:43 AM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH10001005: using driver [com.mysql.jdbc.Driver] at URL [jdbc:mysql://localhost:3306/users]
Mar 25, 2020 11:20:43 AM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH10001001: Connection properties: {password=****, user=root}
Mar 25, 2020 11:20:43 AM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH10001003: Autocommit mode: false
Mar 25, 2020 11:20:43 AM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl$PooledConnections <init>
INFO: HHH000115: Hibernate connection pool size: 20 (min=1)
Wed Mar 25 11:20:43 CET 2020 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Mar 25, 2020 11:20:44 AM org.hibernate.dialect.Dialect <init>
INFO: HHH000400: Using dialect: org.hibernate.dialect.MySQL5Dialect
Mar 25, 2020 11:20:44 AM org.hibernate.resource.transaction.backend.jdbc.internal.DdlTransactionIsolatorNonJtaImpl getIsolatedConnection
INFO: HHH10001501: Connection obtained from JdbcConnectionAccess [org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator$ConnectionProviderJdbcConnectionAccess@423b2b62] for (non-JTA) DDL execution was not in auto-commit mode; the Connection 'local transaction' will be committed and the Connection will be set into auto-commit mode.
Mar 25, 2020 11:20:44 AM org.hibernate.tool.schema.internal.ExceptionHandlerLoggedImpl handleException
WARN: GenerationTarget encountered exception accepting command : Error executing DDL "
    create table users (
       firstName varchar(255) not null auto_increment,
        dropOff varchar(255),
        email varchar(255),
        lastName varchar(255),
        pickUp varchar(255),
        telephoneNo integer,
        primary key (firstName)
    ) engine=MyISAM" via JDBC Statement
org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "
    create table users (
       firstName varchar(255) not null auto_increment,
        dropOff varchar(255),
        email varchar(255),
        lastName varchar(255),
        pickUp varchar(255),
        telephoneNo integer,
        primary key (firstName)
    ) engine=MyISAM" via JDBC Statement
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67)
    at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlString(AbstractSchemaMigrator.java:559)
    at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlStrings(AbstractSchemaMigrator.java:504)
    at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.createTable(AbstractSchemaMigrator.java:277)
    at org.hibernate.tool.schema.internal.GroupedSchemaMigratorImpl.performTablesMigration(GroupedSchemaMigratorImpl.java:71)
    at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.performMigration(AbstractSchemaMigrator.java:207)
    at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:114)
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:184)
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:73)
    at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:320)
    at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:462)
    at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:708)
    at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:724)
    at HibernateConfig.buildSessionFactory(HibernateConfig.java:12)
    at HibernateConfig.<clinit>(HibernateConfig.java:7)
    at ManageData.readFile(ManageData.java:14)
    at Main.main(Main.java:4)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Incorrect column specifier for column 'firstName'
    at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500)
    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:481)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.Util.getInstance(Util.java:408)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3933)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3869)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2675)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2465)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2439)
    at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:829)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:729)
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54)
    ... 16 more

Hibernate: 

    create table users (
       firstName varchar(255) not null auto_increment,
        dropOff varchar(255),
        email varchar(255),
        lastName varchar(255),
        pickUp varchar(255),
        telephoneNo integer,
        primary key (firstName)
    ) engine=MyISAM
Mar 25, 2020 11:20:44 AM org.hibernate.engine.transaction.jta.platform.internal.JtaPlatformInitiator initiateService
INFO: HHH000490: Using JtaPlatform implementation: [org.hibernate.engine.transaction.jta.platform.internal.NoJtaPlatform]
Exception in thread "main" java.lang.NumberFormatException: For input string: "Colepio"
    at java.base/java.lang.NumberFormatException.forInputString(NumberFormatException.java:68)
    at java.base/java.lang.Integer.parseInt(Integer.java:658)
    at java.base/java.lang.Integer.parseInt(Integer.java:776)
    at ManageData.readFile(ManageData.java:27)
    at Main.main(Main.java:4)

Process finished with exit code 1

Solution

The exception is:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 
Incorrect column specifier for column 'firstName'

This is because it tries to add a column firstName with auto_increment. auto_increment does not work with VARCHAR columns.

In your code you have:

@Id 
@GeneratedValue(strategy = GenerationType.IDENTITY)

@Column
private String firstName;

I assume that you forgot the id attribute and it should look like:

@Id 
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;

@Column
private String firstName;

Answered By - Simon Martinelli
Answer Checked By - Pedro (JavaFixing Volunteer)

This Answer collected from stackoverflow and tested by JavaFixing community admins, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0