H2 Schema initialization. Syntax error in SQL statement

Question

I have a spring boot application and I trying to initialize some data on application startup.

This is my application properties:

#Database connection
spring.datasource.url=jdbc:h2:mem:test_db
spring.datasource.username=...
spring.datasource.password=...
spring.datasource.driverClassName=org.h2.Driver

spring.datasource.initialize=true
spring.datasource.schema=schema.sql
spring.datasource.data=schema.sql


#Hibernate configuration
#spring.jpa.hibernate.ddl-auto = none

This is schema.sql:

CREATE TABLE IF NOT EXISTS `Person` (
  `id`         INTEGER  PRIMARY KEY AUTO_INCREMENT,
  `first_name` VARCHAR(50) NOT NULL,
  `age`        INTEGER  NOT NULL,
  PRIMARY KEY(`id`)
);

and data.sql

INSERT INTO `Person` (
  `id`,
  `first_name`,
  `age`
) VALUES (
  1,
  'John',
  20
);

But I got 'Syntax error in SQL statement' on application startup:

19:08:45.642 6474 [main] INFO  o.h.tool.hbm2ddl.SchemaExport - HHH000476: Executing import script '/import.sql'
19:08:45.643 6475 [main] ERROR o.h.tool.hbm2ddl.SchemaExport - HHH000388: Unsuccessful: CREATE TABLE Person (
19:08:45.643 6475 [main] ERROR o.h.tool.hbm2ddl.SchemaExport - Syntax error in SQL statement "CREATE TABLE PERSON ( [*]"; expected "identifier"
Syntax error in SQL statement "CREATE TABLE PERSON ( [*]"; expected "identifier"; SQL statement:

I can't understand, what's wrong with this SQL.

budthapa · Accepted Answer · 2017-05-30 16:22:49Z

17

Try this code. Remove PRIMARY KEY(id) and execute it.

CREATE TABLE IF NOT EXISTS `Person` (
    `id`         INTEGER  PRIMARY KEY AUTO_INCREMENT,
     `first_name` VARCHAR(50) NOT NULL,
     `age`        INTEGER  NOT NULL
);

answered May 30, 2017 at 16:22

budthapa

1,04214 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

malvadao · Accepted Answer · 2020-08-28 13:09:13Z

This error results from the structure of the CREATE TABLE declaration.

It will be the result when you have an extra comma in the end of your SQL declaration--no column declaration following the comma. For example:

CREATE TABLE IF NOT EXISTS `Person` (
  `id`         INTEGER  PRIMARY KEY AUTO_INCREMENT,
  `first_name` VARCHAR(50) NOT NULL,
  `age`        INTEGER  NOT NULL,     --note this line has a comma in the end
);

That's because CREATE TABLE expects a list of the columns that will be created along with the table, and the first parameter of the column is the identifier. As you check here, the column declaration follows the structure:

identifier datatype <constraints> <autoincrement> <functions>

Thus, in your case, as @budthapa and @Vishwanath Mataphati have mentioned, you could simply remove the PRIMARY KEY(id) line from the CREATE TABLE declaration. Moreover, you have already stated that id is a primary key on the first line of the column definitions.

In case you do not have a statement as the PRIMARY KEY declaration, be sure to check for the extra comma following your last column declaration.

cng.buff · Accepted Answer · 2022-10-31 08:56:49Z

9

I was add below in to application.properties and it work for me

spring.jpa.properties.hibernate.globally_quoted_identifiers=true spring.jpa.properties.hibernate.globally_quoted_identifiers_skip_column_definitions = true

answered Oct 31, 2022 at 8:56

cng.buff

6216 silver badges7 bronze badges

1 Comment

Human programmer Over a year ago

This worked for me. I'm not sure what it does, but it's related to H2 memory database, if you are using one.

Suraj Rao · Accepted Answer · 2020-08-12 09:33:24Z

3

Try this, as you have used Table_name

CREATE TABLE IF NOT EXISTS Person (
    id        INTEGER  PRIMARY KEY AUTO_INCREMENT,
     first_name VARCHAR(50) NOT NULL,
     age        INTEGER  NOT NULL
);

edited Aug 12, 2020 at 9:33

Suraj Rao

29.7k11 gold badges96 silver badges104 bronze badges

answered Aug 12, 2020 at 9:21

Vishwanath Mataphati

313 bronze badges

Comments

prabhakar_kevat · Accepted Answer · 2023-11-08 14:40:49Z

3

In my case I was missing the semicolon at the end of create table statement.

answered Nov 8, 2023 at 14:40

prabhakar_kevat

311 silver badge3 bronze badges

Comments

Osadhi Virochana · Accepted Answer · 2021-02-18 09:27:09Z

1

What helped in my case was removing single quotes from the table name in my insert query

I had to change this:

INSERT INTO 'translator' (name, email) VALUES ('John Smith', '[email protected]');

to this:

INSERT INTO translator (name, email) VALUES ('John Smith', '[email protected]');

edited Feb 18, 2021 at 9:27

Osadhi Virochana

1,3022 gold badges13 silver badges22 bronze badges

answered Feb 17, 2021 at 10:59

mirkos_javos

112 bronze badges

Comments

user9304291 · Accepted Answer · 2021-11-08 07:23:29Z

1

You set auto increment id, so you can't insert new record with id.

Try INSERT INTO `Person` (
  `first_name`,
  `age`
) VALUES (
  'John',
  20
);

answered Nov 8, 2021 at 7:23

user9304291

Comments

Daniel Barton · Accepted Answer · 2022-10-07 20:32:53Z

1

I ran into same issue. I fixed that with these application.properties:

spring.jpa.properties.hibernate.connection.charSet=UTF-8
spring.jpa.properties.hibernate.hbm2ddl.import_files_sql_extractor=org.hibernate.tool.hbm2ddl.MultipleLinesSqlCommandExtractor

Some issue with multi-line and default encoding.

answered Oct 7, 2022 at 20:32

Daniel Barton

5416 silver badges14 bronze badges

Comments

Laura Liparulo · Accepted Answer · 2023-05-25 22:51:58Z

1

In my case the problem was that I was using an integer field called "year"

answered May 25, 2023 at 22:51

Laura Liparulo

2,91728 silver badges27 bronze badges

Comments

Maciek Murawski · Accepted Answer · 2023-03-06 08:53:14Z

0

I had the same problem when one of my fields in the JPA Entity had the same name as SQL reserved keyword (FROM).

Before:

@Entity
public class Event {

    ...

    @NotNull
    private LocalDateTime from;

}

Fix:

@Entity
public class Event {

    ...

    @NotNull
    private LocalDateTime fromDate;

}

answered Mar 6, 2023 at 8:53

Maciek Murawski

4425 silver badges17 bronze badges

Collectives™ on Stack Overflow

H2 Schema initialization. Syntax error in SQL statement

10 Answers 10

Comments

Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

10 Answers 10

Comments

Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related