Daloradius ERROR: (3) sql: ERROR: rlm_sql_mysql: ERROR 1054 (Unknown column 'acctupdatetime' in 'field list')

Getting the following error after configuring freeradius with daloradius.

ERROR: (3) sql: ERROR: rlm_sql_mysql: ERROR 1054 (Unknown column 'acctupdatetime' in 'field list')

Radius server accounting does not seem to be working.

Used the SQL schema as posted in daloradius github but accounting seems to be failing.

https://github.com/lirantal/daloradius/tree/master/contrib/db

The guide i used to install freeradius+daloradius:

https://computingforgeeks.com/install-freeradius-and-daloradius-on-debian/

Solution

The issue is some table fields are missing.

The SQL schema in daloradius does not create all the fields. To fix this error you have to add the missing SQL fields manually by yourself.

1. Run mysql -u root -p radius

2. Drop the table causing issues

 DROP TABLE radacct;

3. Adding the following record fixed the issue.

CREATE TABLE radacct (
radacctid bigint(21) NOT NULL auto_increment,
acctsessionid varchar(64) NOT NULL default '',
acctuniqueid varchar(32) NOT NULL default '',
username varchar(64) NOT NULL default '',
groupname varchar(64) NOT NULL default '',
realm varchar(64) default '',
nasipaddress varchar(15) NOT NULL default '',
nasportid varchar(15) default NULL,
nasporttype varchar(32) default NULL,
acctstarttime datetime NULL default NULL,
acctupdatetime datetime NULL default NULL,
acctstoptime datetime NULL default NULL,
acctinterval int(12) default NULL,
acctsessiontime int(12) unsigned default NULL,
acctauthentic varchar(32) default NULL,
connectinfo_start varchar(50) default NULL,
connectinfo_stop varchar(50) default NULL,
acctinputoctets bigint(20) default NULL,
acctoutputoctets bigint(20) default NULL,
calledstationid varchar(50) NOT NULL default '',
callingstationid varchar(50) NOT NULL default '',
acctterminatecause varchar(32) NOT NULL default '',
servicetype varchar(32) default NULL,
framedprotocol varchar(32) default NULL,
framedipv6address varchar(32) default NULL,
framedipv6prefix varchar(32) default NULL,
framedinterfaceid varchar(32) default NULL,
delegatedipv6prefix varchar(32) default NULL,
framedipaddress varchar(15) NOT NULL default '',
PRIMARY KEY (radacctid),
UNIQUE KEY acctuniqueid (acctuniqueid),
KEY username (username),
KEY framedipaddress (framedipaddress),
KEY acctsessionid (acctsessionid),
KEY acctsessiontime (acctsessiontime),
KEY acctstarttime (acctstarttime),
KEY acctinterval (acctinterval),
KEY acctstoptime (acctstoptime),
KEY nasipaddress (nasipaddress)
) ENGINE = INNODB;

NB: More help on this https://sourceforge.net/p/daloradius/discussion/684102/thread/56b11486/?limit=25 however in my case the solution proposed did not have all the fields necessary.