3.0.0 'Null value in first column' in unixodbc xlat
Michael Rigoni
michael.rigoni at gmail.com
Wed Oct 23 15:21:15 CEST 2013
Hi list,
I am trying to update a FreeRADIUS deployment on RHEL5 to version 3.0.0.
This configuration used to work with FreeRADIUS 3.0 dev branch from
October 2012...
It uses UnixODBC to connect to MSSQL db (tried it with posgresql odbc
driver and does the same).
To pin down the issue, I have set up a new deployment of FR with latest
v3.0.x branch and did the following set up:
* ./configure
* make
* make install
* enabled sql module (see attachment)
* added the following lines to sites enabled/default, just after
'preprocess' in 'authorize' section:
update reply {
Tmp-String-0 := "%{sql:SELECT username from mytest
WHERE csid = '%{Calling-Station-Id}'}"
}
* Commented out the '-sql' in authorize section (was making FR
segfault, certainly because the SQL tables mentionned in
'queries.conf' did not exist).
On first request (see also full debug attached), it gets the value
correctly:
Tue Oct 22 05:39:55 2013 : Debug: rlm_sql (sql): Reserved connection (4)
Tue Oct 22 05:39:55 2013 : Debug: rlm_sql (sql): Executing query:
'SELECT username from mytest WHERE csid = 'FFFFFFFFFFF0''
Tue Oct 22 05:39:55 2013 : Debug: (0) sql_xlat finished
Tue Oct 22 05:39:55 2013 : Debug: rlm_sql (sql): Released connection (4)
Tue Oct 22 05:39:55 2013 : Info: rlm_sql (sql): Closing connection (0):
Too many free connections (5 > 3)
Tue Oct 22 05:39:55 2013 : Debug: rlm_sql_unixodbc: Socket destructor
called, closing socket
Tue Oct 22 05:39:55 2013 : Debug: (0) *expand: "%{sql:SELECT username
from mytest WHERE csid = '%{Calling-Station-Id}'}" -> 'test'*
But one second request, it fails (returns empty string) saying '*Null
value in first column*':
Tue Oct 22 05:41:36 2013 : Debug: rlm_sql (sql): Executing query:
'SELECT username from mytest WHERE csid = 'FFFFFFFFFFF0''
Tue Oct 22 05:41:36 2013 : Debug: (1) *Null value in first column*
Tue Oct 22 05:41:36 2013 : Debug: rlm_sql (sql): Released connection (4)
Tue Oct 22 05:41:36 2013 : Info: rlm_sql (sql): Closing connection (1):
Too many free connections (4 > 3)
Tue Oct 22 05:41:36 2013 : Debug: rlm_sql_unixodbc: Socket destructor
called, closing socket
Tue Oct 22 05:41:36 2013 : Info: rlm_sql (sql): Closing connection (3):
Hit idle_timeout, was idle for 123 seconds
Tue Oct 22 05:41:36 2013 : rlm_sql (sql): You probably need to lower "min"
Tue Oct 22 05:41:36 2013 : Debug: rlm_sql_unixodbc: Socket destructor
called, closing socket
Tue Oct 22 05:41:36 2013 : Info: rlm_sql (sql): Closing connection (2):
Hit idle_timeout, was idle for 123 seconds
Tue Oct 22 05:41:36 2013 : Debug: rlm_sql_unixodbc: Socket destructor
called, closing socket
Tue Oct 22 05:41:36 2013 : Debug: (1) expand:*"%{sql:SELECT username
from mytest WHERE csid = '%{Calling-Station-Id}'}" -> ''**
*
Sometimes it works with further requests, sometimes not... It seems to
work when a new connection to DB is made:
Tue Oct 22 06:20:44 2013 : Debug: rlm_sql (sql): Released connection (5)
Tue Oct 22 06:20:44 2013 : Info: rlm_sql (sql): *Opening additional
connection* (6)
Tue Oct 22 06:20:44 2013 : Debug: (8) expand: "%{sql:SELECT username
from mytest WHERE csid = '%{Calling-Station-Id}'}" -> 'test'
Tue Oct 22 06:20:44 2013 : Debug: (8) Tmp-String-0 := "test"
I did mess around a bit with
src/modules/rlm_sql/drivers/rlm_sql_unixodbc/rlm_sql_unixodbc.c to check
the return value of 'SQLExecDirect' line 163. When it works, it returns
0 and when it fails -2 (invalid handle ?). The rest of the code is
beyond my comprehension :)
Any ideas how to fix this ?
Michael
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20131023/41c5d5b8/attachment-0001.html>
-------------- next part --------------
# -*- text -*-
##
## sql.conf -- SQL modules
##
## $Id: 029eeed64e4683cd4b976943a9cb52ba785dbbb4 $
######################################################################
#
# Configuration for the SQL module
#
# The database schemas and queries are located in subdirectories:
#
# sql/<DB>/main/schema.sql Schema
# sql/<DB>/main/queries.conf Authorisation and Accounting queries
#
# Where "DB" is mysql, mssql, oracle, or postgresql.
#
sql {
# The sub-module to use to execute queries. This should match
# the database you're attempting to connect to.
#
# * rlm_sql_mysql
# * rlm_sql_mssql
# * rlm_sql_oracle
# * rlm_sql_postgresql
# * rlm_sql_sqlite
# * rlm_sql_null (log queries to disk)
#
driver = "rlm_sql_unixodbc"
# The dialect of SQL you want to use, this should usually match
# the driver you selected above.
#
# If you're using rlm_sql_null, then it should be the type of
# database the logged queries are going to be executed against.
dialect = "mssql"
# Connection info:
#
server = "prodDB"
# port = 3306
login = "test"
password = "test"
# Database table configuration for everything except Oracle
#radius_db = "radius"
# If you are using Oracle then use this instead
# radius_db = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SID=your_sid)))"
# If you want both stop and start records logged to the
# same SQL table, leave this as is. If you want them in
# different tables, put the start table in acct_table1
# and stop table in acct_table2
acct_table1 = "radacct"
acct_table2 = "radacct"
# Allow for storing data after authentication
postauth_table = "radpostauth"
# Tables containing 'check' items
authcheck_table = "radcheck"
groupcheck_table = "radgroupcheck"
# Tables containing 'reply' items
authreply_table = "radreply"
groupreply_table = "radgroupreply"
# Table to keep group info
usergroup_table = "radusergroup"
# If set to 'yes' (default) we read the group tables
# If set to 'no' the user MUST have Fall-Through = Yes in the radreply table
# read_groups = yes
# Remove stale session if checkrad does not see a double login
delete_stale_sessions = yes
# Write SQL queries to a logfile. This is potentially useful for tracing
# issues with authorization queries.
# logfile = ${logdir}/sqllog.sql
# As of version 3.0, the "pool" section has replaced the
# following configuration items:
#
# num_sql_socks
# connect_failure_retry_delay
# lifetime
# max_queries
#
# The connection pool is new for 3.0, and will be used in many
# modules, for all kinds of connection-related activity.
#
pool {
# Number of connections to start
start = 5
# Minimum number of connections to keep open
min = 4
# Maximum number of connections
#
# If these connections are all in use and a new one
# is requested, the request will NOT get a connection.
max = 10
# Spare connections to be left idle
#
# NOTE: Idle connections WILL be closed if "idle_timeout"
# is set.
spare = 3
# Number of uses before the connection is closed
#
# 0 means "infinite"
uses = 0
# The lifetime (in seconds) of the connection
lifetime = 0
# idle timeout (in seconds). A connection which is
# unused for this length of time will be closed.
idle_timeout = 60
# NOTE: All configuration settings are enforced. If a
# connection is closed because of "idle_timeout",
# "uses", or "lifetime", then the total number of
# connections MAY fall below "min". When that
# happens, it will open a new connection. It will
# also log a WARNING message.
#
# The solution is to either lower the "min" connections,
# or increase lifetime/idle_timeout.
}
# Set to 'yes' to read radius clients from the database ('nas' table)
# Clients will ONLY be read on server startup.
# read_clients = yes
# Table to keep radius client info
client_table = "client"
# Read database-specific queries
$INCLUDE ${modconfdir}/${.:name}/main/${dialect}/queries.conf
}
-------------- next part --------------
A non-text attachment was scrubbed...
Name: radiusd-DEBUG.txt.gz
Type: application/gzip
Size: 22037 bytes
Desc: not available
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20131023/41c5d5b8/attachment-0001.bin>
More information about the Freeradius-Users
mailing list