v0.8 potential issue with scenarios and entity activity control?

[Tasqu]

At the behest of @manuelma, I've been testing the v0.8 branches of Toolbox, SpineInterface, and SpineOpt using a workflow I used for a conference paper earlier this year (doesn't really work as-is anymore, but can be updated).

WARNING! The full workflow can take 5-6 hours to finish, unless the SpineOpt model length is reduced from spineopt_template.sqlite! The process_archetypes tool still takes ~hour because of how cumbersome year-long daily weather data forecasts are to process.

I've gotten the original version to work by updating Toolbox to latest master, but the 0.8-dev branch seems to have some problems with parallel execution of scenarios and entity activity control. I'm not yet sure if this is a problem with data migration, or with toolbox itself, I'm still looking into this. However, I thought I might as well post some tracebacks in case some Toolbox experts can figure them out.

Error description

The workflow gets stuck when trying to execute SpineOpt. It doesn't fail properly, but just hangs forever:

This seems be because of the scenarios (which trigger different entity activity for SpineOpt). Only one of the scenarios runs into an error (Perfect for me, not sure if this is consistent), while the two others get stuck:

The final things the SpineOpt executions do is the following, before either erroring or simply hanging forever:

julia> # Running julia SpineOpt_for_Toolbox.jl http://127.0.0.1:52513 http://127.0.0.1:52522
2-element Vector{String}:
 "http://127.0.0.1:52513"
 "http://127.0.0.1:52522"
  Activating project at `C:\_SPINEPROJECTS\FlexiB_BS2023_v0.8\flexib_spineopt_demo\Spine\SpineOpt.jl`
WARNING: using JuMP.Parameter in module SpineInterface conflicts with an existing identifier.

Execution started at 2023-12-15T08:44:53.484
WARNING: using JuMP.parameter_value in module SpineInterface conflicts with an existing identifier.
Preparing SpineOpt for sqlite:///C:\_SPINEPROJECTS\FlexiB_BS2023_v0.8\flexib_spineopt_demo\spineopt_input.sqlite?spinedbfilter=cfg%3Ascenario%3ADeterministic...
Upgrading data structure to the latest version... 
Renaming `spineopt_benders_master` to `spineopt_benders`
Removing `min_startup_ramp`, `min_shutdown_ramp`, `min_res_startup_ramp`, `min_res_shutdown_ramp`, `max_res_startup_ramp` and `max_res_shutdown_ramp`, and renaming `max_startup_ramp` to `start_up_limit` and `max_shutdown_ramp` to `shut_down_limit`
Removing `model__temporal_block` and `model__stochastic_structure`

Julia traceback from SpineOpt

SpineOpt execution with the Perfect scenario (for me) causes an error, which seems to be caused by an error when applying the scenario filter in spinedb_api:

ERROR: LoadError: Traceback (most recent call last):
  File "C:\_SPINEPROJECTS\FlexiB_BS2023_v0.8\flexib_spineopt_demo\Spine\Spine-Toolbox\src\spinedb-api\spinedb_api\spine_db_server.py", line 611, in _get_response
    return handler(*args, **kwargs)
  File "C:\_SPINEPROJECTS\FlexiB_BS2023_v0.8\flexib_spineopt_demo\Spine\Spine-Toolbox\src\spinedb-api\spinedb_api\spine_db_server.py", line 552, in apply_filters
    configs = [
  File "C:\_SPINEPROJECTS\FlexiB_BS2023_v0.8\flexib_spineopt_demo\Spine\Spine-Toolbox\src\spinedb-api\spinedb_api\spine_db_server.py", line 553, in <listcomp>
    {"scenario": scenario_filter_config, "alternatives": alternative_filter_config}[key](value)
KeyError: 'type'

Stacktrace:
  [1] error(s::String)
    @ Base .\error.jl:35
  [2] _process_db_answer(result::Nothing, err::String)
    @ SpineInterface C:\_SPINEPROJECTS\FlexiB_BS2023_v0.8\flexib_spineopt_demo\Spine\SpineInterface.jl\src\api\db.jl:585
  [3] _process_db_answer(answer::Dict{String, Any})
    @ SpineInterface C:\_SPINEPROJECTS\FlexiB_BS2023_v0.8\flexib_spineopt_demo\Spine\SpineInterface.jl\src\api\db.jl:569
  [4] _do_run_server_request(server_uri::URIs.URI, full_request::Vector{Any}; timeout::Float64)
    @ SpineInterface C:\_SPINEPROJECTS\FlexiB_BS2023_v0.8\flexib_spineopt_demo\Spine\SpineInterface.jl\src\api\db.jl:660
  [5] _do_run_server_request
    @ C:\_SPINEPROJECTS\FlexiB_BS2023_v0.8\flexib_spineopt_demo\Spine\SpineInterface.jl\src\api\db.jl:634 [inlined]
  [6] _run_server_request(server_uri::URIs.URI, request::String, args::Tuple{Dict{String, Any}}, kwargs::Dict{Any, Any})
    @ SpineInterface C:\_SPINEPROJECTS\FlexiB_BS2023_v0.8\flexib_spineopt_demo\Spine\SpineInterface.jl\src\api\db.jl:682
  [7] _run_server_request(db::URIs.URI, request::String, args::Tuple{Dict{String, Any}})
    @ SpineInterface C:\_SPINEPROJECTS\FlexiB_BS2023_v0.8\flexib_spineopt_demo\Spine\SpineInterface.jl\src\api\db.jl:669
  [8] _export_data(db::URIs.URI; filters::Dict{String, String})
    @ SpineInterface C:\_SPINEPROJECTS\FlexiB_BS2023_v0.8\flexib_spineopt_demo\Spine\SpineInterface.jl\src\api\db.jl:700
  [9] _export_data
    @ C:\_SPINEPROJECTS\FlexiB_BS2023_v0.8\flexib_spineopt_demo\Spine\SpineInterface.jl\src\api\db.jl:694 [inlined]
 [10] #95
    @ C:\_SPINEPROJECTS\FlexiB_BS2023_v0.8\flexib_spineopt_demo\Spine\SpineInterface.jl\src\api\db.jl:32 [inlined]
 [11] _db(f::SpineInterface.var"#95#96"{Dict{String, String}}, url::String; upgrade::Bool)
    @ SpineInterface C:\_SPINEPROJECTS\FlexiB_BS2023_v0.8\flexib_spineopt_demo\Spine\SpineInterface.jl\src\api\db.jl:552
 [12] _db
    @ C:\_SPINEPROJECTS\FlexiB_BS2023_v0.8\flexib_spineopt_demo\Spine\SpineInterface.jl\src\api\db.jl:549 [inlined]
 [13] using_spinedb(url::String, mod::Module; upgrade::Bool, filters::Dict{String, String})
    @ SpineInterface C:\_SPINEPROJECTS\FlexiB_BS2023_v0.8\flexib_spineopt_demo\Spine\SpineInterface.jl\src\api\db.jl:31
 [14] using_spinedb
    @ C:\_SPINEPROJECTS\FlexiB_BS2023_v0.8\flexib_spineopt_demo\Spine\SpineInterface.jl\src\api\db.jl:30 [inlined]
 [15] macro expansion
    @ C:\_SPINEPROJECTS\FlexiB_BS2023_v0.8\flexib_spineopt_demo\Spine\SpineOpt.jl\src\run_spineopt.jl:232 [inlined]
 [16] macro expansion
    @ .\timing.jl:273 [inlined]
 [17] macro expansion
    @ C:\_SPINEPROJECTS\FlexiB_BS2023_v0.8\flexib_spineopt_demo\Spine\SpineOpt.jl\src\util\misc.jl:51 [inlined]
 [18] prepare_spineopt(url_in::String; upgrade::Bool, log_level::Int64, filters::Dict{String, String})
    @ SpineOpt C:\_SPINEPROJECTS\FlexiB_BS2023_v0.8\flexib_spineopt_demo\Spine\SpineOpt.jl\src\run_spineopt.jl:230
 [19] prepare_spineopt
    @ C:\_SPINEPROJECTS\FlexiB_BS2023_v0.8\flexib_spineopt_demo\Spine\SpineOpt.jl\src\run_spineopt.jl:209 [inlined]
 [20] _run_spineopt(url_in::String, url_out::String; upgrade::Bool, mip_solver::MathOptInterface.OptimizerWithAttributes, lp_solver::Nothing, add_user_variables::Function, add_constraints::Function, log_level::Int64, optimize::Bool, update_names::Bool, alternative::String, write_as_roll::Int64, use_direct_model::Bool, filters::Dict{String, String}, resume_file_path::Nothing)
    @ SpineOpt C:\_SPINEPROJECTS\FlexiB_BS2023_v0.8\flexib_spineopt_demo\Spine\SpineOpt.jl\src\run_spineopt.jl:186
 [21] _run_spineopt
    @ C:\_SPINEPROJECTS\FlexiB_BS2023_v0.8\flexib_spineopt_demo\Spine\SpineOpt.jl\src\run_spineopt.jl:167 [inlined]
 [22] #run_spineopt#34
    @ C:\_SPINEPROJECTS\FlexiB_BS2023_v0.8\flexib_spineopt_demo\Spine\SpineOpt.jl\src\run_spineopt.jl:98 [inlined]
 [23] top-level scope
    @ C:\_SPINEPROJECTS\FlexiB_BS2023_v0.8\flexib_spineopt_demo\Spine\SpineOpt.jl\SpineOpt_for_Toolbox.jl:15
 [24] include(fname::String)
    @ Base.MainInclude .\client.jl:478
 [25] top-level scope
    @ none:1
in expression starting at C:\_SPINEPROJECTS\FlexiB_BS2023_v0.8\flexib_spineopt_demo\Spine\SpineOpt.jl\SpineOpt_for_Toolbox.jl:15

Toolbox traceback in the underlying command prompt

Toolbox also prints tracebacks, but these I have no idea about

Exception in thread Thread-34:
Traceback (most recent call last):
  File "C:\Users\trtopi\AppData\Local\miniconda3\envs\BS2023workflow08\lib\site-packages\sqlalchemy\engine\base.py", line 1256, in _execute_context
    self.dialect.do_executemany(
  File "C:\Users\trtopi\AppData\Local\miniconda3\envs\BS2023workflow08\lib\site-packages\sqlalchemy\engine\default.py", line 605, in do_executemany
    cursor.executemany(statement, parameters)
sqlite3.IntegrityError: UNIQUE constraint failed: entity.class_id, entity.name

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\_SPINEPROJECTS\FlexiB_BS2023_v0.8\flexib_spineopt_demo\Spine\Spine-Toolbox\src\spinedb-api\spinedb_api\db_mapping_commit_mixin.py", line 58, in _do_add_items
    connection.execute(table.insert(), [x.resolve() for x in temp_id_items])
  File "C:\Users\trtopi\AppData\Local\miniconda3\envs\BS2023workflow08\lib\site-packages\sqlalchemy\engine\base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "C:\Users\trtopi\AppData\Local\miniconda3\envs\BS2023workflow08\lib\site-packages\sqlalchemy\sql\elements.py", line 298, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "C:\Users\trtopi\AppData\Local\miniconda3\envs\BS2023workflow08\lib\site-packages\sqlalchemy\engine\base.py", line 1124, in _execute_clauseelement
    ret = self._execute_context(
  File "C:\Users\trtopi\AppData\Local\miniconda3\envs\BS2023workflow08\lib\site-packages\sqlalchemy\engine\base.py", line 1316, in _execute_context
    self._handle_dbapi_exception(
  File "C:\Users\trtopi\AppData\Local\miniconda3\envs\BS2023workflow08\lib\site-packages\sqlalchemy\engine\base.py", line 1510, in _handle_dbapi_exception
    util.raise_(
  File "C:\Users\trtopi\AppData\Local\miniconda3\envs\BS2023workflow08\lib\site-packages\sqlalchemy\util\compat.py", line 182, in raise_
    raise exception
  File "C:\Users\trtopi\AppData\Local\miniconda3\envs\BS2023workflow08\lib\site-packages\sqlalchemy\engine\base.py", line 1256, in _execute_context
    self.dialect.do_executemany(
  File "C:\Users\trtopi\AppData\Local\miniconda3\envs\BS2023workflow08\lib\site-packages\sqlalchemy\engine\default.py", line 605, in do_executemany
    cursor.executemany(statement, parameters)
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: entity.class_id, entity.name
[SQL: INSERT INTO entity (id, class_id, name, description, commit_id) VALUES (?, ?, ?, ?, ?)]
[parameters: ((344, 5, 'unit_flow_op_active', None, 418), (345, 5, 'relative_optimality_gap', None, 418))]
(Background on this error at: http://sqlalche.me/e/13/gkpj)

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\trtopi\AppData\Local\miniconda3\envs\BS2023workflow08\lib\threading.py", line 980, in _bootstrap_inner
    self.run()
  File "C:\Users\trtopi\AppData\Local\miniconda3\envs\BS2023workflow08\lib\threading.py", line 917, in run
    self._target(*self._args, **self._kwargs)
  File "C:\_SPINEPROJECTS\FlexiB_BS2023_v0.8\flexib_spineopt_demo\Spine\Spine-Toolbox\src\spinedb-api\spinedb_api\spine_db_server.py", line 370, in _do_work
    result = handler(*args, **kwargs)
  File "C:\_SPINEPROJECTS\FlexiB_BS2023_v0.8\flexib_spineopt_demo\Spine\Spine-Toolbox\src\spinedb-api\spinedb_api\spine_db_server.py", line 403, in _do_import_data
    self._db_map.commit_session(comment)
  File "C:\_SPINEPROJECTS\FlexiB_BS2023_v0.8\flexib_spineopt_demo\Spine\Spine-Toolbox\src\spinedb-api\spinedb_api\db_mapping.py", line 712, in commit_session
    self._do_add_items(connection, tablename, *to_add)
  File "C:\_SPINEPROJECTS\FlexiB_BS2023_v0.8\flexib_spineopt_demo\Spine\Spine-Toolbox\src\spinedb-api\spinedb_api\db_mapping_commit_mixin.py", line 66, in _do_add_items
    raise SpineDBAPIError(msg) from e
spinedb_api.exception.SpineDBAPIError: DBAPIError while inserting entity items: ('UNIQUE constraint failed: entity.class_id, entity.name',)
Exception in thread Thread-32:
Traceback (most recent call last):
  File "C:\Users\trtopi\AppData\Local\miniconda3\envs\BS2023workflow08\lib\site-packages\sqlalchemy\engine\base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "C:\Users\trtopi\AppData\Local\miniconda3\envs\BS2023workflow08\lib\site-packages\sqlalchemy\engine\default.py", line 608, in do_execute
    cursor.execute(statement, parameters)
sqlite3.IntegrityError: UNIQUE constraint failed: list_value.parameter_value_list_id, list_value.index

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\_SPINEPROJECTS\FlexiB_BS2023_v0.8\flexib_spineopt_demo\Spine\Spine-Toolbox\src\spinedb-api\spinedb_api\db_mapping_commit_mixin.py", line 58, in _do_add_items
    connection.execute(table.insert(), [x.resolve() for x in temp_id_items])
  File "C:\Users\trtopi\AppData\Local\miniconda3\envs\BS2023workflow08\lib\site-packages\sqlalchemy\engine\base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "C:\Users\trtopi\AppData\Local\miniconda3\envs\BS2023workflow08\lib\site-packages\sqlalchemy\sql\elements.py", line 298, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "C:\Users\trtopi\AppData\Local\miniconda3\envs\BS2023workflow08\lib\site-packages\sqlalchemy\engine\base.py", line 1124, in _execute_clauseelement
    ret = self._execute_context(
  File "C:\Users\trtopi\AppData\Local\miniconda3\envs\BS2023workflow08\lib\site-packages\sqlalchemy\engine\base.py", line 1316, in _execute_context
    self._handle_dbapi_exception(
  File "C:\Users\trtopi\AppData\Local\miniconda3\envs\BS2023workflow08\lib\site-packages\sqlalchemy\engine\base.py", line 1510, in _handle_dbapi_exception
    util.raise_(
  File "C:\Users\trtopi\AppData\Local\miniconda3\envs\BS2023workflow08\lib\site-packages\sqlalchemy\util\compat.py", line 182, in raise_
    raise exception
  File "C:\Users\trtopi\AppData\Local\miniconda3\envs\BS2023workflow08\lib\site-packages\sqlalchemy\engine\base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "C:\Users\trtopi\AppData\Local\miniconda3\envs\BS2023workflow08\lib\site-packages\sqlalchemy\engine\default.py", line 608, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: list_value.parameter_value_list_id, list_value.index
[SQL: INSERT INTO list_value (id, parameter_value_list_id, "index", type, value, commit_id) VALUES (?, ?, ?, ?, ?, ?)]
[parameters: (71, 13, 6, None, <memory at 0x000001DD83937640>, 419)]
(Background on this error at: http://sqlalche.me/e/13/gkpj)

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\trtopi\AppData\Local\miniconda3\envs\BS2023workflow08\lib\threading.py", line 980, in _bootstrap_inner
    self.run()
  File "C:\Users\trtopi\AppData\Local\miniconda3\envs\BS2023workflow08\lib\threading.py", line 917, in run
    self._target(*self._args, **self._kwargs)
  File "C:\_SPINEPROJECTS\FlexiB_BS2023_v0.8\flexib_spineopt_demo\Spine\Spine-Toolbox\src\spinedb-api\spinedb_api\spine_db_server.py", line 370, in _do_work
    result = handler(*args, **kwargs)
  File "C:\_SPINEPROJECTS\FlexiB_BS2023_v0.8\flexib_spineopt_demo\Spine\Spine-Toolbox\src\spinedb-api\spinedb_api\spine_db_server.py", line 403, in _do_import_data
    self._db_map.commit_session(comment)
  File "C:\_SPINEPROJECTS\FlexiB_BS2023_v0.8\flexib_spineopt_demo\Spine\Spine-Toolbox\src\spinedb-api\spinedb_api\db_mapping.py", line 712, in commit_session
    self._do_add_items(connection, tablename, *to_add)
  File "C:\_SPINEPROJECTS\FlexiB_BS2023_v0.8\flexib_spineopt_demo\Spine\Spine-Toolbox\src\spinedb-api\spinedb_api\db_mapping_commit_mixin.py", line 66, in _do_add_items
    raise SpineDBAPIError(msg) from e
spinedb_api.exception.SpineDBAPIError: DBAPIError while inserting list_value items: ('UNIQUE constraint failed: list_value.parameter_value_list_id, list_value.index',)

[manuelma]

@Tasqu any chance you could reproduce this with simple steps? Otherwise I think I know what's going on...

[Tasqu]

@manuelma I can try, but no promises. I only have the afternoon before I leave for holidays.

[manuelma]

Take it easy - it feels easy to solve anyways, we just need to figure out why that KeyError in spine_db_server.

[Tasqu]

Hmm, couldn't replicate this with a trivial example. I thought the issue was simply with parallel execution of scenarios, but seems like that works, at least for well-defined alternatives: where and results in no errors and almost the expected behaviour:

1. scen1:

2. scen2:

3. scen3:

However, Base results in something different than what I expected: It seems that for whatever reason, Base scenario returns entities identical to scen2, although no object activity has been defined.

[Tasqu]

I extended the above test to parameters hoping it would cause an error like the one my old conference paper workflow, but it still works. However, Base scenario and alternative output seems unreliable. I would expect that all entities would be assumed to exist by default, but this doesn't seem to be the case.

See the below file for the trivial example. 0.8-scenariotest.zip Note that print_entities.jl needs to be linked to a 0.8-dev SpineInterface locally via the develop <spineinterface_path>.

[Tasqu]

Closing this as obsolete. The issue still persists, but the old modelling workflow used for testing this no longer works as intended even with updated master branches.

[DillonJ]

@Tasqu Just wondering why we are closing the issue if it's not solved? Are you saying the old workflow has issues because of other incompatibilities with latest toolbox? Even so, it looks like activity control is not working as expected?

[Tasqu]

@DillonJ I put it aside, as I don't think this issue is useful as is. All it demonstrates is unexpected behaviour when trying to update and run an old modelling workflow, but I couldn't identify the reason behind it. Furthermore, updating the workflow for current master branches doesn't work either, so this might not even be a problem in 0.8-dev, but in data migration or toolbox project updating.

[DillonJ]

Thanks @Tasqu. It was this that concerned me a little:

However, Base scenario and alternative output seems unreliable. I would expect that all entities would be assumed to exist by default, but this doesn't seem to be the case.

But I gether that this is to do with the old version of the workflow and when you test scenarios in parallel with a toy example in V0.8 , you see the correct Base scenario and alternative output, right?

[Tasqu]

@DillonJ I thought I created a separate issue for that before the holidays, but seems like I didn't. I'll have to check if the inconsistency in the scenario and alternative output is still there, and create a new better issue for it.