H2 mem db failing with LocalDateTime

[Al Grant]

I have a Spring Boot 3.2 project which I want to use h2 mem

The entities I have that contain LocalDateTime properties fail in the auto DDL.

i.e. All the other tables get created for java objects that dont have a LocalDateTime property - but those objects that do fail.

Hibernate is the ORM.

spring.datasource.url=jdbc:h2:file:~/IdeaProjects/kiwi1/h2mem/data;DB_CLOSE_ON_EXIT=FALSE;AUTO_SERVER=TRUE;INIT=CREATE SCHEMA IF NOT EXISTS TEST
spring.datasource.driverClassName=org.h2.Driver
spring.sql.init.mode=always
spring.datasource.username=sa
spring.datasource.password=password
spring.jpa.hibernate.ddl-auto=update
spring.jpa.generate-ddl=true
spring.jpa.show-sql=true
logging.level.org.springframework.boot.autoconfigure=ERROR
spring.sql.init.data-locations=classpath:/datadev.sql

For example:

@AllArgsConstructor
@NoArgsConstructor
@ToString
@Entity
@Data
@JsonIgnoreProperties({"hibernateLazyInitalizer", "handler"})
@SequenceGenerator(name = "health_check_seq", sequenceName = "HEALTH_CHECK_SEQ", initialValue = 100, allocationSize = 50)
@Table(name = "health_check")
public class HealthCheck {

@Id
@GeneratedValue(strategy = SEQUENCE, generator = "health_check_seq")
private Long id;

@JsonManagedReference
@ManyToOne(fetch = FetchType.EAGER, optional = false)
@JoinColumn(name = "bird_id")
private Bird bird;

@JsonManagedReference
@OneToMany(cascade = CascadeType.ALL, orphanRemoval = true, mappedBy = "healthCheck")
private List<Task> tasks;

@JsonFormat(pattern = "yyyy-MM-dd HH:mm")
private LocalDateTime catchDateTime;

If I remove the LocalDateTime field the table is created.

The stacktrace I do not understand what it is telling me during table creation:

Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "
CREATE TABLE HEALTH_CHECK (
    ID BIGINT NOT NULL,
    CATCH_DATE_TIME DATETIME([*]6),
    LOCATION GEOMETRY,
    RELEASE_DATE_TIME DATETIME(6),
    BIRD_ID BIGINT NOT NULL,
    HOLDER_ID BIGINT,
    MEASURER_ID BIGINT,
    PRIMARY KEY (ID)
) ENGINE=INNODB "; expected "FOR, UNSIGNED, INVISIBLE, VISIBLE, NOT, NULL, AS, DEFAULT, GENERATED, ON, NOT, NULL, AUTO_INCREMENT, BIGSERIAL, SERIAL, IDENTITY, NULL_TO_DEFAULT, SEQUENCE, SELECTIVITY, COMMENT, CONSTRAINT, PRIMARY, UNIQUE, NOT, NULL, CHECK, REFERENCES, ,, )"; SQL statement:

create table health_check (
    id bigint not null,
    catch_date_time datetime(6),
    location geometry,
    release_date_time datetime(6),
    bird_id bigint not null,
    holder_id bigint,
    measurer_id bigint,
    primary key (id)
) engine=InnoDB [42001-197]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:357)
at org.h2.message.DbException.getSyntaxError(DbException.java:217)
at org.h2.command.Parser.getSyntaxError(Parser.java:555)
at org.h2.command.Parser.read(Parser.java:3518)
at org.h2.command.Parser.readIfMore(Parser.java:950)
at org.h2.command.Parser.parseCreateTable(Parser.java:6571)
at org.h2.command.Parser.parseCreate(Parser.java:4615)
at org.h2.command.Parser.parsePrepared(Parser.java:380)
at org.h2.command.Parser.parse(Parser.java:335)
at org.h2.command.Parser.parse(Parser.java:311)
at org.h2.command.Parser.prepareCommand(Parser.java:278)
at org.h2.engine.Session.prepareLocal(Session.java:611)
at org.h2.engine.Session.prepareCommand(Session.java:549)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1247)
at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:217)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:205)
at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94)
at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:78)
... 41 common frames omitted
2023-09-09 08:47:22 -

Anyone able to help here? 

Thanks

Al

[Evgenij Ryazanov]

Hello!

You cannot use MySQL/MariaDB-specific features without MySQL or MariaDB compatibility mode:
https://h2database.com/html/features.html#compatibility

If you use H2 in unit tests you also need to disable their automatic configuration by Spring Boot, otherwise your custom JDBC URL with all these settings will be ignored.