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 On Tuesday 2 January 2024 at 11:16:09 UTC+1 lukas...@gmail.com wrote: > 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/ > > On Sat, Dec 30, 2023 at 12:14 PM Paul Aeschlimann <paul.aes...@gmail.com> > wrote: > >> 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. >> >> [image: 2023-12-30 12_01_04-Clipboard.png] >> >> To my surprise, I can even execute the query in PHPMyAdmin and get the >> desired result: >> >> [image: 2023-12-30 12_03_16-Clipboard.png] >> >> 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: >> [image: 2023-12-30 12_05_44-Clipboard.png] >> >> 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/ >> >> -- >> 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 >> >> <https://groups.google.com/d/msgid/jooq-user/a5e159e3-91e3-4349-b6d3-dcae27151fe9n%40googlegroups.com?utm_medium=email&utm_source=footer> >> . >> > -- 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+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/5a21b6fd-7ce3-4fcd-b37f-48e483858df5n%40googlegroups.com.
