SQL error - Incorrect datetime value
Hello everyone, I'm setting up a freeradius server 3.0.8 on FreeBSD 10. I am getting an error in sql: (29) 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 ('00001A59-00000028', '8698639b48376aea32f23dc9a7989089', 'myuser', 'DEFAULT', '172.19.13.15', '0', 'Wireless-802.11', 1436344658, 1436344658, NULL, '0', 'RADIUS', 'CONNECT 0Mbps 802.11b', '', '0', '0', '2A-A4-3C-B2-FB-AD:mySSID', '34-BB-26-C2-1D-60', '', '', '', '') (29) sql: ERROR: rlm_sql_mysql: ERROR 1292 (Incorrect datetime value: '1436344658' for column 'acctstarttime' at row 1): 22007 I created the database using the mysql (version 5.6.24) schema.sql file provided on freeradius. The problem is in mysql or freeradius? Regards, Renato
On Jul 8, 2015, at 8:30 AM, Renato Sousa <rensousa@gmail.com> wrote:
I'm setting up a freeradius server 3.0.8 on FreeBSD 10. I am getting an error in sql:
You're using the wrong queries. All of the times should have FROM_UNIXTIME(...). This is what's in the v3.0.x branch. Alan DeKok.
Hello Alan, I checked the file /usr/local/etc/raddb/mods-config/sql/main/mysql/queries.conf and contains FROM_UNIXTIME () functions. The file keeps the default installation settings. 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}', \ '%{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}')" Why freeradius does not perform the function in the query? Bet Regards, Renato 2015-07-08 9:38 GMT-03:00 Alan DeKok <aland@deployingradius.com>:
On Jul 8, 2015, at 8:30 AM, Renato Sousa <rensousa@gmail.com> wrote:
I'm setting up a freeradius server 3.0.8 on FreeBSD 10. I am getting an error in sql:
You're using the wrong queries. All of the times should have FROM_UNIXTIME(...).
This is what's in the v3.0.x branch.
Alan DeKok.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On Jul 8, 2015, at 9:15 AM, Renato Sousa <rensousa@gmail.com> wrote:
Hello Alan,
I checked the file /usr/local/etc/raddb/mods-config/sql/main/mysql/queries.conf and contains FROM_UNIXTIME () functions. The file keeps the default installation settings.
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}', \ '%{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}')"
Why freeradius does not perform the function in the query?
Because it's easier to use the server side functions to convert the unix timestamps into native date types, instead of writing code to format the timestamps for specific database flavours. -Arran
Why freeradius does not perform the function in the query?
Because it's easier to use the server side functions to convert the unix timestamps into native date types, instead of writing code to format the timestamps for specific database flavours.
Excuse me Arran, I mean because my freeradius does not display the SQL queries with the functions in debug as it is configured in query.conf file Best Regards, Renato
On Jul 8, 2015, at 10:19 AM, Renato Sousa <rensousa@gmail.com> wrote:
Why freeradius does not perform the function in the query?
Because it's easier to use the server side functions to convert the unix timestamps into native date types, instead of writing code to format the timestamps for specific database flavours.
Excuse me Arran,
I mean because my freeradius does not display the SQL queries with the functions in debug as it is configured in query.conf file
Because it's loading a different query.conf file.
On Jul 8, 2015, at 9:15 AM, Renato Sousa <rensousa@gmail.com> wrote:
I checked the file /usr/local/etc/raddb/mods-config/sql/main/mysql/queries.conf and contains FROM_UNIXTIME () functions. The file keeps the default installation settings.
OK.
Why freeradius does not perform the function in the query?
Probably because the server isn't reading that file. It's using *another* file, which has the incorrect queries. As always, read the debug output to see which file it's reading, and what it's doing. Alan DeKok.
Probably because the server isn't reading that file. It's using *another* file, which has the incorrect queries.
As always, read the debug output to see which file it's reading, and what it's doing.
Thank you all! Solved! The value of the variable "dialect" was set to BD sqlite in mods-available/sql Regards, Renato
participants (3)
-
Alan DeKok -
Arran Cudbard-Bell -
Renato Sousa