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 On Wed, Jan 3, 2024 at 6:30 PM Paul Aeschlimann <[email protected]> wrote: > 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 [email protected] 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 <[email protected]> >> 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 [email protected]. >>> 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 [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/jooq-user/5a21b6fd-7ce3-4fcd-b37f-48e483858df5n%40googlegroups.com > <https://groups.google.com/d/msgid/jooq-user/5a21b6fd-7ce3-4fcd-b37f-48e483858df5n%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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO6%2BwPWSYg1VSXoHULPj1MDsu62YCtTKcqd4FG6nSfWvdw%40mail.gmail.com.
