h2+hibernate error when the column name is a keyword

Question

My production uses MySQL 5.7 and I run h2 (MODE=MySQL) for tests. My connection string is like:

jdbc:h2:mem:ci_main;IGNORECASE=TRUE;MODE=MySQL;

I used Hibernate 5.6.3 with MySQL57InnoDBDialect.

It has been working fine with h2 version 1.4.196. But recently, I've tried to upgrade h2 to version 2.0.204 and SQL statements started to fail. The error was 42001 complaining about a column name value, which I believe is a keyword. It seemed at some point between the 2 versions, h2 became more restrictive on the SQL syntax than MySQL 5.7.

If I were writing SQL statements, I could have quoted the column name. But I'm using hibernate (which works fine with MySQL 5.7) and I do not want to change the column name just because of this.

Is there a way to let h2 be more "compatible" with MySQL 5.7?

Thanks.

Bioscopy

Answer 1

You can add ;NON_KEYWORDS=VALUE to JDBC URL.

Usually you should also have ;DATABASE_TO_LOWER=TRUE in addition to ;MODE=MySQL for better compatibility.

Carmelo

Answer 2

What worked for me was just to escape the column name with back-ticks:

@Column(name = "`value`")

Dollop