yahoo-eng-team team mailing list archive

Thread
Date
[Bug 1567899] Re: alembic migration failure with mysql 5.7

To: yahoo-eng-team@xxxxxxxxxxxxxxxxxxx
From: OpenStack Infra <1567899@xxxxxxxxxxxxxxxxxx>
Date: Tue, 12 Apr 2016 19:12:34 -0000
Reply-to: Bug 1567899 <1567899@xxxxxxxxxxxxxxxxxx>
Sender: bounces@xxxxxxxxxxxxx
Reviewed:  https://review.openstack.org/303376
Committed: https://git.openstack.org/cgit/openstack/neutron-vpnaas/commit/?id=3aeae7a3e5a351577002237663ed08ac908d5b45
Submitter: Jenkins
Branch:    master

commit 3aeae7a3e5a351577002237663ed08ac908d5b45
Author: James Page <james.page@xxxxxxxxxx>
Date:   Fri Apr 8 13:17:26 2016 +0100

    Ensure that PK values don't default to NULL
    
    MySQL 5.7 is stricter by default; ensure that the primary key
    of cisco_csr_identifier_map does not end up with NULL default
    otherwise the migration step will fail with:
    
     'All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead'
    
    Behavioural change introduced in 5.7.3:
    
     http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-3.html
    
    Change-Id: Iad2033bec39d223556c2605e3ba3f3dab44223a1
    Closes-Bug: #1567899


** Changed in: neutron
       Status: In Progress => Fix Released

-- 
You received this bug notification because you are a member of Yahoo!
Engineering Team, which is subscribed to neutron.
https://bugs.launchpad.net/bugs/1567899

Title:
  alembic migration failure with mysql 5.7

Status in neutron:
  Fix Released
Status in neutron-vpnaas package in Ubuntu:
  Fix Released

Bug description:
  the migration step 56893333aa52_fix_identifier_map_fk fails to work
  against mysql 5.7 - I validated against mysql-5.5 in trusty as well
  and its fine against this version of MySQL.

  Error: (1171, u'All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead')
  Command: [SQL: u'ALTER TABLE cisco_csr_identifier_map MODIFY ipsec_site_conn_id VARCHAR(36) NULL']

  Log trace

  2016-04-08 11:28:36 INFO shared-db-relation-changed INFO  [alembic.runtime.migration] Running upgrade kilo -> 56893333aa52, fix identifier map fk
  2016-04-08 11:28:36 INFO shared-db-relation-changed Traceback (most recent call last):
  2016-04-08 11:28:36 INFO shared-db-relation-changed   File "/usr/bin/neutron-db-manage", line 10, in <module>
  2016-04-08 11:28:36 INFO shared-db-relation-changed     sys.exit(main())
  2016-04-08 11:28:36 INFO shared-db-relation-changed   File "/usr/lib/python2.7/dist-packages/neutron/db/migration/cli.py", line 749, in main
  2016-04-08 11:28:36 INFO shared-db-relation-changed     return_val |= bool(CONF.command.func(config, CONF.command.name))
  2016-04-08 11:28:36 INFO shared-db-relation-changed   File "/usr/lib/python2.7/dist-packages/neutron/db/migration/cli.py", line 225, in do_upgrade
  2016-04-08 11:28:36 INFO shared-db-relation-changed     desc=branch, sql=CONF.command.sql)
  2016-04-08 11:28:36 INFO shared-db-relation-changed   File "/usr/lib/python2.7/dist-packages/neutron/db/migration/cli.py", line 127, in do_alembic_command
  2016-04-08 11:28:36 INFO shared-db-relation-changed     getattr(alembic_command, cmd)(config, *args, **kwargs)
  2016-04-08 11:28:36 INFO shared-db-relation-changed   File "/usr/lib/python2.7/dist-packages/alembic/command.py", line 174, in upgrade
  2016-04-08 11:28:36 INFO shared-db-relation-changed     script.run_env()
  2016-04-08 11:28:36 INFO shared-db-relation-changed   File "/usr/lib/python2.7/dist-packages/alembic/script/base.py", line 397, in run_env
  2016-04-08 11:28:36 INFO shared-db-relation-changed     util.load_python_file(self.dir, 'env.py')
  2016-04-08 11:28:36 INFO shared-db-relation-changed   File "/usr/lib/python2.7/dist-packages/alembic/util/pyfiles.py", line 81, in load_python_file
  2016-04-08 11:28:36 INFO shared-db-relation-changed     module = load_module_py(module_id, path)
  2016-04-08 11:28:36 INFO shared-db-relation-changed   File "/usr/lib/python2.7/dist-packages/alembic/util/compat.py", line 79, in load_module_py
  2016-04-08 11:28:36 INFO shared-db-relation-changed     mod = imp.load_source(module_id, path, fp)
  2016-04-08 11:28:36 INFO shared-db-relation-changed   File "/usr/lib/python2.7/dist-packages/neutron_vpnaas/db/migration/alembic_migrations/env.py", line 87, in <module>
  2016-04-08 11:28:36 INFO shared-db-relation-changed     run_migrations_online()
  2016-04-08 11:28:36 INFO shared-db-relation-changed   File "/usr/lib/python2.7/dist-packages/neutron_vpnaas/db/migration/alembic_migrations/env.py", line 78, in run_migrations_online
  2016-04-08 11:28:36 INFO shared-db-relation-changed     context.run_migrations()
  2016-04-08 11:28:36 INFO shared-db-relation-changed   File "<string>", line 8, in run_migrations
  2016-04-08 11:28:36 INFO shared-db-relation-changed   File "/usr/lib/python2.7/dist-packages/alembic/runtime/environment.py", line 797, in run_migrations
  2016-04-08 11:28:36 INFO shared-db-relation-changed     self.get_context().run_migrations(**kw)
  2016-04-08 11:28:36 INFO shared-db-relation-changed   File "/usr/lib/python2.7/dist-packages/alembic/runtime/migration.py", line 312, in run_migrations
  2016-04-08 11:28:36 INFO shared-db-relation-changed     step.migration_fn(**kw)
  2016-04-08 11:28:36 INFO shared-db-relation-changed   File "/usr/lib/python2.7/dist-packages/neutron_vpnaas/db/migration/alembic_migrations/versions/liberty/contract/56893333aa52_fix_identifier_map_fk.py", line 57, in upgrade
  2016-04-08 11:28:36 INFO shared-db-relation-changed     existing_nullable=True)
  2016-04-08 11:28:36 INFO shared-db-relation-changed   File "<string>", line 8, in alter_column
  2016-04-08 11:28:36 INFO shared-db-relation-changed   File "<string>", line 3, in alter_column
  2016-04-08 11:28:36 INFO shared-db-relation-changed   File "/usr/lib/python2.7/dist-packages/alembic/operations/ops.py", line 1414, in alter_column
  2016-04-08 11:28:36 INFO shared-db-relation-changed     return operations.invoke(alt)
  2016-04-08 11:28:36 INFO shared-db-relation-changed   File "/usr/lib/python2.7/dist-packages/alembic/operations/base.py", line 318, in invoke
  2016-04-08 11:28:36 INFO shared-db-relation-changed     return fn(self, operation)
  2016-04-08 11:28:36 INFO shared-db-relation-changed   File "/usr/lib/python2.7/dist-packages/alembic/operations/toimpl.py", line 53, in alter_column
  2016-04-08 11:28:36 INFO shared-db-relation-changed     **operation.kw
  2016-04-08 11:28:36 INFO shared-db-relation-changed   File "/usr/lib/python2.7/dist-packages/alembic/ddl/mysql.py", line 66, in alter_column
  2016-04-08 11:28:36 INFO shared-db-relation-changed     else existing_autoincrement
  2016-04-08 11:28:36 INFO shared-db-relation-changed   File "/usr/lib/python2.7/dist-packages/alembic/ddl/impl.py", line 118, in _exec
  2016-04-08 11:28:36 INFO shared-db-relation-changed     return conn.execute(construct, *multiparams, **params)
  2016-04-08 11:28:36 INFO shared-db-relation-changed   File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 914, in execute
  2016-04-08 11:28:36 INFO shared-db-relation-changed     return meth(self, multiparams, params)
  2016-04-08 11:28:36 INFO shared-db-relation-changed   File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection
  2016-04-08 11:28:36 INFO shared-db-relation-changed     return connection._execute_ddl(self, multiparams, params)
  2016-04-08 11:28:36 INFO shared-db-relation-changed   File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 968, in _execute_ddl
  2016-04-08 11:28:36 INFO shared-db-relation-changed     compiled
  2016-04-08 11:28:36 INFO shared-db-relation-changed   File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
  2016-04-08 11:28:36 INFO shared-db-relation-changed     context)
  2016-04-08 11:28:36 INFO shared-db-relation-changed   File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1337, in _handle_dbapi_exception
  2016-04-08 11:28:36 INFO shared-db-relation-changed     util.raise_from_cause(newraise, exc_info)
  2016-04-08 11:28:36 INFO shared-db-relation-changed   File "/usr/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 200, in raise_from_cause
  2016-04-08 11:28:36 INFO shared-db-relation-changed     reraise(type(exception), exception, tb=exc_tb)
  2016-04-08 11:28:36 INFO shared-db-relation-changed   File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
  2016-04-08 11:28:36 INFO shared-db-relation-changed     context)
  2016-04-08 11:28:36 INFO shared-db-relation-changed   File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 450, in do_execute
  2016-04-08 11:28:36 INFO shared-db-relation-changed     cursor.execute(statement, parameters)
  2016-04-08 11:28:36 INFO shared-db-relation-changed   File "/usr/lib/python2.7/dist-packages/pymysql/cursors.py", line 158, in execute
  2016-04-08 11:28:36 INFO shared-db-relation-changed     result = self._query(query)
  2016-04-08 11:28:36 INFO shared-db-relation-changed   File "/usr/lib/python2.7/dist-packages/pymysql/cursors.py", line 308, in _query
  2016-04-08 11:28:36 INFO shared-db-relation-changed     conn.query(q)
  2016-04-08 11:28:36 INFO shared-db-relation-changed   File "/usr/lib/python2.7/dist-packages/pymysql/connections.py", line 820, in query
  2016-04-08 11:28:36 INFO shared-db-relation-changed     self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  2016-04-08 11:28:36 INFO shared-db-relation-changed   File "/usr/lib/python2.7/dist-packages/pymysql/connections.py", line 1002, in _read_query_result
  2016-04-08 11:28:36 INFO shared-db-relation-changed     result.read()
  2016-04-08 11:28:36 INFO shared-db-relation-changed   File "/usr/lib/python2.7/dist-packages/pymysql/connections.py", line 1285, in read
  2016-04-08 11:28:36 INFO shared-db-relation-changed     first_packet = self.connection._read_packet()
  2016-04-08 11:28:36 INFO shared-db-relation-changed   File "/usr/lib/python2.7/dist-packages/pymysql/connections.py", line 966, in _read_packet
  2016-04-08 11:28:36 INFO shared-db-relation-changed     packet.check_error()
  2016-04-08 11:28:36 INFO shared-db-relation-changed   File "/usr/lib/python2.7/dist-packages/pymysql/connections.py", line 394, in check_error
  2016-04-08 11:28:36 INFO shared-db-relation-changed     err.raise_mysql_exception(self._data)
  2016-04-08 11:28:36 INFO shared-db-relation-changed   File "/usr/lib/python2.7/dist-packages/pymysql/err.py", line 120, in raise_mysql_exception
  2016-04-08 11:28:36 INFO shared-db-relation-changed     _check_mysql_exception(errinfo)
  2016-04-08 11:28:36 INFO shared-db-relation-changed   File "/usr/lib/python2.7/dist-packages/pymysql/err.py", line 112, in _check_mysql_exception
  2016-04-08 11:28:36 INFO shared-db-relation-changed     raise errorclass(errno, errorvalue)
  2016-04-08 11:28:36 INFO shared-db-relation-changed oslo_db.exception.DBError: (pymysql.err.DataError) (1171, u'All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead') [SQL: u'ALTER TABLE cisco_csr_identifier_map MODIFY ipsec_site_conn_id VARCHAR(36) NULL']

  ProblemType: Bug
  DistroRelease: Ubuntu 16.04
  Package: python-neutron-vpnaas 2:8.0.0-0ubuntu1
  ProcVersionSignature: User Name 4.4.0-17.33-generic 4.4.6
  Uname: Linux 4.4.0-17-generic x86_64
  ApportVersion: 2.20.1-0ubuntu1
  Architecture: amd64
  Date: Fri Apr  8 11:39:55 2016
  Ec2AMI: ami-00000101
  Ec2AMIManifest: FIXME
  Ec2AvailabilityZone: nova
  Ec2InstanceType: m1.small.osci
  Ec2Kernel: unavailable
  Ec2Ramdisk: unavailable
  PackageArchitecture: all
  SourcePackage: neutron-vpnaas
  UpgradeStatus: No upgrade log present (probably fresh install)
  mtime.conffile..etc.neutron.neutron.vpnaas.conf: 2016-04-08T11:34:40.736000

To manage notifications about this bug go to:
https://bugs.launchpad.net/neutron/+bug/1567899/+subscriptions
References

[Bug 1567899] [NEW] alembic migration failure with mysql 5.7
From: James Page, 2016-04-08