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 ^;

Answer 2

Adding to @Andrews answer:

When using a custom dataSource that is not automatically created by Spring Boot, it can happen that the spring.datasource.separator property is not used. In this cases the separator is not forwarded to the Populator. In this case it can be set directly in the data source initialization. For example, the following can be used in a special update profile assuming dataSource is defined elsewhere:

<jdbc:initialize-database data-source="dataSource" enabled="${initDatabase:false}" separator="^;">
    <jdbc:script location="${update.schema.script}" />
</jdbc:initialize-database>

Or, when the the populator is explicitly stated:

<bean id="dbPopulator" class="org.springframework.jdbc.datasource.init.ResourceDatabasePopulator">
    <property name="separator" value="^;"/>
    <property name="scripts">
        <list>
            <value>${update.schema.script}</value>
        </list>
    </property>
</bean>
<bean id="initDatabase" class="org.springframework.jdbc.datasource.init.DataSourceInitializer">
    <property name="dataSource">
        <ref bean="dataSource"/>
    </property>
    <property name="databasePopulator">
        <ref bean="dbPopulator"/>
    </property>
    <!-- The same can be done for the cleaner: -->
    <!-- <property name="databaseCleaner"><ref bean="dbCleanup"/></property> -->
</bean>