Cant create a mysql database using Spring Boot

Question

I was trying to implement Spring Boot without embedded database like h2 and derby but instead with mysql. I keep getting unknown database error.

pom.xml:

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>1.5.2.RELEASE</version>
    <relativePath/> <!-- lookup parent from repository -->
</parent>

<properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
    <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>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
    </dependency>-->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.38</version>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
</dependencies>

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

application.properties:

#Database Configuration
spring.datasource.url=jdbc:mysql://localhost:3306/sampleDB
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true

error:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown database 'sampledb'
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_111]
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_111]
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_111]
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_111]
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) ~[mysql-connector-java-5.1.38.jar:5.1.38]
    at com.mysql.jdbc.Util.getInstance(Util.java:387) ~[mysql-connector-java-5.1.38.jar:5.1.38]
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:939) ~[mysql-connector-java-5.1.38.jar:5.1.38]
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878) ~[mysql-connector-java-5.1.38.jar:5.1.38]
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814) ~[mysql-connector-java-5.1.38.jar:5.1.38]
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:871) ~[mysql-connector-java-5.1.38.jar:5.1.38]
    at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1694) ~[mysql-connector-java-5.1.38.jar:5.1.38]
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1215) ~[mysql-connector-java-5.1.38.jar:5.1.38]
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2255) ~[mysql-connector-java-5.1.38.jar:5.1.38]
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2286) ~[mysql-connector-java-5.1.38.jar:5.1.38]
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2085) ~[mysql-connector-java-5.1.38.jar:5.1.38]
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:795) ~[mysql-connector-java-5.1.38.jar:5.1.38]
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:44) ~[mysql-connector-java-5.1.38.jar:5.1.38]
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_111]
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_111]
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_111]
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_111]
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) ~[mysql-connector-java-5.1.38.jar:5.1.38]

If I use an embedded database like h2 then it is working without anything mentioned in the application.properties file but if I use MySQL I get this error. Please help.

Are you sure sampleDB is created?

Amer Qarabsa
– Amer Qarabsa

2017-04-06 08:09:45 +00:00
Commented Apr 6, 2017 at 8:09 — Amer Qarabsa
– Amer Qarabsa, Commented Apr 6, 2017 at 8:09

Eugene Maysyuk · Accepted Answer · 2022-11-13 14:35:10Z

43

Neither the Spring Boot nor the Hibernate will not create database instead of you.

The setting below just creates tables and expects that DB already exists:

spring.jpa.hibernate.ddl-auto=create

However when you are using embedded DB (HSQLDB, H2 ...) the database will be created automatically. This is vendor specific behavior.

For MySQL you can use the following configuration to create DB automatically:

spring.datasource.url=jdbc:mysql://localhost:3306/sampleDB?createDatabaseIfNotExist=true

Pay attention to the connection parameter.

In this case database will be created automatically during connection.

edited Nov 13, 2022 at 14:35

answered Apr 5, 2018 at 20:52

Eugene Maysyuk

3,52832 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Clement Osei Tano Over a year ago

Best answer on this page

Jens · Accepted Answer · 2017-04-06 08:17:09Z

8

You connect to the database sampleDB which is not automatically created by mysql in contrast to h2.

You have to create it by yourself using mysql -u <user> -p -e "create database sampleDB"

edited Apr 6, 2017 at 8:17

answered Apr 6, 2017 at 8:09

Jens

69.6k15 gold badges107 silver badges133 bronze badges

1 Comment

GioPoe Over a year ago

I got the same error and I created the database using the mysql workbench beforehand. Would you happen to know why this error still occurs? Or does creating the database using mysql on terminal change something as opposed to workbench

Zubair Nabi · Accepted Answer · 2017-04-06 08:11:57Z

2

You need to create the database first.

answered Apr 6, 2017 at 8:11

Zubair Nabi

1,0568 silver badges29 bronze badges

Comments

Ticherhaz FreePalestine · Accepted Answer · 2022-12-20 00:26:20Z

0

In your application.properties change the line :

spring.datasource.url=jdbc:mysql://localhost:3306/sampleDB

to

spring.datasource.url=jdbc:mysql://localhost:3306/sampleDB?use=sampleDB

MfG.

edited Dec 20, 2022 at 0:26

Ticherhaz FreePalestine

2,4094 gold badges24 silver badges52 bronze badges

answered Dec 17, 2022 at 12:29

Nasser Benoudi

1

Comments

Gayan Mettananda · Accepted Answer · 2023-07-25 10:32:03Z

0

If you use .yml file you should do like below.

jpa:
    hibernate:
    ddl-auto: create
    show-sql: true
    database-platform: org.hibernate.dialect.MySQL8Dialect
    properties:
    '[hibernate.format_sql]': true

edited Jul 25, 2023 at 10:32

Gayan Mettananda

1,51615 silver badges22 bronze badges

answered Jul 20, 2023 at 12:00

Muzaffer Yenice

1

Collectives™ on Stack Overflow

Cant create a mysql database using Spring Boot

5 Answers 5

1 Comment

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related