schema.sql runs whenever the connection is checked

Question

I am facing a strange issue. I have a schema.sql file in my springboot application, which creates tables(with foreign key constraints) and inserts data on the application run. But the issue here is, when the connection is idle, then the connection is been tested. Now, this is creating problems for me as schema.sql file is again executed and as you know my tables contain foreign key constraints, so the script is failing as it is not allowing to drop the tables which contain foreign key constraints.

Is it because of my application.properties?

Here is my application.properties

spring.datasource.url=jdbc:mysql://dummy_url
spring.datasource.username=root
spring.datasource.password=password
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.initialization-mode=always
mybatis.config-location=classpath:/config/mybatis-config.xml

Is there any way to make the schema.sql execute only once(only when the application is run)? My sample schema.sql looks this :

DROP TABLE IF EXISTS `t001_map_mst`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `t001_map_mst` (
  `MAP_ID` int NOT NULL AUTO_INCREMENT,
  `MAP_NAME` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`MAP_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT='Contains map info';
/*!40101 SET character_set_client = @saved_cs_client */;

score 0 · Accepted Answer · answered May 13 '20 at 05:37

0

Because you're using spring.datasource.initialization-mode=always, this property has these values:

ALWAYS Always initialize the data source.
EMBEDDED Only initialize an embedded data source.
NEVER Do not initialize the data source.
check official document

I provide three ways to solve the problem 1. change your schema.sql, if table exist then don't run the script 2. change your application.properties once you finished your first run, change it to spring.datasource.initialization-mode=none
3. init from code: check Spring Boot - Loading Initial Data

answered May 13 '20 at 05:37

PatrickChen

1,350
1
11
19

I feel changing my schema is the best way out of all. – Subham May 13 '20 at 10:17
But will be complicated when you want to change your table schema. – PatrickChen May 13 '20 at 10:36
What do you suggest? – Subham May 13 '20 at 12:45
using two sets of configuration files or change just the property is much easier – PatrickChen May 13 '20 at 13:31
I don't think so changing the property from code is possible. or is it possible? – Subham May 13 '20 at 13:44

schema.sql runs whenever the connection is checked

1 Answers1