[ixpmanager] port utilisation and mariadb

[Peter]

Hi All,

KIXP has V6.2.0 running on Debian with MariaDB which gives the following error for Port Utilisation statistics;

500
Server Error :: SQLSTATE[42000]: Syntax error or access violation: 1305 FUNCTION ixpmanager.ANY_VALUE does not exist (SQL: select c.id AS cid, c.abbreviatedName AS cname, ANY_VALUE( s.name ) as switch, vi.id AS viid, SUM( tdpi.month_max_in ) AS max_in, SUM( tdpi.month_max_out ) AS max_out, COUNT( pi.id ) AS num_ports_in_lag, SUM( COALESCE( pi.rate_limit, pi.speed ) ) AS vi_speed, ROUND( GREATEST( (MAX( tdpi.month_max_in )/1000000/MAX( COALESCE( pi.rate_limit, pi.speed ) ))*100, (MAX( tdpi.month_max_out )/1000000/MAX( COALESCE( pi.rate_limit, pi.speed ) ))*100 ), 2) AS util from `traffic_daily_phys_ints` as `tdpi` left join `physicalinterface` as `pi` on `pi`.`id` = `tdpi`.`physicalinterface_id` left join `virtualinterface` as `vi` on `vi`.`id` = `pi`.`virtualinterfaceid` left join `cust` as `c` on `c`.`id` = `vi`.`custid` left join `switchport` as `sp` on `sp`.`id` = `pi`.`switchportid` left join `switch` as `s` on `s`.`id` = `sp`.`switchid` where `tdpi`.`day` = 2022-01-22 and `tdpi`.`category` = bits group by `vi`.`id`)


Some digging around gives;

https://www.py4u.net/discuss/848038

https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_any-value

https://riptutorial.com/mysql/example/26731/any-value--


Resolved by changing $IXPROOT/app/Models/Aggregators/TrafficDailyPhysIntAggregator.php

156c156
<             "c.id AS cid, c.abbreviatedName AS cname, ANY_VALUE( s.name ) as switch,
---
>             "c.id AS cid, c.abbreviatedName AS cname, s.name as switch,


Stats are now available but is it the correct solution and would it work for MySQL which supports the function anyway?

Regards
Peter Gitau
KIXP