Bug when linking timesteps in sqlite

[j-davies-astro]

I've been using mysql tangos databases recently, but have just tried making a small sqlite database (of some simulations that I know have worked fine in the past) and am hitting this slightly cryptic error that I presume is a bug introduced relatively recently. I'll paste the traceback below as I'm not entirely sure what the issue is, but it seems to be something to do with the field 'ptcls_in_common' being unique in a dictionary..?

Traceback (most recent call last):
  File "/home/jdavies/.conda/envs/eagle/bin/tangos", line 33, in <module>
    sys.exit(load_entry_point('tangos', 'console_scripts', 'tangos')())
  File "/home/jdavies/Code/packages/tangos/tangos/scripts/__init__.py", line 51, in main
    args.func(args)
  File "/home/jdavies/Code/packages/tangos/tangos/tools/__init__.py", line 53, in run
    parallel_tasks.launch(obj.run_calculation_loop, 2, [])
  File "/home/jdavies/Code/packages/tangos/tangos/parallel_tasks/__init__.py", line 52, in launch
    backend.launch(_exec_function_or_server, num_procs, [function, args])
  File "/home/jdavies/Code/packages/tangos/tangos/parallel_tasks/backends/mpi4py.py", line 59, in launch
    function(*args)
  File "/home/jdavies/Code/packages/tangos/tangos/parallel_tasks/__init__.py", line 84, in _exec_function_or_server
    function(*args)
  File "/home/jdavies/Code/packages/tangos/tangos/tools/crosslink.py", line 54, in run_calculation_loop
    if self.args.force or self.need_crosslink_ts(s_x, s, object_type):
  File "/home/jdavies/Code/packages/tangos/tangos/tools/crosslink.py", line 78, in need_crosslink_ts
    core.halo_data.HaloLink.relation_id == same_d_id).count() > 0
  File "/home/jdavies/.conda/envs/eagle/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3145, in count
    return self._from_self(col).enable_eagerloads(False).scalar()
  File "/home/jdavies/.conda/envs/eagle/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 2879, in scalar
    ret = self.one()
  File "/home/jdavies/.conda/envs/eagle/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 2856, in one
    return self._iter().one()
  File "/home/jdavies/.conda/envs/eagle/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 2897, in _iter
    execution_options={"_sa_orm_load_options": self.load_options},
  File "/home/jdavies/.conda/envs/eagle/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 1646, in execute
    _parent_execute_state is not None,
  File "/home/jdavies/.conda/envs/eagle/lib/python3.7/site-packages/sqlalchemy/orm/context.py", line 318, in orm_pre_session_exec
    session._autoflush()
  File "/home/jdavies/.conda/envs/eagle/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2237, in _autoflush
    util.raise_(e, with_traceback=sys.exc_info()[2])
  File "/home/jdavies/.conda/envs/eagle/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/home/jdavies/.conda/envs/eagle/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2226, in _autoflush
    self.flush()
  File "/home/jdavies/.conda/envs/eagle/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 3363, in flush
    self._flush(objects)
  File "/home/jdavies/.conda/envs/eagle/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 3503, in _flush
    transaction.rollback(_capture_exception=True)
  File "/home/jdavies/.conda/envs/eagle/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 72, in __exit__
    with_traceback=exc_tb,
  File "/home/jdavies/.conda/envs/eagle/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/home/jdavies/.conda/envs/eagle/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 3463, in _flush
    flush_context.execute()
  File "/home/jdavies/.conda/envs/eagle/lib/python3.7/site-packages/sqlalchemy/orm/unitofwork.py", line 456, in execute
    rec.execute(self)
  File "/home/jdavies/.conda/envs/eagle/lib/python3.7/site-packages/sqlalchemy/orm/unitofwork.py", line 633, in execute
    uow,
  File "/home/jdavies/.conda/envs/eagle/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py", line 249, in save_obj
    insert,
  File "/home/jdavies/.conda/envs/eagle/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py", line 1224, in _emit_insert_statements
    execution_options=execution_options,
  File "/home/jdavies/.conda/envs/eagle/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1614, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/home/jdavies/.conda/envs/eagle/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 326, in _execute_on_connection
    self, multiparams, params, execution_options
  File "/home/jdavies/.conda/envs/eagle/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1491, in _execute_clauseelement
    cache_hit=cache_hit,
  File "/home/jdavies/.conda/envs/eagle/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
    e, statement, parameters, cursor, context
  File "/home/jdavies/.conda/envs/eagle/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 2027, in _handle_dbapi_exception
    sqlalchemy_exception, with_traceback=exc_info[2], from_=e
  File "/home/jdavies/.conda/envs/eagle/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/home/jdavies/.conda/envs/eagle/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1803, in _execute_context
    cursor, statement, parameters, context
  File "/home/jdavies/.conda/envs/eagle/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely)
(sqlite3.IntegrityError) UNIQUE constraint failed: dictionary.text
[SQL: INSERT INTO dictionary (text) VALUES (?)]
[parameters: ('ptcls_in_common',)]
(Background on this error at: https://sqlalche.me/e/14/gkpj)
Traceback (most recent call last):
  File "/home/jdavies/.conda/envs/eagle/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1803, in _execute_context
    cursor, statement, parameters, context
  File "/home/jdavies/.conda/envs/eagle/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
sqlite3.IntegrityError: UNIQUE constraint failed: dictionary.text

[Martin-Rey]

Hi Jon,

I also have had a similar issue with sqlite and the latest Tangos when writing properties:

  File "/home/mrey/.local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/home/mrey/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "/home/mrey/.local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely)
(sqlite3.OperationalError) database is locked
[SQL: INSERT INTO dictionary (text) VALUES (?)]
[parameters: ('gas_T_rho_histogram_tot',)]
(Background on this error at: https://sqlalche.me/e/14/e3q8)

We were discussing with @apontzen and couldn't actually pinpoint where it comes from...

As a practical fix, I only ever saw this for the very first calculation of something and attempting the calculation again has solved it for me (I assume that after you've tried once, the dictionary entry gets created and sqlite is happier).

[apontzen]

I can actually see the specific issue in Jon's case - Jon could you try using #198?