MariaDB: SQLSyntaxErrorException in escape like queries

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

@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?

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>

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

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

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.

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.