[comixed-dev] MySQL issues: Illegal mix of collations

  • From: "Darryl L. Pierce" <mcpierce@xxxxxxxxx>
  • To: comixed-dev@xxxxxxxxxxxxx
  • Date: Mon, 15 Nov 2021 08:54:01 -0500

Doing some integration testing, I've hit another new issue.
Specifcally, I'm using MySQL 5 as my database and running latest
master against it to stress test loading comics. But when it starts to
gather the list of comics to import, while checking for the comic:

"/Volumes/Development Storage/ComiXed workspace/test_library/Dark
Horse Comics/Serenity - Firefly Class 03-K64⏤No Power In The 'Verse
(2016)/Serenity - Firefly Class 03-K64⏤No Power In The 'Verse Vol.2016
#01 (October, 2016).cbz

the backend is throwing the following exception (it didn't occur with H2):

java.sql.SQLException: Illegal mix of collations
(latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for
operation '='
    at 
com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
~[mysql-connector-java-8.0.27.jar:8.0.27]
    at 
com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
~[mysql-connector-java-8.0.27.jar:8.0.27]
    at 
com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
~[mysql-connector-java-8.0.27.jar:8.0.27]
    at 
com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1009)
~[mysql-connector-java-8.0.27.jar:8.0.27]
    at 
com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
~[HikariCP-4.0.3.jar:na]
    at 
com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
~[HikariCP-4.0.3.jar:na]
    at 
org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57)
~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2297)
~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2050)
~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2012)
~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
    at org.hibernate.loader.Loader.doQuery(Loader.java:948)
~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
    at 
org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:349)
~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2843)
~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2825)
~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2657)
~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
    at org.hibernate.loader.Loader.list(Loader.java:2652)
~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:506)
~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
    at 
org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:400)
~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
    at 
org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:219)
~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1414)
~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
    at 
org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1636)
~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
    at 
org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1604)
~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
    at 
org.hibernate.query.internal.AbstractProducedQuery.getSingleResult(AbstractProducedQuery.java:1652)
~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
    at 
org.springframework.data.jpa.repository.query.JpaQueryExecution$SingleEntityExecution.doExecute(JpaQueryExecution.java:196)
~[spring-data-jpa-2.5.6.jar:2.5.6]
    at 
org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:88)
~[spring-data-jpa-2.5.6.jar:2.5.6]
    at 
org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:155)
~[spring-data-jpa-2.5.6.jar:2.5.6]
    at 
org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:143)
~[spring-data-jpa-2.5.6.jar:2.5.6]
    at 
org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:137)
~[spring-data-commons-2.5.6.jar:2.5.6]
    at 
org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:121)
~[spring-data-commons-2.5.6.jar:2.5.6]
    at 
org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:159)
~[spring-data-commons-2.5.6.jar:2.5.6]
    at 
org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:138)
~[spring-data-commons-2.5.6.jar:2.5.6]
    at 
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
~[spring-aop-5.3.12.jar:5.3.12]
    at 
org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:80)
~[spring-data-commons-2.5.6.jar:2.5.6]
    at 
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
~[spring-aop-5.3.12.jar:5.3.12]
    at 
org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
~[spring-tx-5.3.12.jar:5.3.12]
    at 
org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)
~[spring-tx-5.3.12.jar:5.3.12]
    at 
org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
~[spring-tx-5.3.12.jar:5.3.12]
    at 
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
~[spring-aop-5.3.12.jar:5.3.12]
    at 
org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
~[spring-tx-5.3.12.jar:5.3.12]
    at 
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
~[spring-aop-5.3.12.jar:5.3.12]
    at 
org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:145)
~[spring-data-jpa-2.5.6.jar:2.5.6]
    at 
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
~[spring-aop-5.3.12.jar:5.3.12]
    at 
org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
~[spring-aop-5.3.12.jar:5.3.12]
    at 
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
~[spring-aop-5.3.12.jar:5.3.12]
    at 
org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215)
~[spring-aop-5.3.12.jar:5.3.12]
    at com.sun.proxy.$Proxy165.findByFilename(Unknown Source) ~[na:na]
    at 
org.comixedproject.service.comicbooks.ComicService.findByFilename(ComicService.java:242)
~[classes/:na]

-- 
Darryl L. Pierce <mcpierce@xxxxxxxxx>
"Le centre du monde est partout." - Blaise Pascal
"Let's try and find some point of transcendence and leap together." -
Gord Downie

Other related posts: