DB Differences??

[Migrated From Jforum.net]

I was using v 2.14 but was getting exception whenever I searched

So I switched to cvs 2.15 version last week, now search works!

but edit post causes an exception

So today I updated code with the fixes from CVS but now pretty much nothing works SQL errors


So questiuon here is what must I fix in my DB to match 2.15 code? It appaers there must be some updates I need?


[originally posted on jforum.net by mqjoe1]

[Migrated From Jforum.net]

here's the db queries that happen whne the errors occur

"050808 12:36:18 6410 Connect [e-mail] on jforum
6410 Query SELECT 1
6410 Query SET NAMES utf8
6410 Query SET character_set_results = NULL
6410 Query SHOW VARIABLES
6410 Query SHOW COLLATION
6410 Query SET autocommit=1
6410 Query SET autocommit=0
6410 Query SELECT MAX(post_time), topic_id FROM jforum_posts WHERE forum_id = 11 AND post_time > '2005-08-08 11:18:06' GROUP BY topic_id
6410 Query SELECT MAX(post_time), topic_id FROM jforum_posts WHERE forum_id = 5 AND post_time > '2005-08-08 11:18:06' GROUP BY topic_id
6410 Query SELECT MAX(post_time), topic_id FROM jforum_posts WHERE forum_id = 9 AND post_time > '2005-08-08 11:18:06' GROUP BY topic_id
6410 Query SELECT MAX(post_time), topic_id FROM jforum_posts WHERE forum_id = 10 AND post_time > '2005-08-08 11:18:06' GROUP BY topic_id
6410 Query SELECT MAX(post_time), topic_id FROM jforum_posts WHERE forum_id = 6 AND post_time > '2005-08-08 11:18:06' GROUP BY topic_id
6410 Query SELECT MAX(post_time), topic_id FROM jforum_posts WHERE forum_id = 7 AND post_time > '2005-08-08 11:18:06' GROUP BY topic_id
6410 Query SELECT MAX(post_time), topic_id FROM jforum_posts WHERE forum_id = 8 AND post_time > '2005-08-08 11:18:06' GROUP BY topic_id
6410 Query SELECT DISTINCT u.user_id, u.username, rv.role_value FROM jforum_roles r, jforum_role_values rv, jforum_users u, jforum_user_groups ug, jforum_users u2, jforum_roles r2 WHERE r.role_id = rv.role_id AND r.name = 'perm_moderation_forums' AND rv.role_type = 1 AND rv.role_value = 11 AND r.group_id = ug.group_id AND ug.user_id = u.user_id AND u.user_id = u2.user_id AND r2.name = 'perm_moderation' AND r2.role_type = 1 AND r2.group_id = ug.group_id UNION ( SELECT u.user_id, username, rv.role_value FROM jforum_roles r, jforum_role_values rv, jforum_users u WHERE r.role_id = rv.role_id AND name = 'perm_moderation_forums' AND rv.role_type = 1 AND rv.role_value = 11 AND r.user_id = u.user_id )
6410 Query rollback
6410 Query rollback
6410 Quit
"



this one is causing the trouble:

6410 Query SELECT DISTINCT u.user_id, u.username, rv.role_value FROM jforum_roles r, jforum_role_values rv, jforum_users u, jforum_user_groups ug, jforum_users u2, jforum_roles r2 WHERE r.role_id = rv.role_id AND r.name = 'perm_moderation_forums' AND rv.role_type = 1 AND rv.role_value = 11 AND r.group_id = ug.group_id AND ug.user_id = u.user_id AND u.user_id = u2.user_id AND r2.name = 'perm_moderation' AND r2.role_type = 1 AND r2.group_id = ug.group_id UNION ( SELECT u.user_id, username, rv.role_value FROM jforum_roles r, jforum_role_values rv, jforum_users u WHERE r.role_id = rv.role_id AND name = 'perm_moderation_forums' AND rv.role_type = 1 AND rv.role_value = 11 AND r.user_id = u.user_id )

It doesn't look properly formed?

I tried to run that quey though mysql manually and here is what I get:


Database changed
mysql> SELECT DISTINCT u.user_id, u.username, rv.role_value FROM jforum_roles r, jforum_role_values rv, jforum_user_groups ug, jforum_users u2, jforum_roles r2 WHERE r.role_id = rv.role_id AND r.name = 'perm_moderation_forums' AND rv.role_type = 1 AND 11 AND r.group_id = ug.group_id AND ug.user_id = u.user_id AND u.user_id = u2.user_id AND r2.name = 'perm_moderation' AND r2.role_type = 1= ug.group_id UNION ( SELECT u.user_id, username, rv.role_value FROM jforum_roles r, jforum_role_values rv, jforum_users u WHERE r.role_id =name = 'perm_moderation_forums' AND rv.role_type = 1 AND rv.role_value = 11 AND r.user_id = u.user_id );
ERROR 1064 (42000): 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
mysql>


[originally posted on jforum.net by mqjoe1]

[Migrated From Jforum.net]

Seems to be related to Moderator list mods. I reverted a couple opf html files and java files and I'm mostly back.

So I'm guess there are some query updated need for MySQL? or db updates on my part


[originally posted on jforum.net by mqjoe1]

[Migrated From Jforum.net]

You have syntax error in SQL statement
ERROR 1064 (42000): You have an error in your SQL syntax;

mqjoe1 wrote:
mysql> SELECT DISTINCT u.user_id, u.username, rv.role_value FROM jforum_roles r, jforum_role_values rv, jforum_user_groups ug, jforum_users u2, jforum_roles r2 WHERE r.role_id = rv.role_id AND r.name = 'perm_moderation_forums' AND rv.role_type = 1 AND 11 AND r.group_id = ug.group_id AND ug.user_id = u.user_id AND u.user_id = u2.user_id AND r2.name = 'perm_moderation' AND r2.role_type = 1= ug.group_id UNION ( SELECT u.user_id, username, rv.role_value FROM jforum_roles r, jforum_role_values rv, jforum_users u WHERE r.role_id =name = 'perm_moderation_forums' AND rv.role_type = 1 AND rv.role_value = 11 AND r.user_id = u.user_id );

There are some copy-and-paste errors. You must fix it and test it again in exactly the same SQL.

mqjoe1 wrote:
SELECT DISTINCT u.user_id, u.username, rv.role_value FROM jforum_roles r, jforum_role_values rv, jforum_users u, jforum_user_groups ug, jforum_users u2, jforum_roles r2 WHERE r.role_id = rv.role_id AND r.name = 'perm_moderation_forums' AND rv.role_type = 1 AND rv.role_value = 11 AND r.group_id = ug.group_id AND ug.user_id = u.user_id AND u.user_id = u2.user_id AND r2.name = 'perm_moderation' AND r2.role_type = 1 AND r2.group_id = ug.group_id UNION ( SELECT u.user_id, username, rv.role_value FROM jforum_roles r, jforum_role_values rv, jforum_users u WHERE r.role_id = rv.role_id AND name = 'perm_moderation_forums' AND rv.role_type = 1 AND rv.role_value = 11 AND r.user_id = u.user_id )

Can you report your mysql version? Maybe some people can help you.
[originally posted on jforum.net by andowson]

[Migrated From Jforum.net]

OK, the problem is that version 2.1.4's jforum_roles table doesn't have the records of name='perm_moderation_forums'. You'd better check the cvs file for manual inserting these records.
[originally posted on jforum.net by andowson]

[Migrated From Jforum.net]

andowson wrote:cvs file for manual inserting these records.

What file??
[originally posted on jforum.net by Anonymous]

[Migrated From Jforum.net]

Anonymous wrote:What file??

Sorry that I didn't check the file content of mysql_data_dump.sql .
I didn't find out anything there.
So, I guess they are generated by the application itself.
You can use the admin panel to save the user and group permisson again. Maybe this will work.


[originally posted on jforum.net by andowson]