Issue with executing procedure in spring boot schema.sql file

Question

I am using schema.sql file to CREATE/DROP tables in my Spring Boot application and it works fine.

But when I have added procedure for altering table:

DELIMITER $$
CREATE PROCEDURE Alter_Table()
BEGIN
 IF NOT EXISTS( SELECT NULL
            FROM INFORMATION_SCHEMA.COLUMNS
           WHERE table_name = 'test_table'
             AND table_schema = 'test'
             AND column_name = 'cc_test_id')  THEN

  alter table test_table add cc_test_id VARCHAR(128) NOT NULL;

END IF;
END $$

call Alter_Table;

I received:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException exception.

However, execution of this procedure in MySQL workbench finishes with successful results.

So, should anyone know what is the reason for this issue, let me know?

Answer 1

Here is the solution I found that works well enough, though it is not ideal as you have to change your SQL script.

In your application.properties file change the DataSource separator property:

spring.datasource.separator=^;

Then update your schema.sql file to look as follows:

CREATE PROCEDURE Alter_Table()
BEGIN
 IF NOT EXISTS( SELECT NULL
            FROM INFORMATION_SCHEMA.COLUMNS
           WHERE table_name = 'test_table'
             AND table_schema = 'test'
             AND column_name = 'cc_test_id')  THEN

  alter table test_table add cc_test_id VARCHAR(128) NOT NULL;

END IF;
END ^;

call Alter_Table ^;

The DELIMITER command only works with the MySQL CLI client and Workbench and will not work for Spring Boot database initialization. Once you have removed the DELIMITER commands, Spring Boot will still throw an exception as it will not understand the ; characters in the stored procedures are not separate statements, so you have to change the datasource separator property as a workaround.

Answer 2

Here's the fix:

Set your spring.datasource.separator property to ^^^ END OF SCRIPT ^^^, and Spring Boot will execute your entire schema.sql script as a single statement.

Here's why:

Spring Boot splits your schema.sql script into statements, then sends each statement to the database individually for execution. By default, the script is split on semicolons, as that is the default value of the spring.datasource.separator property (per the documentation). This causes your schema.sql to be split, and the first statement executed is the following:

DELIMITER $$
CREATE PROCEDURE Alter_Table()
BEGIN
 IF NOT EXISTS( SELECT NULL
            FROM INFORMATION_SCHEMA.COLUMNS
           WHERE table_name = 'test_table'
             AND table_schema = 'test'
             AND column_name = 'cc_test_id')  THEN

  alter table test_table add cc_test_id VARCHAR(128) NOT NULL

This is invalid SQL because the dollar-quote is never terminated in the statement.

The javadoc for org.springframework.jdbc.datasource.init.ScriptUtils.EOF_STATEMENT_SEPARATOR provides a good explanation of how my suggested solution works:

End of file (EOF) SQL statement separator: "^^^ END OF SCRIPT ^^^".

This value may be supplied as the separator to executeSqlScript(Connection, EncodedResource, boolean, boolean, String, String, String, String) to denote that an SQL script contains a single statement (potentially spanning multiple lines) with no explicit statement separator. Note that such a script should not actually contain this value; it is merely a virtual statement separator.

Answer 3

Customizing database populator with the appropriate separator EOF_STATEMENT_SEPARATOR resolves this issue:

@Bean
public ConnectionFactoryInitializer initializer(ConnectionFactory connectionFactory) {
    var resource = new ResourceDatabasePopulator(new ClassPathResource("schema.sql"));
    resource.setSeparator(ScriptUtils.EOF_STATEMENT_SEPARATOR);
    var populator = new CompositeDatabasePopulator();
    populator.addPopulators(resource);
}

Answer 4

None of the above answers were working for me in 2022, but they provide a point of focus to come up with my own working solution(s). I posted my answer under the similar question, but for completeness to save you some extra clicks , I'm providing it here too.

I had been facing this issue too and just now I found a solution. Actually 3! So you can choose any of them which suits your needs. But the key to it is the correctly formatted String which represents the DB scripts to create a SP. I haven't figure out how to combine dropping and creation of a SP in one SQL script/String, but I'm happy with those solutions anyway.

FYI:

• No special annotation or setting on the class nor method level is needed
• I'm using Spring FW, which simplifies things, so don't need to do low level implementation of everything
• After searching and trying various definitely working 'tips', the following resources helped me to arrive to those for sure working solutions:
  • Execute SQL file from Spring JDBC Template
  • similar question Spring and MySQL stored procedure on stackoverflow, where I have actually posted my answer there
  • but most useful was official Oracle tutorial on JavaSE & JDBC
  • and other Java/JDBC tutorial where you can see the most important bit of formatting the sql Java String

Solution 1: using JdbcTemplate and Java String

It is very important to have trailing spaces in your nicely formatted String sql script

        String sqlSP = "CREATE PROCEDURE test_stored_proc (IN pInstanceId varchar(255)) " +
                "BEGIN " +
                    "SELECT * FROM vw_subscriptions WHERE instanceId = pInstanceId; " +
                "END";

        jdbcTemplate.execute(sqlSP);

Solution 2: using ResourceDatabasePopulator & ClassPathResource to load SQL script in a file

        ClassPathResource resource = new ClassPathResource("/data/create-sp_TEST_STORED_PROC.sql");
        jdbcTemplate.execute("DROP procedure IF EXISTS `test_stored_proc`;");

        ResourceDatabasePopulator databasePopulator = new ResourceDatabasePopulator(resource);
        databasePopulator.setSeparator(ScriptUtils.EOF_STATEMENT_SEPARATOR);
        databasePopulator.execute(testSetupDataSource);

Solution 3: using ScriptUtils & ClassPathResource to load SQL script in a file

        ClassPathResource resource = new ClassPathResource("/data/create-sp_TEST_STORED_PROC.sql");
        jdbcTemplate.execute("DROP procedure IF EXISTS `test_stored_proc`;");

        ScriptUtils.executeSqlScript(Objects.requireNonNull(testSetupDataSource).getConnection(), new EncodedResource(resource),
                false, false,
                ScriptUtils.DEFAULT_COMMENT_PREFIX, ScriptUtils.EOF_STATEMENT_SEPARATOR,
                ScriptUtils.DEFAULT_BLOCK_COMMENT_START_DELIMITER, ScriptUtils.DEFAULT_BLOCK_COMMENT_END_DELIMITER);

Content of the create-sp_TEST_STORED_PROC.sql script

For solution #1 and #2 the same rule as for the 1st solution applies:

• The formatting and presence of whitespace characters is important especially an extra trailing space at the end of each line.

So the below code is archived by my vim setup of representing whitespace characters

CREATE·PROCEDURE·test_stored_proc·(IN·pInstanceId·varchar(255))~¬
BEGIN~¬
–→SELECT·*·FROM·vw_subscriptions·WHERE·instanceId·=·pInstanceId;~¬
END;~¬

I believe it is internally represented as one line piece of string: CREATE PROCEDURE test_stored_proc (IN pInstanceId varchar(255)) BEGIN SELECT * FROM vw_subscriptions WHERE instanceId = pInstanceId; END

Almost full source-code can be found at my GitHub

Answer 5

If you try to load scripts for SpringBootTest, @Sql and @SqlConfig will work.

@Sql(
    "/db/integration/stored-procedures.sql",
    config = SqlConfig(separator = "$$")
)

In sql scripts, you should remove the DELIMITER $$ statement. Like this:

CREATE PROCEDURE <sp_name>()
<your owesome definition>
END $$

If the script file contains only one CREATE PROCEDURE statement, separator=EOF_STATEMENT_SEPARATOR will be fine.