CDRtool freeradius mysql error

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

CDRtool freeradius mysql error

toqeer ali
Hi all,


I successfully configured Freeradius and CDRtool but when i accesses the CDRs in CDRtool webinterface   i got this error ... it can't get Normalized field in radacct table of Freeradius...

Please help
 Bellow is the error i got...

"Database error: Invalid SQL: select count(*) as c from radacct where (AcctStartTime >= '2009-11-21 07:06' and AcctStartTime < '2009-11-21 23:55') 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)) MySQL error: 1054 (Unknown column 'Normalized' in 'where clause')"

--
Toqeer Ali Syed

Red Hat Certified Engineer
mob:     +92 321 9059916

_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: CDRtool freeradius mysql error

Laszlo
Hi,

2009/11/21 toqeer ali <[hidden email]>
Hi all,


I successfully configured Freeradius and CDRtool but when i accesses the CDRs in CDRtool webinterface   i got this error ... it can't get Normalized field in radacct table of Freeradius...

Please help
 Bellow is the error i got...

"Database error: Invalid SQL: select count(*) as c from radacct where (AcctStartTime >= '2009-11-21 07:06' and AcctStartTime < '2009-11-21 23:55') 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)) MySQL error: 1054 (Unknown column 'Normalized' in 'where clause')"

--
Toqeer Ali Syed

Red Hat Certified Engineer
mob:     +92 321 9059916
 


Did you create the radacct table properly?

Check out /path/to/CDRTool/setup/radius/OpenSIPS/radacct.mysql
It should look like that.

-Laszlo


_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: CDRtool freeradius mysql error

osiris123d
In reply to this post by toqeer ali
I get the same error also.  My radius schema is correct and looks like this

mysql> desc radacct
    -> ;
+-------------------------+----------------------+------+-----+---------------------+----------------+
| Field                   | Type                 | Null | Key | Default             | Extra          |
+-------------------------+----------------------+------+-----+---------------------+----------------+
| RadAcctId               | bigint(21)           | NO   | PRI | NULL                | auto_increment |
| AcctSessionId           | varchar(255)         | NO   | MUL |                     |                |
| AcctUniqueId            | varchar(255)         | NO   | MUL |                     |                |
| UserName                | varchar(64)          | NO   | MUL |                     |                |
| Realm                   | varchar(64)          | YES  | MUL |                     |                |
| NASIPAddress            | varchar(15)          | NO   | MUL |                     |                |
| NASPortId               | varchar(50)          | NO   |     |                     |                |
| NASPortType             | varchar(255)         | NO   |     |                     |                |
| AcctStartTime           | datetime             | NO   | MUL | 0000-00-00 00:00:00 |                |
| AcctStopTime            | datetime             | NO   | MUL | 0000-00-00 00:00:00 |                |
| AcctSessionTime         | int(12)              | YES  |     | NULL                |                |
| AcctAuthentic           | varchar(32)          | YES  |     | NULL                |                |
| ConnectInfo_start       | varchar(32)          | YES  |     | NULL                |                |
| ConnectInfo_stop        | varchar(32)          | YES  |     | NULL                |                |
| AcctInputOctets         | bigint(12)           | YES  |     | NULL                |                |
| AcctOutputOctets        | bigint(12)           | YES  |     | NULL                |                |
| CalledStationId         | varchar(50)          | NO   | MUL |                     |                |
| CallingStationId        | varchar(50)          | NO   | MUL |                     |                |
| AcctTerminateCause      | varchar(32)          | NO   |     |                     |                |
| ServiceType             | varchar(32)          | YES  |     | NULL                |                |
| ENUMtld                 | varchar(64)          | YES  |     | NULL                |                |
| FramedIPAddress         | varchar(15)          | NO   |     |                     |                |
| AcctStartDelay          | int(12)              | YES  |     | NULL                |                |
| AcctStopDelay           | int(12)              | YES  |     | NULL                |                |
| SipMethod               | varchar(50)          | NO   |     |                     |                |
| SipResponseCode         | smallint(5) unsigned | NO   |     | 0                   |                |
| SipToTag                | varchar(128)         | NO   |     |                     |                |
| SipFromTag              | varchar(128)         | NO   |     |                     |                |
| SipTranslatedRequestURI | varchar(255)         | NO   | MUL |                     |                |
| SipUserAgents           | varchar(255)         | NO   |     |                     |                |
| SipApplicationType      | varchar(255)         | NO   |     |                     |                |
| SipCodecs               | varchar(255)         | NO   |     |                     |                |
| SipRPID                 | varchar(255)         | NO   |     |                     |                |
| SipRPIDHeader           | varchar(255)         | NO   |     |                     |                |
| SourceIP                | varchar(255)         | NO   | MUL |                     |                |
| SourcePort              | varchar(255)         | NO   |     |                     |                |
| CanonicalURI            | varchar(255)         | NO   | MUL |                     |                |
| DelayTime               | varchar(5)           | NO   |     |                     |                |
| Timestamp               | bigint(20)           | NO   |     | 0                   |                |
| DestinationId           | varchar(15)          | NO   | MUL |                     |                |
| Rate                    | text                 | NO   |     | NULL                |                |
| Price                   | double(20,4)         | YES  |     | NULL                |                |
| Normalized              | enum('0','1')        | YES  | MUL | 0                   |                |
| BillingId               | varchar(255)         | NO   | MUL |                     |                |
| MediaInfo               | varchar(32)          | YES  | MUL | NULL                |                |
| RTPStatistics           | text                 | NO   |     | NULL                |                |
| FromHeader              | varchar(128)         | NO   |     |                     |                |
| UserAgent               | varchar(128)         | NO   |     |                     |                |
| Contact                 | varchar(128)         | NO   |     |                     |                |
+-------------------------+----------------------+------+-----+---------------------+----------------+
49 rows in set (0.00 sec)



If I try and start up freeradius I get the following error at the bottom and it fails to start

CDRTool:/var/log# freeradius -X
Starting - reading configuration files ...
reread_config:  reading radiusd.conf
Config:   including file: /etc/freeradius/proxy.conf
Config:   including file: /etc/freeradius/clients.conf
Config:   including file: /etc/freeradius/snmp.conf
Config:   including file: /etc/freeradius/sql.conf
 main: prefix = "/usr"
 main: localstatedir = "/var"
 main: logdir = "/var/log/freeradius"
 main: libdir = "/usr/lib/freeradius"
 main: radacctdir = "/var/log/freeradius/radacct"
 main: hostname_lookups = no
 main: snmp = no
 main: max_request_time = 6
 main: cleanup_delay = 5
 main: max_requests = 1024
 main: delete_blocked_requests = 0
 main: port = 0
 main: allow_core_dumps = no
 main: log_stripped_names = no
 main: log_file = "/var/log/freeradius/radius.log"
 main: log_auth = no
 main: log_auth_badpass = no
 main: log_auth_goodpass = no
 main: pidfile = "/var/run/freeradius/freeradius.pid"
 main: user = "freerad"
 main: group = "freerad"
 main: usercollide = no
 main: lower_user = "no"
 main: lower_pass = "no"
 main: nospace_user = "no"
 main: nospace_pass = "no"
 main: checkrad = "/usr/sbin/checkrad"
 main: proxy_requests = yes
 proxy: retry_delay = 1
 proxy: retry_count = 5
 proxy: synchronous = no
 proxy: default_fallback = yes
 proxy: dead_time = 120
 proxy: post_proxy_authorize = yes
 proxy: wake_all_if_all_dead = no
 security: max_attributes = 200
 security: reject_delay = 1
 security: status_server = no
 main: debug_level = 0
read_config_files:  reading dictionary
read_config_files:  reading naslist
Using deprecated naslist file.  Support for this will go away soon.
read_config_files:  reading clients
read_config_files:  reading realms
/etc/freeradius/radiusd.conf[10]: Host radiusp not found

No where in my radiusd.conf file does it say anything about a host radiusp.  What is this for?





toqeer ali wrote
Hi all,


I successfully configured Freeradius and CDRtool but when i accesses the
CDRs in CDRtool webinterface   i got this error ... it can't get Normalized
field in radacct table of Freeradius...

Please help
 Bellow is the error i got...

"Database error: Invalid SQL: select count(*) as c from radacct where
(AcctStartTime >= '2009-11-21 07:06' and AcctStartTime < '2009-11-21 23:55')
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)) MySQL error:
1054 (Unknown column 'Normalized' in 'where clause')"

--
Toqeer Ali Syed

Red Hat Certified Engineer
mob:     +92 321 9059916

_______________________________________________
Users mailing list
Users@lists.opensips.org
http://lists.opensips.org/cgi-bin/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: CDRtool freeradius mysql error

osiris123d
OK.  I am now able to start Freeradius.  I had to edit the /etc/freeradius/proxy.conf file and rename the radiusp to be the IP of my freeradius box, but I am still getting the same MySQL error


Dec 18 13:45:03 CDRTool cdrtool[1089]: Checking user quotas for data source OpenSIPS Proxy/Registrar
Dec 18 13:45:03 CDRTool cdrtool[1089]: Using database queries to block accounts
Dec 18 13:45:03 CDRTool cdrtool[1089]: Normalize lock id 155 aquired for opensips_radius:radacct200912
Dec 18 13:45:03 CDRTool cdrtool[1089]: Init quota of data source opensips_radius for all accounts
Dec 18 13:45:03 CDRTool cdrtool[1089]: 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 radacct200912#012        where AcctStartTime >= '2009-12-01 00:00'#012        and Normalized = '1'#012        and Realm in ('
irock.com','coolbeans.com') #012        #012        group by UserName#012
Dec 18 13:45:03 CDRTool cdrtool[1089]: 63
Dec 18 13:45:03 CDRTool cdrtool[1089]: Unlock opensips_radius:radacct200912
Dec 18 13:45:04 CDRTool cdrtool[1090]: Normalize datasource opensips_radius, database DB_radius, table radacct200912
Dec 18 13:45:04 CDRTool cdrtool[1090]: Normalize lock id 156 aquired for opensips_radius:radacct200912
Dec 18 13:45:04 CDRTool cdrtool[1090]: Database error: Invalid SQL: select *, UNIX_TIMESTAMP(AcctStartTime) as timestamp#012        
from radacct200912 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))
Dec 18 13:45:04 CDRTool cdrtool[1090]: 63
Dec 18 13:45:04 CDRTool cdrtool[1090]: Unlock opensips_radius:radacct200912





I installed Freeradius by using AG-Projects
apt-get install freeradius-xs freeradius-xs-mysql

So Freeradius should have all the required patches.


Any help?


osiris123d wrote
I get the same error also.  My radius schema is correct and looks like this

mysql> desc radacct
    -> ;
+-------------------------+----------------------+------+-----+---------------------+----------------+
| Field                   | Type                 | Null | Key | Default             | Extra          |
+-------------------------+----------------------+------+-----+---------------------+----------------+
| RadAcctId               | bigint(21)           | NO   | PRI | NULL                | auto_increment |
| AcctSessionId           | varchar(255)         | NO   | MUL |                     |                |
| AcctUniqueId            | varchar(255)         | NO   | MUL |                     |                |
| UserName                | varchar(64)          | NO   | MUL |                     |                |
| Realm                   | varchar(64)          | YES  | MUL |                     |                |
| NASIPAddress            | varchar(15)          | NO   | MUL |                     |                |
| NASPortId               | varchar(50)          | NO   |     |                     |                |
| NASPortType             | varchar(255)         | NO   |     |                     |                |
| AcctStartTime           | datetime             | NO   | MUL | 0000-00-00 00:00:00 |                |
| AcctStopTime            | datetime             | NO   | MUL | 0000-00-00 00:00:00 |                |
| AcctSessionTime         | int(12)              | YES  |     | NULL                |                |
| AcctAuthentic           | varchar(32)          | YES  |     | NULL                |                |
| ConnectInfo_start       | varchar(32)          | YES  |     | NULL                |                |
| ConnectInfo_stop        | varchar(32)          | YES  |     | NULL                |                |
| AcctInputOctets         | bigint(12)           | YES  |     | NULL                |                |
| AcctOutputOctets        | bigint(12)           | YES  |     | NULL                |                |
| CalledStationId         | varchar(50)          | NO   | MUL |                     |                |
| CallingStationId        | varchar(50)          | NO   | MUL |                     |                |
| AcctTerminateCause      | varchar(32)          | NO   |     |                     |                |
| ServiceType             | varchar(32)          | YES  |     | NULL                |                |
| ENUMtld                 | varchar(64)          | YES  |     | NULL                |                |
| FramedIPAddress         | varchar(15)          | NO   |     |                     |                |
| AcctStartDelay          | int(12)              | YES  |     | NULL                |                |
| AcctStopDelay           | int(12)              | YES  |     | NULL                |                |
| SipMethod               | varchar(50)          | NO   |     |                     |                |
| SipResponseCode         | smallint(5) unsigned | NO   |     | 0                   |                |
| SipToTag                | varchar(128)         | NO   |     |                     |                |
| SipFromTag              | varchar(128)         | NO   |     |                     |                |
| SipTranslatedRequestURI | varchar(255)         | NO   | MUL |                     |                |
| SipUserAgents           | varchar(255)         | NO   |     |                     |                |
| SipApplicationType      | varchar(255)         | NO   |     |                     |                |
| SipCodecs               | varchar(255)         | NO   |     |                     |                |
| SipRPID                 | varchar(255)         | NO   |     |                     |                |
| SipRPIDHeader           | varchar(255)         | NO   |     |                     |                |
| SourceIP                | varchar(255)         | NO   | MUL |                     |                |
| SourcePort              | varchar(255)         | NO   |     |                     |                |
| CanonicalURI            | varchar(255)         | NO   | MUL |                     |                |
| DelayTime               | varchar(5)           | NO   |     |                     |                |
| Timestamp               | bigint(20)           | NO   |     | 0                   |                |
| DestinationId           | varchar(15)          | NO   | MUL |                     |                |
| Rate                    | text                 | NO   |     | NULL                |                |
| Price                   | double(20,4)         | YES  |     | NULL                |                |
| Normalized              | enum('0','1')        | YES  | MUL | 0                   |                |
| BillingId               | varchar(255)         | NO   | MUL |                     |                |
| MediaInfo               | varchar(32)          | YES  | MUL | NULL                |                |
| RTPStatistics           | text                 | NO   |     | NULL                |                |
| FromHeader              | varchar(128)         | NO   |     |                     |                |
| UserAgent               | varchar(128)         | NO   |     |                     |                |
| Contact                 | varchar(128)         | NO   |     |                     |                |
+-------------------------+----------------------+------+-----+---------------------+----------------+
49 rows in set (0.00 sec)



If I try and start up freeradius I get the following error at the bottom and it fails to start

CDRTool:/var/log# freeradius -X
Starting - reading configuration files ...
reread_config:  reading radiusd.conf
Config:   including file: /etc/freeradius/proxy.conf
Config:   including file: /etc/freeradius/clients.conf
Config:   including file: /etc/freeradius/snmp.conf
Config:   including file: /etc/freeradius/sql.conf
 main: prefix = "/usr"
 main: localstatedir = "/var"
 main: logdir = "/var/log/freeradius"
 main: libdir = "/usr/lib/freeradius"
 main: radacctdir = "/var/log/freeradius/radacct"
 main: hostname_lookups = no
 main: snmp = no
 main: max_request_time = 6
 main: cleanup_delay = 5
 main: max_requests = 1024
 main: delete_blocked_requests = 0
 main: port = 0
 main: allow_core_dumps = no
 main: log_stripped_names = no
 main: log_file = "/var/log/freeradius/radius.log"
 main: log_auth = no
 main: log_auth_badpass = no
 main: log_auth_goodpass = no
 main: pidfile = "/var/run/freeradius/freeradius.pid"
 main: user = "freerad"
 main: group = "freerad"
 main: usercollide = no
 main: lower_user = "no"
 main: lower_pass = "no"
 main: nospace_user = "no"
 main: nospace_pass = "no"
 main: checkrad = "/usr/sbin/checkrad"
 main: proxy_requests = yes
 proxy: retry_delay = 1
 proxy: retry_count = 5
 proxy: synchronous = no
 proxy: default_fallback = yes
 proxy: dead_time = 120
 proxy: post_proxy_authorize = yes
 proxy: wake_all_if_all_dead = no
 security: max_attributes = 200
 security: reject_delay = 1
 security: status_server = no
 main: debug_level = 0
read_config_files:  reading dictionary
read_config_files:  reading naslist
Using deprecated naslist file.  Support for this will go away soon.
read_config_files:  reading clients
read_config_files:  reading realms
/etc/freeradius/radiusd.conf[10]: Host radiusp not found

No where in my radiusd.conf file does it say anything about a host radiusp.  What is this for?





toqeer ali wrote
Hi all,


I successfully configured Freeradius and CDRtool but when i accesses the
CDRs in CDRtool webinterface   i got this error ... it can't get Normalized
field in radacct table of Freeradius...

Please help
 Bellow is the error i got...

"Database error: Invalid SQL: select count(*) as c from radacct where
(AcctStartTime >= '2009-11-21 07:06' and AcctStartTime < '2009-11-21 23:55')
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)) MySQL error:
1054 (Unknown column 'Normalized' in 'where clause')"

--
Toqeer Ali Syed

Red Hat Certified Engineer
mob:     +92 321 9059916

_______________________________________________
Users mailing list
Users@lists.opensips.org
http://lists.opensips.org/cgi-bin/mailman/listinfo/users