Quick fix for [FAIL] Database: incorrect column (sensors/sensor_id) and more


#1

If you are running into this issue:

[OK]    Composer Version: 1.8.2
[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: missing index (sensors_to_state_indexes/state_index_id)
[FAIL]  Database: extra index (sensors_to_state_indexes/sensors_to_state_indexes_ibfk_1)
[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 INDEX `sensors_to_state_indexes_ibfk_1`;
         ALTER TABLE `sensors_to_state_indexes` ADD INDEX `state_index_id` (`state_index_id`);
         ALTER TABLE `state_indexes` CHANGE `state_index_id` `state_index_id` int(10) unsigned NOT NULL auto_increment;

And running those statements returns errors like:

ERROR 1025 (HY000): Error on rename of 'sensors_to_state_indexes' to '#sql2-114d-a6d78' (errno: 152)

or

ERROR 1553 (HY000): Cannot drop index 'sensors_to_state_indexes_ibfk_1': needed in a foreign key constraint

This is what you have to do:

mysql -u librenms -p librenms < sql-schema/278.sql

This will ask for your sql password. It is in the .env and config.php files.

And then run ./daily.sh again


Sql error after changing to montly
unlisted #2

listed #3

pinned #4