Hi, I am in the process of upgrading my radius servers from v2.1.12 to v3.0.12 on Ubuntu xenial 64bit. I have successfully got sql and sqlcounter mods working, (I have tested dailycounter/Max-Daily-Session) however when trying to add a counter to sqlcounter, I have run into the following error: = Failed registering counter attribute ChilliSpot-Max-Output-Octets: Attribute 'ChilliSpot-Max-Output-Octets' already exists = When first adding the attribute I ran into this integer error: = /etc/freeradius/mods-enabled/sqlcounter[101]: Counter attribute ChilliSpot-Max-Output-Octets MUST be integer64 = I rectified this by editing the chillispot dictionary and changing integer to integer64. I also manually changed sqlcounter dialect sections to reference mysql directly, as I was getting an error about referencing modules that haven't loaded yet, which was answered previously on this list. Apart from adding sql to authorize {} in the default and inner-tunnel sites as per the FAQ, my configuration is default. Log: = FreeRADIUS Version 3.0.12 Copyright (C) 1999-2016 The FreeRADIUS server project and contributors There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE You may redistribute copies of FreeRADIUS under the terms of the GNU General Public License For more information about these matters, see the file named COPYRIGHT Starting - reading configuration files ... including dictionary file /usr/share/freeradius/dictionary including dictionary file /usr/share/freeradius/dictionary.dhcp including dictionary file /usr/share/freeradius/dictionary.vqp including dictionary file /etc/freeradius/dictionary including configuration file /etc/freeradius/radiusd.conf including configuration file /etc/freeradius/proxy.conf including configuration file /etc/freeradius/clients.conf including files in directory /etc/freeradius/mods-enabled/ including configuration file /etc/freeradius/mods-enabled/dynamic_clients including configuration file /etc/freeradius/mods-enabled/mschap including configuration file /etc/freeradius/mods-enabled/eap including configuration file /etc/freeradius/mods-enabled/detail including configuration file /etc/freeradius/mods-enabled/utf8 including configuration file /etc/freeradius/mods-enabled/sqlcounter including configuration file /etc/freeradius/mods-config/sql/counter/mysql/dailycounter.conf including configuration file /etc/freeradius/mods-config/sql/counter/mysql/monthlycounter.conf including configuration file /etc/freeradius/mods-config/sql/counter/mysql/noresetcounter.conf including configuration file /etc/freeradius/mods-config/sql/counter/mysql/expire_on_login.conf including configuration file /etc/freeradius/mods-enabled/radutmp including configuration file /etc/freeradius/mods-enabled/sql including configuration file /etc/freeradius/mods-config/sql/main/mysql/queries.conf including configuration file /etc/freeradius/mods-enabled/files including configuration file /etc/freeradius/mods-enabled/preprocess including configuration file /etc/freeradius/mods-enabled/expr including configuration file /etc/freeradius/mods-enabled/unpack including configuration file /etc/freeradius/mods-enabled/replicate including configuration file /etc/freeradius/mods-enabled/digest including configuration file /etc/freeradius/mods-enabled/logintime including configuration file /etc/freeradius/mods-enabled/chap including configuration file /etc/freeradius/mods-enabled/soh including configuration file /etc/freeradius/mods-enabled/sradutmp including configuration file /etc/freeradius/mods-enabled/realm including configuration file /etc/freeradius/mods-enabled/expiration including configuration file /etc/freeradius/mods-enabled/always including configuration file /etc/freeradius/mods-enabled/date including configuration file /etc/freeradius/mods-enabled/exec including configuration file /etc/freeradius/mods-enabled/cache_eap including configuration file /etc/freeradius/mods-enabled/attr_filter including configuration file /etc/freeradius/mods-enabled/passwd including configuration file /etc/freeradius/mods-enabled/echo including configuration file /etc/freeradius/mods-enabled/linelog including configuration file /etc/freeradius/mods-enabled/ntlm_auth including configuration file /etc/freeradius/mods-enabled/pap including configuration file /etc/freeradius/mods-enabled/detail.log including configuration file /etc/freeradius/mods-enabled/unix including files in directory /etc/freeradius/policy.d/ including configuration file /etc/freeradius/policy.d/operator-name including configuration file /etc/freeradius/policy.d/eap including configuration file /etc/freeradius/policy.d/dhcp including configuration file /etc/freeradius/policy.d/cui including configuration file /etc/freeradius/policy.d/moonshot-targeted-ids including configuration file /etc/freeradius/policy.d/filter including configuration file /etc/freeradius/policy.d/abfab-tr including configuration file /etc/freeradius/policy.d/canonicalization including configuration file /etc/freeradius/policy.d/debug including configuration file /etc/freeradius/policy.d/control including configuration file /etc/freeradius/policy.d/accounting including files in directory /etc/freeradius/sites-enabled/ including configuration file /etc/freeradius/sites-enabled/default including configuration file /etc/freeradius/sites-enabled/inner-tunnel main { security { user = "freerad" group = "freerad" allow_core_dumps = no } name = "freeradius" prefix = "/usr" localstatedir = "/var" logdir = "/var/log/freeradius" run_dir = "/var/run/freeradius" } main { name = "freeradius" prefix = "/usr" localstatedir = "/var" sbindir = "/usr/sbin" logdir = "/var/log/freeradius" run_dir = "/var/run/freeradius" libdir = "/usr/lib/freeradius" radacctdir = "/var/log/freeradius/radacct" hostname_lookups = no max_request_time = 30 cleanup_delay = 5 max_requests = 16384 pidfile = "/var/run/freeradius/freeradius.pid" checkrad = "/usr/sbin/checkrad" debug_level = 0 proxy_requests = yes log { stripped_names = no auth = no auth_badpass = no auth_goodpass = no colourise = yes msg_denied = "You are already logged in - access denied" } resources { } security { max_attributes = 200 reject_delay = 1.000000 status_server = yes } } radiusd: #### Loading Realms and Home Servers #### proxy server { retry_delay = 5 retry_count = 3 default_fallback = no dead_time = 120 wake_all_if_all_dead = no } home_server localhost { ipaddr = 127.0.0.1 port = 1812 type = "auth" secret = <<< secret >>> response_window = 20.000000 response_timeouts = 1 max_outstanding = 65536 zombie_period = 40 status_check = "status-server" ping_interval = 30 check_interval = 30 check_timeout = 4 num_answers_to_alive = 3 revive_interval = 120 limit { max_connections = 16 max_requests = 0 lifetime = 0 idle_timeout = 0 } coa { irt = 2 mrt = 16 mrc = 5 mrd = 30 } } home_server_pool my_auth_failover { type = fail-over home_server = localhost } realm example.com { auth_pool = my_auth_failover } realm LOCAL { } radiusd: #### Loading Clients #### client localhost { ipaddr = 127.0.0.1 require_message_authenticator = no secret = <<< secret >>> nas_type = "other" proto = "*" limit { max_connections = 16 lifetime = 0 idle_timeout = 30 } } client localhost_ipv6 { ipv6addr = ::1 require_message_authenticator = no secret = <<< secret >>> limit { max_connections = 16 lifetime = 0 idle_timeout = 30 } } client 0.0.0.0/0 { ipaddr = 0.0.0.0 require_message_authenticator = no secret = <<< secret >>> shortname = "test_name" limit { max_connections = 16 lifetime = 0 idle_timeout = 30 } } Debugger not attached # Creating Auth-Type = mschap # Creating Auth-Type = digest # Creating Auth-Type = eap # Creating Auth-Type = PAP # Creating Auth-Type = CHAP # Creating Auth-Type = MS-CHAP radiusd: #### Instantiating modules #### modules { # Loaded module rlm_dynamic_clients # Loading module "dynamic_clients" from file /etc/freeradius/mods-enabled/dynamic_clients # Loaded module rlm_mschap # Loading module "mschap" from file /etc/freeradius/mods-enabled/mschap mschap { use_mppe = yes require_encryption = no require_strong = no with_ntdomain_hack = yes passchange { } allow_retry = yes } # Loaded module rlm_eap # Loading module "eap" from file /etc/freeradius/mods-enabled/eap eap { default_eap_type = "md5" timer_expire = 60 ignore_unknown_eap_types = no cisco_accounting_username_bug = no max_sessions = 16384 } # Loaded module rlm_detail # Loading module "detail" from file /etc/freeradius/mods-enabled/detail detail { filename = "/var/log/freeradius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d" header = "%t" permissions = 384 locking = no escape_filenames = no log_packet_header = no } # Loaded module rlm_utf8 # Loading module "utf8" from file /etc/freeradius/mods-enabled/utf8 # Loaded module rlm_sqlcounter # Loading module "dailycounter" from file /etc/freeradius/mods-enabled/sqlcounter sqlcounter dailycounter { sql_module_instance = "sql" key = "User-Name" query = "SELECT SUM(acctsessiontime - GREATEST((%%b - UNIX_TIMESTAMP(acctstarttime)), 0)) FROM radacct WHERE username = '%{User-Name}' AND UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '%%b'" reset = "daily" counter_name = "Daily-Session-Time" check_name = "Max-Daily-Session" reply_name = "Session-Timeout" } # Loading module "monthlycounter" from file /etc/freeradius/mods-enabled/sqlcounter sqlcounter monthlycounter { sql_module_instance = "sql" key = "User-Name" query = "SELECT SUM(acctsessiontime - GREATEST((%%b - UNIX_TIMESTAMP(acctstarttime)), 0)) FROM radacct WHERE username='%{User-Name}' AND UNIX_TIMESTAMP(acctstarttime) + acctsessiontime
'%%b'" reset = "monthly" counter_name = "Monthly-Session-Time" check_name = "Max-Monthly-Session" reply_name = "Session-Timeout" } # Loading module "noresetcounter" from file /etc/freeradius/mods-enabled/sqlcounter sqlcounter noresetcounter { sql_module_instance = "sql" key = "User-Name" query = "SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='%{User-Name}'" reset = "never" counter_name = "Max-All-Session-Time" check_name = "Max-All-Session" reply_name = "Session-Timeout" } # Loading module "expire_on_login" from file /etc/freeradius/mods-enabled/sqlcounter sqlcounter expire_on_login { sql_module_instance = "sql" key = "User-Name" query = "SELECT IFNULL( MAX(TIME_TO_SEC(TIMEDIFF(NOW(), acctstarttime))),0) FROM radacct WHERE UserName='%{User-Name}' ORDER BY acctstarttime LIMIT 1;" reset = "never" counter_name = "Expire-After-Initial-Login" check_name = "Expire-After" reply_name = "Session-Timeout" } # Loading module "chillispot_download_limit" from file /etc/freeradius/mods-enabled/sqlcounter sqlcounter chillispot_download_limit { sql_module_instance = "sql" key = "User-Name" query = "SELECT SUM(AcctOutputOctets) FROM radacct WHERE UserName='%{%k}'" reset = "daily" counter_name = "ChilliSpot-Max-Output-Octets" check_name = "ChilliSpot-Max-Output-Octets" reply_name = "ChilliSpot-Max-Output-Octets" } /etc/freeradius/mods-enabled/sqlcounter[101]: Failed registering counter attribute ChilliSpot-Max-Output-Octets: Attribute 'ChilliSpot-Max-Output-Octets' already exists. /etc/freeradius/mods-enabled/sqlcounter[101]: Instantiation failed for module "chillispot_download_limit" =
The counter that I have added to sqlcounter: = sqlcounter chillispot_download_limit { sql_module_instance = sql dialect = "mysql" counter_name = ChilliSpot-Max-Output-Octets check_name = ChilliSpot-Max-Output-Octets reply_name = ChilliSpot-Max-Output-Octets key = User-Name reset = daily $INCLUDE ${modconfdir}/sql/counter/${dialect}/${.:instance}.conf } = The query file: = query = "SELECT SUM(AcctInputOctets) FROM radacct WHERE UserName='%{${key}}'" = Is there something critical configuration-wise that I have missed between 2.x.x and 3.x.x? David