problem with mysql accounting

[basile]

hi
i try to account with mysql
here are the mysql start stop and update definition

accounting_start_query = INSERT into radaact SET\
`User-Name` = '%{User-Name}',\
`Calling-Station-Id` = '%{Calling-Station-Id}',\
`Called-Station-Id` = '%{Called-Station-Id}',\
`NAS-IP-Address` = '%{NAS-IP-Address}',\
`NAS-Port` = %{NAS-Port},\
`Timestamp Start` = NOW(),\
`Acct-Unique-Session-Id` = '%{Acct-Unique-Session-Id}'


accounting_update_query = UPDATE radaact SET\
`Acct-Session-Time` = '%{Acct-Session-Time}',\
`Acct-Input-Octets` = '%{Acct-Input-Octets}',\
`Acct-Output-Octets` = '%{Acct-Output-Octets}',\
`Acct-Input-Packets` = '%{Acct-Input-Packets}',\
`Acct-Output-Packets` = '%{Acct-Output-Packets}'\
WHERE `Acct-Unique-Session-Id` = '%{Acct-Unique-Session-Id}'\
LIMIT 1


accounting_stop_query = UPDATE radaact SET\
`Timestamp Stop` = NOW(),\
`Acct-Session-Time` = '%{Acct-Session-Time}',\
`Acct-Input-Octets` = '%{Acct-Input-Octets}',\
`Acct-Output-Octets` = '%{Acct-Output-Octets}',\
`Acct-Input-Packets` = '%{Acct-Input-Packets}',\
`Acct-Output-Packets` = '%{Acct-Output-Packets}',\
`Acct-Terminate-Cause` = '%{Acct-Terminate-Cause}'\
WHERE `Acct-Unique-Session-Id` = '%{Acct-Unique-Session-Id}'\
LIMIT 1


update works fine

rlm_sql (sql): sql_set_user escaped user -- ''
radius_xlat:  'UPDATE radaact SET??`Acct-Session-Time` =
'292',??`Acct-Input-Octets` = '94237',??`Acct-Output-Octets` =
'937628',??`Acct-Input-Packets` = '597',??`Acct-Output-Packets` =
'816'?WHERE `Acct-Unique-Session-Id` = '814b38bc0e9c60f4'?LIMIT 1 '
rlm_sql (sql): Reserving sql socket id: 2
radius_xlat:  ''
rlm_sql (sql): Released sql socket id: 2
  modcall[accounting]: module sql returns ok for request 61
modcall: leaving group accounting (returns ok) for request 61

but start and stop not

rlm_sql (sql): sql_set_user escaped user -- ''
radius_xlat:  'INSERT into radaact SET  '
rlm_sql (sql): Reserving sql socket id: 4
rlm_sql_mysql: MYSQL check_error: 1064 received
rlm_sql (sql): Couldn't insert SQL accounting START record - You have an
error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near '' at line 1
radius_xlat:  ''
rlm_sql (sql): Released sql socket id: 4
  modcall[accounting]: module sql returns ok for request 59
modcall: leaving group accounting (returns ok) for request 59


rlm_sql (sql): sql_set_user escaped user -- ''
radius_xlat:  'UPDATE radaact SET  '
rlm_sql (sql): Reserving sql socket id: 3
rlm_sql_mysql: MYSQL check_error: 1064 received
rlm_sql (sql): Couldn't update SQL accounting STOP record - You have an
error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near '' at line 1
rlm_sql (sql): Released sql socket id: 3
  modcall[accounting]: module sql returns fail for request 60
modcall: leaving group accounting (returns fail) for request 60

i use mysql 4.1 , freeradius 1.1

thanks for help
basile

- 
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

Re: problem with mysql accounting

[Kevin Bonner]

On Thursday 21 December 2006 06:45, basile wrote:
 but start and stop not

 rlm_sql (sql): sql_set_user escaped user -- ''
 radius_xlat:  'INSERT into radaact SET  '
 rlm_sql (sql): Reserving sql socket id: 4
 rlm_sql_mysql: MYSQL check_error: 1064 received

When you run in debug mode, do you see the full query when the config options 
are printed?  If not, there might be an escaping problem in your sql.conf 
file.

 freeradius 1.1

What freeradius version is that?  1.1.3 is the latest release.

Kevin Bonner


pgppSBvWYbfco.pgp
Description: PGP signature
- 
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

Re: problem with mysql accounting

[Dennis Skinner]

basile wrote:
 accounting_start_query = INSERT into radaact SET\
 `User-Name` = '%{User-Name}',\
 `Calling-Station-Id` = '%{Calling-Station-Id}',\
 `Called-Station-Id` = '%{Called-Station-Id}',\
 `NAS-IP-Address` = '%{NAS-IP-Address}',\
 `NAS-Port` = %{NAS-Port},\
 `Timestamp Start` = NOW(),\
 `Acct-Unique-Session-Id` = '%{Acct-Unique-ession-Id}'
 

Any particular reason you are using backticks (`) instead of single
quotes (') around the column names?  I'm not sure if they have special
meanings in radius/mysql.

You should also ensure that the Acct-Unique-Session-Id is truly unique
when you UPDATE.  Often NAS's will reuse supposedly unique id's.  Esp if
they are rebooted.  Looking for a Timestamp Start within X hours of
NOW() may help you out there.  Otherwise you may update really old rows
and have sessions that look like they lasted 30 days or something.  We
ran into this problem.

-- 
Dennis Skinner
Systems Administrator
BlueFrog Internet
http://www.bluefrog.com
- 
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html