Migration to Newton fails in 97c25b0d2353_add_name_desc

[Brent Tang]

The 97c25b0d2353_add_name_desc.py DB migration script is currently
getting the primary key returned from the standardattributes table
insert to use for the networksegments table insert. However, the
inserted_primary_key attribute returns a list of all of the primary
key values, so this will blow up (at least on MySQL, since it is
trying to insert a list for an integer column.

  File "/usr/lib/python2.7/site-packages/alembic/runtime/migration.py", line 312, in run_migrations
    step.migration_fn(**kw)
  File "/usr/lib/python2.7/site-packages/neutron/db/migration/alembic_migrations/versions/newton/contract/97c25b0d2353_add_name_desc.py", line 74, in upgrade
    update_existing_records()
  File "/usr/lib/python2.7/site-packages/neutron/db/migration/alembic_migrations/versions/newton/contract/97c25b0d2353_add_name_desc.py", line 61, in update_existing_records
    TBL_MODEL.c.id == row[0])
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/orm/session.py", line 1046, in execute
    bind, close_with_result=True).execute(clause, params or {})
  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/elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
    compiled_sql, distilled_params
  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 1337, in _handle_dbapi_exception
    util.raise_from_cause(newraise, exc_info)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/util/compat.py", line 200, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  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)
  File "/usr/lib64/python2.7/site-packages/MySQLdb/cursors.py", line 174, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib64/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
oslo_db.exception.DBError: (_mysql_exceptions.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') WHERE networksegments.id = '5d84887d-ed87-4f5b-8661-6be327d3c541'' at line 1") [SQL: u'UPDATE networksegments SET standard_attr_id=%s WHERE networksegments.id = %s'] [parameters: ([10L], '5d84887d-ed87-4f5b-8661-6be327d3c541')]

[John Schwarz (jschwarz)]

@Brent, We are not able to reproduce this yet. Can you share the steps you're running in order to get this to fail like it did (i.e. a reproducible)? Which OS are you running?

[Brent Tang (btang-d)]

I am trying to get this trimmed down to a smaller scenario to reproduce, since right now it came out of the testing of our full product upgrade (that uses OpenStack)... This is running on RHEL 7.2, using MariaDB 5.5.44, SQLAlchemy 1.0.11.

However, I think this should happen any time that migration script runs when you already have a row in the networksegments table populated, since at least according to the SQLAlchemy spec, the inserted_primary_key attribute should return a list of the primary keys, so unless it is either returning None in other cases (which wouldn't be good, since it needs a value), or SQLAlchemy returns a list in some cases and single value in others (which is possible but doesn't seem likely), or some DB's or versions are more tolerant of passing in a list for an integer field.

I was going to just submit a changeset of the simple change of doing inserted_primary_key[0], but was trying to verify in our full product upgrade first, and if this isn't reproducible in other environments with networksegments already populated, it makes me wonder which of the cases above is occurring and whether we have different behaviors from different environments.

[Carl Baldwin (carl-baldwin)]

Over the course of reviewing this patch, I ran this migration at least once on a system that had existing networks. Since it was an ML2 based system, it would've had networksegments to migrate. But, now I'm second guessing myself and I'm not sure that I tested the version that merged. :( Now, I'm wondering what happened. I can spend a little bit of time on this. I'm also wondering why grenade wouldn't have caught this problem.

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.openstack.org/367690

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/367690
Committed: https://git.openstack.org/cgit/openstack/neutron/commit/?id=7f1caa1310872bbbc35a3277c9db82a3376ef3ca
Submitter: Jenkins
Branch: master

commit 7f1caa1310872bbbc35a3277c9db82a3376ef3ca
Author: Henry Gessau <email address hidden>
Date: Thu Sep 8 19:02:37 2016 -0400

    Add missing index on inserted_primary_key in migration

    MySQL doesn't seem to care, but Postgres and MariaDB barf on it.

    Also add a test with data for this migration.

    Change-Id: Ie464a65b291e5974a23e9b45de1654709092b4ad
    Closes-Bug: #1621490

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/neutron 9.0.0.0rc1 release candidate.