Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

#88 breaks old databases? #139

Closed
Martin-Rey opened this issue Mar 24, 2021 · 18 comments
Closed

#88 breaks old databases? #139

Martin-Rey opened this issue Mar 24, 2021 · 18 comments

Comments

@Martin-Rey
Copy link

Hi @mtremmel and @apontzen,

After updating my master branch to test #138, I am unable to access my older databases that were generated before. I get an error saying the database cannot find the column halos.finder_offset which I think were introduced by #88. Is this behaviour expected? I would rather avoid regenerating the databases if I can avoid it, would there be a workaround otherwise?

Here are tracebacks for the web server

Traceback (most recent call last):
  File "/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1249, in _execute_context
    cursor, statement, parameters, context
  File "/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 580, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: no such column: halos.finder_offset

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

Traceback (most recent call last):
  File "/opt/anaconda3/lib/python3.7/site-packages/waitress-1.4.2-py3.7.egg/waitress/channel.py", line 349, in service
    task.service()
  File "/opt/anaconda3/lib/python3.7/site-packages/waitress-1.4.2-py3.7.egg/waitress/task.py", line 169, in service
    self.execute()
  File "/opt/anaconda3/lib/python3.7/site-packages/waitress-1.4.2-py3.7.egg/waitress/task.py", line 439, in execute
    app_iter = self.channel.server.application(environ, start_response)
  File "/opt/anaconda3/lib/python3.7/site-packages/pyramid-1.10.4-py3.7.egg/pyramid/router.py", line 270, in __call__
    response = self.execution_policy(environ, self)
  File "/opt/anaconda3/lib/python3.7/site-packages/pyramid_retry-2.1-py3.7.egg/pyramid_retry/__init__.py", line 121, in retry_policy
    response = router.invoke_request(request)
  File "/opt/anaconda3/lib/python3.7/site-packages/pyramid-1.10.4-py3.7.egg/pyramid/router.py", line 249, in invoke_request
    response = handle_request(request)
  File "/opt/anaconda3/lib/python3.7/site-packages/pyramid_tm-2.4-py3.7.egg/pyramid_tm/__init__.py", line 178, in tm_tween
    reraise(*exc_info)
  File "/opt/anaconda3/lib/python3.7/site-packages/pyramid_tm-2.4-py3.7.egg/pyramid_tm/compat.py", line 36, in reraise
    raise value
  File "/opt/anaconda3/lib/python3.7/site-packages/pyramid_tm-2.4-py3.7.egg/pyramid_tm/__init__.py", line 143, in tm_tween
    response = handler(request)
  File "/opt/anaconda3/lib/python3.7/site-packages/pyramid-1.10.4-py3.7.egg/pyramid/tweens.py", line 43, in excview_tween
    response = _error_handler(request, exc)
  File "/opt/anaconda3/lib/python3.7/site-packages/pyramid-1.10.4-py3.7.egg/pyramid/tweens.py", line 17, in _error_handler
    reraise(*exc_info)
  File "/opt/anaconda3/lib/python3.7/site-packages/pyramid-1.10.4-py3.7.egg/pyramid/compat.py", line 179, in reraise
    raise value
  File "/opt/anaconda3/lib/python3.7/site-packages/pyramid-1.10.4-py3.7.egg/pyramid/tweens.py", line 41, in excview_tween
    response = handler(request)
  File "/opt/anaconda3/lib/python3.7/site-packages/pyramid-1.10.4-py3.7.egg/pyramid/router.py", line 148, in handle_request
    registry, request, context, context_iface, view_name
  File "/opt/anaconda3/lib/python3.7/site-packages/pyramid-1.10.4-py3.7.egg/pyramid/view.py", line 667, in _call_view
    response = view_callable(context, request)
  File "/opt/anaconda3/lib/python3.7/site-packages/pyramid-1.10.4-py3.7.egg/pyramid/viewderivers.py", line 436, in rendered_view
    result = view(context, request)
  File "/opt/anaconda3/lib/python3.7/site-packages/pyramid-1.10.4-py3.7.egg/pyramid/viewderivers.py", line 144, in _requestonly_view
    response = view(request)
  File "/Users/martinrey/Documents/tangos/tangos/web/views/timestep_view.py", line 29, in timestep_view
    filter_by(timestep_id=ts.id, object_typecode=typecode).order_by(core.Halo.halo_number).all()
  File "/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3186, in all
    return list(self)
  File "/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3342, in __iter__
    return self._execute_and_instances(context)
  File "/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3367, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 988, in execute
    return meth(self, multiparams, params)
  File "/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1107, in _execute_clauseelement
    distilled_params,
  File "/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1253, in _execute_context
    e, statement, parameters, cursor, context
  File "/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1473, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 152, in reraise
    raise value.with_traceback(tb)
  File "/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1249, in _execute_context
    cursor, statement, parameters, context
  File "/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 580, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: halos.finder_offset

and through the python interface

Traceback (most recent call last):
  File "/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1249, in _execute_context
    cursor, statement, parameters, context
  File "/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 580, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: no such column: halos.finder_offset

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

Traceback (most recent call last):
  File "/Users/martinrey/Documents/stellar_halos/plot_discussion_dwarf_sfhs.py", line 135, in <module>
    params_suite1 = plot_suite(suite1, positions_suite1, positions_suite1_total)
  File "/Users/martinrey/Documents/stellar_halos/plot_discussion_dwarf_sfhs.py", line 39, in plot_suite
    h = tangos.get_halo(sim + "/output_00101/halo_1", session2)
  File "/Users/martinrey/Documents/tangos/tangos/query.py", line 79, in get_object
    return ts[halo]
  File "/Users/martinrey/Documents/tangos/tangos/core/timestep.py", line 83, in __getitem__
    return session.query(Halo).filter_by(timestep_id=self.id, halo_number=halo_number, object_typecode=object_typecode).first()
  File "/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3240, in first
    ret = list(self[0:1])
  File "/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3018, in __getitem__
    return list(res)
  File "/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3342, in __iter__
    return self._execute_and_instances(context)
  File "/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3367, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 988, in execute
    return meth(self, multiparams, params)
  File "/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1107, in _execute_clauseelement
    distilled_params,
  File "/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1253, in _execute_context
    e, statement, parameters, cursor, context
  File "/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1473, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 152, in reraise
    raise value.with_traceback(tb)
  File "/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1249, in _execute_context
    cursor, statement, parameters, context
  File "/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 580, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: halos.finder_offset
@Martin-Rey Martin-Rey changed the title #88 breaks old databases #88 breaks old databases? Mar 24, 2021
@apontzen
Copy link
Member

apontzen commented Mar 24, 2021 •
edited
Loading

I can reproduce this -- it's a big deal, and I should have spotted it. With a heavy heart... we probably have to revert the merge until we can find and test a backwards-compatibility fix. It may be that we have to go as far as using a versioning tool like alembic.

Any thoughts @mtremmel before I revert the merge?

@mtremmel
Copy link
Contributor

can we not use sqlite3 to add a new column to the halo table to create finder_offset and initialize it to the existing finder_id values?

@mtremmel
Copy link
Contributor

I had some things in there that I thought would help with backwards compatibility, but maybe in the end the changes were too pervasive

@apontzen
Copy link
Member

Not all users might be comfortable with that... but, worse, isn't the correct value for finder_offset sometimes finder_id but other times finder_id-1?

@mtremmel
Copy link
Contributor

I agree it isn't the perfect solution, but if the current database works with the current values of finder_id it should work with finder_offset = finder_id, since the "old" finder_id is essentially just finder_offset now

@apontzen
Copy link
Member

OK, that sounds alright then, but I think it somehow has to be automated for users who aren't comfortable editing their databases manually

@mtremmel
Copy link
Contributor

is there a way to do this at the time tangos tries to access the halo table for the first time?

@apontzen
Copy link
Member

It's not something that sqlalchemy does naturally (I thought it did, but that's only for entire new tables). But presumably it's possible in principle. I think the 'correct' solution is to use a tool like alembic but maybe that's overkill here.

@mtremmel
Copy link
Contributor

I don't know anything about alembic, but you are probably right moving forward we have the potential for many more users that can be affected by deep changes like this.

@mtremmel
Copy link
Contributor

Looking at @Martin-Rey's traceback I'm also a bit confused... it doesn't seem like the query should even try to read in that column? maybe this is a basic misunderstanding of how sqlalchemy is working in this instance

@mtremmel
Copy link
Contributor

so I checked and this seems to work as a fix (after running this i can access the database correctly and the finder_offsets are appropriately set to the value of finder_id).

sqlite3 my_old_database.db
sqlite> ALTER TABLE halos ADD COLUMN finder_offset Integer;
sqlite> UPDATE halos SET finder_offset = finder_id;

@apontzen
Copy link
Member

OK, I think I can hack this into an automated fix. (I can't quite wrap my head around alembic on a short timescale, although I'm sure that's a better way in the longer term.)

@apontzen
Copy link
Member

I put a fix in #140 - could you both check whether this works?

@mtremmel
Copy link
Contributor

just to be clear, we should try to use this branch to read in an "old" database?

@apontzen
Copy link
Member

yep!

@mtremmel
Copy link
Contributor

Ok looks like it works! I love how fast this works even on the large Romulus databases!

@apontzen
Copy link
Member

SQLite is pretty amazing

@Martin-Rey
Copy link
Author

My old databases are fixed to, and I got the "updating from old schema" warning. Many thanks!

@apontzen apontzen closed this as completed Aug 3, 2021
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@apontzen @mtremmel @Martin-Rey