Win a copy of Svelte and Sapper in Action this week in the JavaScript forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)

 
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Me and my users are getting this error on login:

"Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='"

How can I fix this?

In the page source I see

<!-- <br /> java.lang.reflect.InvocationTargetException <br /> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) <br /> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) <br /> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) <br /> at java.lang.reflect.Method.invoke(Method.java:585) <br /> at net.jforum.Command.process(Command.java:88) <br /> at net.jforum.JForum.service(JForum.java:262) <br /> at javax.servlet.http.HttpServlet.service(HttpServlet.java:802) <br /> at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237) <br /> at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157) <br /> at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214) <br /> at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104) <br /> at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520) <br /> at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198) <br /> at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152) <br /> at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104) <br /> at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520) <br /> at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137) <br /> at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104) <br /> at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117) <br /> at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102) <br /> at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:535) <br /> at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102) <br /> at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520) <br /> at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) <br /> at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104) <br /> at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520) <br /> at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929) <br /> at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160) <br /> at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799) <br /> at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705) <br /> at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577) <br /> at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683) <br /> at java.lang.Thread.run(Thread.java:595) <br /> Caused by: java.sql.SQLException: General error message from server: "Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='" <br /> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1997) <br /> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1167) <br /> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1278) <br /> at com.mysql.jdbc.Connection.execSQL(Connection.java:2247) <br /> at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1586) <br /> at net.jforum.drivers.generic.UserModel.validateLogin(UserModel.java:456) <br /> at net.jforum.view.forum.UserAction.validateLogin(UserAction.java:297) <br /> at net.jforum.view.forum.UserAction.validateLogin(UserAction.java:241) <br /> ... 33 more <br /> [originally posted on jforum.net by TheDruidXpawX]
 
Migrated From Jforum.net
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Take a look in this post:

https://coderanch.com/t/574688

Rafael
[originally posted on jforum.net by Rafael Steil]
 
Migrated From Jforum.net
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry wasn't logged in. I'm sure that I restarted tomcat in-betwixt this too...
[originally posted on jforum.net by TheDruidXpawX]
 
Migrated From Jforum.net
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
is there any way for me to simply set the character encoding for the db/tables to utf8_general_ci?
[originally posted on jforum.net by TheDruidXpawX]
 
Migrated From Jforum.net
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I appear to have edited the wrong/ file ;) I needed to edit WEB-INF/config/tomcat.conf Once I made the changes taking out the unicode and encoding, it fired up just fine.

Will I run into any problems because of this though?

Thanks!

Druid

http://www.disastrousconsequences.com
[originally posted on jforum.net by TheDruidXpawX]
 
Migrated From Jforum.net
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Not a problem at all. JForum creates a file named <username>.conf to store configurations, so you don't need to write on the original files.

Glad your forum is running fine now!

Rafael
[originally posted on jforum.net by Rafael Steil]
 
Migrated From Jforum.net
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have JForum 2.1.4 configured and working properly on a single server (Win2003, Resin & MySQL 4.1). When I try to connect JForum to a MySQL db on another server, I am getting this error on login: 'Caused by: java.sql.SQLException: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'like''.

I have only updated these parameters in both mysql.properties & jforum-custom.conf:
database.connection.host
database.connection.username
database.connection.password
database.connection.port

Am I missing something? It works perfectly on 1 server, but when I connect to another db server, I can't login?

Thanks!!!
[originally posted on jforum.net by Didas]
 
Migrated From Jforum.net
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Edit WEB-INF/config/database/mysql.properties and set change the keys "mysql.encoding" and "mysql.unicode" to empty.. Eg.



Also, take a look in WEB-INF/config/jforum-custom.conf. If these keys are there, change them as well.

Then restart the application server.

Rafael
[originally posted on jforum.net by Rafael Steil]
 
Migrated From Jforum.net
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, they are already empty. The strange thing is... everything works fine with the MySQL db on the same server. It's only when I configure JForum to connect to MySQL on another server that I get problems. I can access the forums... I just can't login? Is there any known issues with connecting to another server? Am I missing something obvious?
[originally posted on jforum.net by Didas]
 
Migrated From Jforum.net
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The local mysql and the remote one are the same version? This collations issue occurs only with mysql 4.1+, but the connection string changes were supposed to fix. As workaround, you can try to set the key "database.connection.string" by "hand":



Please note that if this key is in jforum-custom.conf, you need to change there.

Rafael
[originally posted on jforum.net by Rafael Steil]
 
Migrated From Jforum.net
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, the MySQL versions are identical (4.1) on both servers. It is connecting to the second database, because I can view the forums. When I attempt to login, is when I get the "Illegal mix of collations". The same JForum configuration works perfect with the MySQL on the same server? Any idea where the issue may be?
[originally posted on jforum.net by Didas]
 
Migrated From Jforum.net
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Have you tried the connection string I put in the previous post? the OS are the same in both servers where mysql is running?

Rafael
[originally posted on jforum.net by Rafael Steil]
 
Migrated From Jforum.net
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes both OS are identical Win2003 (equal service packs and RAM).
ALSO: Yes, I did try to hardcode the 'database.connection.string'. It behaves the same as with the strings.

Very Strange - it is connecting to the database, I can view forums and post anonymously. I can NOT login, search or register. Getting the same error. Any ideas?
[originally posted on jforum.net by Didas]
 
Migrated From Jforum.net
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So, these collation problema occur only in certain situations - more preciselly, when you use "=" with Strings. That's why the rest of the system works, since ids are used.

I don't have any more ideas. All these info I put here I found in mysql documentations / lists, or were contributions by other users. If you want some idea, I personally would try to check the default database server charset, look for any configuration differences, try to set encoding in the connection string (characterEncoding).. things like that..

Rafael
[originally posted on jforum.net by Rafael Steil]
 
Migrated From Jforum.net
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Rafael, thanks for your help. I resolved the problem - it was an issue with MySQL. 1 configuration was set to default charset: utf8, the other latin1. All is working fine now.

Thanks - great job!
[originally posted on jforum.net by Didas]
 
Migrated From Jforum.net
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Didas,
Where did you find these two settings which were different?!

[originally posted on jforum.net by time]
 
Migrated From Jforum.net
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I found it in the jforum-custom.conf file. Thanks.
[originally posted on jforum.net by time]
 
The problems of the world fade way as you eat a piece of pie. This tiny ad has never known problems:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    Bookmark Topic Watch Topic
  • New Topic