taskotron / resultsdb

#65 Can't migrate resultsdb's db schema from 1.0 to 2.0
Closed: Fixed Opened by mkrizek.

Closed: Fixed
Reopen Issue

So I did this:

[mkrizek@resultsdb-dev01 ~][PROD]$ resultsdb upgrade_db
doing stream logging
/usr/lib/python2.7/site-packages/flask_sqlalchemy/__init__.py:800: UserWarning: SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and will be disabled by default in the future.  Set it to True to suppress this warning.
  warnings.warn('SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and will be disabled by default in the future.  Set it to True to suppress this warning.')
Proceeding in non-destructive mode. To perform destructive steps use -d option.
Upgrading Database to Latest Revision
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 978007ecd2b -> 540dbe71fa91, Change schema to v2.0 - step 1 - prepare columns
Traceback (most recent call last):
  File "/usr/bin/resultsdb", line 9, in <module>
    load_entry_point('resultsdb==2.0.0', 'console_scripts', 'resultsdb')()
  File "/usr/lib/python2.7/site-packages/resultsdb/cli.py", line 168, in main
    command(options.destructive)
  File "/usr/lib/python2.7/site-packages/resultsdb/cli.py", line 47, in upgrade_db
    al_command.upgrade(alembic_cfg, "head")
  File "/usr/lib/python2.7/site-packages/alembic/command.py", line 174, in upgrade
    script.run_env()
  File "/usr/lib/python2.7/site-packages/alembic/script/base.py", line 397, in run_env
    util.load_python_file(self.dir, 'env.py')
  File "/usr/lib/python2.7/site-packages/alembic/util/pyfiles.py", line 81, in load_python_file
    module = load_module_py(module_id, path)
  File "/usr/lib/python2.7/site-packages/alembic/util/compat.py", line 79, in load_module_py
    mod = imp.load_source(module_id, path, fp)
  File "/usr/share/resultsdb/alembic/env.py", line 81, in <module>
    run_migrations_online()
  File "/usr/share/resultsdb/alembic/env.py", line 74, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/usr/lib/python2.7/site-packages/alembic/runtime/environment.py", line 797, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/usr/lib/python2.7/site-packages/alembic/runtime/migration.py", line 312, in run_migrations
    step.migration_fn(**kw)
  File "/usr/share/resultsdb/alembic/versions/540dbe71fa91_change_schema_to_v2_0_step_1.py", line 26, in upgrade
    op.create_unique_constraint(None, 'group', ['uuid'])
  File "<string>", line 8, in create_unique_constraint
  File "<string>", line 3, in create_unique_constraint
  File "/usr/lib/python2.7/site-packages/alembic/operations/ops.py", line 394, in create_unique_constraint
    return operations.invoke(op)
  File "/usr/lib/python2.7/site-packages/alembic/operations/base.py", line 318, in invoke
    return fn(self, operation)
  File "/usr/lib/python2.7/site-packages/alembic/operations/toimpl.py", line 135, in create_constraint
    operation.to_constraint(operations.migration_context)
  File "/usr/lib/python2.7/site-packages/alembic/ddl/impl.py", line 180, in add_constraint
    self._exec(schema.AddConstraint(const))
  File "/usr/lib/python2.7/site-packages/alembic/ddl/impl.py", line 118, in _exec
    return conn.execute(construct, *multiparams, **params)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 968, in _execute_ddl
    compiled
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
    context)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
    exc_info
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/util/compat.py", line 202, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (psycopg2.IntegrityError) could not create unique index "group_uuid_key"
DETAIL:  Key (uuid)=(f6194c80-0702-11e6-8401-525400571835) is duplicated.
 [SQL: 'ALTER TABLE "group" ADD UNIQUE (uuid)']

Which might have something to do with:

resultsdb_dev=# select * from job where uuid = 'f6194c80-0702-11e6-8401-525400571835';
   id   |  status   |         start_time         |          end_time          |                                         ref_url                                          |    name     |                 uuid      
--------+-----------+----------------------------+----------------------------+------------------------------------------------------------------------------------------+-------------+---------------------------
-----------
 183977 | COMPLETED | 2016-04-20 14:23:41.543406 | 2016-04-20 14:23:43.259253 | https://taskotron-dev.fedoraproject.org/execdb/jobs/f6194c80-0702-11e6-8401-525400571835 | qa.depcheck | f6194c80-0702-11e6-8401-52
5400571835
 183978 | COMPLETED | 2016-04-20 14:23:43.518683 | 2016-04-20 14:23:45.388949 | https://taskotron-dev.fedoraproject.org/execdb/jobs/f6194c80-0702-11e6-8401-525400571835 | qa.depcheck | f6194c80-0702-11e6-8401-52
5400571835
(2 rows)

Figure out what needs to be done to fix the alembic migration script and provide a patch. This is currently blocking (and seems to be the last bit of) the dev rebuild and redeploy.

This ticket had assigned some Differential requests:
D1054
D1058

@mkrizek - I don't see a reasonable way to "automagically" solve this in alembic. It is essentially a database in an inconsistent state, and it IMO needs to be taken care of manually (I would not even want to have it done automatically, honestly).

I'll take care of it, though.

So, after spending some time on it, I must admit that solving it by hand is probably not wise, so I've changed the alembic migration in a way that merges the duplicate jobs to one. Diff coming up.

The patch is in develop, please let me know once you perform the migration.

So I tried running the migration on dev, unfortunately it failed with OOM :/ We have 20G of memory on that machine and 2G swap. I wonder if tweaking postgres config for performance would help? So unless we don't find anything wrong with the migration script, we might talk to infra about this.

The migration has been done on a limited number of results on all machines. The rest of the data will be migrated offline. Closing.
Metadata
None
None
None
None
Lowest
Static archive. pagure.io was sunset in 2026; this is a read-only snapshot.
Red Hat Logo

Fedora is sponsored by Red Hat. Learn more about the relationship between Red Hat and Fedora »

© Red Hat, Inc. and others.