Hello I am new in this list I am setting up a freeradius server with mysql and sqlippool but I am facing some problems with sqlippool which cannot deliver an ip address to subscribers. I installed Freeradius 2.1.3 on OpenBSD 4.6 with mysql support here is the output of radiusd -X sqlippool { sql-instance-name = "sql" lease-duration = 3600 pool-name = "" allocate-begin = "START TRANSACTION" allocate-clear = "UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', expiry_time = 'now'::timestamp(0) - '1 second'::interval WHERE nasipaddress = '%{NAS-IP-Address}' AND pool_key = '%{NAS-Port}'" allocate-find = "SELECT framedipaddress FROM radippool WHERE pool_name = '%{control:Pool-Name}' AND expiry_time < 'now'::timestamp(0) ORDER BY (username <> '%{SQL-User-Name}'), (callingstationid <> '%{Calling-Station-Id}'), expiry_time LIMIT 1 FOR UPDATE" allocate-update = "UPDATE radippool SET nasipaddress = '%{NAS-IP-Address}', pool_key = '%{NAS-Port}', callingstationid = '%{Calling-Station-Id}', username = '%{SQL-User-Name}', expiry_time = 'now'::timestamp(0) + '3600 second'::interval WHERE framedipaddress = '%I'" allocate-commit = "COMMIT" allocate-rollback = "ROLLBACK" pool-check = "SELECT id FROM radippool WHERE pool_name='%{control:Pool-Name}' LIMIT 1" start-begin = "START TRANSACTION" start-update = "UPDATE radippool SET expiry_time = 'now'::timestamp(0) + '3600 second'::interval WHERE nasipaddress = '%{NAS-IP-Address}' AND pool_key = '%{NAS-Port}'" start-commit = "COMMIT" start-rollback = "ROLLBACK" alive-begin = "START TRANSACTION" alive-update = "UPDATE radippool SET expiry_time = 'now'::timestamp(0) + '3600 seconds'::interval WHERE nasipaddress = '%{Nas-IP-Address}' AND pool_key = '%{NAS-Port}' AND framedipaddress = '%{Framed-IP-Address}' AND username = '%{SQL-User-Name}' AND callingstationid = '%{Calling-Station-Id}'" alive-commit = "COMMIT" alive-rollback = "ROLLBACK" stop-begin = "START TRANSACTION" stop-clear = "UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', expiry_time = 'now'::timestamp(0) - '1 second'::interval WHERE nasipaddress = '%{Nas-IP-Address}' AND pool_key = '%{NAS-Port}' AND username = '%{SQL-User-Name}' AND callingstationid = '%{Calling-Station-Id}' AND framedipaddress = '%{Framed-IP-Address}'" stop-commit = "COMMIT" stop-rollback = "ROLLBACK" on-begin = "START TRANSACTION" on-clear = "UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', expiry_time = 'now'::timestamp(0) - '1 second'::interval WHERE nasipaddress = '%{Nas-IP-Address}'" on-commit = "COMMIT" on-rollback = "ROLLBACK" off-begin = "START TRANSACTION" off-clear = "UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', expiry_time = 'now'::timestamp(0) - '1 second'::interval WHERE nasipaddress = '%{Nas-IP-Address}'" off-commit = "COMMIT" off-rollback = "ROLLBACK" sqlippool_log_exists = "Existing IP: %{reply:Framed-IP-Address} (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name})" sqlippool_log_success = "Allocated IP: %{reply:Framed-IP-Address} from %{control:Pool-Name} (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name})" sqlippool_log_clear = "Released IP %{Framed-IP-Address} (did %{Called-Station-Id} cli %{Calling-Station-Id} user %{User-Name})" sqlippool_log_failed = "IP Allocation FAILED from %{control:Pool-Name} (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name})" sqlippool_log_nopool = "No Pool-Name defined (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name})" defaultpool = "main_pool" } Module: Instantiating attr_filter.accounting_response attr_filter attr_filter.accounting_response { attrsfile = "/etc/raddb/attrs.accounting_response" key = "%{User-Name}" } Module: Checking session {...} for more modules to load Module: Checking post-auth {...} for more modules to load } radiusd: #### Opening IP addresses and Ports #### listen { type = "auth" ipaddr = * port = 0 } listen { type = "acct" ipaddr = * port = 0 } Listening on authentication address * port 1812 Listening on accounting address * port 1813 and here are my tables on the raidus sql database mysql> select * from radcheck; +----+----------+--------------------+----+--------+ | id | username | attribute | op | value | +----+----------+--------------------+----+--------+ | 1 | tahar | Cleartext-Password | := | 123 | | 2 | tba | Cleartext-Password | := | 123456 | +----+----------+--------------------+----+--------+ mysql> select * from radgroupcheck; +----+-----------+-----------+----+-----------+ | id | groupname | attribute | op | value | +----+-----------+-----------+----+-----------+ | 1 | static | Pool-Name | := | main_pool | | 2 | dynamic | Pool-Name | := | test_pool | +----+-----------+-----------+----+-----------+ mysql> select * from radippool; +----+-----------+-----------------+--------------+-----------------+------------------+-------------+----------+----------+ | id | pool_name | framedipaddress | nasipaddress | calledstationid | callingstationid | expiry_time | username | pool_key | +----+-----------+-----------------+--------------+-----------------+------------------+-------------+----------+----------+ | 1 | main_pool | 192.168.100.10 | | | | NULL | | | | 2 | test_pool | 192.168.100.50 | | | | NULL | | | | 3 | main_pool | 192.168.100.11 | | | | NULL | | | | 4 | main_pool | 192.168.100.12 | | | | NULL | | | | 5 | main_pool | 192.168.100.13 | | | | NULL | | | | 6 | main_pool | 192.168.100.14 | | | | NULL | | | | 7 | main_pool | 192.168.100.15 | | | | NULL | | | | 8 | test_pool | 192.168.100.51 | | | | NULL | | | | 9 | test_pool | 192.168.100.52 | | | | NULL | | | | 10 | test_pool | 192.168.100.53 | | | | NULL | | | | 11 | test_pool | 192.168.100.54 | | | | NULL | | | | 12 | test_pool | 192.168.100.55 | | | | NULL | | | +----+-----------+-----------------+--------------+-----------------+------------------+-------------+----------+----------+ mysql> select * from radgroupreply; +----+-----------+--------------------+---------------------+------+ | id | GroupName | Attribute | Value | Op | +----+-----------+--------------------+---------------------+------+ | 34 | dynamic | Framed-Compression | Van-Jacobsen-TCP-IP | := | | 33 | dynamic | Framed-Protocol | PPP | := | | 32 | dynamic | Service-Type | Framed-User | := | | 35 | dynamic | Framed-MTU | 1500 | := | | 37 | static | Framed-Protocol | PPP | := | | 38 | static | Service-Type | Framed-User | := | | 39 | static | Framed-Compression | Van-Jacobsen-TCP-IP | := | | 41 | netdial | Service-Type | Framed-User | := | | 42 | netdial | Framed-Protocol | PPP | := | +----+-----------+--------------------+---------------------+------+ I left radreply empty because I would to have dinamic IPs When I do a test radtest tahar 123 localhost 1812 testing123 I have this output and i says that the IP cannot be allocate anyone know why ? Thank you rad_recv: Access-Request packet from host 127.0.0.1 port 16536, id=68, length=57 User-Name = "tahar" User-Password = "123" NAS-IP-Address = 193.95.93.219 NAS-Port = 1812 +- entering group authorize {...} ++[preprocess] returns ok ++[chap] returns noop ++[mschap] returns noop [suffix] No '@' in User-Name = "tahar", looking up realm NULL [suffix] No such realm "NULL" ++[suffix] returns noop ++[files] returns noop [sql] expand: %{User-Name} -> tahar [sql] sql_set_user escaped user --> 'tahar' rlm_sql (sql): Reserving sql socket id: 2 [sql] expand: SELECT id, username, attribute, value, op FROM radcheck WHERE username = '%{SQL-User-Name}' ORDER BY id -> SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'tahar' ORDER BY id [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 = 'tahar' ORDER BY id [sql] expand: SELECT groupname FROM radusergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority -> SELECT groupname FROM radusergroup WHERE username = 'tahar' ORDER BY priority [sql] expand: SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = '%{Sql-Group}' ORDER BY id -> SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = 'dynamic' ORDER BY id [sql] User found in group dynamic [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 = 'dynamic' ORDER BY id rlm_sql (sql): Released sql socket id: 2 ++[sql] returns ok ++[expiration] returns noop ++[logintime] returns noop ++[pap] returns updated Found Auth-Type = PAP +- entering group PAP {...} [pap] login attempt with password "123" [pap] Using clear text password "123" [pap] User authenticated successfully ++[pap] returns ok +- entering group post-auth {...} rlm_sql (sql): Reserving sql socket id: 1 [sqlippool] expand: %{User-Name} -> tahar [sqlippool] sql_set_user escaped user --> 'tahar' [sqlippool] expand: START TRANSACTION -> START TRANSACTION [sqlippool] expand: UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', expiry_time = 'now'::timestamp(0) - '1 second'::interval WHERE nasipaddress = '%{NAS-IP-Address}' AND pool_key = '%{NAS-Port}' -> UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', expiry_time = 'now'::timestamp(0) - '1 second'::interval WHERE nasipaddress = '193.95.93.219' AND pool_key = '1812' rlm_sql_mysql: MYSQL check_error: 1064 received sqlippool_command: database query error in: 'UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', expiry_time = 'now'::timestamp(0) - '1 second'::interval WHERE nasipaddress = '193.95.93.219' AND pool_key = '1812'' [sqlippool] expand: SELECT framedipaddress FROM radippool WHERE pool_name = '%{control:Pool-Name}' AND expiry_time < 'now'::timestamp(0) ORDER BY (username <> '%{SQL-User-Name}'), (callingstationid <> '%{Calling-Station-Id}'), expiry_time LIMIT 1 FOR UPDATE -> SELECT framedipaddress FROM radippool WHERE pool_name = 'test_pool' AND expiry_time < 'now'::timestamp(0) ORDER BY (username <> 'tahar'), (callingstationid <> ''), expiry_time LIMIT 1 FOR UPDATE rlm_sql_mysql: MYSQL check_error: 1064 received sqlippool_query1: database query error [sqlippool] expand: COMMIT -> COMMIT [sqlippool] expand: SELECT id FROM radippool WHERE pool_name='%{control:Pool-Name}' LIMIT 1 -> SELECT id FROM radippool WHERE pool_name='test_pool' LIMIT 1 rlm_sql (sql): Released sql socket id: 1 [sqlippool] pool appears to be full [sqlippool] expand: IP Allocation FAILED from %{control:Pool-Name} (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name}) -> IP Allocation FAILED from test_pool (did cli port 1812 user tahar) IP Allocation FAILED from test_pool (did cli port 1812 user tahar) ++[sqlippool] returns notfound ++[exec] returns noop Sending Access-Accept of id 68 to 127.0.0.1 port 16536 Finished request 1. Going to the next request Waking up in 4.9 seconds. Cleaning up request 1 ID 68 with timestamp +58 Ready to process requests.