DB patches 8.085_update_radacct_ipv6.sql and 8.090_add_radacct_triggers.sql lead to mysql 1136 errors

[gkelle]

I applied patch 8.085_update_radacct_ipv6.sql and patch 8.090_add_radacct_triggers.sql to my database and noticed that I started getting the following error: sql: ERROR: rlm_sql_mysql: ERROR 1136 (Column count doesn't match value count at row 1): 21S01

This would occur whenever a client would log off of an SSID using RADIUS+PPSK.

The trigger causing the 1136 error looks like this:

     -- Check if acctstoptime has changed from NULL to NOT NULL
    IF OLD.acctstoptime IS NULL AND NEW.acctstoptime IS NOT NULL THEN
        -- Insert the updated row into radacct_history
        INSERT INTO radacct_history
        SELECT * FROM radacct WHERE radacctid = NEW.radacctid;
    END IF;

Comparing the table definition for radacct and raddact_history, I noticed that raddact_history had fewer columns and was missing framedipv6address, framedipv6prefix, framedinterfaceid, and delegatedipv6prefix

To fix this, I updated 8.085_update_radacct_ipv6.sql to also update the radacct_history table with the new columns and keys.

After updating radacct_history, the trigger and history table behavior was corrected.

[AhmedHanyAbdulazim]

just use it move 9.001_add_group_id_for_sharding.sql and 9.002_adjust_radacct_triggers.sql to /var/www/html/cake4/rd_cake/setup/db/

and

sudo mysql -u root rd < /var/www/html/cake4/rd_cake/setup/db/rd.sql

cd /var/www/html/cake4/rd_cake/setup/db/ for f in $(ls -1v 8..sql 9..sql); do echo "Applying patch: $f" sudo mysql -u root rd < "$f" done

ALTER TABLE radacct_history ADD COLUMN framedipv6address VARCHAR(45) DEFAULT NULL, ADD COLUMN framedipv6prefix VARCHAR(45) DEFAULT NULL, ADD COLUMN framedinterfaceid VARCHAR(44) DEFAULT NULL, ADD COLUMN delegatedipv6prefix VARCHAR(45) DEFAULT NULL;

thats all

[AhmedHanyAbdulazim]

also u will need this triger

DELIMITER //

CREATE TRIGGER copy_to_radacct_history_after_update AFTER UPDATE ON radacct FOR EACH ROW BEGIN IF NEW.acctstoptime IS NOT NULL AND OLD.acctstoptime IS NULL THEN INSERT INTO radacct_history ( radacctid, acctsessionid, acctuniqueid, username, groupname, realm, nasipaddress, nasidentifier, nasportid, nasporttype, acctstarttime, acctupdatetime, acctstoptime, acctinterval, acctsessiontime, acctauthentic, connectinfo_start, connectinfo_stop, acctinputoctets, acctoutputoctets, calledstationid, callingstationid, acctterminatecause, servicetype, framedprotocol, framedipaddress, acctstartdelay, acctstopdelay, xascendsessionsvrkey, operator_name, group_id, framedipv6address, framedipv6prefix, framedinterfaceid, delegatedipv6prefix ) VALUES ( NEW.radacctid, NEW.acctsessionid, NEW.acctuniqueid, NEW.username, NEW.groupname, NEW.realm, NEW.nasipaddress, NEW.nasidentifier, NEW.nasportid, NEW.nasporttype, NEW.acctstarttime, NEW.acctupdatetime, NEW.acctstoptime, NEW.acctinterval, NEW.acctsessiontime, NEW.acctauthentic, NEW.connectinfo_start, NEW.connectinfo_stop, NEW.acctinputoctets, NEW.acctoutputoctets, NEW.calledstationid, NEW.callingstationid, NEW.acctterminatecause, NEW.servicetype, NEW.framedprotocol, NEW.framedipaddress, NEW.acctstartdelay, NEW.acctstopdelay, NEW.xascendsessionsvrkey, NEW.operator_name, NEW.group_id, NEW.framedipv6address, NEW.framedipv6prefix, NEW.framedinterfaceid, NEW.delegatedipv6prefix ); END IF; END; //

DELIMITER ;