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.

score 16 · Accepted Answer · answered May 30 '17 at 16:22

16

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 '17 at 16:22

budthapa

974
14
22

score 9 · Answer 2 · answered Aug 28 '20 at 13:09

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.

score 8 · Answer 3 · answered Oct 31 '22 at 08:56

8

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 '22 at 08:56

cng.buff

405
4
5

1

This worked for me. I'm not sure what it does, but it's related to H2 memory database, if you are using one. – Human programmer Dec 05 '22 at 18:43

score 3 · Answer 4 · edited Aug 12 '20 at 09:33

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 '20 at 09:33

Suraj Rao

29,388
11
94
103

answered Aug 12 '20 at 09:21

Vishwanath Mataphati

31
3

score 1 · Answer 5 · edited Feb 18 '21 at 09:27

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', 'john@mail.com');

to this:

INSERT INTO translator (name, email) VALUES ('John Smith', 'john@mail.com');

edited Feb 18 '21 at 09:27

Osadhi Virochana

1,294
2
11
21

answered Feb 17 '21 at 10:59

mirkos_javos

11
2

score 1 · Answer 6 · answered Nov 08 '21 at 07:23

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 08 '21 at 07:23

Marek Borecki

402
1
4
20

score 1 · Answer 7 · answered Oct 07 '22 at 20:32

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.

score 0 · Answer 8 · answered Mar 06 '23 at 08:53

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;

}

score 0 · Answer 9 · answered May 25 '23 at 22:51

0

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

answered May 25 '23 at 22:51

Laura Liparulo

2,849
26
27

H2 Schema initialization. Syntax error in SQL statement

9 Answers9

Linked