CODEWITHSUNDEEP
javaspring-bootspring-data-jpaspring-datah2
Giorgi Tsiklauri
Giorgi Tsiklauri

Reputation: 11130

Syntax error in SQL statement after upgrading spring-boot-starter-parent from 2.1.1 to 2.3.3

In order to test and tweak some internals of Spring Boot, I have a sample Spring Boot project, which I often break, on purpose, and learn new things of its internals.

Now, I have a Spring Boot project, with pom, like:

<?xml version='1.0' encoding='UTF-8'?>
<project xmlns='http://maven.apache.org/POM/4.0.0'
         xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'
         xsi:schemaLocation='http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd'>
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.test.springboot</groupId>
    <artifactId>spring-boot-application</artifactId>
    <version>1.0-SNAPSHOT</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.1.RELEASE</version>
    </parent>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>2.1.4.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
            <version>2.1.4.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.6</version>
            <scope>provided</scope>
        </dependency>

        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>org.flywaydb</groupId>
            <artifactId>flyway-core</artifactId>
            <version>5.2.4</version>
        </dependency>
    </dependencies>


    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

and Flyway's migration, with:

CREATE TABLE PERSON(
    PERSON_ID BIGINT AUTO_INCREMENT PRIMARY KEY,
    PERSON_NAME VARCHAR(20) NOT NULL,
    PERSON_SURNAME VARCHAR(20) NOT NULL,
    PERSON_AGE BIGINT,
);

works fine, until spring-boot-starter-parent version is 2.1.1.

I now decided upgrade some dependencies and see, how and what would break (as it quite often happens when working with Spring infrastructure). So, after upgrading spring-boot-starter-parent version to 2.3.3, above SQL script started to throw:

Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flywayInitializer' defined in class path resource [org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration$FlywayConfiguration.class]: Invocation of init method failed; nested exception is org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateException: 
Migration V1__initial_schema.sql failed

---------------------------------------
SQL State  : 42001
Error Code : 42001
Message    : Syntax error in SQL statement "CREATE TABLE PERSON(
    PERSON_ID BIGINT AUTO_INCREMENT PRIMARY KEY,
    PERSON_NAME VARCHAR(20) NOT NULL,
    PERSON_SURNAME VARCHAR(20) NOT NULL,
    PERSON_AGE BIGINT,
    )[*]"; expected "identifier"; SQL statement:
CREATE TABLE PERSON(
    PERSON_ID BIGINT AUTO_INCREMENT PRIMARY KEY,
    PERSON_NAME VARCHAR(20) NOT NULL,
    PERSON_SURNAME VARCHAR(20) NOT NULL,
    PERSON_AGE BIGINT,
    ) [42001-200]

To be frank, even though exception seems clear, at the same time, it does not clearly state what is the exact problem, and it's a bit tricky to guess how the same syntax worked with just a little bit older version.

I took a guess and removed final comma (after the definition of last PERSON_AGE BIGINT column), and it started to work again.

I would love to understand what really causes this exception, behind the scenes. I can't really see any transitive dependencies changing in the project, when upgrading spring-boot-starter-parent, to blame any particular library or .jar.. yet, something has to be causing this exception, and I would like to understand - what does.

I've tested both syntaxes (with and without final comma ",") and both work just fine, as a standalone SQL query, in an SQL shell; however, it doesn't work in the scenario described above.

Would anyone shed some more light on this?

Appreciations.

Upvotes: 1

Views: 1403

Answers (1)

Andy Wilkinson
Andy Wilkinson

Reputation: 116171

H2 dropped support for trailing commas in 1.4.200. Spring Boot upgraded to H2 1.4.200 in 2.1.10 and 2.2.1 and, at the time of writing, it has been the default version of H2 in all Spring Boot releases since then. When you upgrade to Spring Boot 2.3.3 you are also upgrading to H2 1.4.200 so the trailing commas in your migration are no longer support.

If you're curious where these versions are coming from, they're inherited via spring-boot-starter-parent which uses spring-boot-dependencies as its parent. spring-boot-dependencies contains dependency management for numerous Spring projects and third-party dependencies, including H2. The default versions for any given version of Spring Boot are included in the reference documentation:

Upvotes: 3

Related Questions