Duplicate entry 'alert.macros.rule.sensor_port_link

Help
1

Hi All,

My install of librenms has been slow over the past couple of weeks so I started to dig into the issue…

First things first, I ran the validate to see if there are any obvious issues and I came across this.

:/opt/librenms# ./validate.php

Component Version
LibreNMS 1.38-59-g052b9d1
DB Schema 247
PHP 7.0.28-0ubuntu0.16.04.1
MySQL 5.7.21-0ubuntu0.16.04.1
RRDTool 1.5.5
SNMP NET-SNMP 5.7.3
====================================

[OK] Composer Version: 1.6.4
[OK] Dependencies up-to-date.
[OK] Database connection successful
[FAIL] Database: missing index (config/uniqueindex_configname)
[FAIL] We have detected that your database schema may be wrong, please report the following to us on IRC or the community site (Report database schema issues here - LibreNMS Community):
[FIX] Run the following SQL statements to fix.
SQL Statements:
ALTER TABLE config ADD UNIQUE uniqueindex_configname (config_name);
[FAIL] Discovery has not completed in the last 24 hours.
[FIX] Check the cron job to make sure it is running and using discovery-wrapper.py

I’ve tried to execute the SQL statement in my database and this error below…

mysql> ALTER TABLE config ADD UNIQUE uniqueindex_configname (config_name);
ERROR 1062 (23000): Duplicate entry ‘alert.macros.rule.sensor_port_link’ for key ‘uniqueindex_configname’

And also tailing librenms.log i’m getting these SQL errors

MySQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘sensors.sensor_limit_low = && sensors.sensor_alert macros.device_up = )’ at line 1 (SELECT * FROM sensors WHERE (sensors.device_id = ‘42’) && (sensors.sensor_current sensors.sensor_limit_low = && sensors.sensor_alert macros.device_up = ))
2018-04-19 16:10:11 MySQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘= “” && syslog.msg REGEXP “failed login” && = “” )’ at line 1 (SELECT * FROM syslog WHERE (syslog.device_id = ‘42’) && (syslog.timestamp >= (DATE_SUB(NOW(),INTERVAL 5 MINUTE)) && = “” && syslog.msg REGEXP “failed login” && = “” ))
2018-04-19 16:10:11 MySQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘invalid user name/password. = 0 )’ at line 1 (SELECT * FROM syslog WHERE (syslog.device_id = ‘42’) && (syslog.timestamp = 0 && (DATE_SUB(NOW(),INTERVAL 5 MINUTE)) = 0 && syslog.msg = 0 && .invalid user name/password. = 0 ))

I can’t find anything on google or in this forum, so please any assistance will be highly appreciated.

Thank you.

2

Run DELETE FROM config WHERE config_name = 'alert.macros.rule.sensor_port_link';

Don’t worry default macros aren’t stored in the database any more.

Then add the index.

3

@murrant,

Thanks for your response. I ran the command you provided and then ran the validate.php script again and I got the same errors.

[OK] Composer Version: 1.6.4
[OK] Dependencies up-to-date.
[OK] Database connection successful
[FAIL] Database: missing index (config/uniqueindex_configname)
[FAIL] We have detected that your database schema may be wrong, please report the following to us on IRC or the community site (Report database schema issues here - LibreNMS Community):
[FIX] Run the following SQL statements to fix.
SQL Statements:
ALTER TABLE config ADD UNIQUE uniqueindex_configname (config_name);

I copied the SQL command from the “validate” output and ran it but I still get this error.

ERROR 1062 (23000): Duplicate entry ‘alert.macros.rule.state_sensor_critical’ for key ‘uniqueindex_configname’

Thanks again for your help with this…

4

DELETE FROM config WHERE config_name LIKE 'alert.macros.%';

5

@laf,

Thanks for also jumping in to assist. So I ran your command which ran OK and then when I executed the spit out from validate script I got a duplicate entry for a different table, so I basically went through and deleted all the “duplicate” entries that popped up and I was finally able to run the script.

mysql> DELETE FROM config WHERE config_name LIKE ‘alert.macros.%’;
Query OK, 64 rows affected (0.00 sec)

mysql> ALTER TABLE config ADD UNIQUE uniqueindex_configname (config_name);
ERROR 1062 (23000): Duplicate entry ‘alert.admins’ for key ‘uniqueindex_configname’
mysql> DELETE FROM config WHERE config_name LIKE ‘alert.macros.%’;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE config ADD UNIQUE uniqueindex_configname (config_name);
ERROR 1062 (23000): Duplicate entry ‘alert.admins’ for key ‘uniqueindex_configname’
mysql> DELETE FROM config WHERE config_name LIKE ‘alert.admins.%’;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE config ADD UNIQUE uniqueindex_configname (config_name);
ERROR 1062 (23000): Duplicate entry ‘alert.admins’ for key ‘uniqueindex_configname’
mysql> DELETE FROM config WHERE config_name LIKE ‘alert.admins%’;
Query OK, 2 rows affected (0.01 sec)

mysql> ALTER TABLE config ADD UNIQUE uniqueindex_configname (config_name);
ERROR 1062 (23000): Duplicate entry ‘alert.default_mail’ for key ‘uniqueindex_configname’
mysql> DELETE FROM config WHERE config_name LIKE ‘alert.default_mail%’;
Query OK, 2 rows affected (0.00 sec)

mysql> ALTER TABLE config ADD UNIQUE uniqueindex_configname (config_name);
ERROR 1062 (23000): Duplicate entry ‘alert.default_only’ for key ‘uniqueindex_configname’
mysql> DELETE FROM config WHERE config_name LIKE ‘alert.default_only%’;
Query OK, 2 rows affected (0.00 sec)

mysql> ALTER TABLE config ADD UNIQUE uniqueindex_configname (config_name);
ERROR 1062 (23000): Duplicate entry ‘alert.fixed-contacts’ for key ‘uniqueindex_configname’
mysql> DELETE FROM config WHERE config_name LIKE ‘alert.*%’;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM config WHERE config_name LIKE ‘alert.%’;
Query OK, 67 rows affected (0.01 sec)

mysql> ALTER TABLE config ADD UNIQUE uniqueindex_configname (config_name);
ERROR 1062 (23000): Duplicate entry ‘email_backend’ for key ‘uniqueindex_configname’
mysql> DELETE FROM config WHERE config_name LIKE ‘email_backend%’;
Query OK, 2 rows affected (0.00 sec)

mysql> ALTER TABLE config ADD UNIQUE uniqueindex_configname (config_name);
ERROR 1062 (23000): Duplicate entry ‘email_from’ for key ‘uniqueindex_configname’
mysql> DELETE FROM config WHERE config_name LIKE ‘email_%’;
Query OK, 22 rows affected (0.01 sec)

mysql> ALTER TABLE config ADD UNIQUE uniqueindex_configname (config_name);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

6

most of those you shouldn’t have deleted as you’ve now removed a large portion of your config.

You may as well now just bin off the rest and re-import: DELETE FROM config;

Then copy and paste all the lines from:

grep config sql-schema/*|grep -Pi 'insert|update'