Multiple sql accounting queries

[Joe Holden]

Fajar A. Nugraha wrote:
> On Sun, Mar 11, 2012 at 11:05 AM, Joe Holden <lists at rewt.org.uk> wrote:
>> Hi guys,
>>
>> I'm looking into the best way to implement multiple sql queries for
>> accounting... what is the "proper" way of doing this (besides evil sql
>> magic),
> 
> stored procedure works fine.
> 
>> can I define another sql instance?
> 
> yes, but I wouldn't do that
> 
>> How do I go about mapping queries
>> to that etc?
> 
> If you have two sql instances (e.g. sql1 and sql2) on accounting
> section, queries from both of them will be executed. However it's not
> atomic. It's possible for example:
> - sql1 is executed
> - sql2 executed, but it fails (due to some temporary database error)
> - accounting section will fail. depending on the setup, the client
> would usually resend the acct packet. Or (in detail reader setup) the
> same line will be read again.
> 
> That could mean sql1 is executed multiple times while sql2 is only
> executed once for the same acct packet. Or sql2 not executed at all
> (if the db error is persistent).
> 
> That's why I'd rather use stored procedure instead.
> 
Thanks for your response, not sure if a stored procedure will work in 
this case as the second query is for a seperate table with non-relevant 
data. Is it possible to define a second query (mysqld/radiusd is on the 
same box, so if one query fails the other is likely to as well) but 
return OK regardless of whether it completes or not, the completion of 
the query isn't as critical as accurate accounting data.

Thanks,
J