Problem setting permissions to group

[Migrated From Jforum.net]

I have problems setting permissions to group.
I change the values in the different fields, and save.
It looks like the values are correctly saved in the database (for example, searching for the role perm_category), but when I reopen the page with the group permission, I see again the old permissions. These permissions are also used by the application itself, as I can't see some categories.

I really don't understand how to solve the problem .... any idea?

Thanks in advance
Enrico
[originally posted on jforum.net by enricod]

[Migrated From Jforum.net]

I found the problem at last, after hours of troubles.
I'm using MySQL 4.0, and the error seems to be in the query PermissionControl.loadGroupRoles

The union is not correctly made.
For instance, in my database:

FIRST SELECT:


mysql> SELECT r.name, '0' AS role_value FROM jforum_roles r WHERE r.group_id IN (2) and r.name = 'perm_forum';
+------------+------------+
| name | role_value |
+------------+------------+
| perm_forum | 0 |
+------------+------------+
1 row in set (0.00 sec)

SECOND SELECT:

mysql> SELECT r.name, rv.role_value FROM jforum_roles r, jforum_role_values rv WHERE r.role_id = rv.role_id AND r.group_id IN (2) and r.name='perm_forum' ORDER BY name ;
+------------+------------+
| name | role_value |
+------------+------------+
| perm_forum | 7 |
| perm_forum | 9 |
| perm_forum | 6 |
| perm_forum | 4 |
| perm_forum | 5 |
| perm_forum | 8 |
| perm_forum | 2 |
| perm_forum | 12 |
| perm_forum | 3 |
+------------+------------+
9 rows in set (0.00 sec)

THE UNION IS:


mysql> SELECT r.name, '0' AS role_value FROM jforum_roles r WHERE r.group_id IN (2) and r.name = 'perm_forum' UNION SELECT r.name, rv.role_value FROM jforum_roles r, jforum_role_values rv WHERE r.role_id = rv.role_id AND r.group_id IN (2) and r.name='perm_forum' ORDER BY name ;
+------------+------------+
| name | role_value |
+------------+------------+
| perm_forum | 0 |
| perm_forum | 7 |
| perm_forum | 9 |
| perm_forum | 6 |
| perm_forum | 4 |
| perm_forum | 5 |
| perm_forum | 8 |
| perm_forum | 2 |
| perm_forum | 1 |
| perm_forum | 3 |
+------------+------------+
10 rows in set (0.00 sec)


Notice how the 12 values has disappeared and has been replaced by 0.

Any idea how to change the query?

Thanks in advance
Enrico
[originally posted on jforum.net by enricod]

[Migrated From Jforum.net]

Sorry, an update during the UNION 12 is seen as 1.

It's a bug/feature of MySQL 4.0

see http://dev.mysql.com/doc/refman/4.1/en/union.html


[originally posted on jforum.net by enricod]

[Migrated From Jforum.net]

ok, found the solution.

Change the query in

PermissionControl.loadGroupRoles = SELECT r.name, '1234567890123456789012345678901234567890' as role_value from jforum_roles r where 1=0 \
UNION ALL SELECT r.name, '0' AS role_value FROM jforum_roles r WHERE r.group_id IN (#IN#) \
UNION ALL \
SELECT r.name, rv.role_value \
FROM jforum_roles r, jforum_role_values rv \
WHERE r.role_id = rv.role_id \
AND r.group_id IN (#IN#) \
ORDER BY name


This solves the problem in MySQL 4.0


[originally posted on jforum.net by enricod]