On Sun 27 Aug 2006 01:47, Chris Knipe wrote:
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@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.
It is very unlikely that it will work with MySQL. It is designed for Postgresql. -- Peter Nixon http://www.peternixon.net/ PGP Key: http://www.peternixon.net/public.asc