[SOLVED] Email transport and Alerting issues dues to database issues

nic0 · 4 September 2017 13:48

Hi everyone, i have an issue with the “Email transport” section;

When I am going to : Settings / Alerting Settings / Email tranport

If i toogle “Enable email alerting” to Yes, a “ERROR : No config item” popup appears.
I have the same issue on the “General alert settings” if I toogle “Disable alerting” to “No” the same happen.

I have just upgrade the server from Debian Jessie to Stretch, but I can’t tell if this issue was here before …

This is my output of ./validate.php

====================================

Component	Version
LibreNMS	1.31.03-13-gda0dcda40
DB Schema	205
PHP	7.0.19-1
MySQL	10.1.26-MariaDB-0+deb9u1
RRDTool	1.6.0
SNMP	NET-SNMP 5.7.3

====================================

[OK] Database connection successful
[FAIL] You have not set sql_mode=’’ in your mysql config
[OK] Database schema correct

I really don’t know if this sql_mode is really important are may cause this issue.

Thanks a lot

Regards

Nicolas

Kevin_Krumm · 4 September 2017 13:50

fix this first

nic0 · 4 September 2017 14:05

I set sql_mode = “” to my.cnf

There is no warning, but the problem on the toogle button is still there.

====================================

Component	Version
LibreNMS	1.31.03-13-gda0dcda40
DB Schema	205
PHP	7.0.19-1
MySQL	10.1.26-MariaDB-0+deb9u1
RRDTool	1.6.0
SNMP	NET-SNMP 5.7.3

====================================

[OK] Database connection successful
[OK] Database schema correct

Kevin_Krumm · 4 September 2017 14:08

Try restarting MySQL

nic0 · 4 September 2017 14:08

In fact I set it to :

sql_mode = “NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”

Because it’s the default setting shown by a show global variables like ‘%mod%’; before force it in my.cnf.

Right now there is no advertissment by validate.php

Kevin_Krumm · 4 September 2017 14:09

its part of the install doc

nic0 · 4 September 2017 14:10

sorry ok, I set it to :

sql_mode = ‘’

…

Kevin_Krumm · 4 September 2017 14:12

after restarting check your config.php

nic0 · 4 September 2017 14:13

ok you can standby my ticket, I will re-read the install guide.
It seem some things have changed since our fist install …

Thanks

nic0 · 4 September 2017 14:42

Ok I have checked the install guide.

I had forgotten the innodb_file_per_table=1 and lower_case_table_names=0

Now

All seem ok, no error on validate.php.
My config.php seems to be ok (there is no references inside about email settings.
I don’t see any errors in the libreNMS logs nor php or apache logs.

But the problem is still there.

This could be a database issue ?

Regards

Kevin_Krumm · 4 September 2017 15:39

To me sounds like web UI can’t talk to config.php, maybe a permission issue.

nic0 · 4 September 2017 15:50

Hmm ok, in config.php librenms user is defined as main user

$config['user'] = "librenms";

And config.php right are :

-rwxr-xr-x 1 librenms librenms 4421 sept. 4 16:36 config.php

Maybe something to configure are verify from the WebUI ?

Thanks

Kevin_Krumm · 4 September 2017 15:59

To be honest, at this point I’m not sure…maybe check your Web Server…Are you running Apache?

nic0 · 4 September 2017 16:12

Yes apache is running fine.
I have added www-data user to ibrenms group to test.

When I click on the toogle button, I only show this 200 ok in apaches logs :

"POST /ajax_form.php HTTP/1.1" 200 399 "https://librenms.admin.celeste.fr/settings/sub=alerting/"

This is my config.php if you have some time to see :

https://zerobin.net/?2b95db80f3ac0e6f#9xlgW7+ivyHbi4Wzhrz95mpUXPKUZn+OZtTs3hGBl6I=

In fact, it seem to have a problem with it because, thereis conf about ldap, oxidized … that are working correctly but are not seen in the webUi.

For example for oxidized, I see nothing in the WebUi “Oxidized integration” tab.

Thanks a lot.

laf · 4 September 2017 18:52

Re-post the output of validate.php, it should show as clear.

If this install isn’t being used then you probably should just delete the DB and re-run php ./build-base.php

If the install is running then you will need to manually import the missing config:

INSERT INTO `config` VALUES (441,'alert.macros.rule.now','NOW()','NOW()','Macro currenttime','alerting',0,'macros',0,'1','0'),(442,'alert.macros.rule.past_5m','DATE_SUB(NOW(),INTERVAL 5 MINUTE)','DATE_SUB(NOW(),INTERVAL 5 MINUTE)','Macro past 5 minutes','alerting',0,'macros',0,'1','0'),(443,'alert.macros.rule.past_10m','DATE_SUB(NOW(),INTERVAL 10 MINUTE)','DATE_SUB(NOW(),INTERVAL 10 MINUTE)','Macro past 10 minutes','alerting',0,'macros',0,'1','0'),(444,'alert.macros.rule.past_15m','DATE_SUB(NOW(),INTERVAL 15 MINUTE)','DATE_SUB(NOW(),INTERVAL 15 MINUTE)','Macro past 15 minutes','alerting',0,'macros',0,'1','0'),(445,'alert.macros.rule.past_30m','DATE_SUB(NOW(),INTERVAL 30 MINUTE)','DATE_SUB(NOW(),INTERVAL 30 MINUTE)','Macro past 30 minutes','alerting',0,'macros',0,'1','0'),(446,'alert.macros.rule.device','(%devices.disabled = 0 && %devices.ignore = 0)','(%devices.disabled = 0 && %devices.ignore = 0)','Devices that aren\'t disabled or ignored','alerting',0,'macros',0,'1','0'),(447,'alert.macros.rule.device_up','(%devices.status = 1 && %macros.device)','(%devices.status = 1 && %macros.device)','Devices that are up','alerting',0,'macros',0,'1','0'),(448,'alert.macros.rule.device_down','(%devices.status = 0 && %macros.device)','(%devices.status = 0 && %macros.device)','Devices that are down','alerting',0,'macros',0,'1','0'),(449,'alert.macros.rule.port','(%ports.deleted = 0 && %ports.ignore = 0 && %ports.disabled = 0)','(%ports.deleted = 0 && %ports.ignore = 0 && %ports.disabled = 0)','Ports that aren\'t disabled, ignored or delete','alerting',0,'macros',0,'1','0'),(450,'alert.macros.rule.port_up','(%ports.ifOperStatus = \"up\" && %ports.ifAdminStatus = \"up\" && %macros.port)','(%ports.ifOperStatus = \"up\" && %ports.ifAdminStatus = \"up\" && %macros.port)','Ports that are up','alerting',0,'macros',0,'1','0'),(451,'alert.admins','true','true','Alert administrators','alerting',0,'general',0,'0','0'),(452,'alert.default_only','true','true','Only alert default mail contact','alerting',0,'general',0,'0','0'),(453,'alert.default_mail','','','The default mail contact','alerting',0,'general',0,'0','0'),(454,'alert.transports.pagerduty','','','Pagerduty transport - put your API key here','alerting',0,'transports',0,'0','0'),(455,'alert.pagerduty.account','','','Pagerduty account name','alerting',0,'transports',0,'0','0'),(456,'alert.pagerduty.service','','','Pagerduty service name','alerting',0,'transports',0,'0','0'),(457,'alert.tolerance_window','5','5','Tolerance window in seconds','alerting',0,'general',0,'0','0'),(458,'email_backend','mail','mail','The backend to use for sending email, can be mail, sendmail or smtp','alerting',0,'general',0,'0','0'),(459,'alert.macros.rule.past_60m','DATE_SUB(NOW(),INTERVAL 60 MINUTE)','DATE_SUB(NOW(),INTERVAL 60 MINUTE)','Macro past 60 minutes','alerting',0,'macros',0,'1','0'),(460,'alert.macros.rule.port_usage_perc','((%ports.ifInOctets_rate*8)/%ports.ifSpeed)*100','((%ports.ifInOctets_rate*8)/%ports.ifSpeed)*100','Ports using more than X perc','alerting',0,'macros',0,'1','0'),(461,'alert.transports.dummy','false','false','Dummy transport','alerting',0,'transports',0,'0','0'),(462,'alert.transports.mail','true','true','Mail alerting transport','alerting',0,'transports',0,'0','0'),(463,'alert.transports.irc','FALSE','false','IRC alerting transport','alerting',0,'transports',0,'0','0'),(464,'alert.globals','true','TRUE','Alert read only administrators','alerting',0,'general',0,'0','0'),(465,'email_from','NULL','NULL','Email address used for sending emails (from)','alerting',0,'general',0,'0','0'),(466,'email_user','LibreNMS','LibreNMS','Name used as part of the from address','alerting',0,'general',0,'0','0'),(467,'email_sendmail_path','/usr/sbin/sendmail','/usr/sbin/sendmail','Location of sendmail if using this option','alerting',0,'general',0,'0','0'),(468,'email_smtp_host','localhost','localhost','SMTP Host for sending email if using this option','alerting',0,'general',0,'0','0'),(469,'email_smtp_port','25','25','SMTP port setting','alerting',0,'general',0,'0','0'),(470,'email_smtp_timeout','10','10','SMTP timeout setting','alerting',0,'general',0,'0','0'),(471,'email_smtp_secure','','','Enable / disable encryption (use tls or ssl)','alerting',0,'general',0,'0','0'),(472,'email_smtp_auth','false','FALSE','Enable / disable smtp authentication','alerting',0,'general',0,'0','0'),(473,'email_smtp_username','NULL','NULL','SMTP Auth username','alerting',0,'general',0,'0','0'),(474,'email_smtp_password','NULL','NULL','SMTP Auth password','alerting',0,'general',0,'0','0'),(475,'alert.macros.rule.port_down','(%ports.ifOperStatus = \"down\" && %ports.ifAdminStatus != \"down\" && %macros.port)','(%ports.ifOperStatus = \"down\" && %ports.ifAdminStatus != \"down\" && %macros.port)','Ports that are down','alerting',0,'macros',0,'1','0'),(486,'alert.syscontact','true','TRUE','Issue alerts to sysContact','alerting',0,'general',0,'0','0'),(487,'alert.fixed-contacts','true','TRUE','If TRUE any changes to sysContact or users emails will not be honoured whilst alert is active','alerting',0,'general',0,'0','0'),(488,'alert.transports.nagios','','','Nagios compatible via FIFO','alerting',0,'transports',0,'0','0');
insert into config (config_name,config_value,config_default,config_descr,config_group,config_group_order,config_sub_group,config_sub_group_order,config_hidden,config_disabled) values ('alert.transports.pushbullet','','','Pushbullet access token','alerting',0,'transports',0,'0','0');
INSERT INTO config (config_name,config_value,config_default,config_descr,config_group,config_group_order,config_sub_group,config_sub_group_order,config_hidden,config_disabled) values ('alert.transports.clickatell.token','','','Clickatell access token','alerting',0,'transports',0,'0','0'),('alert.transports.playsms.url','','','PlaySMS API URL','alerting',0,'transports',0,'0','0'),('alert.transports.playsms.user','','','PlaySMS User','alerting',0,'transports',0,'0','0'),('alert.transports.playsms.from','','','PlaySMS From','alerting',0,'transports',0,'0','0'),('alert.transports.playsms.token','','','PlaySMS Token','alerting',0,'transports',0,'0','0'), ('alert.transports.victorops.url','','','VictorOps Post URL - Replace routing_key!','alerting',0,'transports',0,'0','0');
INSERT INTO `config` (`config_name`,`config_value`,`config_default`,`config_descr`,`config_group`,`config_group_order`,`config_sub_group`,`config_sub_group_order`,`config_hidden`,`config_disabled`) VALUES ('alert.transports.canopsis.user','','','Canopsis User','alerting',0, 'transports', 0, '0', '0');
INSERT INTO `config` (`config_name`,`config_value`,`config_default`,`config_descr`,`config_group`,`config_group_order`,`config_sub_group`,`config_sub_group_order`,`config_hidden`,`config_disabled`) VALUES ('alert.transports.canopsis.passwd','','','Canopsis Password','alerting',0, 'transports', 0, '0', '0');
INSERT INTO `config` (`config_name`,`config_value`,`config_default`,`config_descr`,`config_group`,`config_group_order`,`config_sub_group`,`config_sub_group_order`,`config_hidden`,`config_disabled`) VALUES ('alert.transports.canopsis.host','','','Canopsis Hostname','alerting',0, 'transports', 0, '0', '0');
INSERT INTO `config` (`config_name`,`config_value`,`config_default`,`config_descr`,`config_group`,`config_group_order`,`config_sub_group`,`config_sub_group_order`,`config_hidden`,`config_disabled`) VALUES ('alert.transports.canopsis.vhost','','','Canopsis vHost','alerting',0, 'transports', 0, '0', '0');
INSERT INTO config (`config_name`,`config_value`,`config_default`,`config_descr`,`config_group`,`config_group_order`,`config_sub_group`,`config_sub_group_order`,`config_hidden`,`config_disabled`) VALUES ('alert.transports.canopsis.port','','','Canopsis Port number','alerting',0, 'transports', 0, '0', '0');
INSERT INTO config (`config_name`,`config_value`,`config_default`,`config_descr`,`config_group`,`config_group_order`,`config_sub_group`,`config_sub_group_order`,`config_hidden`,`config_disabled`) VALUES ('alert.transports.osticket.url','','','osTicket API URL','alerting',0, 'transports', 0, '0', '0');
INSERT INTO config (`config_name`,`config_value`,`config_default`,`config_descr`,`config_group`,`config_group_order`,`config_sub_group`,`config_sub_group_order`,`config_hidden`,`config_disabled`) VALUES ('alert.transports.osticket.token','','','osTicket API Token','alerting',0, 'transports', 0, '0', '0');
INSERT INTO config (`config_name`,`config_value`,`config_default`,`config_descr`,`config_group`,`config_group_order`,`config_sub_group`,`config_sub_group_order`,`config_hidden`,`config_disabled`) VALUES ('alert.transports.msteams.url','','','Microsoft Teams Webhook URL','alerting',0, 'transports', 0, '0', '0');
INSERT INTO config (`config_name`, `config_value`, `config_default`, `config_descr`, `config_group`, `config_group_order`, `config_sub_group`, `config_sub_group_order`, `config_hidden`, `config_disabled`) VALUES ('alert.transports.ciscospark.token','','','Cisco Spark API Token','alerting',0, 'transports', 0, '0', '0'), ('alert.transports.ciscospark.roomid','','','Cisco Spark RoomID','alerting',0, 'transports', 0, '0', '0');
INSERT INTO config (config_name,config_value,config_default,config_descr,config_group,config_group_order,config_sub_group,config_sub_group_order,config_hidden,config_disabled) values ('alert.transports.smseagle.url','','','SMSEagle API URL','alerting',0,'transports',0,'0','0'),('alert.transports.smseagle.user','','','SMSEagle User','alerting',0,'transports',0,'0','0'),('alert.transports.smseagle.token','','','SMSEagle Token','alerting',0,'transports',0,'0','0');
INSERT INTO config (config_name,config_value,config_default,config_descr,config_group,config_group_order,config_sub_group,config_sub_group_order,config_hidden,config_disabled) values ('alert.transports.syslog.syslog_host','','','Syslog Host','alerting',0,'transports',0,'0','0'),('alert.transports.syslog.syslog_port','','','Syslog Port','alerting',0,'transports',0,'0','0'),('alert.transports.syslog.syslog_facility','','','Syslog Facility','alerting',0,'transports',0,'0','0');
INSERT INTO config (config_name,config_value,config_default,config_descr,config_group,config_group_order,config_sub_group,config_sub_group_order,config_hidden,config_disabled) values ('alert.transports.elasticsearch.es_host','','127.0.0.1','Elasticsearch Host','alerting',0,'transports',0,'0','0'),('alert.transports.elasticsearch.es_port','','9200','Elasticsearch Port','alerting',0,'transports',0,'0','0'),('alert.transports.elasticsearch.es_index','','','Elasticsearch Index','alerting',0,'transports',0,'0','0'),('alert.transports.elasticsearch.es_proxy','','FALSE','Use Proxy?','alerting',0,'transports',0,'0','0');
INSERT INTO `config` (config_name,config_value,config_default,config_descr,config_group,config_group_order,config_sub_group,config_sub_group_order,config_hidden,config_disabled) values('alert.transports.jira.prjkey','','','Jira Project Key','alerting',0,'transports',0,default,default);
INSERT INTO `config` (config_name,config_value,config_default,config_descr,config_group,config_group_order,config_sub_group,config_sub_group_order,config_hidden,config_disabled) values('alert.transports.jira.url','','','Jira URL','alerting',0,'transports',0,default,default);
INSERT INTO `config` (config_name,config_value,config_default,config_descr,config_group,config_group_order,config_sub_group,config_sub_group_order,config_hidden,config_disabled) values('alert.transports.jira.username','','','Jira Username','alerting',0,'transports',0,default,default);
INSERT INTO `config` (config_name,config_value,config_default,config_descr,config_group,config_group_order,config_sub_group,config_sub_group_order,config_hidden,config_disabled) values('alert.transports.jira.password','','','Jira Password','alerting',0,'transports',0,default,default);
INSERT INTO `config` (config_name,config_value,config_default,config_descr,config_group,config_group_order,config_sub_group,config_sub_group_order,config_hidden,config_disabled) values('alert.transports.jira.issuetype','','','Jira Issue Type','alerting',0,'transports',0,default,default);

nic0 · 5 September 2017 07:31

Hello,

I think I have a big problem with the database, I had to import 051.sql from sql-schemas because your requests have failed.

They failed because the config table structure was not the expected one.
I had a foreign key on one field wich cause errors … I had an old scheme so

Know email alerting parameters are running but I think there are other problems elsewhere …

Maybe one upgrade, one day cause this databases issues wich are not revealed by validate.php.

Unfortunately I can’t really run again the build of the entire database because this install runs 200 devices with large history …

laf · 5 September 2017 07:47

I’ve not yet seen validate.php not show schema issues so I’m not sure it’s that. It doesn’t check the data within tables hence why I gave you the info above.

051.sql is from a long time ago so it seems like you’ve probably had issues for a while, or you’ve ended up with some corruption in your db.

What exactly was the issue you’ve seen?

nic0 · 6 September 2017 07:09

In fact I repaired (I hope) the install by replay some SQL files until it works correctly.

On Alerting Settings some fields under General alert setting and Email transport were not available when using toogle button and generated an “ERROR : No config item”.

Thanks to Kevin_Krumm I understood that my database was not correct.
Some records were missing and the structure of somes table was not correct.

So I played the 051.sql file for first.
But I as still receiving error on the webUI.
I also re-import 153.sql 108.sql 191.sql 075.sql 177.sql to create (or recreate) all the record afferent to the email alerting, TLS support, default contact …

I could identify the broken fields in the webUI because on some of them, when I mousse over the “?” icon there were not message printed.

In short, every work fine now !!
Alerts and email alerts

It just a little goofy but it works …

Thank again to the LibreNMS community