Error when changing the order of alternatives in scenarios

[lilanger]

I get this error message when I try to rearrange the order of alternatives within a scenario. The error appears when committing the change. From input: DBAPIError while committing changes: ('UNIQUE constraint failed: scenario_alternative.id',) I can send the database if it helps. Funny enough it only happens for two scenarios. Are there any name conventions that I broke?

[soininen]

I don't think there's anything wrong with naming. The problem seems to be with database IDs that the widget somehow messes up. Would you be be so kind and send the database? It helps a lot if we can reproduce the problem quickly. It may be possible that you can purge all other data than scenarios, alternatives and scenario alternatives before sending if you don't want to leak your data.

[jkiviluo]

There are no special cases for scenario or alternative names. Something like this happened to me once and it was (I think), because of database that had become corrupted over multiple updates to DB structure (it was an old DB). I think it was about those scenario_alternative_ids. I think I copied all the data (but left the scenarios in the dust) to a new DB and rebuilt the scenarios by hand.

Seems like @soininen beat me to this. :)

[jkiviluo]

I used DB Browser to see the ids - but it's quite cumbersome to spot the problems. I even tried to fix them with the DB Browser, but somehow I didn't get it to work. It was just easier to restart the scens. But it can also be that there is something in the widget as @soininen is suggesting. Especially if the DB is new.

[soininen]

In fact, you should avoid commas , in alternative names as we use that character when concatenating alternative names when querying "wide" scenarios. Same applies to object and object class names.

The scenario widget was forcefully ripped off from the combined Alternative/Scenario tree quite recently. Even though I tried to avoid touching the code that handles the scenario alternative numbering in that particular list, I may still have broken something.

[lilanger]

I tried renaming it without the . at some point but that did not fix it. I purged the database and the error remains. I guess I could try to build the two scenarios again, but it might be interesting to find out what the problem is. input.zip

[soininen]

@lilanger Thanks for test database! I can reproduce the error here and it is as @jkiviluo suspected: the database is a bit broken. Namely, the scenario_alternative table refers to an alternative that does not exist.

If you have access to DB Browser for SQLite or similar program, you can fix the problem by deleting the offending scenario_alternatives by hand by executing the following SQL command:

delete from scenario_alternative where alternative_id=26

If you cannot execute direct SQL commands, you will need to do a bit more work. Unfortunately, purging the scenarios and scenario_alternatives from Database editor does not fix the problem as the offending rows will be left in the scenario_alternative table. Instead, you can "sanitize" the database by cloning it to another SQLite file. This can be done e.g. by this nifty Toolbox workflow:

"Original input" contains the broken database while "new input" points to a fresh SQLite file. When the project is executed, "Merger 1" copies all data from "original input" to "new input" while dropping the invalid rows from "original input". Note that no filters should be selected in the funnel link between "original input" and "Merger 1".

Meanwhile, I will try to find a way to prevent this happening ever again.

[lilanger]

Great, I did the merger workflow. Works like charm !