java - Spring Boot MySQL Database Initialization Error with Stored Procedures

Question

In a Spring Boot application I am attempting to initialize some MySQL database tables and stored procedures before running integration tests by placing a schema.sql file in my resources directory as recommended in the documentation.

The create table statements work but the create procedure statements throw an exception. A sample schema.sql file statement that causes the exception is shown below:

DROP PROCEDURE IF EXISTS `database`.FOO;
CREATE PROCEDURE `database`.FOO()

BEGIN
  SELECT * from `database`.employees;
END;

The issue is that the ; character within the stored procedure is being parsed out by the Spring ScriptUtils class that parses the schema.sql file before executing it, which then causes MySQL to throw a syntax error on the script.

I have looked at the ScriptUtils class and have not been able to find a way to escape the ; characters with the procedures. Using \ and as escape characters did not work either, as well as the MySQL DELIMITER command.

Has anyone been able to create MySQL stored procedures using the schema.sql file with Spring Boot? If so could they show an example?

For some additional information, the following Spring JIRA issue addresses the same topic but was closed with a Won't Fix label.

Answer 1

The answer turned out to be very simple. Spring Boot has a DataSource separator property that can be set in the application.properties file:

spring.datasource.separator=^;

Then in the schema.sql file all ; statements not within the stored procedure need to be updated with the new separator.

DROP PROCEDURE IF EXISTS `database`.FOO;
CREATE PROCEDURE `database`.FOO()

BEGIN
  SELECT * from `database`.employees;
END ^;