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.

I don't have much info but may be try `^` as escape character. — Sanjay Rawat, Mar 08 '16 at 04:43

score 8 · Accepted Answer · answered Mar 08 '16 at 15:23

8

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

answered Mar 08 '16 at 15:23

Andrew

722
9
17

Don't miss the semicolon, in the separator (^;) – Nayeem Dec 24 '19 at 03:26

kap · Answer 2 · 2020-07-17T09:13:23.663

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>

score 1 · Answer 3 · answered Aug 21 '20 at 10:02

In case someone steps on this thread using spring-boot + testcontainers as I did, one can just omit separators, cause testcontainers' parser is aware of procedures synthax (although still doesn't work for MS-SQL). So original script will work:

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

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

score 0 · Answer 4 · answered Jan 16 '23 at 12:28

If annotations are used for the test, then you can do the following:

@Sql(
        scripts = "/myTestSQL.sql",
        config = @SqlConfig(separator = "^;")
)
@Test
void mySQLTest() {
    // my test logic
}

given that myTestSQL.sql is stored in the test resources folder. Also, only different procedures or queries are to be separated with ^;, what's inside the procedure should still be separated with ;, just like in https://stackoverflow.com/a/35871322/2092109 answer

Spring Boot MySQL Database Initialization Error with Stored Procedures

4 Answers4

Linked