Major problem when upgrading from Mysql 4 to Mysql 5

[Migrated From Jforum.net]

Hi,

We just upgraded our database servers from mysql 4.0.20 to 5.0 and now Jforum is not working.

I've edited the mysql.properties file and commented out the unicode lines (2 of them).

When I start tomcat in the demontools logs I see the following error when going to the main page.

@40000000458b9cba2f334e2c 08:52:00,791 ERROR [ExceptionWriter     ] net.jforum.exceptions.SecurityLoadException: java.sql.SQLException: Bad format for number '?   0.00' in column 51.
@40000000458b9cba2f33659c       at net.jforum.repository.SecurityRepository.get(SecurityRepository.java:231)
@40000000458b9cba2f337154       at net.jforum.repository.SecurityRepository.canAccess(SecurityRepository.java:203)
@40000000458b9cba2f337924       at net.jforum.repository.SecurityRepository.canAccess(SecurityRepository.java:198)
@40000000458b9cba2f3384dc       at net.jforum.repository.SecurityRepository.canAccess(SecurityRepository.java:173)
@40000000458b9cba2f341d34       at net.jforum.JForum.setupTemplateContext(JForum.java:227)
@40000000458b9cba2f3428ec       at net.jforum.JForum.service(JForum.java:272)
@40000000458b9cba2f3430bc       at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
@40000000458b9cba2f343c74       at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
@40000000458b9cba2f34482c       at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
@40000000458b9cba2f347324       at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
@40000000458b9cba2f347edc       at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
@40000000458b9cba2f34c52c       at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:482)
@40000000458b9cba2f34d0e4       at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
@40000000458b9cba2f34dc9c       at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
@40000000458b9cba2f34e46c       at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:526)
@40000000458b9cba2f350f64       at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
@40000000458b9cba2f351b1c       at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
@40000000458b9cba2f3526d4       at org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:306)
@40000000458b9cba2f352ea4       at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:385)
@40000000458b9cba2f3555b4       at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:745)
@40000000458b9cba2f35616c       at org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:675)
@40000000458b9cba2f356d24       at org.apache.jk.common.SocketConnection.runIt(ChannelSocket.java:868)
@40000000458b9cba2f3578dc       at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
@40000000458b9cba2f359434       at java.lang.Thread.run(Thread.java:534)
@40000000458b9cba2f35bb44 Caused by: java.sql.SQLException: Bad format for number '?   0.00' in column 51.
@40000000458b9cba2f36b15c       at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
@40000000458b9cba2f36bd14       at com.mysql.jdbc.ResultSet.getDoubleInternal(ResultSet.java:2278)
@40000000458b9cba2f36c4e4       at com.mysql.jdbc.ResultSet.getDoubleInternal(ResultSet.java:2217)
@40000000458b9cba2f36d09c       at com.mysql.jdbc.ResultSet.getDouble(ResultSet.java:2178)
@40000000458b9cba2f36d86c       at com.mysql.jdbc.ResultSet.getDouble(ResultSet.java:2196)
@40000000458b9cba2f36ff7c       at net.jforum.drivers.generic.UserModel.fillUserFromResultSet(UserModel.java:173)
@40000000458b9cba2f370b34       at net.jforum.drivers.generic.UserModel.selectById(UserModel.java:97)
@40000000458b9cba2f371304       at net.jforum.repository.SecurityRepository.load(SecurityRepository.java:94)
@40000000458b9cba2f371ebc       at net.jforum.repository.SecurityRepository.load(SecurityRepository.java:112)
@40000000458b9cba2f372a74       at net.jforum.repository.SecurityRepository.get(SecurityRepository.java:228)

I'm confused and unsure how to fix, I have updated the driver in the lib folder to latest connector for mysql (5.0.4).
[originally posted on jforum.net by amir]

[Migrated From Jforum.net]

So what I'd like to know is has anyone gone from a live installation of JForum from using mysql4 to mysql5 and was it without issue?
[originally posted on jforum.net by amir]

[Migrated From Jforum.net]

Have you executed mysql_upgrade?

http://dev.mysql.com/doc/refman/5.0/en/mysql-upgrade.html
[originally posted on jforum.net by lazee]

[Migrated From Jforum.net]

That page wont load for me, i suspect its my inet connection.

Anyway the problem is solved. The issue is that there is binary incompatability with using mysqlhotcopy files. The solution is to use mysqldump to dump [b]sql[/b[ files and then "source" these files into the new empty database in mysql. all works fine :-)

In my case in one table there is a row with text "....appear int he KPI's and no" .. but when using the hotdumps the corrupted text looks like this



Use SQL files and its all good

Thanks for the swift response anyhow, nice to see a helpful and active community
[originally posted on jforum.net by amir]

[Migrated From Jforum.net]

had to also change the connection string as the driver was complaining about truncation.... now reads

database.connection.string=jdbc\:mysql\://${database.connection.host}\:${database.connection.port}/${dbname}?user\=${database.connection.username}&password\=${database.connection.password}&autoReconnect\=true${mysql.unicode}${mysql.encoding}&useNewIO\=false&zeroDateTimeBehavior\=convertToNull&useServerPrepStmts\=false&dumpQueriesOnException\=true&jdbcCompliantTruncation\=false

[originally posted on jforum.net by amir]

[Migrated From Jforum.net]

Thank you for this information.

Rafael
[originally posted on jforum.net by Rafael Steil]