SQLite files remain open if unlang query has no results

[pauldekkers]

Issue type

• Defect - Crash or memory corruption.

Defect/Feature description

I'm running out of file descriptors by using ${sql queries to a SQLite database (with default schema) if the query has no result set.

If the query result isn't empty, everything is fine.

The error

Info: rlm_sql_sqlite: Opening SQLite database "/etc/freeradius/users.db"
ERROR: (20570)     ERROR: SQL query failed: no connection

is logged as soon as the query-set is empty.

... and obviously as soon as I run out of descriptors this gives me

Info: rlm_sql_sqlite: Opening SQLite database "/etc/freeradius/users.db"
Error: rlm_sql_sqlite: Error opening SQLite database "/etc/freeradius/users.db": Code 0x000e (14): unable to open database file
Error: rlm_sql (sql): Opening connection failed (1364)

I noticed the issue on FreeRADIUS 3.0.x (git, 3.0.13 at the time of writing) and 3.0.12

It could be I'm not using the query/unlang statement as I should.

How to reproduce issue

The following unlang code in authorize in my default server.

if ("%{sql: select id from radreply where username = '%{User-Name}' limit 1}" == "") {
    update control {
        Proxy-To-Realm := "someplaceelse"
    }
}

If I replace %{User-Name} for a user that always matches, the issue does not occur.

There's not a lot of traffic actually, but after 10 minutes I can see users.db is open > 100 times via lsof

Output of [radiusd|freeradius] -X showing issue occurring

(4)     if ("%{sql: select id from radreply where username = '%{User-Name}' limit 1}" == "") {
(4)     EXPAND %{User-Name}
(4)        --> 1...@wlan.mnc008.mcc204.3gppnetwork.org
(4)     SQL-User-Name set to '1...@wlan.mnc008.mcc204.3gppnetwork.org'
rlm_sql (sql): Reserved connection (4)
(4)     Executing select query:  select id from radreply where username = '1...@wlan.mnc008.mcc204.3gppnetwork.org' limit 1
(4)     ERROR: SQL query failed: no connection
rlm_sql (sql): Released connection (4)
rlm_sql (sql): Need 1 more connections to reach 10 spares
rlm_sql (sql): Opening additional connection (9), 1 of 23 pending slots used
rlm_sql_sqlite: Opening SQLite database "/etc/freeradius/users.db"
(4)     EXPAND %{sql: select id from radreply where username = '%{User-Name}' limit 1}

[arr2036]

Fixed by 35e03ea

[pauldekkers]

Unfortunately, a clean build of v3.0.x now fails for me (on Ubuntu 16.04)

git clone -b v3.0.x https://github.com/FreeRADIUS/freeradius-server.git freeradius-server-3.0.13
./configure
make deb

gives:

CC src/modules/rlm_ippool/rlm_ippool_tool.c
src/modules/rlm_ippool/rlm_ippool_tool.c: In function ‘viewdb’:
src/modules/rlm_ippool/rlm_ippool_tool.c:431:13: warning: ‘key’ may be used uninitialized in this function [-Wmaybe-uninitialized]
  ippool_key key;
             ^
LINK build/bin/rlm_ippool_tool
CC src/modules/rlm_rediswho/rlm_rediswho.c
LINK build/lib/rlm_rediswho.la
CC src/modules/rlm_sqlippool/rlm_sqlippool.c
LINK build/lib/rlm_sqlippool.la
CC src/modules/rlm_test/rlm_test.c
LINK build/lib/rlm_test.la
CC src/modules/rlm_sql/rlm_sql.c
src/modules/rlm_sql/rlm_sql.c: In function ‘sql_xlat’:
src/modules/rlm_sql/rlm_sql.c:192:9: error: ‘rlm_sql_t {aka struct sql_inst}’ has no member named ‘driver’
    (inst->driver->sql_finish_query)(handle, inst->config);
         ^
src/modules/rlm_sql/rlm_sql.c:230:8: error: ‘rlm_sql_t {aka struct sql_inst}’ has no member named ‘driver’
   (inst->driver->sql_finish_select_query)(handle, inst->config);
        ^
scripts/boiler.mk:635: recipe for target 'build/objs/src/modules/rlm_sql/rlm_sql.lo' failed

[arr2036]

fixed

[pauldekkers]

Thanks! This fixed the open files problems!

I still wonder why I see
ERROR: (509) ERROR: SQL query failed: no connection
for an empty result-set in SQLite, while I don't see that for MySQL for instance?

[arr2036]

Mm, pushed some more changes. There was no unified return code for "no more rows" between the drivers.

[pauldekkers]

But does it need to error when there's no rows? (I myself don't consider that an error per se; could be there's no particular user... no query error or database failure.)
ERROR: (10) ERROR: SQL query failed: no more rows
This now also happens with MySQL (while that driver didn't log empty result-sets before).

[alandekok]

I've pushed a fix. It's now just a debug message.