Script-based database initialization fails with an unhelpful error message when configured with a resource that points to a directory #36386
Description
For SB 3.1.1 with the following dependencies
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>And having in the src/main/resources location the schema.sql, data.sql and application.properties files, where for this latest mentioned is declared as follows:
spring.datasource.url=jdbc:mysql://192.168.1.222:3307/....
spring.datasource.username=root
spring.datasource.password=secret
spring.profiles.active=cache,exception-nonIf is executed the mvn clean compile and mvn spring-boot:run commands, the app works in peace.
Observation
The .sql files work as expected - they are totally valid about their own syntax.
Until here no reason to create this post.
Situation
If these scripts files are renamed as schema-mysql.sql and data-mysql.sql respectively and both moved to the com/manuel/jordan/mysql/v2 package/directory located in the src/main/resources location - and having now the application-mysql.properties file in the same src/main/resources location .
For a better understanding see the following structure:
src/main/resources
com
manuel
jordan
mysql
v2
data-mysql.sql
schema-mysql.sql
application.properties
application-mysql.properties
Therefore the application-mysql.properties file being empty then is edited to:
spring.datasource.url=jdbc:mysql://192.168.1.222:3307/....
spring.datasource.username=root
spring.datasource.password=secret
spring.sql.init.mode=always
spring.sql.init.platform=mysql
spring.sql.init.schema-locations=classpath:/com/manuel/jordan/mysql/v2/
spring.sql.init.data-locations=classpath:/com/manuel/jordan/mysql/v2/And the application.properties file is updated to:
spring.profiles.active=mysql,cache,exception-nonAnd executing the following commands:
mvn clean compilemvn spring-boot:run
The app throws this error:
Unsatisfied dependency expressed through constructor parameter 0:
Error creating bean with name 'dataSourceScriptDatabaseInitializer' defined in class path resource
[org/springframework/boot/autoconfigure/sql/init/DataSourceInitializationConfiguration.class]:
Failed to execute SQL script statement #1 of class path resource [com/manuel/jordan/mysql/v2/]: data-mysql.sql
...
Caused by: org.springframework.beans.factory.BeanCreationException:
Error creating bean with name 'dataSourceScriptDatabaseInitializer' defined in class path resource
[org/springframework/boot/autoconfigure/sql/init/DataSourceInitializationConfiguration.class]:
Failed to execute SQL script statement #1 of class path resource [com/manuel/jordan/mysql/v2/]: data-mysql.sql
...
Caused by: org.springframework.jdbc.datasource.init.ScriptStatementFailedException:
Failed to execute SQL script statement #1 of class path resource [com/manuel/jordan/mysql/v2/]: data-mysql.sql
...
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right syntax to use near 'data-mysql.sql' at line 1
Yes, I know the correct configuration is with (now the scripts files names are included):
spring.sql.init.schema-locations=classpath:/com/manuel/jordan/mysql/v2/schema-mysql.sql
spring.sql.init.data-locations=classpath:/com/manuel/jordan/mysql/v2/data-mysql.sqlBut the error stack trace is tricky due the following points (here the problems and reasons of this post):
- The error is about the
data-mysql.sqlfile and not about theschema-mysql.sql.
Is expected the latter be executed first than the former right? Therefore can be assumed by error that the schema-mysql.sql file was used and not the data-mysql.sql file.
- The most important, the error indicates an error of SQL Syntax and it is not correct. Remember the files were only renamed and moved to other location.
Therefore should be better indicate that does not exist the v2.sql scripts files ... it because was used the classpath:/com/manuel/jordan/mysql/v2 declaration and it ends with v2
Thanks for your understanding