Report database schema issues here


#447

MariaDB [librenms]> LOCK TABLES sensors WRITE, sensors_to_state_indexes WRITE, state_indexes WRITE;
Query OK, 0 rows affected (0.00 sec)

MariaDB [librenms]> alter table sensors_to_state_indexes drop foreign key sensors_to_state_indexes_ibfk_2, drop foreign key sensors_to_state_indexes_sensor_id_foreign;
ERROR 1025 (HY000): Error on rename of ‘./librenms/sensors_to_state_indexes’ to ‘./librenms/#sql2-1661-2202ee’ (errno: 152)


#448

Likely the indexes on that table have a different name. Check validate output it recently added the ability to check foreign key indexes.


#449

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

Component Version
LibreNMS 1.47-87-g45e0e5e
DB Schema 1000
PHP 7.2.13
MySQL 5.5.60-MariaDB
RRDTool 1.4.8
SNMP NET-SNMP 5.7.2

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

[OK] Composer Version: 1.8.0
[OK] Dependencies up-to-date.
[OK] Database connection successful
[FAIL] Database: incorrect column (sensors/sensor_id)
[FAIL] Database: incorrect column (sensors_to_state_indexes/sensor_id)
[FAIL] Database: incorrect column (sensors_to_state_indexes/state_index_id)
[FAIL] Database: incorrect column (state_indexes/state_index_id)
[FAIL] We have detected that your database schema may be wrong, please report the following to us on Discord (https://t.libren.ms/discord) or the community site (https://t.libren.ms/5gscd):
[FIX]:
Run the following SQL statements to fix.
SQL Statements:
ALTER TABLE sensors CHANGE sensor_id sensor_id int(10) unsigned NOT NULL auto_increment;
ALTER TABLE sensors_to_state_indexes CHANGE sensor_id sensor_id int(10) unsigned NOT NULL ;
ALTER TABLE sensors_to_state_indexes CHANGE state_index_id state_index_id int(10) unsigned NOT NULL ;
ALTER TABLE state_indexes CHANGE state_index_id state_index_id int(10) unsigned NOT NULL auto_increment;

MariaDB [librenms]> ALTER TABLE sensors CHANGE sensor_id sensor_id int(10) unsigned NOT NULL auto_increment;
ERROR 1025 (HY000): Error on rename of ‘./librenms/#sql-1672_50e7d’ to ‘./librenms/sensors’ (errno: 150)
MariaDB [librenms]> SHOW COLUMNS FROM sensors;
±--------------------------±-----------------±-----±----±------------------±----------------------------+
| Field | Type | Null | Key | Default | Extra |
±--------------------------±-----------------±-----±----±------------------±----------------------------+
| sensor_id | int(11) | NO | PRI | NULL | auto_increment |
| sensor_deleted | tinyint(1) | NO | | 0 | |
| sensor_class | varchar(64) | NO | MUL | NULL | |
| device_id | int(10) unsigned | NO | MUL | 0 | |
| poller_type | varchar(16) | NO | | snmp | |
| sensor_oid | varchar(255) | NO | | NULL | |
| sensor_index | varchar(128) | YES | | NULL | |
| sensor_type | varchar(255) | NO | MUL | NULL | |
| sensor_descr | varchar(255) | YES | | NULL | |
| group | varchar(255) | YES | | NULL | |
| sensor_divisor | bigint(20) | NO | | 1 | |
| sensor_multiplier | int(11) | NO | | 1 | |
| sensor_current | double | YES | | NULL | |
| sensor_limit | double | YES | | NULL | |
| sensor_limit_warn | double | YES | | NULL | |
| sensor_limit_low | double | YES | | NULL | |
| sensor_limit_low_warn | double | YES | | NULL | |
| sensor_alert | tinyint(1) | NO | | 1 | |
| sensor_custom | enum(‘No’,‘Yes’) | NO | | No | |
| entPhysicalIndex | varchar(16) | YES | | NULL | |
| entPhysicalIndex_measured | varchar(16) | YES | | NULL | |
| lastupdate | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| sensor_prev | double | YES | | NULL | |
| user_func | varchar(100) | YES | | NULL | |
±--------------------------±-----------------±-----±----±------------------±----------------------------+
24 rows in set (0.00 sec)

MariaDB [librenms]> ALTER TABLE table_name
-> ;
ERROR 1146 (42S02): Table ‘librenms.table_name’ doesn’t exist
MariaDB [librenms]> LOCK TABLES sensors WRITE, sensors_to_state_indexes WRITE, state_indexes WRITE;
Query OK, 0 rows affected (0.00 sec)

MariaDB [librenms]> alter table sensors_to_state_indexes drop foreign key sensors_to_state_indexes_ibfk_2, drop foreign key sensors_to_state_indexes_sensor_id_foreign;
ERROR 1025 (HY000): Error on rename of ‘./librenms/sensors_to_state_indexes’ to ‘./librenms/#sql2-1672-50e7d’ (errno: 152)

MariaDB [librenms]> ALTER TABLE table_name
-> ;
ERROR 1146 (42S02): Table ‘librenms.table_name’ doesn’t exist
MariaDB [librenms]> LOCK TABLES sensors WRITE, sensors_to_state_indexes WRITE, state_indexes WRITE;
Query OK, 0 rows affected (0.00 sec)

MariaDB [librenms]> alter table sensors_to_state_indexes drop foreign key sensors_to_state_indexes_ibfk_2, drop foreign key sensors_to_state_indexes_sensor_id_foreign;
ERROR 1025 (HY000): Error on rename of ‘./librenms/sensors_to_state_indexes’ to ‘./librenms/#sql2-1672-50e7d’ (errno: 152)


#450

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

Component Version
LibreNMS 1.48-4-gf13caae2e
DB Schema 2019_01_16_195644_add_vrf_id_and_bgpLocalAs (131)
PHP 7.1.17-0ubuntu0.17.10.1
MySQL 10.1.30-MariaDB-0ubuntu0.17.10.1
RRDTool 1.7.0
SNMP NET-SNMP 5.7.3

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

[OK] Composer Version: 1.8.3
[OK] Dependencies up-to-date.
[OK] Database connection successful
[FAIL] Database: incorrect column (sensors/sensor_id)
[FAIL] Database: incorrect column (sensors_to_state_indexes/sensor_id)
[FAIL] Database: incorrect column (sensors_to_state_indexes/state_index_id)
[FAIL] Database: extra constraint (sensors_to_state_indexes/sensors_to_state_indexes_ibfk_10)
[FAIL] Database: extra constraint (sensors_to_state_indexes/sensors_to_state_indexes_ibfk_11)
[FAIL] Database: extra constraint (sensors_to_state_indexes/sensors_to_state_indexes_ibfk_12)
[FAIL] Database: extra constraint (sensors_to_state_indexes/sensors_to_state_indexes_ibfk_13)
[FAIL] Database: extra constraint (sensors_to_state_indexes/sensors_to_state_indexes_ibfk_14)
[FAIL] Database: extra constraint (sensors_to_state_indexes/sensors_to_state_indexes_ibfk_15)
[FAIL] Database: extra constraint (sensors_to_state_indexes/sensors_to_state_indexes_ibfk_16)
[FAIL] Database: extra constraint (sensors_to_state_indexes/sensors_to_state_indexes_ibfk_17)
[FAIL] Database: extra constraint (sensors_to_state_indexes/sensors_to_state_indexes_ibfk_18)
[FAIL] Database: extra constraint (sensors_to_state_indexes/sensors_to_state_indexes_ibfk_19)
[FAIL] Database: extra constraint (sensors_to_state_indexes/sensors_to_state_indexes_ibfk_20)
[FAIL] Database: extra constraint (sensors_to_state_indexes/sensors_to_state_indexes_ibfk_21)
[FAIL] Database: extra constraint (sensors_to_state_indexes/sensors_to_state_indexes_ibfk_22)
[FAIL] Database: extra constraint (sensors_to_state_indexes/sensors_to_state_indexes_ibfk_23)
[FAIL] Database: extra constraint (sensors_to_state_indexes/sensors_to_state_indexes_ibfk_24)
[FAIL] Database: extra constraint (sensors_to_state_indexes/sensors_to_state_indexes_ibfk_25)
[FAIL] Database: extra constraint (sensors_to_state_indexes/sensors_to_state_indexes_ibfk_26)
[FAIL] Database: extra constraint (sensors_to_state_indexes/sensors_to_state_indexes_ibfk_27)
[FAIL] Database: extra constraint (sensors_to_state_indexes/sensors_to_state_indexes_ibfk_28)
[FAIL] Database: extra constraint (sensors_to_state_indexes/sensors_to_state_indexes_ibfk_29)
[FAIL] Database: extra constraint (sensors_to_state_indexes/sensors_to_state_indexes_ibfk_30)
[FAIL] Database: extra constraint (sensors_to_state_indexes/sensors_to_state_indexes_ibfk_31)
[FAIL] Database: extra constraint (sensors_to_state_indexes/sensors_to_state_indexes_ibfk_32)
[FAIL] Database: extra constraint (sensors_to_state_indexes/sensors_to_state_indexes_ibfk_33)
[FAIL] Database: extra constraint (sensors_to_state_indexes/sensors_to_state_indexes_ibfk_6)
[FAIL] Database: extra constraint (sensors_to_state_indexes/sensors_to_state_indexes_ibfk_7)
[FAIL] Database: extra constraint (sensors_to_state_indexes/sensors_to_state_indexes_ibfk_8)
[FAIL] Database: extra constraint (sensors_to_state_indexes/sensors_to_state_indexes_ibfk_9)
[FAIL] Database: incorrect column (state_indexes/state_index_id)
[FAIL] We have detected that your database schema may be wrong, please report the following to us on Discord (https://t.libren.ms/discord) or the community site (https://t.libren.ms/5gscd):
[FIX]:
Run the following SQL statements to fix.
SQL Statements:
ALTER TABLE sensors CHANGE sensor_id sensor_id int(10) unsigned NOT NULL auto_increment;
ALTER TABLE sensors_to_state_indexes CHANGE sensor_id sensor_id int(10) unsigned NOT NULL ;
ALTER TABLE sensors_to_state_indexes CHANGE state_index_id state_index_id int(10) unsigned NOT NULL ;
ALTER TABLE sensors_to_state_indexes DROP FOREIGN KEY sensors_to_state_indexes_ibfk_10;
ALTER TABLE sensors_to_state_indexes DROP FOREIGN KEY sensors_to_state_indexes_ibfk_11;
ALTER TABLE sensors_to_state_indexes DROP FOREIGN KEY sensors_to_state_indexes_ibfk_12;
ALTER TABLE sensors_to_state_indexes DROP FOREIGN KEY sensors_to_state_indexes_ibfk_13;
ALTER TABLE sensors_to_state_indexes DROP FOREIGN KEY sensors_to_state_indexes_ibfk_14;
ALTER TABLE sensors_to_state_indexes DROP FOREIGN KEY sensors_to_state_indexes_ibfk_15;
ALTER TABLE sensors_to_state_indexes DROP FOREIGN KEY sensors_to_state_indexes_ibfk_16;
ALTER TABLE sensors_to_state_indexes DROP FOREIGN KEY sensors_to_state_indexes_ibfk_17;
ALTER TABLE sensors_to_state_indexes DROP FOREIGN KEY sensors_to_state_indexes_ibfk_18;
ALTER TABLE sensors_to_state_indexes DROP FOREIGN KEY sensors_to_state_indexes_ibfk_19;
ALTER TABLE sensors_to_state_indexes DROP FOREIGN KEY sensors_to_state_indexes_ibfk_20;
ALTER TABLE sensors_to_state_indexes DROP FOREIGN KEY sensors_to_state_indexes_ibfk_21;
and 17 more…


#451

DROP the foreign keys first, then run the other alter statements.