fedora-infrastructure

Clone
Source Code
GIT

#5745 Alembic not permitted to drop Bodhi's karma column by PostgreSQL
Closed: Fixed 7 years ago Opened 7 years ago by bowlofeggs.

Closed: Fixed
Reopen Issue

The following is an error message I received from Ansible when trying to apply the bodhi-2.4.0 beta migrations to staging:

TASK [Upgrade the database _raw_params=/usr/bin/alembic -c /usr/share/bodhi/alembic.ini upgrade head, chdir=/usr/share/bodhi/] ***
Monday 06 February 2017  19:57:31 +0000 (0:00:00.355)       0:01:30.909 ******* 
fatal: [bodhi-backend01.stg.phx2.fedoraproject.org]: FAILED! => {"changed": true, "cmd": ["/usr/bin/alembic", "-c", "/usr/share/bodhi/alembic.ini", "upgrade", "head"], "delta": "0:00:01.186043", "end": "2017-02-06 19:57:32.979753", "failed": true, "rc": 1, "start": "2017-02-06 19:57:31.793710", "stderr": "INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 3cde3882442a -> 06aa0e8aa5d2, Drop the Update.karma column.
Traceback (most recent call last):
  File \"/usr/bin/alembic\", line 12, in <module>
    sys.exit(load_entry_point('alembic', 'console_scripts', 'alembic')())
  File \"/usr/lib/python2.7/site-packages/alembic/config.py\", line 450, in main
    CommandLine(prog=prog).main(argv=argv)
  File \"/usr/lib/python2.7/site-packages/alembic/config.py\", line 444, in main
    self.run_cmd(cfg, options)
  File \"/usr/lib/python2.7/site-packages/alembic/config.py\", line 427, in run_cmd
    **dict((k, getattr(options, k)) for k in kwarg)
  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 \"alembic/env.py\", line 72, in <module>
    run_migrations_online()
  File \"alembic/env.py\", line 65, 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/bodhi/alembic/versions/06aa0e8aa5d2_drop_the_update_karma_column.py\", line 20, in upgrade
    op.drop_column('updates', 'karma')
  File \"<string>\", line 8, in drop_column
  File \"<string>\", line 3, in drop_column
  File \"/usr/lib/python2.7/site-packages/alembic/operations/ops.py\", line 1637, in drop_column
    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 81, in drop_column
    **operation.kw
  File \"/usr/lib/python2.7/site-packages/alembic/ddl/impl.py\", line 175, in drop_column
    self._exec(base.DropColumn(table_name, column, schema=schema))
  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.OperationalError: (psycopg2.OperationalError) Global DDL locking attempt rejected by configuration
DETAIL:  bdr.permit_ddl_locking is false and the attempted command would require the global lock to be acquired. Command rejected.
HINT:  See the 'DDL replication' chapter of the documentation.
 [SQL: 'ALTER TABLE updates DROP COLUMN karma']", "stdout": "", "stdout_lines": [], "warnings": []}

It sounds like I might need that bdr.permit_ddl_locking setting set to true. I presume that's a PostgreSQL server-side setting?

This issue is blocking Bodhi 2.4.0 beta from being deployed to staging.

For reference, this occurred while running the bodhi upgrade playbook on batcave01:

sudo rbac-playbook manual/upgrade/bodhi.yml -l staging

Here are some docs I found that seem relevant to this:

http://bdr-project.org/docs/1.0/bdr-configuration-variables.html

http://bdr-project.org/docs/1.0/ddl-replication.html

I don't know if this is possible, but I wonder if we can have the migration run this inside the transaction before dropping the column:

SET LOCAL bdr.permit_ddl_locking = on;

The first docs link I provided above doesn't make it clear whether sessions are allowed to do this or not.

I talked with @puiterwijk on Freenode today and he suggested that I could use alembic to generate the SQL that would be executed and modify that to insert the above SET LOCAL statement. Here's the alembic migration:

[bowlofeggs@bodhi-backend01 bodhi][STG]$ sudo /usr/bin/alembic -c /usr/share/bodhi/alembic.ini upgrade 3cde3882442a:06aa0e8aa5d2 --sql > ~/drop_karma.sql
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Generating static SQL
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 3cde3882442a -> 06aa0e8aa5d2, Drop the Update.karma column.
[bowlofeggs@bodhi-backend01 bodhi][STG]$ cat ~/drop_karma.sql 
BEGIN;

-- Running upgrade 3cde3882442a -> 06aa0e8aa5d2

ALTER TABLE updates DROP COLUMN karma;

UPDATE alembic_version SET version_num='06aa0e8aa5d2' WHERE alembic_version.version_num = '3cde3882442a';

COMMIT;

To allow it to run, I think we can use this:

BEGIN;

-- Running upgrade 3cde3882442a -> 06aa0e8aa5d2

SET LOCAL bdr.permit_ddl_locking = on; 
ALTER TABLE updates DROP COLUMN karma;

UPDATE alembic_version SET version_num='06aa0e8aa5d2' WHERE alembic_version.version_num = '3cde3882442a';

COMMIT;

@puiterwijk suggests that the alembic version table will need a primary key. I think something like this should do the trick if there are no duplicates:

BEGIN;

ALTER TABLE alembic_version ADD PRIMARY KEY(version_num);

COMMIT;

If there are duplicates, we will need to write some code to drop the duplicate entries first.

 
bodhi2=# BEGIN;
BEGIN
bodhi2=# SET LOCAL bdr.permit_ddl_locking = on; 
SET
bodhi2=# ALTER TABLE alembic_version ADD PRIMARY KEY(version_num);
ALTER TABLE
bodhi2=# COMMIT;
COMMIT

bodhi2=# BEGIN;
BEGIN
bodhi2=# 
bodhi2=# -- Running upgrade 3cde3882442a -> 06aa0e8aa5d2
bodhi2=# 
bodhi2=# SET LOCAL bdr.permit_ddl_locking = on; 
SET
bodhi2=# ALTER TABLE updates DROP COLUMN karma;
ALTER TABLE
bodhi2=# 
bodhi2=# UPDATE alembic_version SET version_num='06aa0e8aa5d2' WHERE alembic_version.version_num = '3cde3882442a';
UPDATE 1
bodhi2=# 
bodhi2=# COMMIT;
COMMIT
bodhi2=#

Thanks @puiterwijk! Looks like there is one more migration that is failing in this same way, so I've generated another SQL script to get us all the way to the latest alembic head:

[bowlofeggs@bodhi-backend01 bodhi][STG]$ sudo /usr/bin/alembic -c /usr/share/bodhi/alembic.ini heads
4f2f825bcf4a (head)
[bowlofeggs@bodhi-backend01 bodhi][STG]$ sudo /usr/bin/alembic -c /usr/share/bodhi/alembic.ini upgrade 06aa0e8aa5d2:4f2f825bcf4a --sql > ~/drop_karma.sql
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Generating static SQL
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 06aa0e8aa5d2 -> 4f2f825bcf4a, Disallow NULL values in autokarma.
[bowlofeggs@bodhi-backend01 bodhi][STG]$ cat ~/drop_karma.sql 
BEGIN;

-- Running upgrade 06aa0e8aa5d2 -> 4f2f825bcf4a

UPDATE updates SET autokarma=%(autokarma)s WHERE updates.autokarma IS NULL;

ALTER TABLE updates ALTER COLUMN autokarma SET NOT NULL;

UPDATE alembic_version SET version_num='4f2f825bcf4a' WHERE alembic_version.version_num = '06aa0e8aa5d2';

COMMIT;

If we add in the needed SET LOCAL, this script should work for us:

BEGIN;

-- Running upgrade 06aa0e8aa5d2 -> 4f2f825bcf4a

SET LOCAL bdr.permit_ddl_locking = on;

UPDATE updates SET autokarma=0 WHERE updates.autokarma IS NULL;

ALTER TABLE updates ALTER COLUMN autokarma SET NOT NULL;

UPDATE alembic_version SET version_num='4f2f825bcf4a' WHERE alembic_version.version_num = '06aa0e8aa5d2';

COMMIT;
Edited 7 years ago by bowlofeggs 
bodhi2=# BEGIN;
BEGIN
bodhi2=# 
bodhi2=# -- Running upgrade 06aa0e8aa5d2 -> 4f2f825bcf4a
bodhi2=# 
bodhi2=# SET LOCAL bdr.permit_ddl_locking = on;
SET
bodhi2=# 
bodhi2=# UPDATE updates SET autokarma=false WHERE updates.autokarma IS NULL;
UPDATE 37816
bodhi2=# ALTER TABLE updates ALTER COLUMN autokarma SET NOT NULL;
ALTER TABLE
bodhi2=# 
bodhi2=# UPDATE alembic_version SET version_num='4f2f825bcf4a' WHERE alembic_version.version_num = '06aa0e8aa5d2';
UPDATE 1
bodhi2=# 
bodhi2=# COMMIT;
COMMIT

Bodhi 2.4.0b1 seems to be successfully running in staging, thanks to @puiterwijk!

@bowlofeggs changed the status to Closed
7 years ago

Login to comment on this ticket.

Metadata
None
None
None
None
None
Powered by Pagure 5.13.3
Documentation • File an Issue • About • SSH Hostkey/Fingerprint
© Red Hat, Inc. and others.