Spring boot - MySQL settings are not working

Question

I am trying to develop an application using Spring boot and MySQL. As the documentation said, First I created the project using Spring initializr using Intelij Idea, configured the application.properties file, and wrote schema-mysql.sql file and data-mysql.sql file. After I ran the project, I found there are no tables or data in the MySQL database. What is wrong with my configuration? Please help.

application.properties file,

spring.datasource.url=jdbc:mysql://localhost:3306/testdb?useSSL=false
spring.datasource.username=root
spring.datasource.password=password

spring.datasource.testWhileIdle=true
spring.datasource.validationQuery=SELECT 1

spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.show-sql = true
spring.jpa.hibernate.naming-strategy = org.hibernate.cfg.ImprovedNamingStrategy
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect

spring.datasource.schema=schema-mysql.sql
spring.datasource.data=data-mysql.sql

dependencies in pom.xml file,

<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>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-security</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

schema-mysql.sql file,

CREATE TABLE IF NOT EXISTS `SOL16_USERS` (
  `USERNAME` VARCHAR(200) NOT NULL,
  'PASSWORD' VARCHAR(200) NOT NULL,
  PRIMARY KEY (`USERNAME`)
);

CREATE TABLE IF NOT EXISTS 'SOL16_PRIVILEGES' (
  'PRIVILEGE_ID' INT(2)      NOT NULL,
  'PRIVILEGE'    VARCHAR(15) NOT NULL,
  PRIMARY KEY ('PRIVILEGE_ID')
);

CREATE TABLE IF NOT EXISTS 'SOL16_USER_PRIVILEGES' (
  'USERNAME'     VARCHAR(200) NOT NULL,
  'PRIVILEGE_ID' VARCHAR(2)   NOT NULL,
  PRIMARY KEY ('USERNAME')
);

And the file/directory structure is,

src
|----main
|    |----java
|    |----resources
|    |    |----static
|    |    |----templates
|    |    |----application.properties
|    |    |----data-mysql.sql
|    |    |----schema-mysql.sql

are you checking while the application is running? `create-drop` removes the tables once the session is closed - try `create` or better `update` instead — baao, Oct 25 '16 at 18:41
@baao Yes. I am checking while running. I have used update and create before, but no luck. — lpsandaruwan, Oct 25 '16 at 18:48
Enable logging for spring classes. You should see if the files are used at startup — davidxxx, Oct 25 '16 at 18:50
Where are your `schema-mysql.sql` and `data-mysql.sql` placed (please add your folder structure to your question)? Also you are using Hibernate `create-drop` which will interfere with the schema creation of Spring Boot. Assuming you want to use the `schema-mysql.sql` only and not rely on Hibernate change that to `validate` instead of what you have now. — M. Deinum, Oct 26 '16 at 06:07
@Deinum Thank you. I have edited the question. Also I tried changing value to 'validate'. But still no luck. I don't get any errors while starting the app. I am running the app using '--debug' — lpsandaruwan, Oct 26 '16 at 07:41
You don't get errors with `validate`??? That should blow up your application if there are no tables... Prefix the names for the .sql files with `classpath:` to have them explicitly loaded from the classpath. The tables you create are those mapped to hibernate entities? — M. Deinum, Oct 26 '16 at 08:17
@Denium mapping to hibernate entities is what fixed my problem. Thank you. — lpsandaruwan, Oct 27 '16 at 10:15

score 6 · Accepted Answer · answered Oct 28 '16 at 15:53

As the Spring boot documentation mentioned, what have fixed my issue was adding spring.datasource.platform to the application.properties. That is what I have been missing to initialize the schema using schema-{platform}.sql and data-{platform}.sql.

{platform} = value of spring.datasource.platform

So my final application.properties file is,

spring.datasource.url = jdbc:mysql://localhost:3306/testdb?useSSL=false
spring.datasource.username = root
spring.datasource.password = password

spring.datasource.testWhileIdle = true
spring.datasource.validationQuery = SELECT 1

spring.datasource.driverClassName = com.mysql.jdbc.Driver
spring.jpa.hibernate.ddl-auto = validate
spring.jpa.show-sql = true
spring.jpa.hibernate.naming-strategy = org.hibernate.cfg.ImprovedNamingStrategy
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect

spring.datasource.platform=mysql
spring.datasource.schema=schema-mysql.sql
spring.datasource.data=data-mysql.sql
spring.datasource.initialize=true
spring.datasource.continue-on-error=true

score 3 · Answer 2 · answered Aug 12 '18 at 17:10

3

I faced the same issue and resolved it applying the below code in spring boot 2.0

spring.datasource.initialization-mode=always

answered Aug 12 '18 at 17:10

tanson

82
3

score 1 · Answer 3 · answered Dec 25 '18 at 18:44

You need to add below code in application.properties and open your xammp/wamp or server then create yourdatabase for example studentdb same name give in application.properties file

spring.datasource.url=jdbc:mysql://localhost:3306/yourdatabase
spring.datasource.username=root
spring.datasource.password=   
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.jpa.database-platform = org.hibernate.dialect.MySQL5Dialect
spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto = update

score 0 · Answer 4 · edited Nov 12 '20 at 20:11

0

Please use the key field given below---->

spring.jpa.hibernate.ddl-auto=create

or

spring.jpa.hibernate.ddl-auto=update

edited Nov 12 '20 at 20:11

DaFois

2,197
8
26
43

answered Nov 12 '20 at 19:27

priyranjan

674
6
15

Spring boot - MySQL settings are not working

4 Answers4

Linked