I have a new install of LibreNMS. I have alerting set up to only go to Alert Transports, not admins or the default contact. However, no alerts are sent. The log file has this error:
[2018-11-06 16:21:02] production.ERROR: SQLSTATE[21000]: Cardinality violation: 1222 The used SELECT statements have a different number of columns (SQL: SELECT b.transport_
id, b.transport_type, b.transport_name FROM alert_transport_map AS a LEFT JOIN alert_transports AS b ON b.transport_id=a.transport_or_group_id WHERE a.target_type=‘single’
AND a.rule_id=2 UNION DISTINCT SELECT d.transport_id, d.transport_type FROM alert_transport_map AS a LEFT JOIN alert_transport_groups AS b ON a.transport_or_group_id=b.tran
sport_group_id LEFT JOIN transport_group_transport AS c ON b.transport_group_id=c.transport_group_id LEFT JOIN alert_transports AS d ON c.transport_id=d.transport_id WHERE
a.target_type=‘group’ AND a.rule_id=2) (SQL: SELECT b.transport_id, b.transport_type, b.transport_name FROM alert_transport_map AS a LEFT JOIN alert_transports AS b ON b.tr
ansport_id=a.transport_or_group_id WHERE a.target_type=‘single’ AND a.rule_id=2 UNION DISTINCT SELECT d.transport_id, d.transport_type FROM alert_transport_map AS a LEFT JO
IN alert_transport_groups AS b ON a.transport_or_group_id=b.transport_group_id LEFT JOIN transport_group_transport AS c ON b.transport_group_id=c.transport_group_id LEFT JO
IN alert_transports AS d ON c.transport_id=d.transport_id WHERE a.target_type=‘group’ AND a.rule_id=2)
/opt/librenms/LibreNMS/Alert/AlertUtil.php:30
/opt/librenms/includes/alerts.inc.php:834
/opt/librenms/includes/alerts.inc.php:609
/opt/librenms/includes/alerts.inc.php:626
/opt/librenms/alerts.php:50
When I try that query through the mysql command line, I get the error:
ERROR 1222 (21000): The used SELECT statements have a different number of columns
Looking in GIT, this query doesn’t look like it’s changed since this file was created. Is there an issue with this query? Or an issue with my database (MariaDB 5.5.60 on CentOS 7.5)?
production.ERROR: SQLSTATE[21000]: Cardinality violation: 1222 The used SELECT statements have a different number of columns (SQL: SELECT b.transport_id, b.transport_type, b.transport_name FROM alert_transport_map AS a LEFT JOIN alert_transports AS b ON b.transport_id=a.transport_or_group_id WHERE a.target_type=‘single’ AND a.rule_id=24 UNION DISTINCT SELECT d.transport_id, d.transport_type FROM alert_transport_map AS a LEFT JOIN alert_transport_groups AS b ON a.transport_or_group_id=b.transport_group_id LEFT JOIN transport_group_transport AS c ON b.transport_group_id=c.transport_group_id LEFT JOIN alert_transports AS d ON c.transport_id=d.transport_id WHERE a.target_type=‘group’ AND a.rule_id=24) (SQL: SELECT b.transport_id, b.transport_type, b.transport_name FROM alert_transport_map AS a LEFT JOIN alert_transports AS b ON b.transport_id=a.transport_or_group_id WHERE a.target_type=‘single’ AND a.rule_id=24 UNION DISTINCT SELECT d.transport_id, d.transport_type FROM alert_transport_map AS a LEFT JOIN alert_transport_groups AS b ON a.transport_or_group_id=b.transport_group_id LEFT JOIN transport_group_transport AS c ON b.transport_group_id=c.transport_group_id LEFT JOIN alert_transports AS d ON c.transport_id=d.transport_id WHERE a.target_type=‘group’ AND a.rule_id=24)
/opt/librenms/LibreNMS/Alert/AlertUtil.php:30
/opt/librenms/includes/alerts.inc.php:834
/opt/librenms/includes/alerts.inc.php:609
/opt/librenms/includes/alerts.inc.php:811
/opt/librenms/alerts.php:48
====================================
Component
Version
LibreNMS
1.45-14-g4e8acdd89
DB Schema
270
PHP
7.2.10-0ubuntu0.18.04.1
MySQL
10.1.34-MariaDB-0ubuntu0.18.04.1
RRDTool
1.7.0
SNMP
NET-SNMP 5.7.3
====================================
[OK] Composer Version: 1.7.3
[OK] Dependencies up-to-date.
[OK] Database connection successful
[FAIL] MySQL column collation is wrong:
[FIX]:
Check https://t.libren.ms/-zdwk for info on how to fix.
Columns:
syslog facility utf8 utf8_general_ci
syslog priority utf8 utf8_general_ci
syslog level utf8 utf8_general_ci
syslog tag utf8 utf8_general_ci
syslog program utf8 utf8_general_ci
syslog msg utf8 utf8_general_ci
[OK] Database schema correct
[2018-11-07 14:29:01] production.ERROR: SQLSTATE[21000]: Cardinality violation: 1222 The used SELECT statements have a different number of columns (SQL: SELECT b.transport_id, b.transport_type, b.transport_name FROM alert_transport_map AS a LEFT JOIN alert_transports AS b ON b.transport_id=a.transport_or_group_id WHERE a.target_type=‘single’ AND a.rule_id=7 UNION DISTINCT SELECT d.transport_id, d.transport_type FROM alert_transport_map AS a LEFT JOIN alert_transport_groups AS b ON a.transport_or_group_id=b.transport_group_id LEFT JOIN transport_group_transport AS c ON b.transport_group_id=c.transport_group_id LEFT JOIN alert_transports AS d ON c.transport_id=d.transport_id WHERE a.target_type=‘group’ AND a.rule_id=7) (SQL: SELECT b.transport_id, b.transport_type, b.transport_name FROM alert_transport_map AS a LEFT JOIN alert_transports AS b ON b.transport_id=a.transport_or_group_id WHERE a.target_type=‘single’ AND a.rule_id=7 UNION DISTINCT SELECT d.transport_id, d.transport_type FROM alert_transport_map AS a LEFT JOIN alert_transport_groups AS b ON a.transport_or_group_id=b.transport_group_id LEFT JOIN transport_group_transport AS c ON b.transport_group_id=c.transport_group_id LEFT JOIN alert_transports AS d ON c.transport_id=d.transport_id WHERE a.target_type=‘group’ AND a.rule_id=7)
/opt/librenms/LibreNMS/Alert/AlertUtil.php:30
/opt/librenms/includes/alerts.inc.php:834
/opt/librenms/includes/alerts.inc.php:609
/opt/librenms/includes/alerts.inc.php:811
/opt/librenms/alerts.php:48
[2018-11-07 14:29:01] production.ERROR: Symfony\Component\Debug\Exception\FatalThrowableError: Parse error: syntax error, unexpected ‘}’, expecting end of file in /opt/librenms/includes/alerts.inc.php(403) : eval()'d code:35
[OK] Composer Version: 1.7.3
[OK] Dependencies up-to-date.
[OK] Database connection successful
[OK] Database schema correct
[FAIL] Some devices have not completed their polling run in 5 minutes, this will create gaps in data.
[FIX]:
Check your poll log and see: http://docs.librenms.org/Support/Performance/
Devices:
Test alerts work, but we don’t get any device alerts anymore.
giving an update…Tried to rollback the AlertUtil.php to the old one. Seems to be working fine again except for the mail transport will try to look for additional logs and configs.
Already performed ./daily.sh but error still persists. Also noticed file was updated 2 days ago there might be missing parameters on php file. Will try to look at it.
I updated to the daily, but there is still a small error in the query. The second reference to “transport_name” should be d.transport_name instead of b.transport_name (about col 285). In other words, this section: