rlm_sqlippool

[Chris Knipe]

>> Can someone perhaps please just take a moment to explain what
>> exactly is going on in those queries??  I'm not referring to the SQL
>> as such, but rather as to what is updated, and why.  A table
>> structure accompanying those queries in sqlippool.conf may help
>> significantly as well, as I'm guessing at the moment what needs to
>> go where :(
>
>  The general idea is to... grab IP's from a pool, based on a unique
> key.  If an allocated IP for that key doesn't exist, then a free IP is
> taken, and the key written to the table.

Fair enough, and yes, that bit I figured out as well....   Quick debug 
output however:

Value Of the Pool-Name is [MYPOOL] and its [6] Chars
rlm_sql (sql): Reserving sql socket id: 10
radius_xlat:  'BEGIN'
rlm_sql_mysql: MYSQL check_error: 2006, returning SQL_DOWN
rlm_sql (sql): Attempting to connect rlm_sql_mysql #10
rlm_sql_mysql: Starting connect to MySQL server for #10
rlm_sql (sql): Connected new DB handle, #10
radius_xlat:  'UPDATE radippool   SET nasipaddress = '', pool_key = 0, 
callingstationid = '',   expiry_time = 'now'::timestamp(0) - '1 
second'::interval   WHERE pool_key = '00:0F:EA:61:0F:B3''
rlm_sql_mysql: MYSQL check_error: 1064 received
sqlippool_command: database query error
radius_xlat:  'SELECT framedipaddress FROM radippool   WHERE pool_name = '' 
AND expiry_time < 'now'::timestamp(0)   ORDER BY pool_name, (username <> 
'user at domain.com'), (callingstationid <> '00:0F:EA:61:0F:B3'), expiry_time 
LIMIT 1   FOR UPDATE'
rlm_sql_mysql: MYSQL check_error: 1064 received
sqlippool_query1: database query error
rlm_sqlippool: ip=[] len=0
radius_xlat:  'COMMIT'
rlm_sqlippool: IP number could not be allocated.
rlm_sql (sql): Released sql socket id: 10

Ok, now sure... We have SQL errors because the tables doesn't even exist in 
the database (that is not why I'm asking about how the module works).  I use 
a rather complex system, and it's important that it's integrated correctly.

Looking at the UPDATE and the SELECT queries above... They are completely 
invalid.  It's not even propper SQL syntax...   UNLESS, expiry time *should* 
be a varchar instead of a datetime field - which again makes the database 
very big.  Let's face it, a varchar uses a lot more space than a datetime, 
or even a int - should UNIX_TIMESTAMP be used instead.

I *suppose* what it is trying to do, is that it saw that a user is trying to 
authenticate, and then it tried via the UPDATE to release any possible IP 
address which may have been allocated (I must admit, that is actually very 
clever!).  It then proceeded to attempt to get a new dymaic IP address from 
the pool, but completely disregarded the pool-name ???

The config is stock standard, query in the config:
  allocate-find = "SELECT framedipaddress FROM radippool \
  WHERE pool_name = '%{reply:Pool-Name}' AND expiry_time < 
'now'::timestamp(0) \
  ORDER BY pool_name, (username <> '%{User-Name}'), (callingstationid <> 
'%{Calling-Station-Id}'), expiry_time \
  LIMIT 1 \
  FOR UPDATE"

Should it not use the *check* attribute variable for the pool name 
(Pool-Name is, surely, a Check item and not a Reply item)??? Why also does 
timestamp(0) not expand?  Is timestamp(0) a valid variable (It's not in 
variables.txt), and what is this supposed to be format wise?  Can it be 
substituted with MySQL's NOW() instead?

Not ranting or anything Alan, I'll figure this out... But even the example 
configuration file needs to get a serious kick up the rear....  It's invalid 
as it ships standard with the distribution tarball, and I'm possitive, it 
will confuse the living daylight out of users that is not so experienced....

--
C