Problem with multiple groups

[Volker Lieder]

Hi there,
we have a setup running for ppp user on a freeradius/mysql base. 
We recognized that not all group values are given to the user while the login is running.
After some debugging we found out, that freeradius didn't get all information from the database while its inside of the tables.

Attached you find our used versions, database setting and a sql debug log from a testing user.  

Tested on debian 6.0.7, 2.1.10+dfsg-2+squeeze1 and
debian 7.1, 2.1.12+dfsg-1.2


mysql> select * from radusergroup where username like 'dsluser%';+-----------------+------------------------------+----------+
| username        | groupname                    | priority |
+-----------------+------------------------------+----------+
| dsluser at realm.net | Default                      |        1 |
| dsluser at realm.net | 5Uhr-Trennung                |        2 |
| dsluser at realm.net | Default_dsl-mobile.de        |        1 |
| dsluser at realm.net | PM_DSL_8000                  |        1 |
+-----------------+------------------------------+----------+


select * from radgroupreply where groupname='PM_DSL_8000';
+----+------------------------------+--------------+----+-------------------------------------------------------------------------+
| id | groupname                    | attribute    | op | value                                                                   |
+----+------------------------------+--------------+----+-------------------------------------------------------------------------+
| 35 | PM_DSL_8000                  | Cisco-AVPair | := | lcp:interface-config=service-policy output PM_DSL_8000_DSCP46_50PROZENT |
+----+------------------------------+--------------+----+-------------------------------------------------------------------------+

mysql> select * from radgroupreply where groupname='Default';
+----+-----------+-----------------+----+----------------------------------+
| id | groupname | attribute       | op | value                            |
+----+-----------+-----------------+----+----------------------------------+
|  9 | Default   | Framed-Protocol | =  | PPP                              |
| 10 | Default   | Framed-Routing  | =  | None                             |
| 11 | Default   | Service-Type    | =  | Framed-User                      |
| 24 | Default   | Cisco-AVPair    | += | lcp:interface-config=ip mtu 1492 |
+----+-----------+-----------------+----+----------------------------------+

mysql> select * from radgroupreply where groupname='5Uhr-Trennung';
+----+----------------+-----------------+----+-----------------+
| id | groupname      | attribute       | op | value           |
+----+----------------+-----------------+----+-----------------+
|  2 | 5Uhr-Trennung  | Session-Timeout | =  | `%{expr:05:00}` |
+----+----------------+-----------------+----+-----------------+

mysql> select * from radgroupreply where groupname='Default_dsl-mobile.de';
+----+--------------------------+-----------------+----+----------------------------------+
| id | groupname                | attribute       | op | value                            |
+----+--------------------------+-----------------+----+----------------------------------+
| 44 | Default_dsl-mobile.de    | Framed-Protocol | =  | PPP                              |
| 45 | Default_dsl-mobile.de    | Framed-Routing  | =  | None                             |
| 46 | Default_dsl-mobile.de    | Service-Type    | =  | Framed-User                      |
| 48 | Default_dsl-mobile.de    | Cisco-AVPair    | += | lcp:interface-config=ip mtu 1448 |
+----+--------------------------+-----------------+----+----------------------------------+


Output from "/usr/sbin/freeradius -d /etc/freeradius -X -f" and a new dsl login try:

[sql] User found in radcheck table
[sql] 	expand: SELECT id, username, attribute, value, op           FROM radreply           WHERE username = '%{SQL-User-Name}'           ORDER BY id -> SELECT id, username, attribute, value, op           FROM radreply           WHERE username = 'dsluser at realm.net'           ORDER BY id
rlm_sql_mysql: query:  SELECT id, username, attribute, value, op           FROM radreply           WHERE username = 'dsluser at realm.net'           ORDER BY id
[sql] 	expand: SELECT groupname           FROM radusergroup           WHERE username = '%{SQL-User-Name}'           ORDER BY priority -> SELECT groupname           FROM radusergroup           WHERE username = 'dsluser at realm.net'           ORDER BY priority
rlm_sql_mysql: query:  SELECT groupname           FROM radusergroup           WHERE username = 'dsluser at realm.net'           ORDER BY priority
[sql] 	expand: SELECT id, groupname, attribute,           Value, op           FROM radgroupreply           WHERE groupname = '%{Sql-Group}'           ORDER BY id -> SELECT id, groupname, attribute,           Value, op           FROM radgroupreply           WHERE groupname = 'Default'           ORDER BY id
rlm_sql_mysql: query:  SELECT id, groupname, attribute,           Value, op           FROM radgroupreply           WHERE groupname = 'Default'           ORDER BY id
Invalid operator for item Framed-Protocol: reverting to '=='
[sql] 	expand: SELECT id, groupname, attribute,           Value, op           FROM radgroupreply           WHERE groupname = '%{Sql-Group}'           ORDER BY id -> SELECT id, groupname, attribute,           Value, op           FROM radgroupreply           WHERE groupname = 'Default_dsl-mobile.de'           ORDER BY id
rlm_sql_mysql: query:  SELECT id, groupname, attribute,           Value, op           FROM radgroupreply           WHERE groupname = 'Default_dsl-mobile.de'           ORDER BY id
Invalid operator for item Framed-Protocol: reverting to '=='
[sql] 	expand: SELECT id, groupname, attribute,           Value, op           FROM radgroupreply           WHERE groupname = '%{Sql-Group}'           ORDER BY id -> SELECT id, groupname, attribute,           Value, op           FROM radgroupreply           WHERE groupname = 'PM_DSL_8000'           ORDER BY id
rlm_sql_mysql: query:  SELECT id, groupname, attribute,           Value, op           FROM radgroupreply           WHERE groupname = 'PM_DSL_8000'           ORDER BY id
[sql] User found in group PM_DSL_8000
[sql] 	expand: SELECT id, groupname, attribute,           value, op           FROM radgroupreply           WHERE groupname = '%{Sql-Group}'           ORDER BY id -> SELECT id, groupname, attribute,           value, op           FROM radgroupreply           WHERE groupname = 'PM_DSL_8000'           ORDER BY id
rlm_sql_mysql: query:  SELECT id, groupname, attribute,           value, op           FROM radgroupreply           WHERE groupname = 'PM_DSL_8000'           ORDER BY id
rlm_sql (sql): Released sql socket id: 0
++[sql] returns ok

As you can see, the radius didn't get infos about the group 5Uhr-Trennung and the group PM_DSL_8000 seems to be there two times.
It doesn't matter on what position a group is, we can reproduce it with any settings.

Any idea or restrictions known, why freeradius won't lookup all groups?

Or any hint, what to test?

Regards,
Volker