Online demo
Download
Contact

Because information matters

  • SQL Error while importing IMAP emails. Illegal mix of collations

  • OpenKM has many interesting features, but requires some configuration process to show its full potential.
It is currently Sat Jun 14, 2025 1:30 pm

SQL Error while importing IMAP emails. Illegal mix of collations

OpenKM has many interesting features, but requires some configuration process to show its full potential.
Forum rules: Please, before asking something see the documentation wiki or use the search feature of the forum. And remember we don't have a crystal ball or mental readers, so if you post about an issue tell us which OpenKM are you using and also the browser and operating system version. For more info read How to Report Bugs Effectively.
 Reply

SQL Error while importing IMAP emails. Illegal mix of collations

 #54781  by rosario
 
Hi all,

I'm running OpenKM Community Edition Version: 6.3.11 (build: d7abb25) with Tomkat 8.5.34 on top of Ubuntu 20.04.06 LTS.

While importing emails via IMAP, the process now stops with the following error:
Code: Select all
2024-06-29 12:37:39,531 [ajp-nio-127.0.0.1-8009-exec-10] INFO  com.openkm.util.MailUtils - ======= ======= 0 ======= =======
2024-06-29 12:37:39,532 [ajp-nio-127.0.0.1-8009-exec-10] INFO  com.openkm.util.MailUtils - Folder: INBOX
2024-06-29 12:37:39,689 [ajp-nio-127.0.0.1-8009-exec-10] INFO  com.openkm.util.MailUtils - Subject: 👍 Unsere Auswahl beliebter Artikel: Tiffany Deckenlampe und Herren-Taschen
2024-06-29 12:37:39,690 [ajp-nio-127.0.0.1-8009-exec-10] INFO  com.openkm.util.MailUtils - From: eBay <ebay@reply.ebay.de>
2024-06-29 12:37:39,690 [ajp-nio-127.0.0.1-8009-exec-10] INFO  com.openkm.util.MailUtils - Received: Wed Jul 15 10:58:48 CEST 2020
2024-06-29 12:37:39,690 [ajp-nio-127.0.0.1-8009-exec-10] INFO  com.openkm.util.MailUtils - Sent: Wed Jul 15 10:58:48 CEST 2020
2024-06-29 12:37:40,136 [ajp-nio-127.0.0.1-8009-exec-10] WARN  o.h.util.JDBCExceptionReporter - SQL Error: 1267, SQLState: HY000
2024-06-29 12:37:40,137 [ajp-nio-127.0.0.1-8009-exec-10] ERROR o.h.util.JDBCExceptionReporter - (conn=42) Illegal mix of collations (utf8_bin,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '='
2024-06-29 12:37:40,146 [ajp-nio-127.0.0.1-8009-exec-10] WARN  com.openkm.util.MailUtils - could not execute query
com.openkm.core.DatabaseException: could not execute query
        at com.openkm.dao.NodeBaseDAO.calculateUuidFromPath(NodeBaseDAO.java:216) ~[classes/:6.3.11]
        at com.openkm.dao.NodeBaseDAO.getUuidFromPath(NodeBaseDAO.java:102) ~[classes/:6.3.11]
        at com.openkm.dao.NodeBaseDAO.itemPathExists(NodeBaseDAO.java:117) ~[classes/:6.3.11]
        at com.openkm.module.db.DbRepositoryModule.hasNode(DbRepositoryModule.java:626) ~[classes/:6.3.11]
        at com.openkm.api.OKMRepository.hasNode(OKMRepository.java:165) ~[classes/:6.3.11]
        at com.openkm.util.MailUtils.importMail(MailUtils.java:856) [classes/:6.3.11]
        at com.openkm.util.MailUtils.importMessages(MailUtils.java:608) [classes/:6.3.11]
        at com.openkm.util.MailUtils.importMessages(MailUtils.java:527) [classes/:6.3.11]
        at com.openkm.core.UserMailImporter.runAs(UserMailImporter.java:80) [classes/:6.3.11]
        at com.openkm.servlet.admin.MailAccountServlet.checkAll(MailAccountServlet.java:294) [classes/:6.3.11]
        at com.openkm.servlet.admin.MailAccountServlet.doPost(MailAccountServlet.java:138) [classes/:6.3.11]
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:661) [servlet-api.jar:na]
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:742) [servlet-api.jar:na]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) [catalina.jar:8.5.34]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [catalina.jar:8.5.34]
        at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) [tomcat-websocket.jar:8.5.34]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [catalina.jar:8.5.34]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [catalina.jar:8.5.34]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:118) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:84) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:113) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:154) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:199) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:50) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:106) [spring-web-3.2.18.RELEASE.jar:3.2.18.RELEASE]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:192) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:160) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:343) [spring-web-3.2.18.RELEASE.jar:3.2.18.RELEASE]
        at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:260) [spring-web-3.2.18.RELEASE.jar:3.2.18.RELEASE]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [catalina.jar:8.5.34]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [catalina.jar:8.5.34]
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198) [catalina.jar:8.5.34]
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) [catalina.jar:8.5.34]
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:493) [catalina.jar:8.5.34]
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140) [catalina.jar:8.5.34]
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81) [catalina.jar:8.5.34]
        at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:650) [catalina.jar:8.5.34]
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87) [catalina.jar:8.5.34]
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342) [catalina.jar:8.5.34]
        at org.apache.coyote.ajp.AjpProcessor.service(AjpProcessor.java:479) [tomcat-coyote.jar:8.5.34]
        at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) [tomcat-coyote.jar:8.5.34]
        at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:806) [tomcat-coyote.jar:8.5.34]
        at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1498) [tomcat-coyote.jar:8.5.34]
        at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) [tomcat-coyote.jar:8.5.34]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [na:1.8.0_392]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [na:1.8.0_392]
        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-util.jar:8.5.34]
        at java.lang.Thread.run(Thread.java:750) [na:1.8.0_392]
Caused by: org.hibernate.exception.GenericJDBCException: could not execute query
        at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:140) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:128) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.loader.Loader.doList(Loader.java:2545) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.loader.Loader.listUsingQueryCache(Loader.java:2319) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.loader.Loader.list(Loader.java:2268) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:459) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:365) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1268) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:890) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at com.openkm.dao.NodeBaseDAO.calculateUuidFromPath(NodeBaseDAO.java:239) ~[classes/:6.3.11]
        at com.openkm.dao.NodeBaseDAO.calculateUuidFromPath(NodeBaseDAO.java:212) ~[classes/:6.3.11]
        ... 61 common frames omitted
Caused by: java.sql.SQLException: (conn=42) Illegal mix of collations (utf8_bin,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '='
        at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:198) ~[mariadb-java-client-2.2.5.jar:na]
        at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:110) ~[mariadb-java-client-2.2.5.jar:na]
        at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:228) ~[mariadb-java-client-2.2.5.jar:na]
        at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeInternal(MariaDbPreparedStatementClient.java:216) ~[mariadb-java-client-2.2.5.jar:na]
        at org.mariadb.jdbc.MariaDbPreparedStatementClient.execute(MariaDbPreparedStatementClient.java:150) ~[mariadb-java-client-2.2.5.jar:na]
        at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeQuery(MariaDbPreparedStatementClient.java:164) ~[mariadb-java-client-2.2.5.jar:na]
        at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:107) ~[tomcat-dbcp.jar:8.5.34]
        at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:107) ~[tomcat-dbcp.jar:8.5.34]
        at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.loader.Loader.getResultSet(Loader.java:1953) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.loader.Loader.doQuery(Loader.java:802) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.loader.Loader.doList(Loader.java:2542) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        ... 71 common frames omitted
Caused by: java.sql.SQLException: Illegal mix of collations (utf8_bin,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '='
Query is: /* select nb.uuid from NodeBase nb where nb.parent=:parent and nb.name=:name */ select nodebase0_.NBS_UUID as col_0_0_ from OKM_NODE_BASE nodebase0_ where nodebase0_.NBS_PARENT=? and nodebase0_.NBS_NAME=? limit ?, parameters ['ba505d8d-3b31-49c8-97e5-4e1bc95544b4','46295-👍 Unsere Auswahl beliebter Artikel: Tiffany Deckenlampe und Herren-Taschen',1]
        at org.mariadb.jdbc.internal.util.LogQueryTool.exceptionWithQuery(LogQueryTool.java:153) ~[mariadb-java-client-2.2.5.jar:na]
        at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:255) ~[mariadb-java-client-2.2.5.jar:na]
        at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeInternal(MariaDbPreparedStatementClient.java:209) ~[mariadb-java-client-2.2.5.jar:na]
        ... 80 common frames omitted
2024-06-29 12:37:40,216 [ajp-nio-127.0.0.1-8009-exec-10] WARN  o.h.util.JDBCExceptionReporter - SQL Error: 1366, SQLState: 22007
2024-06-29 12:37:40,216 [ajp-nio-127.0.0.1-8009-exec-10] ERROR o.h.util.JDBCExceptionReporter - (conn=42) Incorrect string value: '\xF0\x9F\x91\x8D U...' for column `okmdb`.`OKM_MAIL_IMPORT_ERROR`.`MIE_ERROR_MESSAGE` at row 1
2024-06-29 12:37:40,224 [ajp-nio-127.0.0.1-8009-exec-10] ERROR com.openkm.core.UserMailImporter - could not insert: [com.openkm.dao.bean.MailImportError]
com.openkm.core.DatabaseException: could not insert: [com.openkm.dao.bean.MailImportError]
        at com.openkm.dao.MailAccountDAO.update(MailAccountDAO.java:87) ~[classes/:6.3.11]
        at com.openkm.util.MailUtils.importMessages(MailUtils.java:674) ~[classes/:6.3.11]
        at com.openkm.util.MailUtils.importMessages(MailUtils.java:527) ~[classes/:6.3.11]
        at com.openkm.core.UserMailImporter.runAs(UserMailImporter.java:80) ~[classes/:6.3.11]
        at com.openkm.servlet.admin.MailAccountServlet.checkAll(MailAccountServlet.java:294) [classes/:6.3.11]
        at com.openkm.servlet.admin.MailAccountServlet.doPost(MailAccountServlet.java:138) [classes/:6.3.11]
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:661) [servlet-api.jar:na]
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:742) [servlet-api.jar:na]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) [catalina.jar:8.5.34]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [catalina.jar:8.5.34]
        at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) [tomcat-websocket.jar:8.5.34]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [catalina.jar:8.5.34]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [catalina.jar:8.5.34]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:118) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:84) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:113) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:154) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:199) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:50) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:106) [spring-web-3.2.18.RELEASE.jar:3.2.18.RELEASE]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:192) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:160) [spring-security-web-3.2.10.RELEASE.jar:na]
        at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:343) [spring-web-3.2.18.RELEASE.jar:3.2.18.RELEASE]
        at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:260) [spring-web-3.2.18.RELEASE.jar:3.2.18.RELEASE]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [catalina.jar:8.5.34]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [catalina.jar:8.5.34]
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198) [catalina.jar:8.5.34]
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) [catalina.jar:8.5.34]
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:493) [catalina.jar:8.5.34]
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140) [catalina.jar:8.5.34]
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81) [catalina.jar:8.5.34]
        at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:650) [catalina.jar:8.5.34]
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87) [catalina.jar:8.5.34]
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342) [catalina.jar:8.5.34]
        at org.apache.coyote.ajp.AjpProcessor.service(AjpProcessor.java:479) [tomcat-coyote.jar:8.5.34]
        at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) [tomcat-coyote.jar:8.5.34]
        at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:806) [tomcat-coyote.jar:8.5.34]
        at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1498) [tomcat-coyote.jar:8.5.34]
        at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) [tomcat-coyote.jar:8.5.34]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [na:1.8.0_392]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [na:1.8.0_392]
        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-util.jar:8.5.34]
        at java.lang.Thread.run(Thread.java:750) [na:1.8.0_392]
Caused by: org.hibernate.exception.DataException: could not insert: [com.openkm.dao.bean.MailImportError]
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:102) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:63) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2346) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2853) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:71) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:273) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:320) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:203) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:129) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:210) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:195) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.performSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:117) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:93) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.impl.SessionImpl.fireSaveOrUpdate(SessionImpl.java:685) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:677) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.engine.CascadingAction$5.cascade(CascadingAction.java:252) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.engine.Cascade.cascadeToOne(Cascade.java:392) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.engine.Cascade.cascadeAssociation(Cascade.java:335) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.engine.Cascade.cascadeProperty(Cascade.java:204) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.engine.Cascade.cascadeCollectionElements(Cascade.java:425) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.engine.Cascade.cascadeCollection(Cascade.java:362) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.engine.Cascade.cascadeAssociation(Cascade.java:338) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.engine.Cascade.cascadeProperty(Cascade.java:204) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.engine.Cascade.cascade(Cascade.java:161) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.engine.Cascade.cascade(Cascade.java:127) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.cascadeOnUpdate(DefaultSaveOrUpdateEventListener.java:376) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.performUpdate(DefaultSaveOrUpdateEventListener.java:350) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsDetached(DefaultSaveOrUpdateEventListener.java:246) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.event.def.DefaultUpdateEventListener.performSaveOrUpdate(DefaultUpdateEventListener.java:57) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:93) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.impl.SessionImpl.fireUpdate(SessionImpl.java:742) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.impl.SessionImpl.update(SessionImpl.java:730) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.impl.SessionImpl.update(SessionImpl.java:722) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at com.openkm.dao.MailAccountDAO.update(MailAccountDAO.java:83) ~[classes/:6.3.11]
        ... 56 common frames omitted
Caused by: java.sql.SQLDataException: (conn=42) Incorrect string value: '\xF0\x9F\x91\x8D U...' for column `okmdb`.`OKM_MAIL_IMPORT_ERROR`.`MIE_ERROR_MESSAGE` at row 1
        at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:167) ~[mariadb-java-client-2.2.5.jar:na]
        at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:110) ~[mariadb-java-client-2.2.5.jar:na]
        at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:228) ~[mariadb-java-client-2.2.5.jar:na]
        at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeInternal(MariaDbPreparedStatementClient.java:216) ~[mariadb-java-client-2.2.5.jar:na]
        at org.mariadb.jdbc.MariaDbPreparedStatementClient.execute(MariaDbPreparedStatementClient.java:150) ~[mariadb-java-client-2.2.5.jar:na]
        at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeUpdate(MariaDbPreparedStatementClient.java:183) ~[mariadb-java-client-2.2.5.jar:na]
        at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:121) ~[tomcat-dbcp.jar:8.5.34]
        at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:121) ~[tomcat-dbcp.jar:8.5.34]
        at org.hibernate.id.IdentityGenerator$GetGeneratedKeysDelegate.executeAndExtract(IdentityGenerator.java:93) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:56) ~[hibernate-core-3.6.10.Final.jar:3.6.10.Final]
        ... 88 common frames omitted
Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x91\x8D U...' for column `okmdb`.`OKM_MAIL_IMPORT_ERROR`.`MIE_ERROR_MESSAGE` at row 1
Query is: /* insert com.openkm.dao.bean.MailImportError */ insert into OKM_MAIL_IMPORT_ERROR (MIE_ERROR_MESSAGE, MIE_IMPORT_DATE, MIE_MAIL_SUBJECT, MIE_MAIL_UID, MIE_MAIL_ACCOUNT) values (?, ?, ?, ?, ?), parameters [<Reader>,'2024-06-29 12:37:40.172',' Unsere Auswahl beliebter Artikel: Tiffany Deckenlampe und Herren-Taschen','46295',1]
        at org.mariadb.jdbc.internal.util.LogQueryTool.exceptionWithQuery(LogQueryTool.java:153) ~[mariadb-java-client-2.2.5.jar:na]
        at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:255) ~[mariadb-java-client-2.2.5.jar:na]
        at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeInternal(MariaDbPreparedStatementClient.java:209) ~[mariadb-java-client-2.2.5.jar:na]
        ... 94 common frames omitted
Now, while it seems a common SQL issues with its own workaround, I am not familiar with SQL and I would not want to mess it up.

Any advice from the community?

Thank you
Rosario

Re: SQL Error while importing IMAP emails. Illegal mix of collations

 #54782  by rosario
 
So far, the workaround has been to delete the incriminated emails, but clearly this is a sw bug.
Code: Select all
2024-06-30 14:48:55,916 [ajp-nio-127.0.0.1-8009-exec-7] WARN  o.h.util.JDBCExceptionReporter - SQL Error: 1267, SQLState: HY000
2024-06-30 14:48:55,916 [ajp-nio-127.0.0.1-8009-exec-7] ERROR o.h.util.JDBCExceptionReporter - (conn=60) Illegal mix of collations (utf8_bin,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '='
2024-06-30 14:48:55,925 [ajp-nio-127.0.0.1-8009-exec-7] WARN  com.openkm.util.MailUtils - could not execute query
Is there a place were to submit bugs?

Thanks
Rosario

Re: SQL Error while importing IMAP emails. Illegal mix of collations

 #54787  by rosario
 
Some progress, but far from solving:

Being on Ubuntu 20.04, I decided to apply the mariadb configuration recommendation as on the official documentation, although it was only recommended for ubuntu 16.04 (perhaps an update on the documentation is needed?)

now the error I get while importing is
Code: Select all
2024-07-04 11:28:02,494 [ajp-nio-127.0.0.1-8009-exec-9] INFO  com.openkm.core.UserMailImporter - *** User mail importer activated ***
2024-07-04 11:28:04,143 [ajp-nio-127.0.0.1-8009-exec-9] INFO  com.openkm.util.MailUtils - ======= ======= 0 ======= =======
2024-07-04 11:28:04,144 [ajp-nio-127.0.0.1-8009-exec-9] INFO  com.openkm.util.MailUtils - Folder: INBOX
2024-07-04 11:28:04,298 [ajp-nio-127.0.0.1-8009-exec-9] INFO  com.openkm.util.MailUtils - Subject: 🛄 Thanks! Your booking is confirmed at Fährhaus
2024-07-04 11:28:04,299 [ajp-nio-127.0.0.1-8009-exec-9] INFO  com.openkm.util.MailUtils - From: Fahrhaus <customer.service@booking.com>
2024-07-04 11:28:04,299 [ajp-nio-127.0.0.1-8009-exec-9] INFO  com.openkm.util.MailUtils - Received: Mon Jul 27 16:06:32 CEST 2020
2024-07-04 11:28:04,299 [ajp-nio-127.0.0.1-8009-exec-9] INFO  com.openkm.util.MailUtils - Sent: Mon Jul 27 16:06:31 CEST 2020
2024-07-04 11:28:04,726 [ajp-nio-127.0.0.1-8009-exec-9] WARN  o.h.util.JDBCExceptionReporter - SQL Error: 1366, SQLState: 22007
2024-07-04 11:28:04,727 [ajp-nio-127.0.0.1-8009-exec-9] ERROR o.h.util.JDBCExceptionReporter - (conn=43) Incorrect string value: '\xF0\x9F\x9B\x84 T...' for column `okmdb`.`OKM_NODE_BASE`.`NBS_NAME` at row 1
2024-07-04 11:28:04,737 [ajp-nio-127.0.0.1-8009-exec-9] WARN  com.openkm.util.MailUtils - Could not execute JDBC batch update
com.openkm.core.DatabaseException: Could not execute JDBC batch update
Anyone any idea?

EDIT
It didn't work out as Mail subjects include character that require 4 bytes to be stored while the corresponding column allows only 3 bytes. Moved back to the original default configuration of the database.

Seems to me like MailUtils written to import emails doesn't work with utf8mb character sets.
Last edited by rosario on Thu Jul 04, 2024 9:42 am, edited 1 time in total.

Re: SQL Error while importing IMAP emails. Illegal mix of collations

 #54789  by rosario
 
To overcome the issue, seems like it is needed for the corresponding table in the database to support utf8mb4 character set.

Is there a way to replace the default settings and overcome below issue on an existing database?
Code: Select all
MariaDB [okmdb]> alter table OKM_NODE_BASE convert to character set utf8mb4 collate utf8mb4_unicode_ci;
ERROR 1833 (HY000): Cannot change column 'NBS_UUID': used in a foreign key constraint 'FK159C2BDFAE6D6084' of table 'okmdb.OKM_NODE_SUBSCRIPTOR'
MariaDB [okmdb]> alter table OKM_NODE_SUBSCRIPTOR convert to character set utf8mb4 collate utf8mb4_unicode_ci;
ERROR 1832 (HY000): Cannot change column 'NSB_NODE': used in a foreign key constraint 'FK159C2BDFAE6D6084'
Any help will be greatly appreciated.
Thanks
 Reply
 Return to “Configuration”

Favorites

About Us

OpenKM is part of the management software. A management software is a program that facilitates the accomplishment of administrative tasks. OpenKM is a document management system that allows you to manage business content and workflow in a more efficient way. Document managers guarantee data protection by establishing information security for business content.

Powered By phpBB -
 ©OpenKM 2021
 - All times are UTC -