Error running alembic migrations: database locked

[ayleph]

I've got a test instance using the sqlite backend. I recently updated to current master and tried to run dbupdate. I received the error below.

(mediagoblin-dev) ~/mediagoblin-dev $ bin/gmg dbupdate
/path/to/mediagoblin/mediagoblin/media_types/video/transcoders.py:37: PyGIWarning: GstPbutils was imported without specifying a version first. Use gi.require_version('GstPbutils', '1.0') before import to ensure that the right version gets loaded.
  from gi.repository import GstPbutils
/path/to/mediagoblin/lib/python2.7/site-packages/SQLAlchemy-0.8.7-py2.7-linux-x86_64.egg/sqlalchemy/engine/default.py:459: SAWarning: Unicode type received non-unicode bind param value.
INFO  [alembic.migration] Context impl SQLiteImpl.
INFO  [alembic.migration] Will assume non-transactional DDL.
INFO  [alembic.migration] Running upgrade 101510e3a713 -> 8429e33fdf7, Remove the Graveyard objects from CommentNotification objects
Traceback (most recent call last):
  File "bin/gmg", line 9, in <module>
    load_entry_point('mediagoblin', 'console_scripts', 'gmg')()
  File "/path/to/mediagoblin/mediagoblin/gmg_commands/__init__.py", line 142, in main_cli
    args.func(args)
  File "/path/to/mediagoblin/mediagoblin/gmg_commands/dbupdate.py", line 162, in dbupdate
    run_dbupdate(app_config, global_config)
  File "/path/to/mediagoblin/mediagoblin/gmg_commands/dbupdate.py", line 134, in run_dbupdate
    run_alembic_migrations(db, app_config, global_config)
  File "/path/to/mediagoblin/mediagoblin/gmg_commands/dbupdate.py", line 116, in run_alembic_migrations
    manager.init_or_migrate()
  File "/path/to/mediagoblin/mediagoblin/db/migration_tools.py", line 84, in init_or_migrate
    self.upgrade(version)
  File "/path/to/mediagoblin/mediagoblin/db/migration_tools.py", line 60, in upgrade
    return command.upgrade(self.alembic_cfg, version or 'head')
  File "/path/to/mediagoblin/lib/python2.7/site-packages/alembic-0.6.6-py2.7.egg/alembic/command.py", line 125, in upgrade
    script.run_env()
  File "/path/to/mediagoblin/lib/python2.7/site-packages/alembic-0.6.6-py2.7.egg/alembic/script.py", line 203, in run_env
    util.load_python_file(self.dir, 'env.py')
  File "/path/to/mediagoblin/lib/python2.7/site-packages/alembic-0.6.6-py2.7.egg/alembic/util.py", line 215, in load_python_file
    module = load_module_py(module_id, path)
  File "/path/to/mediagoblin/lib/python2.7/site-packages/alembic-0.6.6-py2.7.egg/alembic/compat.py", line 58, in load_module_py
    mod = imp.load_source(module_id, path, fp)
  File "/path/to/mediagoblin/mediagoblin/db/migrations/env.py", line 62, in <module>
    run_migrations_online()
  File "/path/to/mediagoblin/mediagoblin/db/migrations/env.py", line 57, in run_migrations_online
    context.run_migrations()
  File "<string>", line 7, in run_migrations
  File "/path/to/mediagoblin/lib/python2.7/site-packages/alembic-0.6.6-py2.7.egg/alembic/environment.py", line 689, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/path/to/mediagoblin/lib/python2.7/site-packages/alembic-0.6.6-py2.7.egg/alembic/migration.py", line 263, in run_migrations
    change(**kw)
  File "/path/to/mediagoblin/mediagoblin/db/migrations/versions/8429e33fdf7_remove_the_graveyard_objects_from_.py", line 45, in upgrade
    notification_table.c.object_id == gmr.id
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1614, in execute
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 662, in execute
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 761, in _execute_clauseelement
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 908, in _execute_context
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 491, in _commit_impl
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1024, in _handle_dbapi_exception
  File "build/bdist.linux-x86_64/egg/sqlalchemy/util/compat.py", line 196, in raise_from_cause
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 488, in _commit_impl
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/default.py", line 297, in do_commit
sqlalchemy.exc.OperationalError: (OperationalError) database is locked None None

[Christopher Allan Webber]

All 0.8.2 tickets are being rolled over to 0.9.0

[Christopher Allan Webber]

I'm assuming you weren't running the server when you ran dbupdate, right?

Maybe sqlalchemy-migrate doesn't let go of its connection before Alembic takes over. Otherwise I'm out of ideas!

[ayleph]

Sorry, didn't get an email with your comments. Earlier today I pulled from master again and re-ran python2 setup.py develop --upgrade in my virtualenv. Then I tried to update the sqlite database again and got a slightly different error.

Now I get sqlalchemy.exc.ResourceClosedError: This Connection is closed instead of sqlalchemy.exc.OperationalError: (OperationalError) database is locked None None.

INFO  [alembic.migration] Context impl SQLiteImpl.
INFO  [alembic.migration] Will assume non-transactional DDL.
INFO  [alembic.migration] Running upgrade 101510e3a713 -> 8429e33fdf7, Remove the Graveyard objects from CommentNotification objects
Traceback (most recent call last):
  File "bin/gmg", line 9, in <module>
    load_entry_point('mediagoblin', 'console_scripts', 'gmg')()
  File "/path/to/mediagoblin/mediagoblin/gmg_commands/__init__.py", line 142, in main_cli
    args.func(args)
  File "/path/to/mediagoblin/mediagoblin/gmg_commands/dbupdate.py", line 162, in dbupdate
    run_dbupdate(app_config, global_config)
  File "/path/to/mediagoblin/mediagoblin/gmg_commands/dbupdate.py", line 134, in run_dbupdate
    run_alembic_migrations(db, app_config, global_config)
  File "/path/to/mediagoblin/mediagoblin/gmg_commands/dbupdate.py", line 116, in run_alembic_migrations
    manager.init_or_migrate()
  File "/path/to/mediagoblin/mediagoblin/db/migration_tools.py", line 84, in init_or_migrate
    self.upgrade(version)
  File "/path/to/mediagoblin/mediagoblin/db/migration_tools.py", line 60, in upgrade
    return command.upgrade(self.alembic_cfg, version or 'head')
  File "/srv/mediagoblin/mediagoblin-dev/lib/python2.7/site-packages/alembic-0.6.6-py2.7.egg/alembic/command.py", line 125, in upgrade
    script.run_env()
  File "/srv/mediagoblin/mediagoblin-dev/lib/python2.7/site-packages/alembic-0.6.6-py2.7.egg/alembic/script.py", line 203, in run_env
    util.load_python_file(self.dir, 'env.py')
  File "/srv/mediagoblin/mediagoblin-dev/lib/python2.7/site-packages/alembic-0.6.6-py2.7.egg/alembic/util.py", line 215, in load_python_file
    module = load_module_py(module_id, path)
  File "/srv/mediagoblin/mediagoblin-dev/lib/python2.7/site-packages/alembic-0.6.6-py2.7.egg/alembic/compat.py", line 58, in load_module_py
    mod = imp.load_source(module_id, path, fp)
  File "/path/to/mediagoblin/mediagoblin/db/migrations/env.py", line 62, in <module>
    run_migrations_online()
  File "/path/to/mediagoblin/mediagoblin/db/migrations/env.py", line 57, in run_migrations_online
    context.run_migrations()
  File "<string>", line 7, in run_migrations
  File "/srv/mediagoblin/mediagoblin-dev/lib/python2.7/site-packages/alembic-0.6.6-py2.7.egg/alembic/environment.py", line 689, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/srv/mediagoblin/mediagoblin-dev/lib/python2.7/site-packages/alembic-0.6.6-py2.7.egg/alembic/migration.py", line 263, in run_migrations
    change(**kw)
  File "/path/to/mediagoblin/mediagoblin/db/migrations/versions/8429e33fdf7_remove_the_graveyard_objects_from_.py", line 45, in upgrade
    notification_table.c.object_id == gmr.id
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1991, in execute
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 914, in execute
  File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1166, in _execute_context
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 693, in _commit_impl
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 318, in connection
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1344, in _handle_dbapi_exception
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 316, in connection
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 396, in _revalidate_connection
sqlalchemy.exc.ResourceClosedError: This Connection is closed

[ayleph]

Replying to cwebber:

I'm assuming you weren't running the server when you ran dbupdate, right?

I can't remember whether I had properly stopped the server before updating when I reported the "database locked" error originally. But I had definitely stopped the server before updating when I received the "Connection is closed" error.

[ayleph]

I found a ​Stack Overflow article related to this issue, talking about how sqlite3 only supports 1 simultaneous connection. The original poster had a workaround using the list() function. This may be useful here, though there may also be a better way to do this.

I've tested this change with sqlite and it seems to work. That is, the migration claimed to run, and no error output was produced. If I try to run migrations again, the migration does not run. I don't know whether that's expected behaviour or not. I have not tried this change against a postgresql database.

--- a/mediagoblin/db/migrations/versions/8429e33fdf7_remove_the_graveyard_objects_from_.py
+++ b/mediagoblin/db/migrations/versions/8429e33fdf7_remove_the_graveyard_objects_from_.py
@@ -28,7 +28,8 @@ def upgrade():
     graveyard_table = inspect_table(metadata, "core__graveyard")
     gmr_table = inspect_table(metadata, "core__generic_model_reference")

-    for tombstone in db.execute(gmr_table.select()):
+    res = list(db.execute(gmr_table.select()))
+    for tombstone in res:
         # Look up the gmr for the tombstone8
         gmr = db.execute(gmr_table.select().where(and_(
             gmr_table.c.obj_pk == tombstone.id,

[ayleph]

If this fix is valid, the same would need to be applied to other migrations.

--- a/mediagoblin/db/migrations/versions/101510e3a713_removes_graveyard_items_from_.py
+++ b/mediagoblin/db/migrations/versions/101510e3a713_removes_graveyard_items_from_.py
@@ -32,7 +32,8 @@ def upgrade():
     collection_items_table = inspect_table(metadata, "core__collection_items")
     graveyard_table = inspect_table(metadata, "core__graveyard")

-    for tombstone in db.execute(graveyard_table.select()):
+    res = list(db.execute(graveyard_table.select()))
+    for tombstone in res:
         # Get GMR for tombstone
         gmr = db.execute(gmr_table.select().where(and_(
             gmr_table.c.obj_pk == tombstone.id,

[Boris Bobrov]

Fixed in 1fe6f5d, thanks ayleph!