LLDP show wrong neighbors

just found the reason for this problem
in .mysql_history

drop tables neighbours;
CREATE TABLE `neighbours` ( `neighbour_id` int(11) NOT NULL, `device_id` int(11) DEFAULT 0, `port_id` int(11) DEFAULT NULL, `remote_port_id` int(11) DEFAULT NULL, `active` tinyint(1) NOT NULL DEFAULT 1, `protocol` varchar(11) CHARACTER SET utf8 DEFAULT NULL, `remote_hostname` varchar(253) CHARACTER SET utf8 NOT NULL, `remote_port` varchar(128) CHARACTER SET utf8 NOT NULL, `remote_platform` varchar(256) COLLATE utf8_unicode_ci DEFAULT NULL, `remote_version` text COLLATE utf8_unicode_ci DEFAULT NULL, `remote_address` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
----------------------------

i have maked a clone of my vm, and the try run
DROP TABLE `neighbours`;
CREATE TABLE `neighbours` ( `neighbour_id` int(11) NOT NULL, `device_id` int(11) DEFAULT 0, `port_id` int(11) DEFAULT NULL, `remote_port_id` int(11) DEFAULT NULL, `active` tinyint(1) NOT NULL DEFAULT 1, `protocol` varchar(11) CHARACTER SET utf8 DEFAULT NULL, `remote_hostname` varchar(253) CHARACTER SET utf8 NOT NULL, `remote_port` varchar(128) CHARACTER SET utf8 NOT NULL, `remote_platform` varchar(256) COLLATE utf8_unicode_ci DEFAULT NULL, `remote_version` text COLLATE utf8_unicode_ci DEFAULT NULL, `remote_address` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ALTER TABLE `neighbours` ADD INDEX `count` (`port_id`, `active`);
ALTER TABLE `neighbours` CHANGE `remote_version` `remote_version` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;
ALTER TABLE `neighbours` CHANGE `remote_platform` `remote_platform` VARCHAR(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;
UPDATE `neighbours` SET `remote_port_id` = NULL WHERE `remote_port_id` = '0';
ALTER TABLE `neighbours` ADD `device_id` INT NULL DEFAULT '0' AFTER `neighbour_id`, ADD INDEX `device_id` (`device_id`);
UPDATE `neighbours` INNER JOIN `ports` ON `neighbours`.`port_id` = `ports`.`port_id` SET `neighbours`.`device_id` = `ports`.`device_id`;
DELETE FROM `neighbours` WHERE `device_id` = '0';
ALTER TABLE `neighbours` CHANGE `protocol` `protocol` VARCHAR(11) CHARACTER SET latin1 COLLATE latin1_general_ci NULL DEFAULT NULL, CHANGE `remote_hostname` `remote_hostname` VARCHAR(253) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL, CHANGE `remote_platform` `remote_platform` VARCHAR(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, CHANGE `remote_address` `remote_address` VARCHAR(128) CHARACTER SET latin1 COLLATE latin1_general_ci NULL DEFAULT NULL;
ALTER TABLE `neighbours` ADD `autodiscovery_id` INT NULL DEFAULT NULL AFTER `active`;
ALTER TABLE `neighbours` ADD `last_change` TIMESTAMP on update CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `remote_address`;

and then run a /opt/observium/discovery.php -h all -m neighbours

hope this will work

yah, not know what happened on your db.. try run each index separatelly:

ALTER TABLE `neighbours` ADD PRIMARY KEY (`neighbour_id`);

ALTER TABLE `neighbours` ADD KEY `src_if` (`port_id`);

ALTER TABLE `neighbours` ADD KEY `dst_if` (`remote_port_id`);

ALTER TABLE `neighbours` ADD KEY `count` (`port_id`,`active`);

ALTER TABLE `neighbours` ADD KEY `device_id` (`device_id`);

don't worry if any command return error, there main first command (primary key).

getting a error 1061

MariaDB [observium]> ALTER TABLE `neighbours` ADD PRIMARY KEY (`neighbour_id`), ADD KEY `src_if` (`port_id`), ADD KEY `dst_if` (`remote_port_id`), ADD KEY `count` (`port_id`,`active`), ADD KEY `device_id` (`device_id`);
ERROR 1061 (42000): Duplicate key name 'src_if'
MariaDB [observium]>

As I thought, you have a broken database structure. You probably had an old version of MariaDB (or upgraded system).

Anyway, currently neighbours db not have indexes. This possible to fix only by manual SQL command. Try from console connect to db with root user and password (there observium is name of observium db):

mysql -u root -p observium

And run SQL command:

ALTER TABLE `neighbours` ADD PRIMARY KEY (`neighbour_id`), ADD KEY `src_if` (`port_id`), ADD KEY `dst_if` (`remote_port_id`), ADD KEY `count` (`port_id`,`active`), ADD KEY `device_id` (`device_id`);

After all rediscover device.

[2020/07/28 18:20:26 +0000] discovery.php(16104): Error: (1061) Duplicate key name 'user_auth'
[2020/09/03 11:26:28 +0000] discovery.php(89369): ====== Schema update 429 -> 430 ==============
[2020/09/03 11:26:28 +0000] discovery.php(89369): Query: ALTER TABLE `autodiscovery` ADD INDEX `remote_hostname_ip` (`poller_id`, `remote_hostname`, `remote_ip`);
[2020/09/03 11:26:28 +0000] discovery.php(89369): Error: (1061) Duplicate key name 'remote_hostname_ip'
[2020/09/03 11:26:28 +0000] discovery.php(89369): ====== Schema update 430 -> 431 ==============
[2020/09/03 11:26:28 +0000] discovery.php(89369): Query: ALTER TABLE `entity_permissions` ADD `auth_mechanism` VARCHAR(11) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL AFTER `user_id`;
[2020/09/03 11:26:28 +0000] discovery.php(89369): Error: (1060) Duplicate column name 'auth_mechanism'
[2020/09/03 11:26:28 +0000] discovery.php(89369): Query: ALTER TABLE `entity_permissions` ADD UNIQUE `user_auth` (`user_id`, `auth_mechanism`, `entity_id`, `entity_type`);
[2020/09/03 11:26:28 +0000] discovery.php(89369): Error: (1061) Duplicate key name 'user_auth'
[2020/09/03 11:26:28 +0000] discovery.php(89369): Query: ALTER TABLE `roles_users` ADD `auth_mechanism` VARCHAR(11) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL AFTER `user_id`;
[2020/09/03 11:26:28 +0000] discovery.php(89369): Error: (1060) Duplicate column name 'auth_mechanism'
[2020/09/03 11:26:28 +0000] discovery.php(89369): Query: ALTER TABLE `roles_users` ADD UNIQUE `user_auth` (`user_id`, `auth_mechanism`);
[2020/09/03 11:26:28 +0000] discovery.php(89369): Error: (1061) Duplicate key name 'user_auth'
[2020/09/03 16:59:35 +0000] discovery.php(337754): ====== Schema update 428 -> 429 ==============
[2020/09/03 16:59:35 +0000] discovery.php(337754): Query: CREATE TABLE `autodiscovery` ( `autodiscovery_id` INT NOT NULL AUTO_INCREMENT , `poller_id` INT NOT NULL , `device_id` INT NULL DEFAULT NULL , `remote_hostname` VARCHAR(253) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , `remote_ip` VARCHAR(128) CHARACTER SET latin1 COLLATE latin1_general_ci NULL DEFAULT NULL , `remote_device_id` INT NULL DEFAULT NULL , `protocol` VARCHAR(11) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL , `added` TIMESTAMP NOT NULL , `last_checked` TIMESTAMP on update CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , `last_reason` ENUM('ok','no_xdp','no_fqdn','no_dns','no_ip_permit','no_ping','no_snmp','no_db','duplicated','unknown') CHARACTER SET latin1 COLLATE latin1_general_ci NULL DEFAULT NULL , PRIMARY KEY (`autodiscovery_id`)) ENGINE = InnoDB CHARSET=utf8 COLLATE utf8_general_ci;
[2020/09/03 16:59:35 +0000] discovery.php(337754): Error: (1050) Table 'autodiscovery' already exists
[2020/09/03 16:59:35 +0000] discovery.php(337754): Query: ALTER TABLE `autodiscovery` ADD INDEX `remote_hostname_ip` (`poller_id`, `remote_hostname`, `remote_ip`);
[2020/09/03 16:59:35 +0000] discovery.php(337754): Error: (1061) Duplicate key name 'remote_hostname_ip'
[2020/09/03 16:59:35 +0000] discovery.php(337754): ====== Schema update 429 -> 430 ==============
[2020/09/03 16:59:35 +0000] discovery.php(337754): Query: ALTER TABLE `autodiscovery` ADD INDEX `remote_hostname_ip` (`poller_id`, `remote_hostname`, `remote_ip`);
[2020/09/03 16:59:35 +0000] discovery.php(337754): Error: (1061) Duplicate key name 'remote_hostname_ip'
[2020/09/03 16:59:35 +0000] discovery.php(337754): ====== Schema update 430 -> 431 ==============
[2020/09/03 16:59:35 +0000] discovery.php(337754): Query: ALTER TABLE `entity_permissions` ADD `auth_mechanism` VARCHAR(11) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL AFTER `user_id`;
[2020/09/03 16:59:35 +0000] discovery.php(337754): Error: (1060) Duplicate column name 'auth_mechanism'
[2020/09/03 16:59:35 +0000] discovery.php(337754): Query: ALTER TABLE `entity_permissions` ADD UNIQUE `user_auth` (`user_id`, `auth_mechanism`, `entity_id`, `entity_type`);
[2020/09/03 16:59:35 +0000] discovery.php(337754): Error: (1061) Duplicate key name 'user_auth'
[2020/09/03 16:59:35 +0000] discovery.php(337754): Query: ALTER TABLE `roles_users` ADD `auth_mechanism` VARCHAR(11) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL AFTER `user_id`;
[2020/09/03 16:59:35 +0000] discovery.php(337754): Error: (1060) Duplicate column name 'auth_mechanism'
[2020/09/03 16:59:35 +0000] discovery.php(337754): Query: ALTER TABLE `roles_users` ADD UNIQUE `user_auth` (`user_id`, `auth_mechanism`);
[2020/09/03 16:59:35 +0000] discovery.php(337754): Error: (1061) Duplicate key name 'user_auth'

I think you have broken schema db.
Please show content of log file (or just attach this file update-errors.log):

tail -n30 /opt/observium/logs/update-errors.log

And make dump of db schema (there no stored any private info, only db schema):

./test_db.php -e > db.dump

/opt/observium/discovery.php -V
Observium 21.9.11583

Please make and attach additional information about the device:

• full snmp dump from device:

  snmpwalk -v2c -c <community> -t 3 -Cc --hexOutputLength=0 -ObentxU <hostname> .1 > myagent.snmpwalk

  snmpwalk -v2c -c <community> -t 3 -Cc --hexOutputLength=0 -ObentxU <hostname> .1.3.6.1.4.1 >> myagent.snmpwalk

  If device not support SNMP version 2c, replace -v2c with -v1.

• If you have problems with discovery or poller processes, please do and attach these debugs:

  ./discovery.php -d -h <device>

  ./poller.php -d -h <device>

• additionally attach device and/or vendor specific MIB files

Note, this comment is added automatically.