IP Allocation FAILED : Best practice for inserting new IPs into mysql

[Sophie Loewenthal]

>  <shrug>  Use a database that works.

Upgrade is being planned.


On 20-Nov-15 3:47 PM, Alan DeKok wrote:
> On Nov 19, 2015, at 4:15 AM, Sophie Loewenthal <sophie.loewenthal at trimbletl.com> wrote:
>> Hi,
>>
>>     An update from this thread started on 06-Nov-15,
>>
>> I replaced the modified ,
>>   allocate-find = "SELECT framedipaddress FROM ${ippool_table} \
>>    WHERE pool_name = '%{control:Pool-Name}' \
>>    AND username = '%{User-Name}' LIMIT 1"
>    Which is wrong.  Because the whole reason for the "allocate-find" is to update the particular record.
>
>> with the packaged version,
>> allocate-find = "SELECT framedipaddress FROM ${ippool_table} \
>> WHERE pool_name = '%{control:Pool-Name}' \
>> AND expiry_time IS NULL \
>> ORDER BY RAND() \
>> LIMIT 1 \
>> FOR UPDATE"
>    Which is correct.  The "FOR UPDATE" is important, so that it doesn't allocate the same IP to two different users.
>> Wed Nov 18 14:12:43 2015 : Error: Discarding duplicate request from client vodafone-nas port 27968 - ID: 160 due to unfinished request 16110
>> Wed Nov 18 14:12:43 2015 : Error: [sql] Couldn't update SQL accounting STOP record - Deadlock found when trying to get lock; try restarting transaction
>> Wed Nov 18 14:12:43 2015 : Error: rlm_sql_mysql: Cannot store result
>> Wed Nov 18 14:12:43 2015 : Error: rlm_sql_mysql: MySQL error 'Deadlock found when trying to get lock; try restarting transaction'
>    <shrug>  Use a database that works.
>
>    Sorry.  But... if your database can't handle transactions like this, you should throw it in the garbage, and use a real database.  I suggest Postgresql.  I have *never* seen any issues like this with Postgresql.  Only with MySQL and it's derivatives.
>
>> When I rolled back the changes the duplicated request and deadlocking type messages disappeared.
>    Because the change allows for race conditions.  i.e. the same IP can get assigned to two different users.
>
>    Removing locks will make it faster.  But it will also make it wrong.
>
>    Alan DeKok
>
>
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html