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 Apr 06 '17 at 08:09 — Amer Qarabsa, Apr 06 '17 at 08:09

Eugene Maysyuk · Answer 1 · 2022-11-13T14:35:10.477

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.

Best answer on this page – Clement Osei Tano Jul 27 '21 at 14:58 — Clement Osei Tano, Jul 27 '21 at 14:58

Jens · Accepted Answer · 2017-04-06T08:17:09.780

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 06 '17 at 08:17

answered Apr 06 '17 at 08:09

Jens

67,715
15
98
113

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 – GioPoe Jun 07 '20 at 02:39

score 6 · Answer 3 · answered Sep 27 '21 at 16:18

6

you need to add this to your configuration :

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

Write your database name and spring will create the database automatically

answered Sep 27 '21 at 16:18

Nail Khaled Beneloualid

133
3
5

score 2 · Answer 4 · answered Apr 06 '17 at 08:11

2

You need to create the database first.

answered Apr 06 '17 at 08:11

Zubair Nabi

1,016
7
28

score 0 · Answer 5 · edited Dec 20 '22 at 00:26

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 '22 at 00:26

Ticherhaz FreePalestine

2,738
4
20
46

answered Dec 17 '22 at 12:29

Nasser Benoudi

1

score 0 · Answer 6 · answered Jun 08 '23 at 09:59

Solution 1:

In your application.properties change the line :

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

to

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

Solution 2:

You need to create the database first.

score 0 · Answer 7 · edited Jul 25 '23 at 10:32

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 '23 at 10:32

Gayan Mettananda

1,498
14
21

answered Jul 20 '23 at 12:00

Muzaffer Yenice

1

Cant create a mysql database using Spring Boot

7 Answers7

Linked