Neutron bootstrap failing on Ubuntu bionic with Cannot change column 'network_id

[Mark Goddard]

Neutron bootstrap is currently failing on Ubuntu bionic (kolla-ansible-ubuntu-source jobs) with the following error:

INFO [alembic.runtime.migration] Running upgrade 63fd95af7dcd -> c613d0b82681
Traceback (most recent call last):
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context
    cursor, statement, parameters, context
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute
    cursor.execute(statement, parameters)
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/pymysql/connections.py", line 517, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/pymysql/connections.py", line 732, in _read_query_result
    result.read()
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/pymysql/connections.py", line 1075, in read
    first_packet = self.connection._read_packet()
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/pymysql/connections.py", line 684, in _read_packet
    packet.check_error()
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/pymysql/protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.InternalError: (1832, "Cannot change column 'network_id': used in a foreign key constraint 'subnets_ibfk_1'")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/var/lib/kolla/venv/bin/neutron-db-manage", line 10, in <module>
    sys.exit(main())
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/neutron/db/migration/cli.py", line 657, in main
    return_val |= bool(CONF.command.func(config, CONF.command.name))
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/neutron/db/migration/cli.py", line 181, in do_upgrade
    desc=branch, sql=CONF.command.sql)
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/neutron/db/migration/cli.py", line 82, in do_alembic_command
    getattr(alembic_command, cmd)(config, *args, **kwargs)
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/alembic/command.py", line 276, in upgrade
    script.run_env()
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/alembic/script/base.py", line 475, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/alembic/util/pyfiles.py", line 90, in load_python_file
    module = load_module_py(module_id, path)
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/alembic/util/compat.py", line 177, in load_module_py
    spec.loader.exec_module(module)
  File "<frozen importlib._bootstrap_external>", line 678, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/neutron/db/migration/alembic_migrations/env.py", line 120, in <module>
    run_migrations_online()
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/neutron/db/migration/alembic_migrations/env.py", line 114, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/alembic/runtime/environment.py", line 839, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/alembic/runtime/migration.py", line 362, in run_migrations
    step.migration_fn(**kw)
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/neutron/db/migration/alembic_migrations/versions/train/expand/c613d0b82681_subnet_force_network_id.py", line 35, in upgrade
    existing_type=sa.String(36))
  File "<string>", line 8, in alter_column
  File "<string>", line 3, in alter_column
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/alembic/operations/ops.py", line 1775, in alter_column
    return operations.invoke(alt)
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/alembic/operations/base.py", line 345, in invoke
    return fn(self, operation)
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/alembic/operations/toimpl.py", line 56, in alter_column
    **operation.kw
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/alembic/ddl/mysql.py", line 96, in alter_column
    else existing_comment,
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/alembic/ddl/impl.py", line 134, in _exec
    return conn.execute(construct, *multiparams, **params)
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 988, in execute
    return meth(self, multiparams, params)
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/sqlalchemy/sql/ddl.py", line 72, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1050, in _execute_ddl
    compiled,
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
    e, statement, parameters, cursor, context
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1464, in _handle_dbapi_exception
    util.raise_from_cause(newraise, exc_info)
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 152, in reraise
    raise value.with_traceback(tb)
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context
    cursor, statement, parameters, context
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute
    cursor.execute(statement, parameters)
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/pymysql/connections.py", line 517, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/pymysql/connections.py", line 732, in _read_query_result
    result.read()
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/pymysql/connections.py", line 1075, in read
    first_packet = self.connection._read_packet()
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/pymysql/connections.py", line 684, in _read_packet
    packet.check_error()
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/pymysql/protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "/var/lib/kolla/venv/lib/python3.6/site-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
oslo_db.exception.DBError: (pymysql.err.InternalError) (1832, "Cannot change column 'network_id': used in a foreign key constraint 'subnets_ibfk_1'")
[SQL: ALTER TABLE subnets MODIFY network_id VARCHAR(36) NOT NULL]
(Background on this error at: http://sqlalche.me/e/2j85)"

CentOS jobs are not affected. One major difference between these is that Ubuntu uses Mariadb 10.1, whereas CentOS uses MariaDB 10.3.

Example: https://storage.bhs1.cloud.ovh.net/v1/AUTH_dcaab5e32b234d56b626f72581e3644c/logs_58/675658/8/check/kolla-ansible-ubuntu-source/712c02b/primary/logs/ansible/deploy

[Mark Goddard (mgoddard)]

We believe the change in neutron that triggered this is https://review.opendev.org/#/c/677221.

[OpenStack Infra (hudson-openstack)]

Related fix proposed to branch: master
Review: https://review.opendev.org/679213

[Radosław Piliszek (yoctozepto)]

This is mariadb as delivered by Ubuntu Bionic (18.04).

[Mark Goddard (mgoddard)]

Discussed avenues to explore:

* Use upstream MariaDB packages to install a newer version.
* Use a newer version of alembic, to see if there is a workaround for this issue.
* Apply a workaround in neutron - modify the migration to temporarily remove the foreign key constraint while the column is altered.

[Mark Goddard (mgoddard)]

docker run --rm -it kolla/ubuntu-source-neutron-server:master /var/lib/kolla/venv/bin/pip freeze
alembic==1.0.11
amqp==2.5.1
ansible-runner==1.3.4
anyjson==0.3.3
aodhclient==1.3.0
appdirs==1.4.3
asn1crypto==0.24.0
Babel==2.7.0
bcrypt==3.1.7
beautifulsoup4==4.8.0
cachetools==3.1.1
certifi==2019.6.16
cffi==1.12.3
chardet==3.0.4
cliff==2.15.0
cmd2==0.8.9
contextlib2==0.5.5
cryptography==2.7
debtcollector==1.21.0
decorator==4.4.0
dnspython==1.15.0
docutils==0.15.2
dogpile.cache==0.7.1
elasticsearch==2.4.1
etcd3==0.10.0
etcd3gw==0.2.4
eventlet==0.25.1
extras==1.0.0
fasteners==0.14.1
fixtures==3.0.0
funcsigs==1.0.2
future==0.17.1
futurist==1.8.1
gnocchiclient==7.0.5
greenlet==0.4.15
grpcio==1.23.0
httplib2==0.13.1
idna==2.8
importlib-metadata==0.19
iso8601==0.1.12
Jinja2==2.10.1
jmespath==0.9.4
jsonpatch==1.24
jsonpath-rw==1.4.0
jsonpath-rw-ext==1.2.2
jsonpointer==2.0
jsonschema==2.6.0
kazoo==2.6.1
keystoneauth1==3.17.0
keystonemiddleware==7.0.0
kombu==4.6.4
linecache2==1.0.0
lockfile==0.12.2
logutils==0.3.5
lxml==4.3.4
Mako==1.1.0
MarkupSafe==1.1.1
mock==3.0.5
monotonic==1.5
more-itertools==7.2.0
msgpack==0.6.1
munch==2.3.2
murano-pkg-check==0.3.0
mysqlclient==1.4.4
netaddr==0.7.19
netifaces==0.10.9
netmiko==2.4.1
networking-ansible==1.1.0.dev75
networking-baremetal==1.4.0.dev12
networking-generic-switch==2.0.1.dev5
networking-hyperv==5.0.1
networking-l2gw==14.0.0
networking-sfc==8.1.0.dev16
networkx==2.3
neutron==14.1.0.dev693
neutron-dynamic-routing==14.1.0.dev22
neutron-fwaas==14.1.0.dev41
neutron-lib==1.29.0
neutron-vpnaas==14.1.0.dev27
octavia-lib==1.3.1
openstacksdk==0.35.0
os-client-config==1.32.0
os-ken==0.4.0
os-service-types==1.7.0
os-traits==0.16.0
os-vif==1.16.0
os-win==4.3.0
os-xenapi==0.3.4
osc-lib==1.14.0
oslo.cache==1.37.0
oslo.concurrency==3.29.1
oslo.config==6.11.0
oslo.context==2.22.1
oslo.db==5.0.1
oslo.i18n==3.23.1
oslo.log==3.44.0
oslo.messaging==10.1.0
oslo.middleware==3.38.1
oslo.policy==2.3.0
oslo.privsep==1.33.1
oslo.reports==1.29.2
oslo.rootwrap==5.16.0
oslo.serialization==2.29.1
oslo.service==1.40.0
oslo.upgradecheck==0.3.1
oslo.utils==3.41.0
oslo.versionedobjects==1.36.0
oslo.vmware==2.34.0
osprofiler==2.8.1
ovs==2.11.0
ovsdbapp==0.16.0
paramiko==2.6.0
Paste==3.1.0
PasteDeploy==2.0.1
pbr==5.4.2
pecan==1.3.3
pexpect==4.7.0
pika==1.1.0
ply==3.11
prettytable==0.7.2
protobuf==3.9.1
psutil==5.6.3
psycopg2==2.8.3
ptyprocess==0.6.0
pycadf==2.9.0
pycparser==2.19
pydot==1.4.1
pyinotify==0.9.6
pymemcache==2.2.2
pymongo==3.9.0
PyMySQL==0.9.3
PyNaCl==1.3.0
pyngus==2.3.0
pyOpenSSL==19.0.0
pyparsing==2.4.2
pyperclip==1.7.0
pyroute2==0.5.6
pyserial==3.4
python-barbicanclient==4.8.1
python-cinderclient==4.3.0
python-cloudkittyclient==3.0.0
python-congressclient==1.12.0
python-consul==1.1.0
python-daemon==2.2.3
python-dateutil==2.8.0
python-designateclient==2.12.0
python-editor==1.0.4
python-glanceclient==2.16.0
python-heatclient==1.18.0
python-ironicclient==2.8.0
python-keystoneclient==3.20.0
python-magnumclient==2.15.0
python-manilaclient==1.28.0
python-memcached==1.59
python-mimeparse==1.6.0
python-mistralclient==3.9.0
python-muranoclient==1.3.0
python-neutronclient==6.13.0
python-novaclient==14.2.0
python-openstackclient==3.19.0
py...

[OpenStack Infra (hudson-openstack)]

Related fix proposed to branch: master
Review: https://review.opendev.org/679228

[OpenStack Infra (hudson-openstack)]

Related fix proposed to branch: master
Review: https://review.opendev.org/679277

[Radosław Piliszek (yoctozepto)]

I confirmed it's related to MariaDB 10.1: https://review.opendev.org/679282 using latest upstream

It is either alembic producing broken sql code for that particular release or release being "broken" by itself. Either way that's all bare Ubuntu gets, even with UCA enabled (they don't update mariadb currently).

[Radosław Piliszek (yoctozepto)]

k-a fixed by upgrading mariadb in kolla ubuntu images

[OpenStack Infra (hudson-openstack)]

Change abandoned by Radosław Piliszek (<email address hidden>) on branch: master
Review: https://review.opendev.org/679277
Reason: non-lts needs changing more logic

[OpenStack Infra (hudson-openstack)]

Change abandoned by Mark Goddard (<email address hidden>) on branch: master
Review: https://review.opendev.org/679213
Reason: Updating mariadb instead

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.opendev.org/679209
Committed: https://git.openstack.org/cgit/openstack/kolla/commit/?id=761d58717731dd72ce50f6dbb6cd8e2b5e608060
Submitter: Zuul
Branch: master

commit 761d58717731dd72ce50f6dbb6cd8e2b5e608060
Author: Michal Nasiadka <email address hidden>
Date: Thu Aug 29 09:06:46 2019 +0000

    Move to MariaDB upstream repo and new version (10.3) on Ubuntu

    MariaDB in Ubuntu packages is backlevel (10.1).
    Devstack gates are tested on MySQL 5.7 which is not compatible
    with MariaDB 10.1.
    Neutron recently introduced a db migration
    that breaks on MariaDB 10.1, but works on MariaDB 10.3 [0].

    Bumping up version to 10.3 on Ubuntu to match CentOS (RDO) version.

    [0]: https://review.opendev.org/#/c/677221/

    Closes-Bug: #1841907
    Change-Id: I16e4c40208912691e3015bfe2065ea0ce2358edb

[OpenStack Infra (hudson-openstack)]

Change abandoned by Mark Goddard (<email address hidden>) on branch: master
Review: https://review.opendev.org/679228

[Slawek Kaplonski (slaweq)]

I set it as Critical for Neutron as it seems that Mariadb 10.1 is default in Ubuntu Bionic so we have broken upgrade on this platform.

[OpenStack Infra (hudson-openstack)]

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

[OpenStack Infra (hudson-openstack)]

Related fix proposed to branch: master
Review: https://review.opendev.org/702416

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.opendev.org/702416
Committed: https://git.openstack.org/cgit/openstack/neutron/commit/?id=1986865bd5587d303f79f54ff525b38b0bf98bb6
Submitter: Zuul
Branch: master

commit 1986865bd5587d303f79f54ff525b38b0bf98bb6
Author: Rodolfo Alonso Hernandez <email address hidden>
Date: Tue Jan 14 13:24:35 2020 +0000

    Add MariaDB 10.3 repository for Ubuntu Bionic

    In order to execute the periodic job "neutron-tempest-mariadb-full",
    using Ubuntu Bionic and MariaDB, a newer version is needed than the
    default package version distributed (10.1).

    Devstack gates are tested with MySQL 5.7. Current Neutron DB schema
    is not working with the reported version 10.1.

    Change-Id: Ie2b5f85a9c0fc26676d44074424cf5a699303805
    Related-Bug: #1855912
    Related-Bug: #1841907

[Saibal Dey (saibaldey)]

After upgrading the MariaDB from 10.1 to 10.3 resolved this issue.

To get the Installed version of MariaDB
1. dpkg -l | grep -i mariadb
2. mysql -V

To Upgrade the MariaDB to latest version
systemctl stop mysqld
apt-get install software-properties-common
apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
add-apt-repository 'deb [arch=amd64,arm64,ppc64el] http://www.ftp.saix.net/DB/mariadb/repo/10.3/ubuntu bionic main'
apt update
apt install mariadb-server

Check the MariaDB version after upgrade
1. dpkg -l | grep -i mariadb
2. systemctl start mysqld
   mysql -V

& the issue is resolved.

[wang (yunhua)]

stay tuned

[Michal Nasiadka (mnasiadka)]

Adding Stein, noticed it's happening also on kolla-ansible Stein CI jobs:
https://zuul.opendev.org/t/openstack/build/add9590158784b1e830927e569f586eb/log/primary/logs/ansible/deploy#38988

[Michal Nasiadka (mnasiadka)]

Seems to only be hitting kolla-ansible-ubuntu-binary jobs - according to http://reqorts.qa.ubuntu.com/reports/ubuntu-server/cloud-archive/stein_versions.html - neutron 14.2.0 version that has the fix - is in staging, not yet in updates.

[Chris MacNaughton (chris.macnaughton)]

Marking the cloud-archive task for this wontfix as it seems to specifically be a bad interaction between Neutron and MariaDB, and the cloud-archive has never actually carried MariaDB.