Hello Michael -
The problem is due to the incorrect quoting of the string containing the "@" sign.
In most clauses based on SQL, %0 can be used for the username string and it will always be correctly quoted.
regards
Hugh
On Monday, Jul 21, 2003, at 20:24 Australia/Melbourne, Michael Bellears wrote:
Hi Hugh,
You should try the queries by hand to see what is happening.
Upon further investigation, it appears as though the '@' in the username is causing the problem:
Success without '@'
mysql> insert into RADUSAGE (ACCTTERMINATECAUSE,Connect_info,ACCTSESSIONTIME,TIME_STAMP,USERNAME,Cl ient_Phone_Number,FRAMEDIPADDRESS,ACCTDELAYTIME,DNIS,ACCTINPUTOCTETS,AC CTSTATUSTYPE,ACCTSESSIONID,ACCTOUTPUTOCTETS,NASPORT,NASIDENTIFIER) values (1,'31200 LAPM/ V42BIS',344121,1058742326,'connect_test','755798217','203.149.64.50',0, '55849500',28682053,2,'800006D0',89390039,3,'203.149.64.3');
Query OK, 1 row affected (0.01 sec)
mysql> select USERNAME,TIME_STAMP,ACCTSESSIONID,ACCTSESSIONTIME,FRAMEDIPADDRESS,NASID ENTIFIER,NASPORT,ACCTSTATUSTYPE from RADUSAGE where USERNAME = 'connect_test' order by TIME_STAMP;
+--------------+------------+---------------+----------------- +-----------------+---------------+---------+----------------+
| USERNAME | TIME_STAMP | ACCTSESSIONID | ACCTSESSIONTIME | FRAMEDIPADDRESS | NASIDENTIFIER | NASPORT | ACCTSTATUSTYPE |
+--------------+------------+---------------+----------------- +-----------------+---------------+---------+----------------+
| connect_test | 1058742326 | 800006D0 | 344121 | 203.149.64.50 | 203.149.64.3 | 3 | 2 |
+--------------+------------+---------------+----------------- +-----------------+---------------+---------+----------------+
1 row in set (0.00 sec)
Failure with '@'
mysql> insert into RADUSAGE (ACCTTERMINATECAUSE,Connect_info,ACCTSESSIONTIME,TIME_STAMP,USERNAME,Cl ient_Phone_Number,FRAMEDIPADDRESS,ACCTDELAYTIME,DNIS,ACCTINPUTOCTETS,AC CTSTATUSTYPE,ACCTSESSIONID,ACCTOUTPUTOCTETS,NASPORT,NASIDENTIFIER) values (1,'31200 LAPM/ V42BIS',344121,1058742326,'[EMAIL PROTECTED]','755798217', '203.149.64.50',0,'55849500',28682053,2,'800006D0',89390039,3,'203.149. 64.3');
Query OK, 1 row affected (0.00 sec)
mysql> select USERNAME,TIME_STAMP,ACCTSESSIONID,ACCTSESSIONTIME,FRAMEDIPADDRESS,NASID ENTIFIER,NASPORT,ACCTSTATUSTYPE from RADUSAGE where USERNAME = '[EMAIL PROTECTED]' order by TIME_STAMP;
Empty set (0.00 sec)
Insert with correct data, but removing the '@' from username:
mysql> insert into RADUSAGE (ACCTTERMINATECAUSE,ACCTSESSIONTIME,TIME_STAMP,USERNAME,Client_Phone_Nu mber,FRAMEDIPADDRESS,ACCTDELAYTIME,DNIS,ACCTINPUTOCTETS,ACCTSTATUSTYPE, ACCTOUTPUTOCTETS,ACCTSESSIONID,NASPORT,NASIDENTIFIER) values (18,21210,1058781354,'connect_test','atm 11','203.149.70.10',0,'3:2.261#184571819##speed:UBR:256#/ ',74104,2,77696,'00009813',1,'203.63.121.145');
Query OK, 1 row affected (0.00 sec)
mysql> select USERNAME,TIME_STAMP,ACCTSESSIONID,ACCTSESSIONTIME,FRAMEDIPADDRESS,NASID ENTIFIER,NASPORT,ACCTSTATUSTYPE from RADUSAGE where USERNAME = 'connect_test' order by TIME_STAMP;
+--------------+------------+---------------+----------------- +-----------------+----------------+---------+----------------+
| USERNAME | TIME_STAMP | ACCTSESSIONID | ACCTSESSIONTIME | FRAMEDIPADDRESS | NASIDENTIFIER | NASPORT | ACCTSTATUSTYPE |
+--------------+------------+---------------+----------------- +-----------------+----------------+---------+----------------+
| connect_test | 1058742326 | 800006D0 | 344121 | 203.149.64.50 | 203.149.64.3 | 3 | 2 |
| connect_test | 1058781354 | 00009813 | 21210 | 203.149.70.10 | 203.63.121.145 | 1 | 2 |
+--------------+------------+---------------+----------------- +-----------------+----------------+---------+----------------+
2 rows in set (0.00 sec)
Any suggestions on how to get around this? (I would like the username to be inserted with the '@dsl.datafx.com.au' if possible)
Thanks in advance, MB
On Saturday, Jul 19, 2003, at 08:43 Australia/Melbourne, Michael Bellears wrote:
RADUSAGE table,Radiator/Radmin is auth/acct for dialup users (MAX6000/PM3's), and also DSL users(Cisco7200).
Dialup users acct data is successfully updated in thebut DSL clients are not(RADONLINE is working for both).
I see the insert in trace4:
Sat Jul 19 08:05:51 2003: DEBUG: do query is: insert into RADUSAGE
(TIME_STAMP,USERNAME,ACCTDELAYTIME,DNIS,ACCTSTATUSTYPE,ACCTSES SIONID,NASPORT,NASIDENTIFIER) values
(1058565951,'[EMAIL PROTECTED]',0,'3:2.261#184571 819##speed:UBR:256#/',1,'0000980F',1,'203.63.121.145')
And also the insert in the mysql logs:
88528 Query insert into RADUSAGE
(TIME_STAMP,USERNAME,ACCTDELAYTIME,DNIS,ACCTSTATUSTYPE,ACCTSES SIONID,NASPORT,NASIDENTIFIER) values
(1058565951,'[EMAIL PROTECTED]',0,'3:2.261#184571 819##speed:UBR:256#/',1,'0000980F',1,'203.63.121.145')
But no results via Radmin Web Interface, or via CLI:
mysql> select
USERNAME,TIME_STAMP,ACCTSESSIONID,ACCTSESSIONTIME,FRAMEDIPADDR ESS,NASIDENTIFIER,NASPORT,ACCTSTATUSTYPE from RADUSAGE where USERNAME = '[EMAIL PROTECTED]' order by TIME_STAMP; ERROR 1032: Can't find record in 'RADUSAGE'
I see no errors in mysql.err log.
A dialup users succesful insert FYR:
Trcae4: Sat Jul 19 08:06:17 2003: DEBUG: do query is: insert into RADUSAGE
(Connect_info,ACCTSESSIONTIME,TIME_STAMP,USERNAME,Client_Phone _Number,F
RAMEDIPADDRESS,ACCTDELAYTIME,DNIS,ACCTINPUTOCTETS,ACCTSTATUSTY PE,ACCTSESSIONID,ACCTOUTPUTOCTETS,NASPORT,NASIDENTIFIER) values
('49333',863,1058565977,'eplan','755103035','203.149.64.51',0, '55848000',10315,2,'425834844',45523,20206,'203.149.64.4')
MySQL Log: 88528 Query insert into RADUSAGE
(Connect_info,ACCTSESSIONTIME,TIME_STAMP,USERNAME,Client_Phone _Number,F
RAMEDIPADDRESS,ACCTDELAYTIME,DNIS,ACCTINPUTOCTETS,ACCTSTATUSTY PE,ACCTSESSIONID,ACCTOUTPUTOCTETS,NASPORT,NASIDENTIFIER) values
('49333',863,1058565977,'eplan','755103035','203.149.64.51',0, '55848000',10315,2,'425834844',45523,20206,'203.149.64.4')
Thanks in advance.
Regards, MB === Archive at http://www.open.com.au/archives/radiator/ Announcements on [EMAIL PROTECTED] To unsubscribe, email '[EMAIL PROTECTED]' with 'unsubscribe radiator' in the body of the message.
NB: have you included a copy of your configuration file (no secrets), together with a trace 4 debug showing what is happening?
-- Radiator: the most portable, flexible and configurable RADIUS server anywhere. Available on *NIX, *BSD, Windows 95/98/2000, NT, MacOS X. - Nets: internetwork inventory and management - graphical, extensible, flexible with hardware, software, platform and database independence.
NB: have you included a copy of your configuration file (no secrets), together with a trace 4 debug showing what is happening?
-- Radiator: the most portable, flexible and configurable RADIUS server anywhere. Available on *NIX, *BSD, Windows 95/98/2000, NT, MacOS X. - Nets: internetwork inventory and management - graphical, extensible, flexible with hardware, software, platform and database independence.
=== Archive at http://www.open.com.au/archives/radiator/ Announcements on [EMAIL PROTECTED] To unsubscribe, email '[EMAIL PROTECTED]' with 'unsubscribe radiator' in the body of the message.