MariaDB: SQLSyntaxErrorException in escape like queries

Hibernate ORM
1

Mariadb 10.5.x
Spring boot 3.3.6 (also tested with 3.4.0)
Hibernate 6.5.3.Final (also tested with 6.6)

Hi

I am using spring boot and in the process of upgrading spring boot and moving to flyway migration I “suddenly” get a SQLSyntaxErrorException when doing “like” queries. I can not reproduce what the reason was, I tried to downgrade/upgrade various dependencies, mariadb client, hibernate, spring boot but I now have this error persistent.

Here is the query, really no magic here:

Hibernate: 
    select
        b1_0.id,
        b1_0.address,
        b1_0.created_at,
        b1_0.description,
        b1_0.last_modified_at,
        b1_0.name,
        b1_0.vat 
    from
        buildings b1_0 
    where
        lower(b1_0.address) like ? escape '\' 
        or lower(b1_0.description) like ? escape '\' 
        or lower(b1_0.name) like ? escape '\' 
        or b1_0.id=? 
    order by
        b1_0.name 
    offset
        ? rows 
    fetch
        first ? rows only

Exception:

java.sql.SQLSyntaxErrorException: (conn=3161) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '\' or lower(b1_0.name) like ? escape '\' or b1_0.id=? order by b1_0.name offs...' at line 1

After searching the web, I found various topics about the “escape slash in like queries” but nothing really helped to get around this issue. After a while I used old configs and suddently the error disappeared. It seems that setting the MariaDBDialect workarounds this issue.

  jpa:
    properties:
      hibernate:
        dialect: org.hibernate.dialect.MariaDBDialect

After this setting, the query looks like:

Hibernate: 
    select
        b1_0.id,
        b1_0.address,
        b1_0.created_at,
        b1_0.description,
        b1_0.last_modified_at,
        b1_0.name,
        b1_0.vat 
    from
        buildings b1_0 
    where
        lower(b1_0.description) like ? escape '\\' 
        or lower(b1_0.name) like ? escape '\\' 
        or b1_0.id=? 
        or lower(b1_0.address) like ? escape '\\' 
    order by
        b1_0.name 
    limit
        ?, ?

Hope that helps

Regards

2

@resmo thank you for reaching out with your problem and explaining the fix you found for your particular use-case. It looks like Hibernate might have not been able to automatically detect the database vendor correctly, as it should default to using MariaDBDialect automatically. Which JDBC driver are you using?

3

Small correction: MariaDB is not 10.5.x but 10.9.3
The JDBC driver is

<dependency>
    <groupId>org.mariadb.jdbc</groupId>
    <artifactId>mariadb-java-client</artifactId>
    <version>3.4.1</version>
    <scope>runtime</scope>
</dependency>
4

What JDBC URL are you using and what is the datasource configuration?

5 
spring:
  datasource:
    url: jdbc:mariadb://127.0.0.1:3306/app_dev?createDatabaseIfNotExist=true
    driver-class-name: org.mariadb.jdbc.Driver
    username: root
    password: root
  jpa:
    generate-ddl: false
    show-sql: false
    properties:
      hibernate:
        dialect: org.hibernate.dialect.MariaDBDialect
        format_sql: true
    hibernate.ddl-auto: none

and

spring.jpa.hibernate.ddl-auto=none
spring.jpa.defer-datasource-initialization=false
6

So if you remove the dialect configuration, I guess Hibernate ORM resolves to the MySQLDialect for some reason? Could you please debug into productNameMatches and matchesResolutionInfo of org.hibernate.dialect.Database#MARIADB to check what is happening. I guess that the driver might report something wrong for some reason.

7

I debugged it and found out that it uses:

dialectReference=org.hibernate.dialect.H2Dialect
version=10.9.3 (SimpleDatabaseVersion)

Which looked suspicious, so I looked further and found a

database-platform: org.hibernate.dialect.H2Dialect

in application.yml but not in my dev application-dev.yml

Because of this, the setting gets inherited to application-dev.yml. Removing this (or disabling application.yml) resolved the issue.

Sorry for the trouble and thanks for your time help me find this issue.