In Spring Boot JPA, why would I get "Table already exists; SQL statement:"

Question

I am building a Spring Boot project with JPA repositories. For testing, I use an H2 in-memory database. I initialize the database with a schema.sql file, an excerpt of which is:

CREATE TABLE `config_user_preset_dataclass` (
  `user_preset_dataclass_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_preset_id` int(11) NOT NULL,
  `dataclass_name` varchar(100) NOT NULL,
  `ngdp_audit_timestamp` datetime NOT NULL,
  `ngdp_update_timestamp` datetime NOT NULL, 
  PRIMARY KEY (`user_preset_dataclass_id`),
  UNIQUE KEY `idx_config_user_preset_dataclass_1` (`user_preset_id`,`dataclass_name`),
  CONSTRAINT `fk_config_user_preset_dataclass_1` FOREIGN KEY (`user_preset_id`) REFERENCES `config_user_preset` (`user_preset_id`)
);

The schema.sql is wired to initialize the database by setting the database URL to DB_URL=jdbc:h2:mem:data360;INIT=RUNSCRIPT FROM 'classpath:schema.sql';DATABASE_TO_UPPER=false, and I have verified that this is functional.

In my src/test/resources/application.properties, I have set spring.jpa.hibernate.ddl-auto=validate, so there should not be any secondary attempt to generate the table. However, when I run a simple test to save the JPA model, I get:

2018-04-11 17:59:17.847 ERROR 11540 --- [o-auto-1-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : Table "CONFIG_USER_PRESET" already exists; SQL statement:
CREATE TABLE `config_user_preset` (
  `user_preset_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(6) NOT NULL,
  `preset_name` varchar(200) NOT NULL,
  `ngdp_audit_timestamp` datetime NOT NULL,
  `ngdp_update_timestamp` datetime NOT NULL,
  PRIMARY KEY (`user_preset_id`),
  UNIQUE KEY `idx_user_preset_config_1` (`user_id`,`preset_name`)
) [42101-196]

The error clearly indicates that something is attempting to generate the table again.

What could it be?

Interestingly, this error goes away if I use the GenerationType.IDENTITY for the primary key. However, this causes another problem, so I'm not able to do that.

[edit]

@Entity
@Table(name = "config_user_preset_dataclass")
public class UserPresetDataclass {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "user_preset_dataclass_id")
    private long presetDataClassId;

    @Column(name = "user_preset_id")
    private long userPresetId;

    @Column(name = "dataclass_name")
    private String dataclassName;

...

[/edit]

Answer 1

Probably your DB still in memory, that's the reason why you're getting this error. I propose two alternatives:

1. Change the SQL statements. Instead of CREATE TABLE config_user_preset... you can use CREATE TABLE IF NOT EXISTS config_user_preset ...
2. If you're using Spring, you're able to create an embedded DB. Check here Spring embeddeb db table already exists error.