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

[Alan DeKok]

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