Currently we have databases that have both utf8_general_ci and utf8_unicode_ci. In our development environment all db's were utf8_general_ci. We do not have server level setting for collation. We have two regions in Production, one which we recently upgraded, and an another we which we are planning to do this weekend. When we do a dry run for the DB to test the upgrade, we get an error. In the past, setting the database or table in question with the collation utf8_unicode_ci fixed the issue. For this openstack region, the neutron db's ran into issues when converting to collation utf8_general_ci. I converted the tables and over 517 columns that were specifically set to utf8_unicode_ci. The current state for the test is that we can convert most collations from utf8_general_ci to utf8_unicode_ci, except 3 due to foreign keys. For some reason even with the mismatch the dry run for db upgrade goes through without the alter statements, something which failed in dev and the first prod region. + openstack-db --service neutron --update INFO [alembic.migration] Context impl MySQLImpl. INFO [alembic.migration] Will assume non-transactional DDL. INFO [alembic.migration] Context impl MySQLImpl. INFO [alembic.migration] Will assume non-transactional DDL. INFO [alembic.migration] Context impl MySQLImpl. INFO [alembic.migration] Will assume non-transactional DDL. INFO [alembic.migration] Running upgrade juno -> 44621190bc02, add_uniqueconstraint_ipavailability_ranges INFO [alembic.migration] Running upgrade 44621190bc02 -> 1f71e54a85e7, ml2_network_segments models change for multi-segment network. INFO [alembic.migration] Running upgrade 1f71e54a85e7 -> 408cfbf6923c, remove ryu plugin INFO [alembic.migration] Running upgrade 408cfbf6923c -> 28c0ffb8ebbd, remove mlnx plugin INFO [alembic.migration] Running upgrade 28c0ffb8ebbd -> 57086602ca0a, scrap_nsx_adv_svcs_models INFO [alembic.migration] Running upgrade 57086602ca0a -> 38495dc99731, ml2_tunnel_endpoints_table INFO [alembic.migration] Running upgrade 38495dc99731 -> 4dbe243cd84d, nsxv Traceback (most recent call last): File "/bin/neutron-db-manage", line 10, in <module> sys.exit(main()) File "/usr/lib/python2.7/site-packages/neutron/db/migration/cli.py", line 238, in main CONF.command.func(config, CONF.command.name) File "/usr/lib/python2.7/site-packages/neutron/db/migration/cli.py", line 106, in do_upgrade do_alembic_command(config, cmd, revision, sql=CONF.command.sql) File "/usr/lib/python2.7/site-packages/neutron/db/migration/cli.py", line 72, in do_alembic_command getattr(alembic_command, cmd)(config, *args, **kwargs) File "/usr/lib/python2.7/site-packages/alembic/command.py", line 165, in upgrade script.run_env() File "/usr/lib/python2.7/site-packages/alembic/script.py", line 382, in run_env util.load_python_file(self.dir, 'env.py') File "/usr/lib/python2.7/site-packages/alembic/util.py", line 242, in load_python_file module = load_module_py(module_id, path) File "/usr/lib/python2.7/site-packages/alembic/compat.py", line 79, in load_module_py mod = imp.load_source(module_id, path, fp) File "/usr/lib/python2.7/site-packages/neutron/db/migration/alembic_migrations/env.py", line 109, in <module> run_migrations_online() File "/usr/lib/python2.7/site-packages/neutron/db/migration/alembic_migrations/env.py", line 100, in run_migrations_online context.run_migrations() File "<string>", line 7, in run_migrations File "/usr/lib/python2.7/site-packages/alembic/environment.py", line 742, in run_migrations self.get_context().run_migrations(**kw) File "/usr/lib/python2.7/site-packages/alembic/migration.py", line 305, in run_migrations step.migration_fn(**kw) File "/usr/lib/python2.7/site-packages/neutron/db/migration/alembic_migrations/versions/4dbe243cd84d_nsxv.py", line 65, in upgrade sa.PrimaryKeyConstraint('network_purpose')) File "<string>", line 7, in create_table File "/usr/lib/python2.7/site-packages/alembic/operations.py", line 936, in create_table self.impl.create_table(table) File "/usr/lib/python2.7/site-packages/alembic/ddl/impl.py", line 181, in create_table self._exec(schema.CreateTable(table)) File "/usr/lib/python2.7/site-packages/alembic/ddl/impl.py", line 105, in _exec #!/bin/bash return conn.execute(construct, *multiparams, **params) File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 729, in execute return meth(self, multiparams, params) File "/usr/lib64/python2.7/site-packages/sqlalchemy/sql/ddl.py", line 69, in _execute_on_connection return connection._execute_ddl(self, multiparams, params) File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 783, in _execute_ddl compiled File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 958, in _execute_context context) File "/usr/lib/python2.7/site-packages/oslo_db/sqlalchemy/compat/handle_error.py", line 261, in _handle_dbapi_exception e, statement, parameters, cursor, context) File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1155, in _handle_dbapi_exception util.raise_from_cause(newraise, exc_info) File "/usr/lib64/python2.7/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause reraise(type(exception), exception, tb=exc_tb) File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 951, in _execute_context context) File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/default.py", line 436, 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 sqlalchemy.exc.OperationalError: (OperationalError) (1005, "Can't create table 'neutron.nsxv_internal_networks' (errno: 150)") "\nCREATE TABLE nsxv_internal_networks (\n\tnetwork_purpose ENUM('inter_edge_net') NOT NULL, \n\tnetwork_id VARCHAR(36), \n\tPRIMARY KEY (network_purpose), \n\tFOREIGN KEY(network_id) REFERENCES networks (id) ON DELETE CASCADE\n)ENGINE=InnoDB\n\n" () Error updating the database. Please see /var/log/neutron/ logs for details.
> Currently we have databases that have both utf8_general_ci and utf8_unicode_ci. In our development environment all db's were utf8_general_ci. We do not have server level setting for collation. is this collation being set in the CREATE DATABASE statement ? What tool is being used to emit CREATE DATABASE ? Confirm no collation is set in /etc/my.cnf.d/mariadb-server.cnf ?
if this is a retroactive fix, then they should choose one collation or the other, then set it for all existing tables as well as the database: ALTER DATABASE neutron CHARACTER SET utf8_general_ci then, for every table in neutron (output of SHOW TABLES command): ALTER TABLE <tablename> CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; then, as neutron migrations create subsequent tables, they will also have the utf8_general_ci collation and will match for FK definitions.
in Director, charset and collation are handled by the individual puppet-<project> modules, e.g. puppet-nova/manifests/db/mysql.pp, puppet-neutron/manifests/db/mysql.pp, etc. and they are all on utf8/utf8_general_ci. So this issue involves only upgrades from very old versions of openstack, most likely from the previous installer.
We believe this may have been related to upgrading an older, pre-Director deployment. Any more recent deployments should be using the correct collation method, and certainly current versions deploy using the correct method.
The needinfo request[s] on this closed bug have been removed as they have been unresolved for 1000 days