Mitaka -> Newton: Database online_data_migrations in newton fail due to missing keypairs

[Saverio Proto]

Upgrading the deployment from Mitaka to Newton.
This bug blocks people from upgrading to Ocata because the database migration for nova fails.

Running nova newton 14.0.5, the database is 334

root@moby:/backups# nova-manage db online_data_migrations
Option "verbose" from group "DEFAULT" is deprecated for removal. Its value may be silently ignored in the future.
Running batches of 50 until complete
50 rows matched query migrate_flavors, 50 migrated
20 rows matched query migrate_flavors, 20 migrated
Error attempting to run <function migrate_instance_keypairs at 0x7f3373c58b90>
30 rows matched query migrate_instances_add_request_spec, 30 migrated
Error attempting to run <function migrate_instance_keypairs at 0x7f3373c58b90>
50 rows matched query migrate_instances_add_request_spec, 50 migrated
Error attempting to run <function migrate_instance_keypairs at 0x7f3373c58b90>
50 rows matched query migrate_instances_add_request_spec, 50 migrated
Error attempting to run <function migrate_instance_keypairs at 0x7f3373c58b90>
50 rows matched query migrate_instances_add_request_spec, 50 migrated
Error attempting to run <function migrate_instance_keypairs at 0x7f3373c58b90>
50 rows matched query migrate_instances_add_request_spec, 50 migrated
Error attempting to run <function migrate_instance_keypairs at 0x7f3373c58b90>
50 rows matched query migrate_instances_add_request_spec, 50 migrated
Error attempting to run <function migrate_instance_keypairs at 0x7f3373c58b90>
50 rows matched query migrate_instances_add_request_spec, 50 migrated
Error attempting to run <function migrate_instance_keypairs at 0x7f3373c58b90>
50 rows matched query migrate_instances_add_request_spec, 50 migrated
Error attempting to run <function migrate_instance_keypairs at 0x7f3373c58b90>
50 rows matched query migrate_instances_add_request_spec, 50 migrated
Error attempting to run <function migrate_instance_keypairs at 0x7f3373c58b90>
50 rows matched query migrate_instances_add_request_spec, 50 migrated
Error attempting to run <function migrate_instance_keypairs at 0x7f3373c58b90>
50 rows matched query migrate_instances_add_request_spec, 50 migrated
Error attempting to run <function migrate_instance_keypairs at 0x7f3373c58b90>
/usr/lib/python2.7/dist-packages/pkg_resources/__init__.py:188: RuntimeWarning: You have iterated over the result of pkg_resources.parse_version. This is a legacy behavior which is inconsistent with the new version class introduced in setuptools 8.0. In most cases, conversion to a tuple is unnecessary. For comparison of versions, sort the Version instances directly. If you have another use case requiring the tuple, please file a bug with the setuptools project describing that need.
  stacklevel=1,
50 rows matched query migrate_instances_add_request_spec, 5 migrated
2017-04-20 14:48:36.586 396 ERROR nova.objects.keypair [req-565cbe62-030b-4b00-b9db-5ee82117889b - - - - -] Some instances are still missing keypair information. Unable to run keypair migration at this time.
5 rows matched query migrate_aggregates, 5 migrated
5 rows matched query migrate_instance_groups_to_api_db, 5 migrated
2 rows matched query delete_build_requests_with_no_instance_uuid, 2 migrated
Error attempting to run <function migrate_instance_keypairs at 0x7f3373c58b90>
50 rows matched query migrate_instances_add_request_spec, 0 migrated
2017-04-20 14:48:40.620 396 ERROR nova.objects.keypair [req-565cbe62-030b-4b00-b9db-5ee82117889b - - - - -] Some instances are still missing keypair information. Unable to run keypair migration at this time.
root@moby:/backups#

Adding a 'raise' after https://github.com/openstack/nova/blob/stable/newton/nova/cmd/manage.py#L896
you can see:

root@moby:/backups# nova-manage db online_data_migrations
Option "verbose" from group "DEFAULT" is deprecated for removal. Its value may be silently ignored in the future.
Running batches of 50 until complete
Error attempting to run <function migrate_instance_keypairs at 0x7fb45132ab90>
error: 'NoneType' object has no attribute 'key_name'

[Saverio Proto (zioproto)]

This will probably happen to all those operators that in the upgrade Kilo to Liberty did follow this workaround here:
https://bugs.launchpad.net/nova/+bug/1511466

[OpenStack Infra (hudson-openstack)]

Related fix proposed to branch: master
Review: https://review.openstack.org/458564

[Dan Smith (danms)]

Unfortunately, this bug seems to have been triggered by the "workaround" offered in bug 1511466, which disabled FK constraints and broke the model linkage between InstanceExtra and Instance. Doing so breaks a lot of code that assumes this linkage, and this bug is one manifestation of the problem.

The referenced change to this bug does not fix the problem, but logs a warning to the user explaining what is going on. To get yourself out of the jam, you need to delete the orphaned instance_extra records (i.e. the ones that have instance_uuid that does not reference something in the instances table).

[Saverio Proto (zioproto)]

If you messed up the database during the upgrade from Kilo to Liberty following the workaround of bug 1511466 (comments #3 and #9) you can use this python script to fix your database:

import MySQLdb
conn = MySQLdb.connect(host = '127.0.0.1', user = 'nova', passwd = 'my-secret-pw', db = 'nova')
cursor = conn.cursor()

cursor.execute("select instance_uuid from instance_extra;")
instance_extra_t = cursor.fetchall()
cursor.execute("select uuid from instances;")
instances_t = cursor.fetchall()

todelete_list = []

for extra in instance_extra_t:
    if extra not in instances_t:
        todelete_list.append(extra)

for uuid in todelete_list:
    cmd = "delete from instance_extra where instance_uuid='%s';" % uuid
    result = cursor.execute(cmd)
    conn.commit()

[Saverio Proto (zioproto)]

If you messed up the database during the upgrade from Kilo to Liberty following the workaround of bug 1511466 (comments #3 and #9) you can fix with this query:

    DELETE from instance_extra where instance_uuid not in (select uuid from instances);

Sorry the python script in comment #4 is way too complex ! you can do the same with a 1 line SQL query

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/458564
Committed: https://git.openstack.org/cgit/openstack/nova/commit/?id=2cee5bb4d14a5ad6a037857bd3624833317cd931
Submitter: Jenkins
Branch: master

commit 2cee5bb4d14a5ad6a037857bd3624833317cd931
Author: Dan Smith <email address hidden>
Date: Thu Apr 20 09:12:45 2017 -0700

    Warn the user about orphaned extra records during keypair migration

    Operators who have manually deleted Instance records with FK constraints
    disabled may have orphaned InstanceExtra records which will prevent the
    keypair migration from running. Normally, this violation of the data
    model would be something that earns no sympathy. However, that solution
    was (incorrectly) offered up as a workaround in bug 1511466 and multiple
    deployments have broken their data as a result. Since the experience
    is a unhelpful error message and a blocked migration, this patch attempts
    to at least highlight the problem, even though it is on the operator to
    actually fix the problem.

    Change-Id: I0c8bf2c495a98c412eb93e19f832948a779bca11
    Related-Bug: #1684861

[OpenStack Infra (hudson-openstack)]

Related fix proposed to branch: stable/ocata
Review: https://review.openstack.org/463364

[OpenStack Infra (hudson-openstack)]

Related fix proposed to branch: stable/newton
Review: https://review.openstack.org/463366

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/463364
Committed: https://git.openstack.org/cgit/openstack/nova/commit/?id=593e7081148b2b15f5923a8d7c609ae71dcbdbb6
Submitter: Jenkins
Branch: stable/ocata

commit 593e7081148b2b15f5923a8d7c609ae71dcbdbb6
Author: Dan Smith <email address hidden>
Date: Thu Apr 20 09:12:45 2017 -0700

    Warn the user about orphaned extra records during keypair migration

    Operators who have manually deleted Instance records with FK constraints
    disabled may have orphaned InstanceExtra records which will prevent the
    keypair migration from running. Normally, this violation of the data
    model would be something that earns no sympathy. However, that solution
    was (incorrectly) offered up as a workaround in bug 1511466 and multiple
    deployments have broken their data as a result. Since the experience
    is a unhelpful error message and a blocked migration, this patch attempts
    to at least highlight the problem, even though it is on the operator to
    actually fix the problem.

    Change-Id: I0c8bf2c495a98c412eb93e19f832948a779bca11
    Related-Bug: #1684861
    (cherry picked from commit 2cee5bb4d14a5ad6a037857bd3624833317cd931)

[Sean Dague (sdague)]

Found open reviews for this bug in gerrit, setting to In Progress.

review: https://review.openstack.org/463366 in branch: stable/newton

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/463366
Committed: https://git.openstack.org/cgit/openstack/nova/commit/?id=7b29a5cf9e19ef5885759259aab439473d01a6b5
Submitter: Jenkins
Branch: stable/newton

commit 7b29a5cf9e19ef5885759259aab439473d01a6b5
Author: Dan Smith <email address hidden>
Date: Thu Apr 20 09:12:45 2017 -0700

    Warn the user about orphaned extra records during keypair migration

    Operators who have manually deleted Instance records with FK constraints
    disabled may have orphaned InstanceExtra records which will prevent the
    keypair migration from running. Normally, this violation of the data
    model would be something that earns no sympathy. However, that solution
    was (incorrectly) offered up as a workaround in bug 1511466 and multiple
    deployments have broken their data as a result. Since the experience
    is a unhelpful error message and a blocked migration, this patch attempts
    to at least highlight the problem, even though it is on the operator to
    actually fix the problem.

    Change-Id: I0c8bf2c495a98c412eb93e19f832948a779bca11
    Related-Bug: #1684861
    (cherry picked from commit 2cee5bb4d14a5ad6a037857bd3624833317cd931)