CODEWITHSUNDEEP
springspring-bootspring-mvcspring-data-jpa
PiPio
PiPio

Reputation: 99

Spring Boot Error executing DDL via JDBC Statement

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 ?

Upvotes: 4

Views: 26329

Answers (4)

Mohamed Adel
Mohamed Adel

Reputation: 2040

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

Upvotes: 0

Francislainy Campos
Francislainy Campos

Reputation: 4164

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

Upvotes: 3

AbuBaker Hmad
AbuBaker Hmad

Reputation: 189

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

Upvotes: 9

RIJIL SAJI DANIEL
RIJIL SAJI DANIEL

Reputation: 11

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

Upvotes: 1

Related Questions