upgrade from mysql3.23 to mysql4.1 charset problem

[Migrated From Jforum.net]

Hi

I just upgraded my mysql to 4.1
Now I have all local character corrupted.

It seems to me that in previous mysql you wrote UTF8 in the database converting on the fly and in 4.1 you assume you wrote take directly the string to the database.

Since I upgraded I have the UTF8 that show on the pages. (and no changing the charset in the browser is no help)

Do you have some indications on what I could do to solve that?

[originally posted on jforum.net by pala]

[Migrated From Jforum.net]

I found the answers there:

https://coderanch.com/t/575012
[originally posted on jforum.net by pala]

[Migrated From Jforum.net]

Hi the problem is changing now.

In fact I had also the problem of collacte conflict, I solved it by removing encoding informations in the connection String.

I solved the data encoding problem by putting back a mysql with --default-character-set=utf8

I bump now in another problem:

Whenever I do one of the following:

click on a forum to read it.
click on user list.
click on users in admin panel.

I have this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?, ?' at line 1

here come the stack trace:

<!-- BEGIN ERROR STACK TRACE
java.lang.reflect.InvocationTargetException
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:585)
	at net.jforum.Command.process(Command.java:106)
	at net.jforum.JForum.service(JForum.java:298)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:526)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856)
	at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744)
	at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
	at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
	at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
	at java.lang.Thread.run(Thread.java:595)
Caused by: java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?, ?' at line 1
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2816)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1518)
	at com.mysql.jdbc.ServerPreparedStatement.serverPrepare(ServerPreparedStatement.java:1439)
	at com.mysql.jdbc.ServerPreparedStatement.<init>(ServerPreparedStatement.java:150)
	at com.mysql.jdbc.Connection.prepareStatement(Connection.java:1280)
	at com.mysql.jdbc.Connection.prepareStatement(Connection.java:1252)
	at net.jforum.drivers.generic.UserModel.selectAll(UserModel.java:351)
	at net.jforum.view.forum.UserAction.list(UserAction.java:533)
	... 22 more

END ERROR STACK TRACE-->

[originally posted on jforum.net by pala]

[Migrated From Jforum.net]

pala wrote:
I found the answers there:

https://coderanch.com/t/575012

Thank you! I am running (stable) 2.1.4 of JForum and I recently needed to upgrade MySQL to the 4.1.x version. I fixed the first problem with the encoding formats. However, almost immediately after that I hit this problem.

After seeing your post I checked there and found the solution. For anyone else that needs the fix, I'll post it here so that it's more clear. You must replace your mysql jdbc driver in the JForum package, which is the following file:

jforum/WEB-INF/lib/mysql-connector-java-3.1.4-beta-bin.jar

Delete that file and replace it with the older 3.0 branch of the driver from the MySQL site here.

I used version 3.0.17 and this fixed everything for me. Just unzip/untar the download, and copy the file "mysql-connector-java-3.0.17-ga-bin.jar" (or similar version) into your"jforum/WEB-INF/lib" directory. Again, if the other file is still in there ("mysql-connector-java-3.1.4-beta-bin.jar") just delete it. Then restart your application (or container). Everything should then be working properly.
[originally posted on jforum.net by fcgreg]