Can't see MYSQL table information in MYSQL workbench, spring Boot

Question

I am trying to connect my spring boot app to MYSQL workbench but I can't find any of my columns in MYSQL WorkBench.

My set up: This application.properties file

spring.datasource.url=jdbc:mysql://localhost:3306/blog?useUnicode=true&useLegacyDatetimeCode=false&serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.show-sql=true

When I try without "?useUnicode=true&useLegacyDatetimeCode=false&serverTimezone=UTC" I get the following error

HHH000342: Could not obtain connection to query metadata : The server time zone value 'EDT' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the 'serverTimezone' configuration property) to use a more specifc time zone value if you want to utilize time zone support.

My Maven dependencies

<parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.0.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.blogportfolio</groupId>
    <artifactId>blog</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>Bala's Blog</name>
    <description>Blog portfolio backend</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

The Article Class

package com.blogbackend.Model;

import com.sun.istack.NotNull;
import lombok.Getter;
import lombok.Setter;

import javax.persistence.*;
import java.util.Date;


@Entity
@Getter
@Setter
@Table (name = "Articles")
public class Article {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private int id;

    @NotNull
    @Column(name="articleName")
    private String artName;

    @NotNull
    @Column(name="articleBody")
    private String artBody;

    @NotNull
    @Column(name="articleCategory")
    private String artCategory;

    @NotNull
    @Column(name="articleCreated")
    private Date artCreated;

    @Column
    private Author author;

    public Article(int id) {
        this.id = id;
    }
}

When I go to the WorkBench the Blog schema is empty.

Benjamin Eckardt · Answer 1 · 2020-06-03T22:58:55.247

1

As far as I know it has nothing to do with the schema/table you want to use but is more a server configuration issue. According to this it comes with a certain version of the used driver, that it's mandatory to set this property.

In order to accomplish the automatic creation of the desired tables, this might help.

If the creation is already successful while the application is running, just change the ddl-auto paramter from

spring.jpa.hibernate.ddl-auto=create-drop

to

spring.jpa.hibernate.ddl-auto=create

and once created change to validate or update

Further explanation can be found here.

edited Jun 03 '20 at 22:58

answered Jun 03 '20 at 22:16

Benjamin Eckardt

709
6
10

Hi Ben, I updated my question w/ my Maven dependencies. Which one should I change and try again? Thank you for your help. – B_working_K_hard Jun 03 '20 at 22:22
I think you had to go back quite some versions. I'd recommend sticking with the latest version you can and add the property with the desired timezone that fits your setup. – Benjamin Eckardt Jun 03 '20 at 22:24
Ah I think i just got your question. Try adding the property to the jdbc connection string, start your app and while it is running can you check it in workbench? – Benjamin Eckardt Jun 03 '20 at 22:27
In case it is not working, you should check this: https://stackoverflow.com/questions/26881739/unable-to-get-spring-boot-to-automatically-create-database-schema – Benjamin Eckardt Jun 03 '20 at 22:32
In case you see the expected tables: change `spring.jpa.hibernate.ddl-auto=create-drop ` to `spring.jpa.hibernate.ddl-auto=create` and once created change to `validate` or update – Benjamin Eckardt Jun 03 '20 at 22:56
I tried this and nothing has changed, should I use a different MYSql database. I am using MySQLWorkBench right now. – B_working_K_hard Jun 03 '20 at 23:55
If the application does not create the tables check the second link in my answer. – Benjamin Eckardt Jun 04 '20 at 00:02

score 1 · Answer 2 · answered Jun 03 '20 at 22:37

Your identity generation strategy for Article entity is GenerationType.SEQUENCE. But MySQL doesn't support Sequence directly.

That it why if fails when spring.jpa.hibernate.ddl-auto=create-drop is trying to create a schema.

Use AUTO_INCREMENT instead with GenerationType.IDENTITY.

Can't see MYSQL table information in MYSQL workbench, spring Boot

2 Answers2