Getting SQL Syntax Error when loading schema.sql file in Spring Boot

1
votes

I have a pretty basic schema.sql file:

Ideally, I just want the tables to auto-generate. I've disabled the auto ddl setting in my application.properties. However, I am getting errors:

Error:

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSource' defined in class path resource [org/springframework/boot/autoconfigure/jdbc/DataSourceConfiguration$Hikari.class]: Initialization of bean failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'org.springframework.boot.autoconfigure.jdbc.DataSourceInitializerInvoker': Invocation of init method failed; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of URL [file:/Users/schachte/Documents/Habicus-Core/build/resources/main/schema.sql]: CREATE TABLE goal_metrics (; nested exception is org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "CREATE TABLE ""GOAL_METRICS"" ( [*]"; expected "identifier"; SQL statement:

Schema.SQL

CREATE TABLE `goal_metrics` (
  `goal_metrics_id` int(11) NOT NULL,
  `goal_complete` varchar(45) DEFAULT NULL,
  `goal_in_progress` varchar(45) DEFAULT NULL,
  `money_made_on_goal` varchar(45) DEFAULT NULL,
  `time_until_due_date` decimal(10,0) DEFAULT NULL,
  `goals_goal_id` int(11) NOT NULL,
  PRIMARY KEY (`goal_metrics_id`,`goals_goal_id`),
  KEY `fk_goal_metrics_goals1_idx` (`goals_goal_id`),
  CONSTRAINT `fk_goal_metrics_goals1` FOREIGN KEY (`goals_goal_id`) REFERENCES `goals` (`goal_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `metrics` (
  `goalsInProgress` int(11) NOT NULL,
  PRIMARY KEY (`goalsInProgress`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `users` (
  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(45) DEFAULT NULL,
  `password` varchar(45) DEFAULT NULL,
  `email` varchar(255) NOT NULL,
  `dob` datetime DEFAULT NULL,
  `gender` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `email_UNIQUE` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1
javaspring-booth2
You seem to be using MySQL syntax, but your error message indicates you're using h2. h2 is not mysql. I doubt h2 recognizes MySQL syntax such as back-ticks for identifier delimiters. – Bill Karwin

1 Answers

0
votes

I ran into the same issue, only to realise that h2db doesn't like it when you use reserved words. Don't name your DB or Tables or even fields using reserved words like:

  • Group
  • Memory
  • Select
  • Authorization

...etc...

I solved it by renaming my Domain Objects (& made sure that non of the names match the reserved h2db names), since I'm using Spring JPA, Repositories.

A common mistake most people make, is to use MySql Syntax on H2DB. Refrain from making that mistake too. H2 Database has its own syntax.