Inconsistent coding while upgrading

[hanchl]

I am trying to upgrade the platform from queens to rocky . To meet production needs, I cannot upgrade the platform in place. So I need to copy the queens version data to the rocky version environment before upgrading. After I import the queens version database to the rocky version, execute the following command：

"neutron-db-manage \
  --config-file /etc/neutron/neutron.conf \
  --config-file /etc/neutron/plugins/ml2/ml2_conf.ini \
  upgrade head"
Then I got an error:

oslo_db.exception.DBError: (pymysql.err.InternalError) (1005, u'Can\'t create table `neutron`.`portforwardings` (errno: 150 "Foreign key constraint is incorrectly formed")') [SQL: u'\nCREATE TABLE portforwardings (\n\tid VARCHAR(36) NOT NULL, \n\tfloatingip_id VARCHAR(36) NOT NULL, \n\texternal_port INTEGER NOT NULL, \n\tinternal_neutron_port_id VARCHAR(36) NOT NULL, \n\tprotocol VARCHAR(40) NOT NULL, \n\tsocket VARCHAR(36) NOT NULL, \n\tPRIMARY KEY (id), \n\tFOREIGN KEY(floatingip_id) REFERENCES floatingips (id) ON DELETE CASCADE, \n\tFOREIGN KEY(internal_neutron_port_id) REFERENCES ports (id) ON DELETE CASCADE, \n\tCONSTRAINT uniq_port_forwardings0floatingip_id0external_port UNIQUE (floatingip_id, external_port), \n\tCONSTRAINT uniq_port_forwardings0internal_neutron_port_id0socket UNIQUE (internal_neutron_port_id, socket)\n)ENGINE=InnoDB\n\n'] (Background on this error at: http://sqlalche.me/e/2j85)

Then I check the two tables: floatingips and ports, found that there was "DEFAULT CHARSET=utf8"of the two, so I entered the database to manually create table portforwardings and succeeded.

If there is a bug?

[Slawek Kaplonski (slaweq)]

I didn't saw such error anytime during "in place" upgrade of the Neutron DB. Are You 100% sure that this isn't any issue caused by copying Your DB from one server to the other one?

[Rodolfo Alonso (rodolfo-alonso-hernandez)]

Hello hanchl:

Some questions about this problem reported:
- What is the engine of table "ports" and "floatingips"? Should be the same as the new table, InnoDB.
- What DB are you using?
- How did you migrate the original DB to this server?

Regards.

[hanchl (hanchl)]

The queens one is mysql and the rocky one is mariadb.
migrate:
queens backup: mysqldump -uroot -p${root_password} $i > $file/$i.sql
rocky import: mysql -h$IP -p3306 -uroot -p${PASSWORD} $i < ${DBPATH}$i.sql

I may have found the problem:
the reason is that the encoding of the two versions of the database is different. I used the command：
mysql -h$IP -p3306 -uroot -p${PASSWORD} -e 'drop database neutron;'
mysql -h$IP -p3306 -uroot -p${PASSWORD} -e 'create database neutron DEFAULT CHARSET=utf8;'

to recreate the neutron database, and then executed the upgrade and it passed.