Hi,
after our upgrade from 1.42 to 1.49 validate.php threw some errors regarding extra constraints for sensors_to_state_indexes: https://p.libren.ms/view/6665bcb2.
We weren’t able to run the suggested SQL queries:
MariaDB [librenms]> ALTER TABLE `sensors` CHANGE `sensor_id` `sensor_id` int(10) unsigned NOT NULL auto_increment;
ERROR 1833 (HY000): Cannot change column 'sensor_id': used in a foreign key constraint 'sensors_to_state_indexes_ibfk_7' of table 'librenms.sensors_to_state_indexes'
The problem happened during the update from 1.47 to 1.48 and was in sql-schema/278.sql. Adding the following lines to the file before running ./build-base.php solved it:
SELECT COUNT(*) INTO @ibfk_6_EXISTS FROM `information_schema`.`table_constraints` WHERE `table_name` = 'sensors_to_state_indexes' AND `constraint_name` = 'sensors_to_state_indexes_ibfk_6' AND `constraint_type` = 'FOREIGN KEY'; SET @statement6 := IF(@ibfk_6_EXISTS > 0, 'ALTER TABLE sensors_to_state_indexes DROP FOREIGN KEY sensors_to_state_indexes_ibfk_6', 'SELECT "info: foreign key sensors_to_state_indexes_ibfk_6 does not exist."'); PREPARE statement6 FROM @statement6; EXECUTE statement6;
SELECT COUNT(*) INTO @ibfk_7_EXISTS FROM `information_schema`.`table_constraints` WHERE `table_name` = 'sensors_to_state_indexes' AND `constraint_name` = 'sensors_to_state_indexes_ibfk_7' AND `constraint_type` = 'FOREIGN KEY'; SET @statement7 := IF(@ibfk_7_EXISTS > 0, 'ALTER TABLE sensors_to_state_indexes DROP FOREIGN KEY sensors_to_state_indexes_ibfk_7', 'SELECT "info: foreign key sensors_to_state_indexes_ibfk_7 does not exist."'); PREPARE statement7 FROM @statement7; EXECUTE statement7;
SELECT COUNT(*) INTO @ibfk_8_EXISTS FROM `information_schema`.`table_constraints` WHERE `table_name` = 'sensors_to_state_indexes' AND `constraint_name` = 'sensors_to_state_indexes_ibfk_8' AND `constraint_type` = 'FOREIGN KEY'; SET @statement8 := IF(@ibfk_8_EXISTS > 0, 'ALTER TABLE sensors_to_state_indexes DROP FOREIGN KEY sensors_to_state_indexes_ibfk_8', 'SELECT "info: foreign key sensors_to_state_indexes_ibfk_8 does not exist."'); PREPARE statement8 FROM @statement8; EXECUTE statement8;
SELECT COUNT(*) INTO @ibfk_9_EXISTS FROM `information_schema`.`table_constraints` WHERE `table_name` = 'sensors_to_state_indexes' AND `constraint_name` = 'sensors_to_state_indexes_ibfk_9' AND `constraint_type` = 'FOREIGN KEY'; SET @statement9 := IF(@ibfk_9_EXISTS > 0, 'ALTER TABLE sensors_to_state_indexes DROP FOREIGN KEY sensors_to_state_indexes_ibfk_9', 'SELECT "info: foreign key sensors_to_state_indexes_ibfk_9 does not exist."'); PREPARE statement9 FROM @statement9; EXECUTE statement9;
SELECT COUNT(*) INTO @ibfk_10_EXISTS FROM `information_schema`.`table_constraints` WHERE `table_name` = 'sensors_to_state_indexes' AND `constraint_name` = 'sensors_to_state_indexes_ibfk_10' AND `constraint_type` = 'FOREIGN KEY'; SET @statement10 := IF(@ibfk_10_EXISTS > 0, 'ALTER TABLE sensors_to_state_indexes DROP FOREIGN KEY sensors_to_state_indexes_ibfk_10', 'SELECT "info: foreign key sensors_to_state_indexes_ibfk_10 does not exist."'); PREPARE statement10 FROM @statement10; EXECUTE statement10;