migrate_to_ml2.py fails. cannot find table networks

[Atze de Vries]

during upgrade from havana to icehouse the database migration script (migrate_to_ml2.py) fails. I run it with the following options:
python -m neutron.db.migration.migrate_to_ml2 --tunnel-type gre --release icehouse openvswitch mysql://neutron:XXXXX@127.0.0.1/neutron

It returns the following trace:
Traceback (most recent call last):
  File "/usr/lib/python2.7/runpy.py", line 162, in _run_module_as_main
    "__main__", fname, loader, pkg_name)
  File "/usr/lib/python2.7/runpy.py", line 72, in _run_code
    exec code in run_globals
  File "/usr/lib/python2.7/dist-packages/neutron/db/migration/migrate_to_ml2.py", line 462, in <module>
    main()
  File "/usr/lib/python2.7/dist-packages/neutron/db/migration/migrate_to_ml2.py", line 458, in main
    args.vxlan_udp_port)
  File "/usr/lib/python2.7/dist-packages/neutron/db/migration/migrate_to_ml2.py", line 138, in __call__
    metadata.create_all(engine)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/schema.py", line 2848, in create_all
    tables=tables)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1479, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1122, in _run_visitor
    **kwargs).traverse_single(element)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/visitors.py", line 122, in traverse_single
    return meth(obj, **kw)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/ddl.py", line 56, in visit_metadata
    collection = [t for t in sql_util.sort_tables(tables)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/util.py", line 39, in sort_tables
    {'foreign_key': visit_foreign_key})
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/visitors.py", line 258, in traverse
    return traverse_using(iterate(obj, opts), obj, visitors)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/visitors.py", line 249, in traverse_using
    meth(target)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/util.py", line 30, in visit_foreign_key
    parent_table = fkey.column.table
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/util/langhelpers.py", line 612, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/schema.py", line 1474, in column
    tname)
sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'ml2_network_segments.network_id' could not find table 'networks' with which to generate a foreign key to target column 'id'

If i create this table from the mysql console with this:

CREATE TABLE ml2_network_segments ( id VARCHAR(36) NOT NULL, network_id VARCHAR(36) NOT NULL, network_type VARCHAR(32) NOT NULL , physical_network VARCHAR(64), segmentation_id INT);
ALTER TABLE ml2_network_segmentsADD FOREIGN KEY (network_id) REFERENCES networks (id) ON DELETE cascade;
ALTER TABLE ml2_network_segments ADD PRIMARY KEY (id);

No error is given.

OS: Ubuntu 12.04.04 Kernel 3.11

[Eugene Nikanorov (enikanorov)]

If networks table is not found then it seems that database is not in havana/icehouse state and probably it is empty at all.

Can you give more details about the initial state of the database?

[Atze de Vries (atze-devries)]

as far as i know everything is there. This is a overview of the tables

mysql> show tables;
+---------------------------+
| Tables_in_neutron |
+---------------------------+
| agents |
| alembic_version |
| allowedaddresspairs |
| dnsnameservers |
| externalnetworks |
| extradhcpopts |
| floatingips |
| ipallocationpools |
| ipallocations |
| ipavailabilityranges |
| networkdhcpagentbindings |
| networks |
| ovs_network_bindings |
| ovs_tunnel_allocations |
| ovs_tunnel_endpoints |
| ovs_vlan_allocations |
| portbindingports |
| ports |
| quotas |
| routerl3agentbindings |
| routerroutes |
| routers |
| securitygroupportbindings |
| securitygrouprules |
| securitygroups |
| servicedefinitions |
| servicetypes |
| subnetroutes |
| subnets |
+---------------------------+
29 rows in set (0.00 sec)

Here is a dump of the networks table

mysql> select * from networks;
+----------------------------------+--------------------------------------+---------------+--------+----------------+--------+
| tenant_id | id | name | status | admin_state_up | shared |
+----------------------------------+--------------------------------------+---------------+--------+----------------+--------+
| 46f59aa7ad8b43d6942e06256b802aed | a9bfb8b9-c99a-4fd0-8fee-3af481551336 | public | ACTIVE | 1 | 0 |
| 46f59aa7ad8b43d6942e06256b802aed | 8326b51e-f875-4e63-83d0-345e99d27188 | private_admin | ACTIVE | 1 | 0 |
+----------------------------------+--------------------------------------+---------------+--------+----------------+--------+
2 rows in set (0.00 sec)

[Atze de Vries (atze-devries)]

small update. I tried the same with no records in the database. Same story.

[Atze de Vries (atze-devries)]

mmm after some research i found the issue. My mysql tables are in MyISAM. They need to be in InnoDB to support foreign keys.

This issue can be converted in a suggestion to check if table is in MyISAM and give a nice error.

[Atze de Vries (atze-devries)]

i might be a little to eary on my research. the previous post seems to be working on a database with records. It might be a myisam to innodb conversion error.

i'll report more tomorrow.

[Atze de Vries (atze-devries)]

as long as there are values in the neutron.networks table the same error remains.

[Sean M. Collins (scollins)]

I am seeing a similar issue, however the tables are all InnoDB.

ubuntu@server:~$ python -m neutron.db.migration.migrate_to_ml2 openvswitch mysql://USER:PASSWORD@10.251.1.8/neutron
Traceback (most recent call last):
  File "/usr/lib/python2.7/runpy.py", line 162, in _run_module_as_main
    "__main__", fname, loader, pkg_name)
  File "/usr/lib/python2.7/runpy.py", line 72, in _run_code
    exec code in run_globals
  File "/usr/lib/python2.7/dist-packages/neutron/db/migration/migrate_to_ml2.py", line 462, in <module>
    main()
  File "/usr/lib/python2.7/dist-packages/neutron/db/migration/migrate_to_ml2.py", line 458, in main
    args.vxlan_udp_port)
  File "/usr/lib/python2.7/dist-packages/neutron/db/migration/migrate_to_ml2.py", line 138, in __call__
    metadata.create_all(engine)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/schema.py", line 2848, in create_all
    tables=tables)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1479, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1122, in _run_visitor
    **kwargs).traverse_single(element)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/visitors.py", line 122, in traverse_single
    return meth(obj, **kw)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/ddl.py", line 70, in visit_metadata
    self.traverse_single(table, create_ok=True)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/visitors.py", line 122, in traverse_single
    return meth(obj, **kw)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/ddl.py", line 89, in visit_table
    self.connection.execute(schema.CreateTable(table))
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 662, in execute
    params)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 720, in _execute_ddl
    compiled
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 874, in _execute_context
    context)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1024, in _handle_dbapi_exception
    exc_info
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 196, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 867, in _execute_context
    context)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 324, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 174, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
sqlalchemy.exc.OperationalError: (OperationalError) (1005, "Can't create table 'neutron.ml2_network_segments' (errno: 150)") '\nCREATE TABLE ml2_network_segments (\n\tid VARCHAR(36) NOT NULL, \n\tnetwork_id VARCHAR(36) NOT NULL, \n\tnetwork_type VARCHAR(32) NOT NULL, \n\tphysical_network VARCHAR(64), \n\tsegmentation_id INTEGER, \n\tPRIMARY KEY (id), \n\tFOREIGN KEY(net...

[Matt Kassawara (ionosphere80)]

Did you run neutron-db-manage before the conversion script? Also, can you provide your MySQL configuration?

[Sean M. Collins (scollins)]

@Matt Kassawara - I believe so, I checked with Anthony and also did the following SQL:

mysql> select * from alembic_version;
+-------------+
| version_num |
+-------------+
| icehouse |
+-------------+
1 row in set (0.00 sec)

[Matt Kassawara (ionosphere80)]

Sean,

What's your MySQL configuration?

Matt

[Sean M. Collins (scollins)]

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
max_connections = 1024
innodb_file_per_table = OFF
innodb_buffer_pool_size = 2G
skip-external-locking

bind-address = 0.0.0.0

key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 256K
thread_cache_size = 8
myisam-recover = BACKUP
query_cache_limit = 1M
query_cache_size = 16M
expire_logs_days = 10
max_binlog_size = 100M

max_connections = 1024

log_error = /var/log/mysql/error.log

[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
[isamchk]
key_buffer = 16M
!includedir /etc/mysql/conf.d/

[Sean M. Collins (scollins)]

Sridhar Basam from our team figured it out - It needs the charset. All the tables in the existing database are using latin1 - changing the CREATE TABLE statement to:

CREATE TABLE ml2_network_segments (
    id VARCHAR(36) NOT NULL,
    network_id VARCHAR(36) NOT NULL,
    network_type VARCHAR(32) NOT NULL,
    physical_network VARCHAR(64),
    segmentation_id INTEGER,
    PRIMARY KEY (id),
    FOREIGN KEY(network_id) REFERENCES networks (id) ON DELETE CASCADE
)
CHARACTER SET latin1;

Works.

[Matt Kassawara (ionosphere80)]

Interesting. During my upgrade tests, I found these MySQL configuration keys generally help with character set issues:

collation-server = utf8_general_ci
init-connect = 'SET NAMES utf8'
character-set-server = utf8

[Sean M. Collins (scollins)]

So do we need to add logic to the migration script to change the tables from latin1 to utf8, if they are currently set as latin1? Our havana lab is built out using the ubuntu distro packages, so this may be a common issue.

[Matt Kassawara (ionosphere80)]

If it helps, we needed to manually convert the glance tables to UTF-8 in the upgrade guide[1].

[1] http://docs.openstack.org/openstack-ops/content/upgrades_havana-icehouse-ubuntu.html

[Atze de Vries (atze-devries)]

I think i found the core of this issue, at least in my setup.

In our database there where no "foreign keys " set for the neutron tables. This led to the migrate_to_ml2.py error. After creating the foreign keys, all seems to work.

To fix the database you need to
1. upgrade you neutron database to icehouse
2. copy the neutron database
3. drop the neutron database
4 . create a empty neutorn database
5. run the neutron database upgrade command again (neutron-db-manage --config-file some file upgrade icehouse) (same commmand as in 1
6. copy table for table (only values in table) the data back from the copy database to the new neutron database
7. run migrate_to_ml2.py script

I'm not really sure why our neutron database had no foreign keys but but it could be two issues i think.

1. We used the stackforge puppet modules to deploy openstack havana. Maybe they had an issue
2. The version of neutron-db-manage we are using has an issue

I don't think the reason of the empty foreign keys is the myisam database engine because the assumption that foreign keys don't workin in myisam in wrong. Other databases (keystone, glance etc) do have foreign keys and are in myisam.

I can´t give puppetmodule or neutron-db-mange versions at the moment since i'm at home.

I would suggest that all the other reporters should check there neutron database on foreign keys (the foreign keys are in the informatioin schema database, google it!)

[Matt Kassawara (ionosphere80)]

I seem to recall that foreign keys cannot reference tables with different character sets and collation.

[Matt Kassawara (ionosphere80)]

I think neutron (and possibly other projects) should specify at least character set attributes (and database engine for MySQL) when creating tables to avoid relying on defaults and/or global database server configuration.

[Robert van Leeuwen (rovanleeuwen)]

Adding to the comment Matt made:
I would also recommend some way of enforcing the things that could actually break stuff:
A simple check script for e.g. mysql if everything is setup proper would already help quite a bit.
(test if innodb, right character set and constraints exists)

Also thanking Atze for the suggesting of doing a dump and import into a clean Neutron DB that worked also for me.
In our test setup I was missing the constraints, probably due to non-existing proper defaults of our test Mysql server.

Small tip for those wanting to do an dump/import:
You can do a "mysqldump --no-create-info neutron > dumpfile.sql" to dump the database without the table schema.
This dump can be imported into the freshly setup new neutron db.

[George Shuklin (george-shuklin)]

To those who affected by this bug: problem is utf-8/latin1 difference.

You should change mysql settings and restart it.

Under the [mysqld] section, set the following keys to enable InnoDB, UTF-8 character set, and UTF-8 collation by default:

[mysqld]
default-storage-engine = innodb
innodb_file_per_table
collation-server = utf8_general_ci
init-connect = 'SET NAMES utf8'
character-set-server = utf8

[George Shuklin (george-shuklin)]

oops, sorry, mysqld settings does not help. But it add DEFAULT CHARSET=utf8 to each table creation, it works. In manual mode.

[Cedric Brandily (cbrandily)]

This bug is > 365 days without activity. We are unsetting assignee and milestone and setting status to Incomplete in order to allow its expiry in 60 days.

If the bug is still valid, then update the bug status.

[Launchpad Janitor (janitor)]

[Expired for neutron because there has been no activity for 60 days.]