[OpenSIPS-Users] CDRtool freeradius mysql error
osiris123d
duane.larson at gmail.com
Tue Jan 12 00:51:55 CET 2010
I am still not able to get CDRTool to work because it never creates the
months radacct table in the radius database. I have installed everything on
Debian 5.0.3 by just using the following repositories
# AG Projects software
deb http://ag-projects.com/debian unstable main
deb-src http://ag-projects.com/debian unstable main
deb http://ftp.us.debian.org/debian/ unstable main
deb-src http://ftp.us.debian.org/debian/ unstable main
This is all I see in the log files. Nowhere do I see it ever trying to
create the database for the month
MySQL Log
64 Query select `value` from memcache where `key` =
'destinations_sip'
64 Query insert into memcache (`key`,`value`) values
('destinations_sip','
')
100111 17:29:07 64 Query select * from billing_enum_tlds
65 Query SHOW TABLES
64 Init DB cdrtool
64 Query select * from billing_profiles order by name
64 Query select *,
UNIX_TIMESTAMP(startDate) as startDateTimestamp,
UNIX_TIMESTAMP(endDate) as endDateTimestamp
from billing_rates_history
order by name ASC,destination ASC,startDate DESC
64 Query select * from billing_holidays order by day
64 Query select * from billing_enum_tlds
65 Quit
64 Quit
100111 17:30:01 66 Connect radius at CDRTool.a.com on
66 Init DB radius
66 Query SELECT 1
67 Connect cdradmin at CDRTool.a.com on
67 Init DB cdrtool
67 Query select * from sip_status order by code
67 Query select `value` from memcache where `key` =
'destinations'
68 Connect radius at CDRTool.a.com on
68 Init DB radius
68 Query SELECT 1
69 Connect cdradmin at CDRTool.a.com on
100111 17:30:02 69 Init DB cdrtool
69 Query select * from sip_status order by code
69 Query select `value` from memcache where `key` =
'destinations'
67 Query select `value` from memcache where `key` =
'destinations_sip'
69 Query select `value` from memcache where `key` =
'destinations_sip'
67 Query select * from billing_enum_tlds
69 Query select * from billing_enum_tlds
100111 17:30:03 66 Query SHOW TABLES
68 Query SHOW TABLES
70 Connect locker at CDRTool.a.com on
70 Init DB cdrtool
70 Query SELECT
GET_LOCK('opensips_radius:radacct201001',0)
70 Query SELECT
IS_USED_LOCK('opensips_radius:radacct201001')
68 Query select *, UNIX_TIMESTAMP(AcctStartTime) as
timestamp
from radacct201001 where (1=1) and Normalized = '0' and
AcctStopTime != '0000-00-00 00:00:00' and (ConnectInfo_stop is n
ot NULL or MediaInfo is NULL or MediaInfo != '' or (UNIX_TIMESTAMP(NOW()) -
UNIX_TIMESTAMP(AcctStopTime) > 20))
70 Query SELECT
RELEASE_LOCK('opensips_radius:radacct201001')
69 Quit
70 Quit
68 Quit
67 Init DB cdrtool
67 Query select * from settings where var_module =
'notifications'
67 Query select value from memcache where `key` =
'opensips_radius:quotaCheckInit'
71 Connect locker at CDRTool.a.com on
71 Init DB cdrtool
71 Query SELECT
GET_LOCK('opensips_radius:radacct201001',0)
71 Query SELECT
IS_USED_LOCK('opensips_radius:radacct201001')
67 Query select value from memcache where `key` =
'opensips_radius:reset_quota_for'
67 Query delete from quota_usage where datasource =
'opensips_radius'
66 Query select UserName,
count(*) as calls,
sum(AcctSessionTime) as duration,
sum(Price) as cost,
sum(AcctInputOctets + AcctOutputOctets)/2 as traffic
from radacct201001
where AcctStartTime >= '2010-01-01 00:00'
and Normalized = '1'
and Realm in ('irock.com','coolbeans.com')
group by UserName
71 Query SELECT
RELEASE_LOCK('opensips_radius:radacct201001')
71 Quit
67 Quit
66 Quit
67 Init DB cdrtool
67 Query select * from settings where var_module =
'notifications'
67 Query select value from memcache where `key` =
'opensips_radius:quotaCheckInit'
71 Connect locker at CDRTool.a.com on
71 Init DB cdrtool
71 Query SELECT
GET_LOCK('opensips_radius:radacct201001',0)
71 Query SELECT
IS_USED_LOCK('opensips_radius:radacct201001')
67 Query select value from memcache where `key` =
'opensips_radius:reset_quota_for'
67 Query delete from quota_usage where datasource =
'opensips_radius'
66 Query select UserName,
count(*) as calls,
sum(AcctSessionTime) as duration,
sum(Price) as cost,
sum(AcctInputOctets + AcctOutputOctets)/2 as traffic
from radacct201001
where AcctStartTime >= '2010-01-01 00:00'
and Normalized = '1'
and Realm in ('irock.com','coolbeans.com')
group by UserName
71 Query SELECT
RELEASE_LOCK('opensips_radius:radacct201001')
71 Quit
67 Quit
66 Quit
100111 17:35:02 72 Connect radius at CDRTool.a.com on
72 Init DB radius
72 Query SELECT 1
74 Connect cdradmin at CDRTool.a.com on
74 Init DB cdrtool
74 Query select * from sip_status order by code
74 Query select `value` from memcache where `key` =
'destinations'
73 Connect radius at CDRTool.a.com on
73 Init DB radius
73 Query SELECT 1
75 Connect cdradmin at CDRTool.a.com on
75 Init DB cdrtool
75 Query select * from sip_status order by code
75 Query select `value` from memcache where `key` =
'destinations'
100111 17:35:03 74 Query select `value` from memcache where `key` =
'destinations_sip'
75 Query select `value` from memcache where `key` =
'destinations_sip'
74 Query select * from billing_enum_tlds
75 Query select * from billing_enum_tlds
100111 17:35:04 72 Query SHOW TABLES
73 Query SHOW TABLES
76 Connect locker at CDRTool.a.com on
76 Init DB cdrtool
76 Query SELECT
GET_LOCK('opensips_radius:radacct201001',0)
76 Query SELECT
IS_USED_LOCK('opensips_radius:radacct201001')
73 Query select *, UNIX_TIMESTAMP(AcctStartTime) as
timestamp
from radacct201001 where (1=1) and Normalized = '0' and
AcctStopTime != '0000-00-00 00:00:00' and (ConnectInfo_stop is n
ot NULL or MediaInfo is NULL or MediaInfo != '' or (UNIX_TIMESTAMP(NOW()) -
UNIX_TIMESTAMP(AcctStopTime) > 20))
76 Query SELECT
RELEASE_LOCK('opensips_radius:radacct201001')
76 Quit
75 Quit
73 Quit
74 Init DB cdrtool
74 Query select * from settings where var_module =
'notifications'
74 Query select value from memcache where `key` =
'opensips_radius:quotaCheckInit'
77 Connect locker at CDRTool.a.com on
77 Init DB cdrtool
77 Query SELECT
GET_LOCK('opensips_radius:radacct201001',0)
77 Query SELECT
IS_USED_LOCK('opensips_radius:radacct201001')
74 Query select value from memcache where `key` =
'opensips_radius:reset_quota_for'
74 Query delete from quota_usage where datasource =
'opensips_radius'
72 Query select UserName,
count(*) as calls,
sum(AcctSessionTime) as duration,
sum(Price) as cost,
sum(AcctInputOctets + AcctOutputOctets)/2 as traffic
from radacct201001
where AcctStartTime >= '2010-01-01 00:00'
and Normalized = '1'
and Realm in ('irock.com','coolbeans.com')
group by UserName
77 Query SELECT
RELEASE_LOCK('opensips_radius:radacct201001')
77 Quit
74 Quit
72 Quit
100111 17:37:23 78 Connect radius at CDRTool.a.com on radius
79 Connect radius at CDRTool.a.com on radius
80 Connect radius at CDRTool.a.com on radius
81 Connect radius at CDRTool.a.com on radius
82 Connect radius at CDRTool.a.com on radius
83 Connect radius at CDRTool.a.com on radius
84 Connect radius at CDRTool.a.com on radius
85 Connect radius at CDRTool.a.com on radius
86 Connect radius at CDRTool.a.com on radius
87 Connect radius at CDRTool.a.com on radius
88 Connect radius at CDRTool.a.com on radius
89 Connect radius at CDRTool.a.com on radius
90 Connect radius at CDRTool.a.com on radius
91 Connect radius at CDRTool.a.com on radius
92 Connect radius at CDRTool.a.com on radius
93 Connect radius at CDRTool.a.com on radius
94 Connect radius at CDRTool.a.com on radius
95 Connect radius at CDRTool.a.com on radius
96 Connect radius at CDRTool.a.com on radius
97 Connect radius at CDRTool.a.com on radius
98 Connect radius at CDRTool.a.com on radius
99 Connect radius at CDRTool.a.com on radius
100 Connect radius at CDRTool.a.com on radius
101 Connect radius at CDRTool.a.com on radius
102 Connect radius at CDRTool.a.com on radius
Syslogs
Jan 11 17:29:06 CDRTool cdrtool[9187]: Starting CDRTool Rating Engine...
Jan 11 17:29:06 CDRTool cdrtool[9187]: Cached 12291 total, 12291 default, 0
gateway, 0 domain, 0 subscriber destinations
Jan 11 17:29:06 CDRTool cdrtool[9187]: Updated cache for 0 SIP destinations
Jan 11 17:29:07 CDRTool cdrtool[9187]: Memory usage: 6.30MB, memory limit:
32MB
Jan 11 17:29:07 CDRTool cdrtool[9187]: Loaded 1 profiles into memory
Jan 11 17:29:07 CDRTool cdrtool[9187]: Loaded 0 ratesHistory into memory
Jan 11 17:29:07 CDRTool cdrtool[9187]: Loaded 0 holidays into memory
Jan 11 17:29:07 CDRTool cdrtool[9187]: Loaded 1 enumTlds into memory
Jan 11 17:29:07 CDRTool cdrtool[9187]: Memory usage: 6.31MB, memory limit:
32MB
Jan 11 17:29:07 CDRTool cdrtool[9187]: Rating Engine started sucesfully,
going to background...
Jan 11 17:29:07 CDRTool cdrtool[9190]: Rating Engine listening on
12.x.x.218:9024
Jan 11 17:29:07 CDRTool cdrtool[9190]: Rating Engine is now ready to serve
network requests
Jan 11 17:30:03 CDRTool cdrtool[9214]: Checking user quotas for data source
OpenSIPS Proxy/Registrar
Jan 11 17:30:03 CDRTool cdrtool[9217]: Normalize datasource opensips_radius,
database DB_radius, table radacct201001
Jan 11 17:30:03 CDRTool cdrtool[9217]: Normalize lock id 70 aquired for
opensips_radius:radacct201001
Jan 11 17:30:03 CDRTool cdrtool[9217]: Database error: Invalid SQL: select
*, UNIX_TIMESTAMP(AcctStartTime) as timestamp#012
from radacct201001 where (1=1) and Normalized = '0' and AcctStopTime !=
'0000-00-00 00:00:00' and (ConnectInfo_stop is not NULL
or MediaInfo is NULL or MediaInfo != '' or (UNIX_TIMESTAMP(NOW()) -
UNIX_TIMESTAMP(AcctStopTime) > 20))
Jan 11 17:30:03 CDRTool cdrtool[9217]: 63
Jan 11 17:30:03 CDRTool cdrtool[9217]: Unlock opensips_radius:radacct201001
Jan 11 17:30:03 CDRTool cdrtool[9214]: Using database queries to block
accounts
Jan 11 17:30:03 CDRTool cdrtool[9214]: Normalize lock id 71 aquired for
opensips_radius:radacct201001
Jan 11 17:30:03 CDRTool cdrtool[9214]: Init quota of data source
opensips_radius for all accounts
Jan 11 17:30:03 CDRTool cdrtool[9214]: Database error: Invalid SQL: select
UserName,#012 count(*) as calls,#012 sum(Ac
ctSessionTime) as duration,#012 sum(Price) as cost,#012
sum(AcctInputOctets + AcctOutputOctets)/2 as traffic#012
from radacct201001#012 where AcctStartTime >= '2010-01-01
00:00'#012 and Normalized = '1'#012 and Realm in ('
irock.com','coolbeans.com') #012 #012 group by UserName#012
Jan 11 17:30:03 CDRTool cdrtool[9214]: 63
Jan 11 17:30:03 CDRTool cdrtool[9214]: Unlock opensips_radius:radacct201001
Radius Logs
Mon Jan 11 17:37:23 2010 : Info: Using deprecated naslist file. Support for
this will go away soon.
Mon Jan 11 17:37:23 2010 : Info: rlm_sql (sql): Driver rlm_sql_mysql (module
rlm_sql_mysql) loaded and linked
Mon Jan 11 17:37:23 2010 : Info: rlm_sql (sql): Attempting to connect to
radius at 12.x.x.218:/radius
Mon Jan 11 17:37:23 2010 : Info: rlm_sql_mysql: Starting connect to MySQL
server for #0
Mon Jan 11 17:37:23 2010 : Info: rlm_sql_mysql: Starting connect to MySQL
server for #1
Mon Jan 11 17:37:23 2010 : Info: rlm_sql_mysql: Starting connect to MySQL
server for #2
Mon Jan 11 17:37:23 2010 : Info: rlm_sql_mysql: Starting connect to MySQL
server for #3
Mon Jan 11 17:37:23 2010 : Info: rlm_sql_mysql: Starting connect to MySQL
server for #4
Mon Jan 11 17:37:23 2010 : Info: rlm_sql_mysql: Starting connect to MySQL
server for #5
Mon Jan 11 17:37:23 2010 : Info: rlm_sql_mysql: Starting connect to MySQL
server for #6
Mon Jan 11 17:37:23 2010 : Info: rlm_sql_mysql: Starting connect to MySQL
server for #7
Mon Jan 11 17:37:23 2010 : Info: rlm_sql_mysql: Starting connect to MySQL
server for #8
Mon Jan 11 17:37:23 2010 : Info: rlm_sql_mysql: Starting connect to MySQL
server for #9
Mon Jan 11 17:37:23 2010 : Info: rlm_sql_mysql: Starting connect to MySQL
server for #10
Mon Jan 11 17:37:23 2010 : Info: rlm_sql_mysql: Starting connect to MySQL
server for #11
Mon Jan 11 17:37:23 2010 : Info: rlm_sql_mysql: Starting connect to MySQL
server for #12
Mon Jan 11 17:37:23 2010 : Info: rlm_sql_mysql: Starting connect to MySQL
server for #13
Mon Jan 11 17:37:23 2010 : Info: rlm_sql_mysql: Starting connect to MySQL
server for #14
Mon Jan 11 17:37:23 2010 : Info: rlm_sql_mysql: Starting connect to MySQL
server for #15
Mon Jan 11 17:37:23 2010 : Info: rlm_sql_mysql: Starting connect to MySQL
server for #16
Mon Jan 11 17:37:23 2010 : Info: rlm_sql_mysql: Starting connect to MySQL
server for #17
Mon Jan 11 17:37:23 2010 : Info: rlm_sql_mysql: Starting connect to MySQL
server for #18
Mon Jan 11 17:37:23 2010 : Info: rlm_sql_mysql: Starting connect to MySQL
server for #19
Mon Jan 11 17:37:23 2010 : Info: rlm_sql_mysql: Starting connect to MySQL
server for #20
Mon Jan 11 17:37:23 2010 : Info: rlm_sql_mysql: Starting connect to MySQL
server for #21
Mon Jan 11 17:37:23 2010 : Info: rlm_sql_mysql: Starting connect to MySQL
server for #22
Mon Jan 11 17:37:23 2010 : Info: rlm_sql_mysql: Starting connect to MySQL
server for #23
Mon Jan 11 17:37:23 2010 : Info: rlm_sql_mysql: Starting connect to MySQL
server for #24
Mon Jan 11 17:37:23 2010 : Info: Ready to process requests.
As for when a call is made and hung up the only thing I really see is
radius logs
Mon Jan 11 17:46:10 2010 : Error: WARNING: Possible DoS attack from host
12.x.x.218: Too many attributes in request (received 201
, max 200 are allowed).
Mon Jan 11 17:46:15 2010 : Proxy: marking accounting server 12.x.x.218:1813
for realm DEFAULT dead
Mon Jan 11 17:46:15 2010 : Proxy: marking accounting server 12.x.x.218:1813
for realm DEFAULT dead
Mon Jan 11 17:46:15 2010 : Proxy: marking accounting server 12.x.x.218:1813
for realm DEFAULT dead
Mon Jan 11 17:46:15 2010 : Proxy: marking accounting server 12.x.x.218:1813
for realm DEFAULT dead
I have followed the install instructions correctly and have reinstalled many
times to no avail. Something is just not right.
osiris123d wrote:
>
> In my radiusd.conf I have the following
>
>
>
> accounting {
> detail
> sql
> ok
> }
>
> So that should be good.
>
> As for my /var/log/freeradius/radius.log I didn't see anything in the log
> except for starting and stopping freeradius logs. I did notice in the
> OpenSIPS logs that there was an error because it didn't recognize
> something. I found that I needed to uncomment the following in my
> dictionary file on OpenSIPS
> $INCLUDE /etc/radiusclient-ng/dictionary
>
> Once I did that noticed the following logs in
> /var/log/freeradius/radius.log
> Thu Dec 24 10:16:04 2009 : Error: Ignoring request from unknown client
> 12.*.*.218:1814
> Thu Dec 24 10:16:11 2009 : Error: Rejecting request 0 due to lack of any
> response from home server sip:59084
> Thu Dec 24 10:16:14 2009 : Error: Ignoring request from unknown client
> 12.*.*.218:1814
> Thu Dec 24 10:16:21 2009 : Error: Rejecting request 1 due to lack of any
> response from home server sip:59084
> Thu Dec 24 10:16:24 2009 : Error: Ignoring request from unknown client
> 12.*.*.218:1814
> Thu Dec 24 10:16:31 2009 : Error: Rejecting request 2 due to lack of any
> response from home server sip:59084
>
> So I had to install radiusclient-ng on the CDRTool server and set up the
> client.conf and servers file. After that I see the following logs in
> /var/log/freeradius/radius.log
> Sat Dec 26 00:25:11 2009 : Proxy: marking accounting server
> 12.*.*.218:1813 for realm DEFAULT dead
> Sat Dec 26 00:25:11 2009 : Proxy: marking accounting server
> 12.*.*.218:1813 for realm DEFAULT dead
> Sat Dec 26 00:25:11 2009 : Proxy: marking accounting server
> 12.*.*.218:1813 for realm DEFAULT dead
> Sat Dec 26 00:25:11 2009 : Proxy: marking accounting server
> 12.*.*.218:1813 for realm DEFAULT dead
> Sat Dec 26 00:25:11 2009 : Proxy: marking accounting server
> 12.*.*.218:1813 for realm DEFAULT dead
>
> I still have the same issue though. The radacctYYYYMM is still not being
> created.
>
>
>
> Saul Ibarra Corretge wrote:
>>
>> Hi,
>>
>> On 23/12/09 11:21 PM, osiris123d wrote:
>>>
>>> One more bit of info.
>>>
>>> If I start freeradius in debug mode I see the following
>>>
>>
>> Can you see anything unusual in the freeradius logs when a call is
>> accounted? Check /var/log/freeradius/
>>
>> Also, did you uncomment 'sql' from the accounting section in
>> radiusd.conf?
>>
>>
>> Regards,
>>
>>
>> --
>> Saúl Ibarra Corretgé
>> AG Projects
>>
>> _______________________________________________
>> Users mailing list
>> Users at lists.opensips.org
>> http://lists.opensips.org/cgi-bin/mailman/listinfo/users
>>
>>
>
>
--
View this message in context: http://n2.nabble.com/CDRtool-freeradius-mysql-error-tp4200490p4288931.html
Sent from the OpenSIPS - Users mailing list archive at Nabble.com.
More information about the Users
mailing list