wood burning stoves 2.0*
The moose likes JForum and the fly likes Note for MySQL 4.1 Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Products » JForum
Bookmark "Note for MySQL 4.1" Watch "Note for MySQL 4.1" New topic
Author

Note for MySQL 4.1

Migrated From Jforum.net
Ranch Hand

Joined: Apr 22, 2012
Posts: 17424
Hi, just installed 2.0.1 with mysql 4.1. When i tried to register I got a the error

Illegal mix of collations


(utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE)

This looks to be due to changes in mysql 4.1 from 4.0 (see http://mysql.binarycompass.org/doc/mysql/en/Charset-collation-charset.html)

I removed the &useUnicode=true&characterEncoding=UTF-8 from the database url connection (WEB-INF/config/database/mysql/mysql.properties) and it worked ok. Hope this helps someone!

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

Joined: Apr 22, 2012
Posts: 17424
Ok, this is not good. I first thought that upgrading the mysql library to the last development version available ( 3.1.4 ) it will work, but it didn't.
I'll put a section related to this issue in the FAQ until we find a good solution for the problem.

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

Joined: Apr 22, 2012
Posts: 17424
Here are some more detailed steps about how to change the MySQL configuration file:

:arrow: Open the file WEB-INF/config/database/mysql/mysql.properties with any text editor
:arrow: There you will find a key named "database.connection.string" with a big string as a value. Change the value to



:arrow: Save the file and, if you had configured a connection pool, restart the container.

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

Joined: Apr 22, 2012
Posts: 17424
Alternatively, when you create the database, specify utf8 as the default character set, ie:

create database jforum default character set utf8

Regards
Keith
[originally posted on jforum.net by Anonymous]
Migrated From Jforum.net
Ranch Hand

Joined: Apr 22, 2012
Posts: 17424
An error has ocurred. For detailed stack trace, please see the page's source code.
Illegal mix of collations (latin5_turkish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

what should I do to prevent this error ? I need this encoding because of turkish characters..

Thanks

e-mail:ugursamuk@yahoo.com



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

Joined: Apr 22, 2012
Posts: 17424
Anonymous wrote:An error has ocurred. For detailed stack trace, please see the page's source code.
Illegal mix of collations (latin5_turkish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

what should I do to prevent this error ? I need this encoding because of turkish characters..

Thanks

e-mail:ugursamuk@yahoo.com



You can not use both. utf-8 includes all turkish characters. You no not need latin5_turkish when you use utf-8. Modern web browsers work with utf8 without a problem. But some (old ones) web browsers either do not understand utf8 or have bugs in utf8 implementation. In this case you will see some garbage posted.

Basically utf8 is a modern way to go. The biggest advantage of utf8 compared to all latin5_turkish and etc. is that you can SIMULTANEOUSLY use turkish, greek, chinese, hebrew and any other characters from utf8.

But if your users use outdated web browsers and you need only turkish - then latin5_turkish may be the way to go.
[originally posted on jforum.net by Anonymous]
Migrated From Jforum.net
Ranch Hand

Joined: Apr 22, 2012
Posts: 17424
I have tried EVERY suggestion on this list, including even reloading the DB with the UTF8 as the default characterset. I still see the collation error!

I have upgraded to 5.0 on mysql, is this the problem?

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

Joined: Apr 22, 2012
Posts: 17424
It ended up being the 'jforum-custom.conf' file. ops:
[originally posted on jforum.net by time]
Migrated From Jforum.net
Ranch Hand

Joined: Apr 22, 2012
Posts: 17424
are you saying jforum 2.0.2 cannot work with mysql 4.1?
[originally posted on jforum.net by gkatz]
Migrated From Jforum.net
Ranch Hand

Joined: Apr 22, 2012
Posts: 17424
there is a property to specify the encoding character set of the DB and another property to set the collation character set (for example: mysqld --default-character-set=latin1 --default-collation=latin1_swedish_ci

i am just now in the process of migrating to 4.1 from 4.0 and there is a lot of difference regarding the DB itself due to the new character support.

for example, of you use UTF-8 characters in 4.0 (which does not nativly support UTF-8) you need to go coulumn by column and apply a UTF-8 character set to that column (in order to convert the data inside it).

setting the DB character set to UTF8 will not help in that case.

rafael, i did not go though the jforum instructions for some time now (i use 2.0.2), is there a specific part for dealing with mysql 4.1.x?

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

Joined: Apr 22, 2012
Posts: 17424
rafael;
in the installation instructions it states:
" Also for MySQL 4.1+ users, go to WEB-INF/config/database/mysql and rename the file "mysql_41.sql" to "mysql.sql" (delete the existing mysql.sql file, which does not work for MySQL 4.1+) "

can you please explain this? what should i do with 2.0.2 and 4.1? should i take the mysql_41.sql from 2.1 and put in 2.0.2? will it work?
thanks in advance. :?:
[originally posted on jforum.net by gkatz]
Migrated From Jforum.net
Ranch Hand

Joined: Apr 22, 2012
Posts: 17424
JForum 2.0.2? I have no idea how files from 2.1 will work with it.

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

Joined: Apr 22, 2012
Posts: 17424
I guess most features will work, but you will have problems when setting permissions for groups.

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

Joined: Apr 22, 2012
Posts: 17424
thanks;
well, i do not intend to use this feature. i use jforum for the bare minimum of a plain forum (i am the only admin and i stipped most capabilities that are not a part of the "forum basics").

so my question remains, in the 2.1.x installation there is a special file for mysql 4.1.x users that contains some SQL statements. the installation instructions state that there is a need to replace the regular mysql file with the 4.1 one.

if i want to use 2.0.2 and 4.1 should i also take that specific file and use it or not?

thanks for the help.
[originally posted on jforum.net by gkatz]
Migrated From Jforum.net
Ranch Hand

Joined: Apr 22, 2012
Posts: 17424
You can give it a try!

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

Joined: Apr 22, 2012
Posts: 17424
I tried every step suggested in this list but still i am getting the same error while log in. it is displaying
"An error has ocurred. For detailed stack trace, please see the page's source code.

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

plz suggest what to do next.?
should i install mysql3.0 ?

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

Joined: Apr 22, 2012
Posts: 17424
Well, if you have the choice, put mysql 4.0.x then

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

Joined: Apr 22, 2012
Posts: 17424
Dear Rafael,
Need your help badly..

Here is the environmnet I am using.
Suse Linux 10.1
Tomcat 6.0.13
MySql 5.0
Please note I have tried two versions of mysql connector as well
one that comes with JForum mysql-connector-java-5.0.3-bin.jar
and another one mysql-connector-java-5.0.5-bin

JForum 2.1.8

Bwlow is the Problem I am facing when I try to install Jforum it installs sucessfully buy when I try to access the forum I see the error below

An error has occurred.
For detailed error information, please see the HTML source code, and contact the forum Administrator.
java.sql.SQLException: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_unicode_ci,IMPLICIT) for operation 'UNION'

When I see the source code below is the exception stack trace:

<!-- BEGIN ERROR STACK TRACE <br /> net.jforum.exceptions.DatabaseException: java.sql.SQLException: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_unicode_ci,IMPLICIT) for operation 'UNION' <br /> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946) <br /> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2941) <br /> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623) <br /> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1715) <br /> at com.mysql.jdbc.Connection.execSQL(Connection.java:3249) <br /> at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1268) <br /> at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1403) <br /> at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76) <br /> at net.jforum.dao.generic.security.GenericGroupSecurityDAO.loadRoles(GenericGroupSecurityDAO.java:209) <br /> at net.jforum.dao.generic.security.GenericGroupSecurityDAO.loadRolesByUserGroups(GenericGroupSecurityDAO.java:287) <br /> at net.jforum.repository.SecurityRepository.load(SecurityRepository.java:151) <br /> at net.jforum.repository.SecurityRepository.load(SecurityRepository.java:94) <br /> at net.jforum.repository.SecurityRepository.load(SecurityRepository.java:112) <br /> at net.jforum.JForum.service(JForum.java:163) <br /> at javax.servlet.http.HttpServlet.service(HttpServlet.java:803) <br /> at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290) <br /> at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) <br /> at net.jforum.util.legacy.clickstream.ClickstreamFilter.doFilter(ClickstreamFilter.java:59) <br /> at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) <br /> at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) <br /> at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:230) <br /> at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175) <br /> at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128) <br /> at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104) <br /> at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) <br /> at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:261) <br /> at org.apache.coyote.http11.Http11AprProcessor.process(Http11AprProcessor.java:852) <br /> at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:584) <br /> at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:1508) <br /> at java.lang.Thread.run(Thread.java:595) <br /> Caused by: java.sql.SQLException: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_unicode_ci,IMPLICIT) for operation 'UNION' <br /> ... 30 more <br /> <br /> URL is: /fm/forums/list.page?null <br /> END ERROR STACK TRACE-->

<html>
<head>
<title>Error</title>
</head>
<body>

An error has occurred.

For detailed error information, please see the HTML source code, and contact the forum Administrator.

<pre class="pre-error">java.sql.SQLException: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_unicode_ci,IMPLICIT) for operation 'UNION'</pre>

</body>
</html>






I have read this thread but I am still struggling after repeated attempts.

mysql.propertied looks like below:

#Sat Mar 08 20:00:12 EST 2008
database.support.autokeys=true
database.driver.name=mysql
database.connection.pool.timeout=5000
database.connection.dbname=xxxxx
database.support.subqueries=true
dao.driver=net.jforum.dao.mysql.MysqlDataAccessDriver
mysql.unicode=
database.connection.pool.min=5
database.connection.password=xxxxx
database.connection.pool.max=10
database.connection.username=xxxxx
dbencoding=utf-8
database.connection.port=3306
dbunicode=true
database.connection.host=localhost
database.connection.driver=com.mysql.jdbc.Driver
database.connection.string=jdbc:mysql://${database.connection.host}:${database.connection.port}/${database.connection.dbname}?user=${database.connection.username}&password=${database.connection.password}&autoReconnect=true
mysql.encoding=

and jforum-custom.conf looks like below

#Installation specific configuration options
#Sat Mar 08 20:00:06 EST 2008
dao.driver=net.jforum.dao.mysql.MysqlDataAccessDriver
database.connection.dbname=xxxxx
database.connection.driver=com.mysql.jdbc.Driver
database.connection.host=localhost
database.connection.implementation=net.jforum.PooledConnection
database.connection.password=xxxxx
database.connection.pool.max=10
database.connection.pool.min=5
database.connection.pool.timeout=5000
database.connection.port=3306
database.connection.string=jdbc:mysql://${database.connection.host}:${database.connection.port}/${database.connection.dbname}?user=${database.connection.username}&password=${database.connection.password}&autoReconnect=true
database.connection.username=xxxxx
database.driver.name=mysql
database.support.autokeys=true
database.support.subqueries=true
dbencoding=utf-8
dbunicode=true
forum.link=http\://localhost\:8080/fm/
homepage.link=http\://localhost\:8080
i18n.board.default=en_US
installed=true
mysql.encoding=
mysql.unicode=
search.indexing.enabled=true
user.hash.sequence=4b7f9504ac8d40cdc7dfd8b74f901e0d

I have created a database in mysql called "xxxxx" with Character set as "utf8 -- UTF-8 Unicode" and collation "utf8_unicode_ci" please note I have tried with leaving collation blank at the time of database creation and in which case the default collation is "utf8_general_ci". The user I am using has all the rights on this database except grant permissions.

I need your help badly I have tried to install it now like 25 times at least. and have almost read every possible thread on the topic.


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

Joined: Apr 22, 2012
Posts: 17424
Do you know anything about the Oracle db?
I have already define the utf-8 on the admin panel of jforum but i can't see the greek characters (�����?���).

Do you know anything about it?
[originally posted on jforum.net by stavroch]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Note for MySQL 4.1
 
Similar Threads
Are Column names case sensitive in SQL queries?
Error saving group permissions
any chance to know the charset of a remote database?
JForum 2.1.6, Tomcat 5.5.4, JDK 1.5.0_04 installation fails!!
forum miss topics