Syntax error in SQL statement - Expected Identifier

Question

I have a springboot application and I would like to connect my application to the H2 database. Below is my schema.

CREATE TABLE IF NOT EXISTS employee 
(
      id INTEGER AUTO_INCREMENT  PRIMARY KEY,
      first_name VARCHAR(250) NOT NULL,
      last_name VARCHAR(250) NOT NULL,
      email VARCHAR(250) NOT NULL,
      posit VARCHAR(250) NOT NULL,
      mobile INTEGER NOT NULL
);

Below is is my application.properties.

spring.jpa.defer-datasource-initialization=true
spring.h2.console.enabled=true
spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=
spring.datasource.password=
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.h2.console.path=/h2-console
spring.h2.console.settings.trace=false
spring.h2.console.settings.web-allow-others=false

spring.datasource.initialize=true
spring.datasource.schema=schema.sql
spring.datasource.data=schema.sql
spring.jpa.hibernate.ddl-auto = update


logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type=TRACE

However, I face the following issue:

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "create table employee (id bigint not null auto_increment, email varchar(255), first_name varchar(255), last_name varchar(255), mobile integer, posit varchar(255), primary key (id)) engine[*]=InnoDB"; expected "identifier"; SQL statement.

May I know what is the issue and how do I change this?

score 1 · Answer 1 · answered Aug 12 '22 at 02:56

I believe that the schema you're sharing is not the same as the one that has the error, could you check that and share the corresponding one?

CREATE TABLE IF NOT EXISTS employee (
  id INTEGER AUTO_INCREMENT  PRIMARY KEY,
  first_name VARCHAR(250) NOT NULL,
  last_name VARCHAR(250) NOT NULL,
  email VARCHAR(250) NOT NULL,
  posit VARCHAR(250) NOT NULL,
  mobile INTEGER NOT NULL
);

Because in this link the error is in the schema, and the schema you shared works perfectly on fiddle

I'm not a pro in sql so I may be wrong.

score 0 · Answer 2 · answered Sep 15 '22 at 13:52

The error message says that you have a syntax error at this place of your CREATE statement:

engine[*]=InnoDB

h2 doesn't know what to do with the engine keyword. It won't work.

To fix it you should check your CREATE statement of the employee table and remove the engine[*]=InnoDB part. If you're also using the script for the setup of your MySQL database and you need this engine part, then you can set the following line on top of your file:

/*! SET storage_engine=INNODB */;

This should make it work with H2 and MySQL.

Here's also a google group where the same problem appeared.

Syntax error in SQL statement - Expected Identifier

2 Answers2