Question 180481 in Mysql Question posted in
The official MySQL documentation can be found here.

“Can’t create user class in springboot due to ‘Table already exists’ MySQL error?”

So I have already created my database and all the tables with the appropriate columns in Mysql and connected it to springBoot. My issue is that when I create my user class which matches all the right columns on the Mysql setup, and run the springboot application I get: "Error executing DDL…[Table ‘users’ already exists]" Here is my application.properties setup

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

# Hibernate specific properties
spring.jpa.database-platform=org.hibernate.dialect.MySQL8Dialect
spring.jpa.hibernate.ddl-auto=update

and my User class


import jakarta.persistence.*;
@Entity
@Table(name = "Users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    private String firstName;
    private String lastName;
    private String password;
    private Float rating;

    // getters and setters
}

and my Users table in sql

CREATE TABLE Users (
    User_ID INT AUTO_INCREMENT,
    First_Name VARCHAR(100),
    Last_Name VARCHAR(100),
    Password VARCHAR(255), -- storing hashed password
    Rating FLOAT,
    PRIMARY KEY (User_ID)
);

Can someone guide me as to why this is happening?

Tags:

2

Answers


  1. 0

    This error occurs because you have set the spring.jpa.hibernate.ddl-auto property to update, which instructs Hibernate to automatically update the database schema based on the entity classes.

    Since the table already exists in the database, Hibernate is trying to execute a DDL (Data Definition Language) statement to create the table again, resulting in the error. To resolve this issue, you have a few options:

    1. Drop the existing table: If you don’t have any important data in the "Users" table, you can drop the table from the database and let Hibernate recreate it. Make sure you have a backup of your data if necessary.

    2. Change spring.jpa.hibernate.ddl-auto to validate: By changing the value of this property to validate, Hibernate will only validate the entity mapping against the existing database schema. It won’t perform any DDL operations or attempt to create the table again.

      spring.jpa.hibernate.ddl-auto=validate

    Login or Signup to reply.
  2. 0

    I guess problem is your column names. You have set your spring.jpa.hibernate.ddl-auto=update, so that means, when spring boot project runs, it will look at datbase, if table is not exists, it will create new table, if table exists, it will update if necessary. But spring jpa also look up for column names, column types and column lengths and other properties. So if they don’t match, spring thinks that table is not exist. So it will try to create new table. But table with Users name is exist. In this situation, Spring will throw Table is already exists. To fix this problem, you can:

    1. Delete table from database, and run spring boot project again. Let spring project to generate table itself. So table will be same with your entity, problem will be solved.
    2. Change spring.jpa.hibernate.ddl-auto property to spring.jpa.hibernate.ddl-auto=create. So spring boot will drop table in database and will create new one at each running. It is useful at development time. But at each running table will be dropped and re created, so your data will be lost. You have to think twice when switching to this property.
    3. Change spring.jpa.hibernate.ddl-auto property to spring.jpa.hibernate.ddl-auto=none. It will be do nothing. So Spring will use table in database and will not change anything. But if column names or types is different, it will throw another problem.
    4. Change colum names of entity, or table. Make same column names, types, length etc. If you don’t want to change your table in databse, you can change your entity. You can change exactly variable names, or you can use @Column annotation. Example for your entity:
    import jakarta.persistence.*;
@Entity
@Table(name = "Users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "User_ID")
    private Integer id;

    @Column(name = "First_Name")
    private String firstName;

    @Column(name = "Last_Name")
    private String lastName;

    @Column(name = "Password")
    private String password;

    @Column(name = "Rating")
    private Float rating;

    // getters and setters
}

    I hope this will help you.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search