Awx: Upgrade To 1.0.7 Fails

[AStrangwood]

ISSUE TYPE

• Bug Report

COMPONENT NAME

• Installer

SUMMARY

Upgraded to latest version of AWX from 1.0.6.41 is failing

ENVIRONMENT

• AWX version: 1.0.7
• AWX install method:docker on linux
• Ansible version: 2.6.2
• Operating System: Ubuntu 18
• Web Browser: N/A

STEPS TO REPRODUCE

Remove old containers, run awx installer

EXPECTED RESULTS

upgrade works able to use existing data

ACTUAL RESULTS

Fails with errors attached

ADDITIONAL INFORMATION

Logs
https://drive.google.com/file/d/17-SaM11ewW_oOZhtT5RrKv3mSclkHXPq/view?usp=sharing

[onitake]

It might be that manual Django migrations are required, but this hasn't helped in my case.

After manually running

awx-manage makemigrations
awx-manage migrate

inside the awx_web container, I'm still getting errors like

ProgrammingError: column main_organization.job_template_admin_role_id does not exist

[onitake]

This is the output of makemigrations:

Migrations for 'main':
  /usr/lib/python2.7/site-packages/awx/main/migrations/0048_auto_20180814_1232.py
    - Alter field o_auth2_access_token on activitystream
    - Alter field o_auth2_application on activitystream
    - Alter field created_by on credential
    - Alter field modified_by on credential
    - Alter field created_by on credentialtype
    - Alter field modified_by on credentialtype
    - Alter field created_by on custominventoryscript
    - Alter field modified_by on custominventoryscript
    - Alter field created_by on group
    - Alter field modified_by on group
    - Alter field created_by on host
    - Alter field modified_by on host
    - Alter field created_by on inventory
    - Alter field modified_by on inventory
    - Alter field inventory_update on inventoryupdateevent
    - Alter field created_by on label
    - Alter field modified_by on label
    - Alter field notification_type on notification
    - Alter field created_by on notificationtemplate
    - Alter field modified_by on notificationtemplate
    - Alter field notification_type on notificationtemplate
    - Alter field modified on oauth2accesstoken
    - Alter field scope on oauth2accesstoken
    - Alter field created_by on organization
    - Alter field modified_by on organization
    - Alter field project_update on projectupdateevent
    - Alter field created_by on schedule
    - Alter field modified_by on schedule
    - Alter field system_job on systemjobevent
    - Alter field created_by on team
    - Alter field modified_by on team
    - Alter field created_by on unifiedjob
    - Alter field modified_by on unifiedjob
    - Alter field created_by on unifiedjobtemplate
    - Alter field modified_by on unifiedjobtemplate
    - Alter index_together for inventoryupdateevent (3 constraint(s))
    - Alter index_together for projectupdateevent (4 constraint(s))
    - Alter index_together for systemjobevent (3 constraint(s))

No job_template_admin_role field there...

[cehovski]

I have the same problem with same error in logs.

Also tried doing migrations which unfortunately didn't solve the problem.

I've also tried backing up the database. Doing a clean install and restoring the database. It looks like the database structure has changed since I'm getting errors while restoring onto the newer version.

My current version is 1.0.6.41. And sorry for not pasting any logs. I've done this earlier today and didn't save them. I might add them later.

[AStrangwood]

I had to roll back to 1.0.6.41 to get back up and running.
I just try to upgrade to 1.0.6.49 but im getting the exact same error :(

[onitake]

The problem with downgrades is that the database schemas don't get rolled back, as the old release doesn't have the schema change scripts. To restore the DB to a working state, I had to upgrade to the latest version again, then run awx-manage migrate main <last DB state from 1.0.6> with 0037_v330_remove_legacy_fact_cleanup as the schema state. This rolled back all the changes that were made for 1.0.7.
There was also a change to the conf schema, but it seems 1.0.6 still works with that change: https://github.com/ansible/awx/blob/devel/awx/conf/migrations/0005_v330_rename_two_session_settings.py

[AStrangwood]

I have tried to do migrations to get up to 1.0.6.49 and these are the results

[root@awx awx]# awx-manage makemigrations
Migrations for 'main':
  /usr/lib/python2.7/site-packages/awx/main/migrations/0048_auto_20180814_1547.py
    - Alter field o_auth2_access_token on activitystream
    - Alter field o_auth2_application on activitystream
    - Alter field created_by on credential
    - Alter field modified_by on credential
    - Alter field created_by on credentialtype
    - Alter field modified_by on credentialtype
    - Alter field created_by on custominventoryscript
    - Alter field modified_by on custominventoryscript
    - Alter field created_by on group
    - Alter field modified_by on group
    - Alter field created_by on host
    - Alter field modified_by on host
    - Alter field created_by on inventory
    - Alter field modified_by on inventory
    - Alter field inventory_update on inventoryupdateevent
    - Alter field created_by on label
    - Alter field modified_by on label
    - Alter field notification_type on notification
    - Alter field created_by on notificationtemplate
    - Alter field modified_by on notificationtemplate
    - Alter field notification_type on notificationtemplate
    - Alter field modified on oauth2accesstoken
    - Alter field scope on oauth2accesstoken
    - Alter field created_by on organization
    - Alter field modified_by on organization
    - Alter field project_update on projectupdateevent
    - Alter field created_by on schedule
    - Alter field modified_by on schedule
    - Alter field system_job on systemjobevent
    - Alter field created_by on team
    - Alter field modified_by on team
    - Alter field created_by on unifiedjob
    - Alter field modified_by on unifiedjob
    - Alter field created_by on unifiedjobtemplate
    - Alter field modified_by on unifiedjobtemplate
    - Alter index_together for inventoryupdateevent (3 constraint(s))
    - Alter index_together for projectupdateevent (4 constraint(s))
    - Alter index_together for systemjobevent (3 constraint(s))
[root@awx awx]# awx-manage migrate
Operations to perform:
  Apply all migrations: auth, conf, contenttypes, djcelery, main, oauth2_provider, sessions, sites, social_django, sso, taggit
Running migrations:
  Applying main.0043_v330_oauth2accesstoken_modified...Traceback (most recent call last):
  File "/usr/bin/awx-manage", line 9, in <module>
    load_entry_point('awx==1.0.6.49', 'console_scripts', 'awx-manage')()
  File "/usr/lib/python2.7/site-packages/awx/__init__.py", line 116, in manage
    execute_from_command_line(sys.argv)
  File "/var/lib/awx/venv/awx/lib/python2.7/site-packages/django/core/management/__init__.py", line 364, in execute_from_command_line
    utility.execute()
  File "/var/lib/awx/venv/awx/lib/python2.7/site-packages/django/core/management/__init__.py", line 356, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/var/lib/awx/venv/awx/lib/python2.7/site-packages/django/core/management/base.py", line 283, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/var/lib/awx/venv/awx/lib/python2.7/site-packages/django/core/management/base.py", line 330, in execute
    output = self.handle(*args, **options)
  File "/var/lib/awx/venv/awx/lib/python2.7/site-packages/django/core/management/commands/migrate.py", line 204, in handle
    fake_initial=fake_initial,
  File "/var/lib/awx/venv/awx/lib/python2.7/site-packages/django/db/migrations/executor.py", line 115, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/var/lib/awx/venv/awx/lib/python2.7/site-packages/django/db/migrations/executor.py", line 145, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/var/lib/awx/venv/awx/lib/python2.7/site-packages/django/db/migrations/executor.py", line 244, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/var/lib/awx/venv/awx/lib/python2.7/site-packages/django/db/migrations/migration.py", line 129, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/var/lib/awx/venv/awx/lib/python2.7/site-packages/django/db/migrations/operations/fields.py", line 88, in database_forwards
    field,
  File "/var/lib/awx/venv/awx/lib/python2.7/site-packages/django/db/backends/base/schema.py", line 429, in add_field
    self.execute(sql, params)
  File "/var/lib/awx/venv/awx/lib/python2.7/site-packages/django/db/backends/base/schema.py", line 120, in execute
    cursor.execute(sql, params)
  File "/var/lib/awx/venv/awx/lib/python2.7/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/var/lib/awx/venv/awx/lib/python2.7/site-packages/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/var/lib/awx/venv/awx/lib/python2.7/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
django.db.utils.IntegrityError: column "modified" contains null values

[AStrangwood]

I was able to get to 1.0.7 by removing the application i had, but now everything is broken. Almost every page i go to gives me a different "GET returned status: 500 A server error has occurred".

[AStrangwood]

[matburt]

as pointed out in my announcement email on the mailing list, direct upgrades are not supported moving to 1.0.7. You'll need to start afresh or use our migration strategy:
https://github.com/ansible/awx/blob/devel/DATA_MIGRATION.md

[onitake]

This is a joke, right?!?

[matburt]

Yes, I've updated the link: https://github.com/ansible/awx/blob/devel/DATA_MIGRATION.md

[lucacri]

The whole point of SEMVER is to avoid situations like this. You can't expect people to know that a 1.0.X change would wreck their system. Breaking changes should be X.0.0.

Also, the kubernetes installer (as of few months ago) had all the images to :latest which means that your choice is definitely breaking AWX for anyone that even restarted their K8s. Ridiculous

[onitake]

@matburt The link was one thing, but my question was more directed at the "Want to upgrade AWX? You're screwed!" statement. And doing a break-everything change on a minor patch level just puts the icing on the cake.

I understand that making the code base of Ansible Tower open source is a major undertaking and breaking changes are to be expected, but telling users who are _seriously_ testing/using AWX to follow a non-obvious, poorly linked and previously non-documented upgrade path and to scrap 60% of their configuration and 100% of their usage history leaves a very sour aftertaste.

Priorities aside, I also don't understand how it shouldn't be possible to leverage Django's schema upgrade mechanism to _not_ completely break a user's configuration.
It's not perfect, but it should at least take away the grunt work off a DB migration.
Perhaps someone from the community can help with that?

[sightseeker]

I have also experienced this problem. (
So I recovered PostgreSQL table schema in awx database.
That step is:

# Connect DB in PostgreSQL container.
psql -U <USERNAME> <DBNAME>

# Recover main_organization schema
ALTER TABLE main_organization ADD COLUMN job_template_admin_role_id integer;
# I'm not sure this setting up number
# UPDATE main_organization SET job_template_admin_role_id = 9;
ALTER TABLE main_organization ADD FOREIGN KEY (job_template_admin_role_id) REFERENCES main_rbac_roles(id) DEFERRABLE INITIALLY DEFERRED;

[onitake]

@sightseeker aside from the other schema changes that already have migrations, is this the only thing needed? Where did you get that default value '9' for job_template_admin_role_id? Is this specific to your installation or hardcoded?

[sightseeker]

@onitake That value '9' of job_template_admin_role_id was setting up the Default role of main_organization in new installed AWX database.
But I was mistaken when I lookup the main_role_rbacs records of migrated database, and `main_role_rbacs' table records was different between new installed database and migrated database.

That value '9' of job_template_admin_role_id was setting job_template_admin_role in main_rbac_roles at new installed AWX database.

[Tiduster]

Hi !
@sightseeker could you exec this SQL request on your newly installed AWX database ? :-P

select * from main_rbac_roles where role_field = 'project_admin_role';

We upgrade from 1.0.6 and for the moment everything works fine.

Here is the SQL Requests we use:

ALTER TABLE main_oauth2accesstoken ADD COLUMN modified timestamp with time zone;
UPDATE public.main_oauth2accesstoken SET modified = '2018-07-02 09:11:24.119105+00' WHERE created = '2018-07-02 09:11:24.119105+00';
ALTER TABLE public.main_oauth2accesstoken ALTER COLUMN modified SET NOT NULL;

Then we faked migration 0043

And finally:

ALTER TABLE main_organization ADD COLUMN job_template_admin_role_id integer;

But we would like to this new column in organization with something :-) .

[orgito]

Hi @Tiduster . What do you mean by "we faked migration 0043"?

[onitake]

@Tiduster That doesn't seem like a terribly good idea, but it's useful to find out what's needed to actually implement the DB migration in Django. If you can collect all the necessary changes, someone (possibly myself) would surely write the migration script.

[Tiduster]

@orgito : awx-manage migrate main 0043 --fake

Obviously, you have to do manually what the migration does.

@onitake: It won't be possible to collect all necessary changes, because a lot of older migrations have been modified, and each fixes is highly specific to each installation.

[onitake]

Older migrations have been modified? Are you sure about that?
That sounds pretty wrong to me...

But still, I don't see how it shouldn't be possible to collect all the schema changes. Each should have it's own migration script, after all. Of course there will database contents specific to each installation, but this can be addressed in the migration.

[Tiduster]

@onitake : yes I am sure: https://github.com/ansible/awx/compare/1.0.6...1.0.7#diff-c7234edfcd9bb51e834f00b6cf059127

We had some permission issue with job templates because migration 0021_v330_declare_new_rbac_roles.py has been modified.

We manage to fix them with this list of SQL requests but:

• That's crappy and wrong
• I am sure we will find eventually there is more issues
• Every number match our own id, and won't work for other installation

INSERT INTO main_rbac_roles (role_field, implicit_parents, content_type_id, object_id) VALUES ('job_template_admin_role', '[33]', 41, 2), ('job_template_admin_role', '[43]', 41, 3);

UPDATE main_organization SET job_template_admin_role_id = 1286 WHERE id = 2;
UPDATE main_organization SET job_template_admin_role_id = 1287 WHERE id = 3;

UPDATE main_rbac_roles SET implicit_parents = '[30, 32, 33, 34, 35, 37, 39, 1286]' WHERE id = 31;
UPDATE main_rbac_roles SET implicit_parents = '[40, 42, 43, 44, 45, 47, 49, 1287]' WHERE id = 41;

INSERT INTO main_rbac_roles_parents (from_role_id, to_role_id) VALUES (31, 1286), (1286,33), (41,1287), (1287,43);

INSERT INTO main_rbac_role_ancestors (role_field, content_type_id, object_id, ancestor_id, descendent_id) VALUES 
('admin_role', 32, 310, 1, 1293),
('admin_role',32, 310, 1286, 1293),
('admin_role',32, 310, 33, 1293),
('admin_role',32, 310, 39, 1293);

INSERT INTO main_rbac_role_ancestors (role_field, content_type_id, object_id, ancestor_id, descendent_id) VALUES 
('admin_role', 32, 309, 1, 1290),
('admin_role',32, 309, 1286, 1290),
('admin_role',32, 309, 33, 1290),
('admin_role',32, 309, 39, 1290);

INSERT INTO main_rbac_role_ancestors (role_field, content_type_id, object_id, ancestor_id, descendent_id) VALUES 
('job_template_admin_role', 41, 2, 1286, 1286),
('job_template_admin_role',41, 2, 33, 1286),
('job_template_admin_role',41, 2, 1, 1286);

INSERT INTO main_rbac_role_ancestors (role_field, content_type_id, object_id, ancestor_id, descendent_id) VALUES 
('job_template_admin_role', 41, 3, 1287, 1287),
('job_template_admin_role',41, 3, 43, 1287),
('job_template_admin_role',41, 3, 1, 1287);

In short, previously, the role project_admin_role was used for projects and templates.
Now, there is a new job_template_admin_role for templates. So you have to create its RBAC in main_rbac_roles table and link this new role instead of project_admin_role in every rbac tables.

Otherwise, you can't be admin of job templates created by other members of your organization.

Regards,

[towolf]

I also don't understand how it shouldn't be possible to leverage Django's schema upgrade mechanism to not completely break a user's configuration

Nearly convinced this is a scheme to shoo people away from the open-source version and into the arms of Sales.

[jlaska]

@towolf The AWX development team is aware of the pain related to upgrades, and we're committed to making the process much smoother in the near future by versioning appropriately and using migrations to bridge the gap from one version to another whenever possible.

[onitake]

Thank you, @jlaska ! :clap:

I hope this will also carry over to Ansible Tower (assuming it is sufficiently close to AWX).

[anthonyloukinas]

@jlaska Not meaning to bring up an old issue.. but I've not found any commits/issues related to this issue recently.

Has there been any work done with upgrade paths, aside from moving to an entirely new instance?

[ryanpetrello]

@AStrangwood @anthonyloukinas @onitake @lucacri (and others)

We've made some tangible changes here, and they've mostly been around process, not specific commits:

1. We've moved to a versioning model more like semantic versioning (https://semver.org/) to signal when new AWX versions might have breaking API changes. You've probably noticed that AWX version numbers have been going up more quickly now - we're on 4.0.0 as we speak - that's because we generally bump the major version any time we introduce a feature or an architectural change that might result in notable behavior changes (such as the removal of the celery library for task execution, or our recent move to Python 3).

2. We've made a better effort to provide a summarized view of release notes, along with an email to the mailing list about new versions we release.

3. _Where possible_, we've stopped rewriting and "squashing" Django migration files. This has generally resulted in a stable database migration path between major versions of AWX. While we can't promise we'll never introduce a breaking change (sometimes you just don't have a choice), and while there's definitely no guaranteed upgrade path between arbitrary commits on devel, we've come a long way to improving interoperability across major AWX versions. We've even gone so far as to add a test to our pre-merge CI that fails if the PR in question might cause a migration failure: https://github.com/ansible/awx/commit/30fbeb43bb0368e84c6cab7f30ad2ae9253f2454