I fixed this problem by editing $CATALINA_HOME/webapps/jforum/WEB-INF/config/database/generic/generic_queries.sql
There are 7 lines you need to change.
Line 42:
from ConfigModel.update = UPDATE jforum_config SET
config_value = ? WHERE config_name = ?
to ConfigModel.update = UPDATE jforum_config SET
config_value = CAST(? AS VARCHAR(255)) WHERE config_name = ?
Line 248:
from ForumModel.statsFirstPostTime = SELECT MIN(post_time) FROM jforum_posts WHERE
post_time > 0
to ForumModel.statsFirstPostTime = SELECT MIN(post_time) FROM jforum_posts WHERE post_time is not null and
post_time > '2000-01-01'
Line 249:
from ForumModel.statsFirstRegisteredUserTime = SELECT MIN(user_regdate) FROM jforum_users WHERE
user_regdate > 0
to ForumModel.statsFirstRegisteredUserTime = SELECT MIN(user_regdate) FROM jforum_users WHERE user_regdate is not null and
user_regdate > '2000-01-01'
Line 279:
from AND
rv.role_value = ? \
to AND
rv.role_value = CAST(? AS VARCHAR(255)) \
Line 449:
from AND
rv.role_value = ?
to AND
rv.role_value = CAST(? AS VARCHAR(255))
Line 451:
from PermissionControl.deleteRoleValues = DELETE FROM jforum_role_values WHERE
role_value = ? AND role_id IN (#IDS#)
to PermissionControl.deleteRoleValues = DELETE FROM jforum_role_values WHERE
role_value = CAST(? AS VARCHAR(255)) AND role_id IN (#IDS#)
Line 521:
from UserSessionModel.update = UPDATE jforum_sessions SET session_start = ?, session_time = ?,
session_id = ? WHERE session_user_id = ?
to UserSessionModel.update = UPDATE jforum_sessions SET session_start = ?, session_time = ?,
session_id = CAST(? AS VARCHAR(150)) WHERE session_user_id = ?
I'm actually not sure about lines 248 & 249. I haven't found a case yet where it doesn't work, but it seems pointless to specify a date > '2001-01-01' because that's the minimum value for that data type in PostgreSQL. Maybe the "not null" is sufficient.
I haven't tested this with other databases yet, but it should work. I've avoided any postgres-specific syntax. I think a more satisfactory long-term fix would be to change the data types of the columns that are being treated like integers to integer.
These all involve the same kind of error, i.e. comparing data of different types with =, < or >. At some point PostgreSQL got a whole lot less tolerant of this sort of thing.
I wasn't able to check Jira to see if this had been reported because it's been down for about two days now. They don't say where we're supposed to report that.
[originally posted on jforum.net by Isadore Nabi]
