"You have an error in your SQL syntax; [...]" when using MULTISET with MariaDB dialect

[Paul Aeschlimann]

Hi

I struggle to get a query with MULTISET working. I get the following error:

java.sql.SQLSyntaxErrorException: (conn=763) 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 'set @@group_concat_max_len = 4294967295; select `bookstore`.`author`.`first_n...' at line 1
at org.mariadb.jdbc.export.ExceptionFactory.createException(ExceptionFactory.java:282) ~[mariadb-java-client-3.2.0.jar:na]
at org.mariadb.jdbc.export.ExceptionFactory.create(ExceptionFactory.java:370) ~[mariadb-java-client-3.2.0.jar:na]
at org.mariadb.jdbc.message.ClientMessage.readPacket(ClientMessage.java:134) ~[mariadb-java-client-3.2.0.jar:na]
at org.mariadb.jdbc.client.impl.StandardClient.readPacket(StandardClient.java:883) ~[mariadb-java-client-3.2.0.jar:na]
at org.mariadb.jdbc.client.impl.StandardClient.readResults(StandardClient.java:822) ~[mariadb-java-client-3.2.0.jar:na]
at org.mariadb.jdbc.client.impl.StandardClient.readResponse(StandardClient.java:741) ~[mariadb-java-client-3.2.0.jar:na]
at org.mariadb.jdbc.client.impl.StandardClient.execute(StandardClient.java:665) ~[mariadb-java-client-3.2.0.jar:na]
at org.mariadb.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:92) ~[mariadb-java-client-3.2.0.jar:na]
at org.mariadb.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:271) ~[mariadb-java-client-3.2.0.jar:na]
at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44) ~[HikariCP-5.0.1.jar:na]
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java) ~[HikariCP-5.0.1.jar:na]
at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:219) ~[jooq-3.18.7.jar:na]
at org.jooq.impl.Tools.executeStatementAndGetFirstResultSet(Tools.java:4734) ~[jooq-3.18.7.jar:na]
at org.jooq.impl.AbstractResultQuery.execute(AbstractResultQuery.java:236) ~[jooq-3.18.7.jar:na]
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:346) ~[jooq-3.18.7.jar:na]
at org.jooq.impl.AbstractResultQuery.fetch(AbstractResultQuery.java:290) ~[jooq-3.18.7.jar:na]
at org.jooq.impl.SelectImpl.fetch(SelectImpl.java:2838) ~[jooq-3.18.7.jar:na]
at ch.homeresearch.poc.bookstore.BookstoreJooq.repository.BookRepository.getAll(BookRepository.java:80) ~[main/:na]
at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:578) ~[na:na]
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:352) ~[spring-aop-6.1.1.jar:6.1.1]
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196) ~[spring-aop-6.1.1.jar:6.1.1]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-6.1.1.jar:6.1.1]
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:765) ~[spring-aop-6.1.1.jar:6.1.1]
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137) ~[spring-tx-6.1.1.jar:6.1.1]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.1.1.jar:6.1.1]
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:765) ~[spring-aop-6.1.1.jar:6.1.1]
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:717) ~[spring-aop-6.1.1.jar:6.1.1]
at ch.homeresearch.poc.bookstore.BookstoreJooq.repository.BookRepository$$SpringCGLIB$$0.getAll(<generated>) ~[main/:na]
at ch.homeresearch.poc.bookstore.BookstoreJooq.service.BookService.listAll(BookService.java:20) ~[main/:na]
at ch.homeresearch.poc.bookstore.BookstoreJooq.controller.BookController.showBookList(BookController.java:29) ~[main/:na]
at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:578) ~[na:na]
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:254) ~[spring-web-6.1.1.jar:6.1.1]
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:182) ~[spring-web-6.1.1.jar:6.1.1]
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:118) ~[spring-webmvc-6.1.1.jar:6.1.1]
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:917) ~[spring-webmvc-6.1.1.jar:6.1.1]
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:829) ~[spring-webmvc-6.1.1.jar:6.1.1]
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-6.1.1.jar:6.1.1]
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1089) ~[spring-webmvc-6.1.1.jar:6.1.1]
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:979) ~[spring-webmvc-6.1.1.jar:6.1.1]
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1014) ~[spring-webmvc-6.1.1.jar:6.1.1]
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:903) ~[spring-webmvc-6.1.1.jar:6.1.1]
at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:564) ~[tomcat-embed-core-10.1.16.jar:6.0]
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:885) ~[spring-webmvc-6.1.1.jar:6.1.1]
at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:658) ~[tomcat-embed-core-10.1.16.jar:6.0]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:205) ~[tomcat-embed-core-10.1.16.jar:10.1.16]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:149) ~[tomcat-embed-core-10.1.16.jar:10.1.16]
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51) ~[tomcat-embed-websocket-10.1.16.jar:10.1.16]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:174) ~[tomcat-embed-core-10.1.16.jar:10.1.16]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:149) ~[tomcat-embed-core-10.1.16.jar:10.1.16]
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) ~[spring-web-6.1.1.jar:6.1.1]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116) ~[spring-web-6.1.1.jar:6.1.1]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:174) ~[tomcat-embed-core-10.1.16.jar:10.1.16]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:149) ~[tomcat-embed-core-10.1.16.jar:10.1.16]
at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) ~[spring-web-6.1.1.jar:6.1.1]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116) ~[spring-web-6.1.1.jar:6.1.1]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:174) ~[tomcat-embed-core-10.1.16.jar:10.1.16]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:149) ~[tomcat-embed-core-10.1.16.jar:10.1.16]
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) ~[spring-web-6.1.1.jar:6.1.1]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116) ~[spring-web-6.1.1.jar:6.1.1]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:174) ~[tomcat-embed-core-10.1.16.jar:10.1.16]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:149) ~[tomcat-embed-core-10.1.16.jar:10.1.16]
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:167) ~[tomcat-embed-core-10.1.16.jar:10.1.16]
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90) ~[tomcat-embed-core-10.1.16.jar:10.1.16]
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:482) ~[tomcat-embed-core-10.1.16.jar:10.1.16]
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:115) ~[tomcat-embed-core-10.1.16.jar:10.1.16]
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93) ~[tomcat-embed-core-10.1.16.jar:10.1.16]
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) ~[tomcat-embed-core-10.1.16.jar:10.1.16]
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:340) ~[tomcat-embed-core-10.1.16.jar:10.1.16]
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:391) ~[tomcat-embed-core-10.1.16.jar:10.1.16]
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63) ~[tomcat-embed-core-10.1.16.jar:10.1.16]
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:896) ~[tomcat-embed-core-10.1.16.jar:10.1.16]
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1744) ~[tomcat-embed-core-10.1.16.jar:10.1.16]
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52) ~[tomcat-embed-core-10.1.16.jar:10.1.16]
at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191) ~[tomcat-embed-core-10.1.16.jar:10.1.16]
at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659) ~[tomcat-embed-core-10.1.16.jar:10.1.16]
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) ~[tomcat-embed-core-10.1.16.jar:10.1.16]

The generated query looks as expected based on https://www.jooq.org/doc/3.15/manual/sql-building/column-expressions/multiset-value-constructor/ and what jOOQ generates for the MariaDB dialect.

 

To my surprise, I can even execute the query in PHPMyAdmin and get the desired result:

This is the query (copy paste):

set @t = @@group_concat_max_len;
set @@group_concat_max_len = 4294967295;
select
  `bookstore`.`author`.`first_name`,
  `bookstore`.`author`.`last_name`,
  (
    select coalesce(
      json_merge_preserve(
        '[]',
        concat(
          '[',
          group_concat(json_array(`bookstore`.`book`.`id`, `bookstore`.`book`.`title`) separator ','),
          ']'
        )
      ),
      json_array()
    )
    from `bookstore`.`book`
  ) as `books`
from `bookstore`.`author`
order by `bookstore`.`author`.`id`;
set @@group_concat_max_len = @t;

I use Spring Boot 3.2.0 and jOOQ 3.18.7.

I specified the dialect in application.properties:

spring.jooq.sql-dialect=Mariadb

The dialect is respected in the jOOQ configuration:

In build.gradle, I have the following dependencies:

dependencies {
implementation 'org.springframework.boot:spring-boot-starter-jooq'
implementation 'org.springframework.boot:spring-boot-starter-thymeleaf'
implementation 'org.springframework.boot:spring-boot-starter-web'
implementation 'org.jooq:jooq:3.18.7'
implementation('org.mariadb.jdbc:mariadb-java-client')
testImplementation 'org.springframework.boot:spring-boot-starter-test'
}

Why does jOOQ get a syntax error from MariaDB? The MariaDB driver version 3.2.0 is from August this year.

https://mariadb.com/kb/en/about-mariadb-connector-j/

[Lukas Eder]

Hi Paul,

If you google the error message, you should find these resources. Do these help?

- https://stackoverflow.com/q/70351547/521799

- https://blog.jooq.org/mysqls-allowmultiqueries-flag-with-jdbc-and-jooq/

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/a5e159e3-91e3-4349-b6d3-dcae27151fe9n%40googlegroups.com.

[Paul Aeschlimann]

Hi Lukas

Thank you for your quick response, with the allow allowMultiQueries=true in the JDBC connection string it works now.

I obviously did not use appropriate keywords for googling back then - "SQLSyntaxErrorException jooq multiset" and the thread at stackoverflow.com appears as 1st result - sorry for that.

Best regards

Paul

[Lukas Eder]

This wasn't meant as a reproach, more as a hint for future searches. Googling the *exact* error message typically yields best results, because someone already posted the stack trace somewhere (e.g. on stack overflow), and I also often do this explicitly on the jOOQ blog or stack overflow, because it's what many people search for...

I hope this helps,

Lukas

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/5a21b6fd-7ce3-4fcd-b37f-48e483858df5n%40googlegroups.com.