Incorrect datetime issue
Hi, i am facing issue in radacct insert. So it is showing incorrect dattime. So in debug i found datetime cloumn is missing 'FROM_UNIXTIME' in debug o/p . Below you can see debug msg. --------------------------------------------------------------------- EXPAND INSERT INTO radacct (acctsessionid, acctuniqueid, username, realm, nasipaddress, nasportid, nasporttype, acctstarttime, acctupdatetime, acctstoptime, acctsessiontime, acctauthentic, connectinfo_start, connectinfo_stop, acctinputoctets, acctoutputoctets, calledstationid, callingstationid, acctterminatecause, servicetype, framedprotocol, framedipaddress) VALUES ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{%{NAS-Port-ID}:-%{NAS-Port}}', '%{NAS-Port-Type}', (%{%{integer:Event-Timestamp}:-strftime('%%s', 'now')} - %{%{Acct-Session-Time}:-0}), %{%{integer:Event-Timestamp}:-date('now')}, NULL, %{%{Acct-Session-Time}:-NULL}, '%{Acct-Authentic}', '%{Connect-Info}', '', %{%{Acct-Input-Gigawords}:-0} << 32 | %{%{Acct-Input-Octets}:-0}, %{%{Acct-Output-Gigawords}:-0} << 32 | %{%{Acct-Output-Octets}:-0}, '%{Called-Station-Id}', '%{Calling-Station-Id}', '', '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}') (16) sql: --> INSERT INTO radacct (acctsessionid, acctuniqueid, username, realm, nasipaddress, nasportid, nasporttype, acctstarttime, acctupdatetime, acctstoptime, acctsessiontime, acctauthentic, connectinfo_start, connectinfo_stop, acctinputoctets, acctoutputoctets, calledstationid, callingstationid, acctterminatecause, servicetype, framedprotocol, framedipaddress) VALUES ('3C00A8C0-0002A0D8', '40c3d442406733f6f19fe4fb3da8211c', 'bob', '', '192.168.0.4', '0', 'Wireless-802.11', (1536835611 - 600), 1536835611, NULL, 600, 'RADIUS', 'CONNECT Unknown Radio', '', 0 << 32 | 155655, 0 << 32 | 434058, '00:0c:29:67:6d:ec', '18:f0:e4:b0:fe:fd', '', '', '', '192.168.0.60') ---------------------------------------------------------------------- but in insert stmt query looking ok. below is insert stmt query. query = "\ INSERT INTO ${....acct_table1} \ (${...column_list}) \ VALUES \ ('%{Acct-Session-Id}', \ '%{Acct-Unique-Session-Id}', \ '%{SQL-User-Name}', \ '%{Realm}', \ '%{NAS-IP-Address}', \ '%{%{NAS-Port-ID}:-%{NAS-Port}}', \ '%{NAS-Port-Type}', \ FROM_UNIXTIME(%{integer:Event-Timestamp}), \ FROM_UNIXTIME(%{integer:Event-Timestamp}), \ NULL, \ '0', \ '%{Acct-Authentic}', \ '%{Connect-Info}', \ '', \ '0', \ '0', \ '%{Called-Station-Id}', \ '%{Calling-Station-Id}', \ '', \ '%{Service-Type}', \ '%{Framed-Protocol}', \ '%{Framed-IP-Address}')" # So i am not able to understand where is the issue? Can you help me on that?
On Sep 13, 2018, at 7:34 AM, Somanath Mishra <somanath.mishra@planetsbrain.com> wrote:
Hi,
i am facing issue in radacct insert. So it is showing incorrect dattime. So in debug i found datetime cloumn is missing 'FROM_UNIXTIME' in debug o/p . Below you can see debug msg. ... ---------------------------------------------------------------------- but in insert stmt query looking ok. below is insert stmt query.
The query from the debug output is not the same one as the query you posted here. The SQL configuration contains many, many, queries. The debug output is likely showing you a different one. i.e. the debug output includes "strftime". But the query you posted here doesn't use that. So they are different queries. You need to find the *correct* query in the config files and fix that. It looks like you're using the sqlite driver for SQL. Is this what you want to do? Alan DeKok.
Hi, here is all debug output queries. You can see incorrect datetime value below : rlm_sql (sql): Opening additional connection (10), 1 of 25 pending slots used rlm_sql_mysql: Starting connect to MySQL server rlm_sql_mysql: Connected to database 'radius' on Localhost via UNIX socket, server version 5.7.23-0ubuntu0.16.04.1, protocol version 10 (5) [sql] = fail (5) } # accounting = fail (5) Not sending reply to client. (5) Finished request (5) Cleaning up request packet ID 19 with timestamp +63 Waking up in 2.9 seconds. (6) Received Accounting-Request Id 19 from 192.168.0.4:57878 to 192.168.0.252:1813 length 207 (6) Acct-Status-Type = Start (6) Acct-Session-Id = "3C00A8C0-00043495" (6) Acct-Authentic = RADIUS (6) User-Name = "bob" (6) Calling-Station-Id = "18:f0:e4:b0:fe:fd" (6) Called-Station-Id = "00:0c:29:67:6d:ec" (6) Connect-Info = "CONNECT Unknown Radio" (6) NAS-Identifier = "00:0c:29:67:6d:ec" (6) Meru-Access-Point-Id = 10 (6) Meru-Access-Point-Name = "Bangalore" (6) Attr-26.15983.3 = 0xc0a8003c (6) Framed-IP-Address = 192.168.0.60 (6) NAS-IP-Address = 192.168.0.4 (6) NAS-Port-Type = Wireless-802.11 (6) NAS-Port = 0 (6) Acct-Delay-Time = 0 (6) # Executing section preacct from file /etc/freeradius/sites-enabled/default (6) preacct { (6) [preprocess] = ok (6) policy acct_unique { (6) update request { (6) &Tmp-String-9 := "ai:" (6) } # update request = noop (6) if (("%{hex:&Class}" =~ /^%{hex:&Tmp-String-9}/) && ("%{string:&Class}" =~ /^ai:([0-9a-f]{32})/i)) { (6) EXPAND %{hex:&Class} (6) --> (6) EXPAND ^%{hex:&Tmp-String-9} (6) --> ^61693a (6) if (("%{hex:&Class}" =~ /^%{hex:&Tmp-String-9}/) && ("%{string:&Class}" =~ /^ai:([0-9a-f]{32})/i)) -> FALSE (6) else { (6) update request { (6) EXPAND %{md5:%{User-Name},%{Acct-Session-ID},%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}},%{NAS-Identifier},%{NAS-Port-ID},%{NAS-Port}} (6) --> deb7615a3734113b62c4db4bc605a4c4 (6) &Acct-Unique-Session-Id := deb7615a3734113b62c4db4bc605a4c4 (6) } # update request = noop (6) } # else = noop (6) } # policy acct_unique = noop (6) suffix: Checking for suffix after "@" (6) suffix: No '@' in User-Name = "bob", looking up realm NULL (6) suffix: No such realm "NULL" (6) [suffix] = noop (6) [files] = noop (6) } # preacct = ok (6) # Executing section accounting from file /etc/freeradius/sites-enabled/default (6) accounting { (6) detail: EXPAND /var/log/freeradius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d (6) detail: --> /var/log/freeradius/radacct/192.168.0.4/detail-20180913 (6) detail: /var/log/freeradius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d expands to /var/log/freeradius/radacct/192.168.0.4/detail-20180913 (6) detail: EXPAND %t (6) detail: --> Thu Sep 13 12:49:29 2018 (6) [detail] = ok (6) [unix] = ok (6) sql: EXPAND %{tolower:type.%{Acct-Status-Type}.query} (6) sql: --> type.start.query (6) sql: Using query template 'query' rlm_sql (sql): Reserved connection (3) (6) sql: EXPAND %{User-Name} (6) sql: --> bob (6) sql: SQL-User-Name set to 'bob' (6) sql: EXPAND INSERT INTO radacct (acctsessionid, acctuniqueid, username, realm, nasipaddress, nasportid, nasporttype, acctstarttime, acctupdatetime, acctstoptime, acctsessiontime, acctauthentic, connectinfo_start, connectinfo_stop, acctinputoctets, acctoutputoctets, calledstationid, callingstationid, acctterminatecause, servicetype, framedprotocol, framedipaddress) VALUES ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{%{NAS-Port-ID}:-%{NAS-Port}}', '%{NAS-Port-Type}', %{%{integer:Event-Timestamp}:-date('now')}, %{%{integer:Event-Timestamp}:-date('now')}, NULL, '0', '%{Acct-Authentic}', '%{Connect-Info}', '', '0', '0', '%{Called-Station-Id}', '%{Calling-Station-Id}', '', '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}') (6) sql: --> INSERT INTO radacct (acctsessionid, acctuniqueid, username, realm, nasipaddress, nasportid, nasporttype, acctstarttime, acctupdatetime, acctstoptime, acctsessiontime, acctauthentic, connectinfo_start, connectinfo_stop, acctinputoctets, acctoutputoctets, calledstationid, callingstationid, acctterminatecause, servicetype, framedprotocol, framedipaddress) VALUES ('3C00A8C0-00043495', 'deb7615a3734113b62c4db4bc605a4c4', 'bob', '', '192.168.0.4', '0', 'Wireless-802.11', 1536839369, 1536839369, NULL, '0', 'RADIUS', 'CONNECT Unknown Radio', '', '0', '0', '00:0c:29:67:6d:ec', '18:f0:e4:b0:fe:fd', '', '', '', '192.168.0.60') (6) sql: Executing query: INSERT INTO radacct (acctsessionid, acctuniqueid, username, realm, nasipaddress, nasportid, nasporttype, acctstarttime, acctupdatetime, acctstoptime, acctsessiontime, acctauthentic, connectinfo_start, connectinfo_stop, acctinputoctets, acctoutputoctets, calledstationid, callingstationid, acctterminatecause, servicetype, framedprotocol, framedipaddress) VALUES ('3C00A8C0-00043495', 'deb7615a3734113b62c4db4bc605a4c4', 'bob', '', '192.168.0.4', '0', 'Wireless-802.11', 1536839369, 1536839369, NULL, '0', 'RADIUS', 'CONNECT Unknown Radio', '', '0', '0', '00:0c:29:67:6d:ec', '18:f0:e4:b0:fe:fd', '', '', '', '192.168.0.60') (6) sql: ERROR: rlm_sql_mysql: ERROR 1292 (Incorrect datetime value: '1536839369' for column 'acctstarttime' at row 1): 22007 (6) sql: SQL query returned: server error rlm_sql (sql): Released connection (3) Need 2 more connections to reach 10 spares rlm_sql (sql): Opening additional connection (11), 1 of 24 pending slots used rlm_sql_mysql: Starting connect to MySQL server rlm_sql_mysql: Connected to database 'radius' on Localhost via UNIX socket, server version 5.7.23-0ubuntu0.16.04.1, protocol version 10 (6) [sql] = fail (6) } # accounting = fail (6) Not sending reply to client. (6) Finished request (6) Cleaning up request packet ID 19 with timestamp +65 Waking up in 0.9 seconds. (3) Cleaning up request packet ID 17 with timestamp +61 Ready to process requests --------------------------------------------------------------------- On Thu, September 13, 2018 5:16 pm, Alan DeKok wrote:
On Sep 13, 2018, at 7:34 AM, Somanath Mishra <somanath.mishra@planetsbrain.com> wrote:
Hi,
i am facing issue in radacct insert. So it is showing incorrect dattime. So in debug i found datetime cloumn is missing 'FROM_UNIXTIME' in debug o/p . Below you can see debug msg. ... ---------------------------------------------------------------------- but in insert stmt query looking ok. below is insert stmt query.
The query from the debug output is not the same one as the query you posted here.
The SQL configuration contains many, many, queries. The debug output is likely showing you a different one.
i.e. the debug output includes "strftime". But the query you posted here doesn't use that. So they are different queries.
You need to find the *correct* query in the config files and fix that.
It looks like you're using the sqlite driver for SQL. Is this what you want to do?
Alan DeKok.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On Sep 13, 2018, at 7:52 AM, Somanath Mishra <somanath.mishra@planetsbrain.com> wrote:
here is all debug output queries. You can see incorrect datetime value below :
You either edited the default queries, *or*, you're running a version of FreeRADIUS that is many, many, years old. The default MySQL queries have have "FROM_UNIXTIME" for at least 4 years, maybe longer. I didn't bother to go further than that. Use 3.0.17. Use the default queries. It will work. Alan DeKok.
ok, I am using latest version freeradius. I modified queries.conf to implement GroupName in radacct. So revert changes. Now it is as it was before. and insert stmt to radacct quries: start { # # Insert a new record into the sessions table # query = "\ INSERT INTO ${....acct_table1} \ (${...column_list}) \ VALUES \ ('%{Acct-Session-Id}', \ '%{Acct-Unique-Session-Id}', \ '%{SQL-User-Name}', \ '%{Realm}', \ '%{NAS-IP-Address}', \ '%{%{NAS-Port-ID}:-%{NAS-Port}}', \ '%{NAS-Port-Type}', \ FROM_UNIXTIME(%{integer:Event-Timestamp}), \ FROM_UNIXTIME(%{integer:Event-Timestamp}), \ NULL, \ '0', \ '%{Acct-Authentic}', \ '%{Connect-Info}', \ '', \ '0', \ '0', \ '%{Called-Station-Id}', \ '%{Calling-Station-Id}', \ '', \ '%{Service-Type}', \ '%{Framed-Protocol}', \ '%{Framed-IP-Address}')" # # Key constraints prevented us from inserting a new session, # use the alternate query to update an existing session. # I am not able to understand where is the issue. Queries.conf looks correct. Can you help me please? --------------------------------------------------------------------- On Thu, September 13, 2018 5:30 pm, Alan DeKok wrote:
On Sep 13, 2018, at 7:52 AM, Somanath Mishra <somanath.mishra@planetsbrain.com> wrote:
here is all debug output queries. You can see incorrect datetime value below :
You either edited the default queries, *or*, you're running a version of FreeRADIUS that is many, many, years old.
The default MySQL queries have have "FROM_UNIXTIME" for at least 4 years, maybe longer. I didn't bother to go further than that.
Use 3.0.17. Use the default queries. It will work.
Alan DeKok.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On Sep 13, 2018, at 8:59 AM, Somanath Mishra <somanath.mishra@planetsbrain.com> wrote:
ok, I am using latest version freeradius. I modified queries.conf to implement GroupName in radacct. So revert changes. Now it is as it was before. and insert stmt to radacct quries:
OK...
I am not able to understand where is the issue. Queries.conf looks correct. Can you help me please?
Read the debug output to see which files it's reading. Odds are that you have it misconfigured. i.e. your earlier posts showed you using the sqlite queries. But other posts show MySQL. That's a problem. Use the default configuration. Configure the SQL module as per mods-enabled/sql. That's it. It will work. If you made massive changes to the config, then that explains why it doesn't work. Don't do that. And, don't debug any massive changes you made. It's a waste of time. I won't help you with that, because it's a waste of time, and there's no way to tell what you changed. Just use the default configuration, and make small changes. It WILL work. Alan DeKok.
participants (2)
-
Alan DeKok -
Somanath Mishra