alembic migration failure with mysql 5.7

[James Page]

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

[Robie Basak (racb)]

I'm told that putting NOT NULL in the schema should do it, and there should be no consequences because it was enforced like that previously anyway.

[James Page (james-page)]

Reference:

http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-3.html

"Columns in a PRIMARY KEY must be NOT NULL, but if declared explicitly as NULL produced no error. Now an error occurs. For example, a statement such as CREATE TABLE t (i INT NULL PRIMARY KEY) is rejected. The same occurs for similar ALTER TABLE statements. (Bug #13995622, Bug #66987, Bug #15967545, Bug #16545198)"

[OpenStack Infra (hudson-openstack)]

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

[Robie Basak (racb)]

Also: http://dev.mysql.com/doc/refman/5.6/en/create-table.html

"A PRIMARY KEY is a unique index where all key columns must be defined as NOT
NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so
implicitly (and silently). A table can have only one PRIMARY KEY. The name of
a PRIMARY KEY is always PRIMARY, which thus cannot be used as the name for
any other kind of index."

[Launchpad Janitor (janitor)]

This bug was fixed in the package neutron-vpnaas - 2:8.0.0-0ubuntu2

---------------
neutron-vpnaas (2:8.0.0-0ubuntu2) xenial; urgency=medium

  * d/p/fix-nullable-pk.patch: Fix issue with migration step that attempts
    to set the default for a primary key to NULL; MySQL 5.7 errors on this
    as its much stricter as of 5.7.3 (LP: #1567899).

 -- James Page <email address hidden> Fri, 08 Apr 2016 13:24:24 +0100

[OpenStack Infra (hudson-openstack)]

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 <email address hidden>
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

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: stable/mitaka
Review: https://review.openstack.org/305135

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: stable/liberty
Review: https://review.openstack.org/305141

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/305135
Committed: https://git.openstack.org/cgit/openstack/neutron-vpnaas/commit/?id=58a5251515b7d58ee55f9604ad882f6a678c9bf4
Submitter: Jenkins
Branch: stable/mitaka

commit 58a5251515b7d58ee55f9604ad882f6a678c9bf4
Author: James Page <email address hidden>
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
    (cherry picked from commit 3aeae7a3e5a351577002237663ed08ac908d5b45)

[Thierry Carrez (ttx)]

This issue was fixed in the openstack/neutron-vpnaas 8.1.1 release.

[Doug Hellmann (doug-hellmann)]

This issue was fixed in the openstack/neutron-vpnaas 9.0.0.0b1 development milestone.

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/305141
Committed: https://git.openstack.org/cgit/openstack/neutron-vpnaas/commit/?id=be6602854cb6399b44d0768a7ec73055e9e6dd8b
Submitter: Jenkins
Branch: stable/liberty

commit be6602854cb6399b44d0768a7ec73055e9e6dd8b
Author: James Page <email address hidden>
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
    (cherry picked from commit 3aeae7a3e5a351577002237663ed08ac908d5b45)

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/neutron-vpnaas 7.2.0 release.