Situation

I am attempting to initialize a database following the instructions in the Spring Boot documentation under the "Initialize a Database Using Basic SQL Scripts" section.

When initializing the database with SQL files using sql.init.mode=always, you can specify the SQL file for initialization in the /resources directory (e.g., /resources/schema.sql).

Below is the content of the /resources/schema.sql file for initializing the schema:

# 1. remove table start
drop table if exists firstTable;
drop table if exists secondTable;
# 1. remove table end

# 2. create table start
create table firstTable
(
    id                   bigint auto_increment
        primary key,
    test_field           bigint               not null
);

create table secondTable
(
    id                   bigint auto_increment
        primary key,
    test_field           bigint               not null
);
# 2. create table end

In schema.sql, '#' is used for inline comments. However, since the DEFAULT_COMMENT_PREFIX is '--', some SQL statements were not parsed correctly, leading to issues as shown in the following image:

Parsing Issue

As a result, the table creation for firstTable, as specified in schema.sql, was ignored:

Table Creation Ignored

To avoid any potential misunderstanding, I suggest adding a mention of this issue in the documentation.

Changes

Added the following statement to [[howto.data-initialization.using-basic-sql-scripts]]

NOTE: When including comments in your SQL file, it is advisable to use -- for single-line comments and /* and */ for block comments. Be cautious when using other comment formats, as they may lead to the omission of certain SQL statements during parsing.

Comment From: mhalbritter

Hello! Which SQL dialect are you using that # starts a comment? I've only encountered SQL comments with -- so far.

Comment From: PENEKhun

@mhalbritter

mysql and mariadb support # comments. Please refer to the following official references

Comment From: mhalbritter

I wonder if we should make the comment settings configurable so that you can continue to use # for comments. Let's see what the rest of the team thinks.

Comment From: mhalbritter

Ah, never mind, i found an old discussion about that: https://github.com/spring-projects/spring-boot/issues/16820

Comment From: mhalbritter

Thank you very much and congratulations on your first contribution :tada:!