scenario_alternative table may accumulate non-existent alternatives and scenarios

spine-tools / Spine-Database-API

Database interface to Spine generic data model

https://www.tools-for-energy-system-modelling.org/

GNU Lesser General Public License v3.0

6 stars 5 forks source link

scenario_alternative table may accumulate non-existent alternatives and scenarios #224

Closed soininen closed 1 year ago

soininen commented 1 year ago

Issue #223 has a link to a Spine database that contains a corrupted scenario_alternative table. Three records in the table refer to alternative_id 26 which doesn't exist in alternative table. Further, one of the three records has scenario_id 36 which does not exist in scenario table.

I am not sure how the database got corrupted in the first place. Probably it has been modified exclusively via Spine Toolbox. In any case, we should find out how such corruption is possible and how to prevent it in the future.

manuelma commented 1 year ago

I have seen it too in a db @jkiviluo provided. I think at some point we had a bug in removing scenario_alternatives in cascade when removing alternatives for instance...

soininen commented 1 year ago

By looking at the code in DatabaseMappingRemoveMixin, there does not seem to be apparent reason how this could happen. We always use fresh ids when cascade_remove_items() is called from Toolbox and we never call the dangerous remove_items() directly as far as I can see.

I think at some point we had a bug in removing scenario_alternatives in cascade when removing alternatives for instance...

This may well have been the cause for the corruption. I'll close the issue for now. Lets hope it does not surface again.