aspose file tools*
The moose likes JForum and the fly likes Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » JForum
Bookmark "Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)" Watch "Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)" New topic
Author

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

Migrated From Jforum.net
Ranch Hand

Joined: Apr 22, 2012
Posts: 17424
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

Joined: Apr 22, 2012
Posts: 17424
Take a look in this post:

http://www.coderanch.com/t/574688

Rafael
[originally posted on jforum.net by Rafael Steil]
Migrated From Jforum.net
Ranch Hand

Joined: Apr 22, 2012
Posts: 17424
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

Joined: Apr 22, 2012
Posts: 17424
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

Joined: Apr 22, 2012
Posts: 17424
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

Joined: Apr 22, 2012
Posts: 17424
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

Joined: Apr 22, 2012
Posts: 17424
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

Joined: Apr 22, 2012
Posts: 17424
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

Joined: Apr 22, 2012
Posts: 17424
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

Joined: Apr 22, 2012
Posts: 17424
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

Joined: Apr 22, 2012
Posts: 17424
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

Joined: Apr 22, 2012
Posts: 17424
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

Joined: Apr 22, 2012
Posts: 17424
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

Joined: Apr 22, 2012
Posts: 17424
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

Joined: Apr 22, 2012
Posts: 17424
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

Joined: Apr 22, 2012
Posts: 17424
Didas,
Where did you find these two settings which were different?!

[originally posted on jforum.net by time]
Migrated From Jforum.net
Ranch Hand

Joined: Apr 22, 2012
Posts: 17424
I found it in the jforum-custom.conf file. Thanks.
[originally posted on jforum.net by time]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)
 
Similar Threads
error when trying to login
wierd exception - please help
Error when storing profile
SQL Problem(Mysql5) when I install
A Problem about Chinese