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.