Patch for raddb/sql/mysql/ippool.conf

[Chris Moules]

Alan DeKok wrote:
> Chris Moules wrote:
>> Sorry this is not a GIT patch.
>>
>> Here are two minor fixes to the MySQL ippool.conf file.
>>
>> 1) A Syntax correction for the alternative 'allocate-find' query.
> 
>   Uh... which version of MySQL are you using?  The examples were changed
> FROM "is null" TO '= null" in January.

Sorry for a late-ish reply, I have been away.

I am running MySQL 5.0.51a.

I know that some of the examples have been changes, I believe that I had posted some corrections to this before. I had just 
noted that one of the non-default queries still had the 'incorrect' syntax:

(Copy / paste from patch in previous mail)
  # allocate-find = "SELECT framedipaddress FROM ${ippool_table} \
  #  WHERE pool_name = '%{control:Pool-Name}' \
-#  AND expiry_time = NULL \
+#  AND expiry_time IS NULL \
  #  ORDER BY RAND() \
  #  LIMIT 1 \
  #  FOR UPDATE"

MySQL is a little odd in it's usage of 'NULL' data. As far as I understand things MySQL uses different syntax for the assignment 
and checking of the NULL value:

In a "SET" part of the statement you use the "=" operator. EG "UPDATE mytable SET value = NULL".
In the "WHERE" part of a statement you use the "IS NULL" or "IS NOT NULL" syntax.
   EG "SELECT * from mytable WHERE value IS NULL".

Documentation (Link to latest v6.0 docs, also the same for 5.0 and 5.1):
<http://dev.mysql.com/doc/refman/6.0/en/non-typed-operators.html>

The query "SELECT NULL = NULL" returns "NULL" not "TRUE" or "1" so no sensible logic can be based on it. Reading the docs I also 
saw the "NULL-safe equal to operator", ("<=>"), that seems to do what you would expect...

I hope the makes (MySQL) sense. So yes, there is both "= NULL" and "IS NULL" syntax, just the appropriate one needs to be use in 
the appropriate place.

> 
>> 2) There was an issue with the 'start-update' query updating more than
>> it should.
>> I am using IMSI codes as my pool-key. In the case that a phone
>> disconnects, but no Acct-Stop is received the expiry_time is updated
>> every time that this phone reconnects as only the NAS and the pool-key
>> are used as update limiters.
>>
>> By including at least the framedipaddress this is solved.
> 
>   OK.
> 
>> As both the 'stop-clear' and 'alive-update' sections are using the 3
>> additional qualifiers, it makes sense to use them here too. (I see no
>> reason no to).
> 
>   Looks good to me.

Cool.

> 
>   Alan DeKok.
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/devel.html
> 

Chris