Spring Boot Error executing DDL via JDBC Statement

Question

So i wanted to make a simple CRUD apps and i used the MYSQL Workbench for the database . My connection in MySQL Workbench

Hostname : 127.0.0.1
Port : 3306

I don't use password in MySQL Workbench.

and now my application properties looks like this

spring.datasource.url=jdbc:mysql://localhost:3306/employee_management_system?useSSL=false
spring.datasource.username=root
spring.datasource.password=

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

spring.jpa.hibernate.ddl-auto=update

The error that i'm getting

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "create table employees (id bigint not null auto_increment, email_id varchar(255), first_name varchar(255), last_name varchar(255), primary key (id)) type=InnoDB" via JDBC Statement

and another one at the bottom part

Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'type=InnoDB' at line 1

Please can someone help ?

Maybe this response is good for you https://stackoverflow.com/questions/37066024/what-is-the-mariadb-dialect-class-name-for-hibernate — e.g78, Sep 22 '20 at 14:38

score 9 · Accepted Answer · edited Dec 10 '20 at 08:51

9

I think your database does not support the InnoDB engine. Try to replace

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

by

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

edited Dec 10 '20 at 08:51

Community

1
1

answered Sep 22 '20 at 14:46

AbuBaker Hmad

179
4

score 2 · Answer 2 · answered Jan 17 '21 at 03:39

In my case I needed to inspect the sql that it was generating by adding these two lines to my application.properties file

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

And only then I was able to copy the sql, paste it into the console editor and get a more detailed description for the error. There it complained I was trying to name my table with a reserved word. I changed that and it worked (I was using postgres).

score 1 · Answer 3 · edited Sep 22 '20 at 15:05

1

Use this query instead:

create table employees 
  ( 
     id         bigint not null auto_increment, 
     email_id   varchar(255), 
     first_name varchar(255), 
     last_name  varchar(255), 
     primary key (id) 
  ) 
engine=InnoDB;

For reference: Using "TYPE = InnoDB" in MySQL throws exception

edited Sep 22 '20 at 15:05

David Buck

3,752
35
31
35

answered Sep 22 '20 at 14:43

RIJIL SAJI DANIEL

11
1

score 0 · Answer 4 · answered Apr 24 '21 at 03:37

The problem is that you now try to access either database not exist or the default "information_schema" which this user is not allowed to create a table on it so simply

Create a new database:

create database example;

Create a new user to work on this database or you can use root access but the new user will be better try to not use root user everywhere

create user 'exampleuser'@'%' identified by 'PASSWORD;

Then assign the user to have all permissions on this db

grant all on example.* to 'exampleuser'@'%';

Finally, make sure you update your spring configuration

spring.jpa.hibernate.ddl-auto=update
spring.datasource.url=jdbc:mysql://${MYSQL_HOST:localhost}:3306/example
spring.datasource.username=exampleuser
spring.datasource.password=12345

Spring Boot Error executing DDL via JDBC Statement

4 Answers4