Sql error after changing to montly

Help
1

i have uncommented $config[‘update_channel’] = ‘release’;

i get no errors from ./daily
but when i do ./validate i first got alot of errors,i have excuted the sql commands that were possible but i got a few that is not working:

ALTER TABLE sensors CHANGE sensor_id sensor_id int(11) NOT NULL auto_increment;
ERROR: Cannot change column ‘sensor_id’: used in a foreign key constraint ‘sensors_to_state_indexes_sensor_id_foreign’ of table ‘librenms.sensors_to_state_indexes’

ALTER TABLE sensors_to_state_indexes CHANGE sensor_id sensor_id int(11) NOT NULL ;
ERROR: Cannot change column ‘sensor_id’: used in a foreign key constraint ‘sensors_to_state_indexes_sensor_id_foreign’

ALTER TABLE sensors_to_state_indexes CHANGE state_index_id state_index_id int(11) NOT NULL ;
ERROR: Cannot change column ‘state_index_id’: used in a foreign key constraint ‘sensors_to_state_indexes_ibfk_1’

ALTER TABLE state_indexes CHANGE state_index_id state_index_id int(11) NOT NULL auto_increment;
ERROR: Cannot change column ‘state_index_id’: used in a foreign key constraint ‘sensors_to_state_indexes_ibfk_1’ of table ‘librenms.sensors_to_state_indexes’

How do i solve this? :slight_smile:

may be worth knowing im running the commands trough phpmyadmin if that matters

2

You picked the worst time to change to monthly. I suggest you wait about 1-2 days. Then you will be on 1.48.

1 Like
3

Haha ok, same with upgrading my pfsense the other day, they had really bad problems with the repo… oh well shit happens :slight_smile:

4

I’m on 1.48.1 (Wed Jan 30 2019 20:53:43 GMT+0000) and seeing this when running a validation - is it fine to go ahead and run the suggested SQL commands?

5

Look more closely, the commands are not the same.

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

6

Ah thanks - I didn’t quite see the difference as I was searching for sensors_to_state_indexes rather than the full error. Unfortunately when I try to import the 278 SQL schema in the linked post, I get the following error;

ERROR 3061 (42000) at line 113: User variable name 'FOREIGN_KEY_sensors_to_state_indexes_ibfk_1_ON_TABLE_sensors_to_state_indexes_EXISTS' is illegal

As a result, I cannot apply the remaining two SQL statements that the validate script suggests;

MySQL [librenms]> ALTER TABLE `sensors_to_state_indexes` CHANGE `state_index_id` `state_index_id` int(10) unsigned NOT NULL ;
ERROR 1832 (HY000): Cannot change column 'state_index_id': used in a foreign key constraint 'sensors_to_state_indexes_ibfk_1'
MySQL [librenms]> ALTER TABLE `state_indexes` CHANGE `state_index_id` `state_index_id` int(10) unsigned NOT NULL auto_increment;
ERROR 1833 (HY000): Cannot change column 'state_index_id': used in a foreign key constraint 'sensors_to_state_indexes_ibfk_1' of table 'librenms.sensors_to_state_indexes'

I can split this off to a seperate thread now that I know it is a different issue if that’s best?

7

I think you have an old 278.sql

Check https://github.com/librenms/librenms/blob/master/sql-schema/278.sql and compare with yours.

9

Thank you - you’re right. My version of 278.sql comes from the 1.48.1 tag, however there’s one commit to that file since then in master which fixes SQL variable naming issues (PR #9766).

I manually updated sql-schema/278.sql to the master version, applied it to my database and then the validation only flagged one missing database entry (ALTER TABLEeventlogCHANGEdevice_iddevice_idint(10) unsigned NULL ;). After applying this, the validation completes without error (aside from noting that sql-schema/278.sql has been modified locally, but i’ll switch it back to the 1.48.1 tag version now that i’ve been able to resolve this).

Thanks for your help.