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)
Thank you for your help. I think I have solved the problem.
Yes, solved my problem.
The solution is as follows:
Thank you for your help.
Could you try to pass
-H:+AddAllCharsetsto the native image build?