spring-boot: springboot native sql error Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (gb18030_chinese_ci,COERCIBLE) for operation 'like'

springboot-version: 3.2.1 graal-vm-version: GraalVM 17.0.9+11.1 mysql-version: 8.0

Works normally in jvm environment. Not working properly in native environment.

When I execute SQL containing Chinese, the following error appears:

java.sql.SQLException: Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (gb18030_chinese_ci,COERCIBLE) for operation ‘like’

jdbc url parameter:

useUnicode=true&characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2b8
==>  Preparing: SELECT count(0) FROM product_info t WHERE t.product_name LIKE ? AND t.classify_id = ?
==> Parameters: %啤%(String), 88899(Long)

all log:

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::                (v3.2.1)

2024-01-31 08:33:09.165 [] - [main] - INFO  - com.qianyefamily.QingYeFamilyApplication - Starting AOT-processed QingYeFamilyApplication using Java 17.0.10 with PID 617431 (/home/waitylyou/food-supply/target/food-supply started by root in /home/waitylyou) 
2024-01-31 08:33:09.165 [] - [main] - INFO  - com.qianyefamily.QingYeFamilyApplication - The following 1 profile is active: "dev" 
2024-01-31 08:33:09.189 [] - [main] - WARN  - io.undertow.websockets.jsr - UT026010: Buffer pool was not set on WebSocketDeploymentInfo, the default pool will be used 
2024-01-31 08:33:09.189 [] - [main] - INFO  - io.undertow.servlet - Initializing Spring embedded WebApplicationContext 
2024-01-31 08:33:09.189 [] - [main] - INFO  - o.s.b.w.s.c.ServletWebServerApplicationContext - Root WebApplicationContext: initialization completed in 24 ms 
Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.


,------.                           ,--.  ,--.         ,--.                         
|  .--. '  ,--,--.  ,---.   ,---.  |  '--'  |  ,---.  |  |  ,---.   ,---.  ,--.--. 
|  '--' | ' ,-.  | | .-. | | .-. : |  .--.  | | .-. : |  | | .-. | | .-. : |  .--' 
|  | --'  \ '-'  | ' '-' ' \   --. |  |  |  | \   --. |  | | '-' ' \   --. |  |    
`--'       `--`--' .`-  /   `----' `--'  `--'  `----' `--' |  |-'   `----' `--'    
                   `---'                                   `--'                        is intercepting.

2024-01-31 08:33:09.304 [] - [main] - INFO  - io.undertow - starting server: Undertow - 2.3.10.Final 
2024-01-31 08:33:09.304 [] - [main] - INFO  - org.xnio - XNIO version 3.8.8.Final 
2024-01-31 08:33:09.305 [] - [main] - INFO  - org.xnio.nio - XNIO NIO Implementation Version 3.8.8.Final 
2024-01-31 08:33:09.306 [] - [main] - INFO  - org.jboss.threads - JBoss Threads version 3.5.0.Final 
2024-01-31 08:33:09.311 [] - [main] - INFO  - o.s.boot.web.embedded.undertow.UndertowWebServer - Undertow started on port 8088 (http) with context path '/qingye-api' 
2024-01-31 08:33:09.312 [] - [main] - INFO  - com.qianyefamily.QingYeFamilyApplication - Started QingYeFamilyApplication in 0.175 seconds (process running for 0.179) 
2024-01-31 08:33:18.516 [] - [XNIO-1 task-2] - INFO  - io.undertow.servlet - Initializing Spring DispatcherServlet 'dispatcherServlet' 
2024-01-31 08:33:18.516 [] - [XNIO-1 task-2] - INFO  - org.springframework.web.servlet.DispatcherServlet - Initializing Servlet 'dispatcherServlet' 
2024-01-31 08:33:18.516 [] - [XNIO-1 task-2] - INFO  - org.springframework.web.servlet.DispatcherServlet - Completed initialization in 0 ms 
2024-01-31 08:33:18.813 [f0912bdb40c945e3856e2400ba7bf568] - [XNIO-1 task-2] - INFO  - com.waitylyou.util.RedisUtil - toBean:class com.waitylyou.domain.entity.UserInfo-{"userId":99999999,"userName":"超级管理员","userPhone":"18898989898","userType":"ADMIN","userLanguage":"zh_CN","createDate":1704521883000,"createId":99999999,"createName":"admin","updateId":99999999,"updateName":"admin","updateDate":1705266070771} 
2024-01-31 08:33:18.864 [f0912bdb40c945e3856e2400ba7bf568] - [XNIO-1 task-2] - INFO  - com.waitylyou.util.RedisUtil - 基础数据类型强制转换:class java.lang.String-28773a80-28bb-4dd9-a604-62a8b95fa498 
2024-01-31 08:33:18.867 [f0912bdb40c945e3856e2400ba7bf568] - [XNIO-1 task-2] - INFO  - c.q.app.service.impl.ProductInfoServiceImpl - 打印中文:{"classifyId":88899,"keyword":"啤"} 
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1636430] was not registered for synchronization because synchronization is not active
Cache Hit Ratio [SQL_CACHE]: 0.0
2024-01-31 08:33:18.868 [f0912bdb40c945e3856e2400ba7bf568] - [XNIO-1 task-2] - INFO  - com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Starting... 
2024-01-31 08:33:19.060 [f0912bdb40c945e3856e2400ba7bf568] - [XNIO-1 task-2] - INFO  - com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@34d1d800 
2024-01-31 08:33:19.060 [f0912bdb40c945e3856e2400ba7bf568] - [XNIO-1 task-2] - INFO  - com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Start completed. 
JDBC Connection [HikariProxyConnection@1211072321 wrapping com.mysql.cj.jdbc.ConnectionImpl@34d1d800] will not be managed by Spring
==>  Preparing: SELECT count(0) FROM product_info t WHERE t.product_name LIKE ? AND t.classify_id = ?
==> Parameters: %啤%(String), 88899(Long)
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1636430]
2024-01-31 08:33:19.079 [f0912bdb40c945e3856e2400ba7bf568] - [XNIO-1 task-2] - INFO  - o.s.jdbc.support.SQLErrorCodesFactory - Default sql-error-codes.xml not found (should be included in spring-jdbc jar) 
2024-01-31 08:33:19.080 [f0912bdb40c945e3856e2400ba7bf568] - [XNIO-1 task-2] - ERROR - c.w.component.web.advice.GlobalExceptionHandler - [Exception] 
2024-01-31 08:33:19.080 [f0912bdb40c945e3856e2400ba7bf568] - [XNIO-1 task-2] - ERROR - c.w.component.web.advice.GlobalExceptionHandler - org.springframework.jdbc.UncategorizedSQLException: 
### Error querying database.  Cause: java.sql.SQLException: Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (gb18030_chinese_ci,COERCIBLE) for operation 'like'
### The error may exist in com/qianyefamily/infra/mapper/ProductInfoMapper.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT count(0) FROM product_info t WHERE t.product_name LIKE ? AND t.classify_id = ?
### Cause: java.sql.SQLException: Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (gb18030_chinese_ci,COERCIBLE) for operation 'like'
; uncategorized SQLException; SQL state [HY000]; error code [1267]; Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (gb18030_chinese_ci,COERCIBLE) for operation 'like'
        at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:93)
        at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:439)
        at jdk.proxy4/jdk.proxy4.$Proxy236.selectList(Unknown Source)
        at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:224)
        at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:147)
        at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:80)
        at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:141)
        at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:86)
        at jdk.proxy4/jdk.proxy4.$Proxy49.selectList(Unknown Source)
        at com.qianyefamily.infra.repository.impl.ProductInfoRepositoryImpl.selectList(ProductInfoRepositoryImpl.java:35)
        at com.qianyefamily.infra.repository.impl.ProductInfoRepositoryImpl.lambda$selectPage$0(ProductInfoRepositoryImpl.java:25)
        at com.github.pagehelper.Page.doSelectPageInfo(Page.java:569)
        at com.qianyefamily.infra.repository.impl.ProductInfoRepositoryImpl.selectPage(ProductInfoRepositoryImpl.java:25)
        at java.base@17.0.10/java.lang.reflect.Method.invoke(Method.java:568)
        at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:352)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:765)
        at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:765)
        at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:717)
        at com.qianyefamily.infra.repository.impl.ProductInfoRepositoryImpl$$SpringCGLIB$$0.selectPage(<generated>)
        at com.qianyefamily.app.service.impl.ProductInfoServiceImpl.selectPage(ProductInfoServiceImpl.java:28)
        at com.qianyefamily.api.controller.v1.ProductInfoController.page(ProductInfoController.java:23)
        at java.base@17.0.10/java.lang.reflect.Method.invoke(Method.java:568)
        at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:262)
        at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:190)
        at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:118)
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:917)
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:829)
        at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
        at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1089)
        at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:979)
        at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1014)
        at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:914)
        at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:547)
        at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:885)
        at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:614)
        at io.undertow.servlet.handlers.ServletHandler.handleRequest(ServletHandler.java:74)
        at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:129)
        at com.waitylyou.component.filter.AuthenticationFilter.checkToken(AuthenticationFilter.java:97)
        at com.waitylyou.component.filter.AuthenticationFilter.doFilter(AuthenticationFilter.java:65)
        at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:67)
        at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
        at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
        at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:67)
        at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
        at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
        at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:67)
        at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
        at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
        at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:67)
        at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
        at com.waitylyou.component.filter.ThreadMdcFilter.doFilter(ThreadMdcFilter.java:25)
        at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:67)
        at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
        at io.undertow.servlet.handlers.FilterHandler.handleRequest(FilterHandler.java:84)
        at io.undertow.servlet.handlers.security.ServletSecurityRoleHandler.handleRequest(ServletSecurityRoleHandler.java:62)
        at io.undertow.servlet.handlers.ServletChain$1.handleRequest(ServletChain.java:68)
        at io.undertow.servlet.handlers.ServletDispatchingHandler.handleRequest(ServletDispatchingHandler.java:36)
        at io.undertow.servlet.handlers.RedirectDirHandler.handleRequest(RedirectDirHandler.java:68)
        at io.undertow.servlet.handlers.security.SSLInformationAssociationHandler.handleRequest(SSLInformationAssociationHandler.java:117)
        at io.undertow.servlet.handlers.security.ServletAuthenticationCallHandler.handleRequest(ServletAuthenticationCallHandler.java:57)
        at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
        at io.undertow.security.handlers.AbstractConfidentialityHandler.handleRequest(AbstractConfidentialityHandler.java:46)
        at io.undertow.servlet.handlers.security.ServletConfidentialityConstraintHandler.handleRequest(ServletConfidentialityConstraintHandler.java:64)
        at io.undertow.security.handlers.AuthenticationMechanismsHandler.handleRequest(AuthenticationMechanismsHandler.java:60)
        at io.undertow.servlet.handlers.security.CachedAuthenticatedSessionHandler.handleRequest(CachedAuthenticatedSessionHandler.java:77)
        at io.undertow.security.handlers.AbstractSecurityContextAssociationHandler.handleRequest(AbstractSecurityContextAssociationHandler.java:43)
        at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
        at io.undertow.servlet.handlers.SendErrorPageHandler.handleRequest(SendErrorPageHandler.java:52)
        at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
        at io.undertow.servlet.handlers.ServletInitialHandler.handleFirstRequest(ServletInitialHandler.java:276)
        at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:135)
        at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:132)
        at io.undertow.servlet.core.ServletRequestContextThreadSetupAction$1.call(ServletRequestContextThreadSetupAction.java:48)
        at io.undertow.servlet.core.ContextClassLoaderSetupAction$1.call(ContextClassLoaderSetupAction.java:43)
        at io.undertow.servlet.handlers.ServletInitialHandler.dispatchRequest(ServletInitialHandler.java:256)
        at io.undertow.servlet.handlers.ServletInitialHandler$1.handleRequest(ServletInitialHandler.java:101)
        at io.undertow.server.Connectors.executeRootHandler(Connectors.java:393)
        at io.undertow.server.HttpServerExchange$1.run(HttpServerExchange.java:859)
        at org.jboss.threads.ContextHandler$1.runWith(ContextHandler.java:18)
        at org.jboss.threads.EnhancedQueueExecutor$Task.run(EnhancedQueueExecutor.java:2513)
        at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1512)
        at org.xnio.XnioWorker$WorkerThreadFactory$1$1.run(XnioWorker.java:1282)
        at java.base@17.0.10/java.lang.Thread.run(Thread.java:842)
        at org.graalvm.nativeimage.builder/com.oracle.svm.core.thread.PlatformThreads.threadStartRoutine(PlatformThreads.java:807)
        at org.graalvm.nativeimage.builder/com.oracle.svm.core.posix.thread.PosixPlatformThreads.pthreadStartRoutine(PosixPlatformThreads.java:210)
Caused by: java.sql.SQLException: Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (gb18030_chinese_ci,COERCIBLE) for operation 'like'
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:130)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com 

About this issue

  • Original URL
  • State: closed
  • Created 3 years ago
  • Comments: 20 (3 by maintainers)

Most upvoted comments

I think I found the problem, you can refer to this of mine

#39362 (comment)

Thank you for your help. I think I have solved the problem.

@admintertar Please let us know if this solves your problem.

Yes, solved my problem.

The solution is as follows:

  1. Add parameter -H:+AddAllCharsets
  2. Ensure that the compilation environment supports multiple encodings
  3. (Optional) Delete jdbc link encoding (useUnicode=true&characterEncoding=UTF-8)

Thank you for your help.

Could you try to pass -H:+AddAllCharsets to the native image build?