[ops][neutron] Neutron-db-manage upgrade from Antelope to Bobcat fails with "foreign key constraint 'porthints_ibfk_1' are incompatible" error
Dear all, we are running an OpenStack deployment Yoga on AlmaLinux 9.5 and we are attempting the upgrade Openstack up to Caracal release, one release at time (Zed--Antelope--Bobcat--Caracal) The procedure we are following is to shut down all openstack services, to remove the "old" openstack release and to install the new one, upgrading all service databases. This procedure works well for Zed and Antelope, but during the installation of the Bobcat release, the Neutron database upgrade fails when creating the new "porthints" table. The command we are using is su -s /bin/sh -c "neutron-db-manage --config-file /etc/neutron/neutron.conf --config-file /etc/neutron/plugins/ml2/ml2_conf.ini upgrade head" neutron and the error message we receive is ... File "/usr/lib/python3.9/site-packages/pymysql/err.py", line 107, in raise_mysql_exception raise errorclass(errno, errval) sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (3780, "Referencing column 'port_id' and referenced column 'id' in foreign key constraint 'porthints_ibfk_1' are incompatible.") [SQL: CREATE TABLE porthints ( port_id VARCHAR(36) NOT NULL, hints VARCHAR(4095) NOT NULL, PRIMARY KEY (port_id), FOREIGN KEY(port_id) REFERENCES ports (id) ON DELETE CASCADE )ENGINE=InnoDB ... Our "ports" table uses the collation utf8mb3_general_ci mysql> SHOW FULL COLUMNS FROM ports; +------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+ | project_id | varchar(255) | utf8mb3_general_ci | YES | MUL | NULL | | select,insert,update,references | | | id | varchar(36) | utf8mb3_general_ci | NO | PRI | NULL | | select,insert,update,references | | | name | varchar(255) | utf8mb3_general_ci | YES | | NULL | | select,insert,update,references | | | network_id | varchar(36) | utf8mb3_general_ci | NO | MUL | NULL | | select,insert,update,references | | | mac_address | varchar(32) | utf8mb3_general_ci | NO | | NULL | | select,insert,update,references | | | admin_state_up | tinyint(1) | NULL | NO | | NULL | | select,insert,update,references | | | status | varchar(16) | utf8mb3_general_ci | NO | | NULL | | select,insert,update,references | | | device_id | varchar(255) | utf8mb3_general_ci | NO | MUL | NULL | | select,insert,update,references | | | device_owner | varchar(255) | utf8mb3_general_ci | NO | | NULL | | select,insert,update,references | | | standard_attr_id | bigint | NULL | NO | UNI | NULL | | select,insert,update,references | | | ip_allocation | varchar(16) | utf8mb3_general_ci | YES | | NULL | | select,insert,update,references | | +------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+ in Bobcat the collation required for the new "porthints" table is latin1_swedish_ci MariaDB [neutron]> show create table porthints; +-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | porthints | CREATE TABLE `porthints` ( `port_id` varchar(36) NOT NULL, `hints` varchar(4095) NOT NULL, PRIMARY KEY (`port_id`), CONSTRAINT `porthints_ibfk_1` FOREIGN KEY (`port_id`) REFERENCES `ports` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci | So, we have tried to create the porthints table in mysql, forcing the collation to utf8mb3_general_ci mysql> CREATE TABLE porthints ( port_id VARCHAR(36) NOT NULL, hints VARCHAR(4095) NOT NULL, PRIMARY KEY (port_id), FOREIGN KEY(port_id) REFERENCES ports (id) ON DELETE CASCADE )ENGINE=InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE utf8mb3_general_ci; and commenting out the creation of the new table in the neutron code /usr/lib/python3.9/site-packages/neutron/db/migration/alembic_migrations/versions/2023.2/expand/6f1145bff34c_port_hints.py. In this way, the upgrade works, but we encounter the same kind of problem during the upgrade to Caracal, specifically with the creation of the new table porthardwareoffloadtype. Have you already encountered this issue during the Neutron upgrade? Do you have any suggestions on how to upgrade the neutron DB, without manually creating new tables? Thanks a lot, cheers, Federica -- Federica Fanzago INFN Sezione di Padova Via Marzolo, 8 35131 Padova - Italy Tel: +39 049.967.7367 --
Dear all, I find the same problem during the upgrade of neutron db, from Bobcat to Caracal, with the creation of the new "porthardwareoffloadtype" table. The error is " Referencing column 'port_id' and referenced column 'id' in foreign key constraint 'porthardwareoffloadtype_ibfk_1' are incompatible". I tried adding in the neutron.conf file the values /[database]/ /connection = mysql+pymysql://neutron:PWD@xxx/neutron?charset=utf8// //engine = "InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE utf8"// //connection_parameters = charset=utf8/ but they don't resolve the upgrade problem. Is it compulsory to have the ports.id columns in latin1? Thanks, cheers Federica On 1/20/25 6:55 PM, federica fanzago wrote:
Dear all,
we are running an OpenStack deployment Yoga on AlmaLinux 9.5 and we are attempting the upgrade Openstack up to Caracal release, one release at time (Zed--Antelope--Bobcat--Caracal)
The procedure we are following is to shut down all openstack services, to remove the "old" openstack release and to install the new one, upgrading all service databases.
This procedure works well for Zed and Antelope, but during the installation of the Bobcat release, the Neutron database upgrade fails when creating the new "porthints" table.
The command we are using is
su -s /bin/sh -c "neutron-db-manage --config-file /etc/neutron/neutron.conf --config-file /etc/neutron/plugins/ml2/ml2_conf.ini upgrade head" neutron
and the error message we receive is
...
File "/usr/lib/python3.9/site-packages/pymysql/err.py", line 107, in raise_mysql_exception raise errorclass(errno, errval) sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (3780, "Referencing column 'port_id' and referenced column 'id' in foreign key constraint 'porthints_ibfk_1' are incompatible.") [SQL: CREATE TABLE porthints ( port_id VARCHAR(36) NOT NULL, hints VARCHAR(4095) NOT NULL, PRIMARY KEY (port_id), FOREIGN KEY(port_id) REFERENCES ports (id) ON DELETE CASCADE )ENGINE=InnoDB ...
Our "ports" table uses the collation utf8mb3_general_ci
mysql> SHOW FULL COLUMNS FROM ports; +------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| project_id | varchar(255) | utf8mb3_general_ci | YES | MUL | NULL | | select,insert,update,references | | | id | varchar(36) | utf8mb3_general_ci | NO | PRI | NULL | | select,insert,update,references | | | name | varchar(255) | utf8mb3_general_ci | YES | | NULL | | select,insert,update,references | | | network_id | varchar(36) | utf8mb3_general_ci | NO | MUL | NULL | | select,insert,update,references | | | mac_address | varchar(32) | utf8mb3_general_ci | NO | | NULL | | select,insert,update,references | | | admin_state_up | tinyint(1) | NULL | NO | | NULL | | select,insert,update,references | | | status | varchar(16) | utf8mb3_general_ci | NO | | NULL | | select,insert,update,references | | | device_id | varchar(255) | utf8mb3_general_ci | NO | MUL | NULL | | select,insert,update,references | | | device_owner | varchar(255) | utf8mb3_general_ci | NO | | NULL | | select,insert,update,references | | | standard_attr_id | bigint | NULL | NO | UNI | NULL | | select,insert,update,references | | | ip_allocation | varchar(16) | utf8mb3_general_ci | YES | | NULL | | select,insert,update,references | | +------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
in Bobcat the collation required for the new "porthints" table is latin1_swedish_ci
MariaDB [neutron]> show create table porthints; +-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table | +-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| porthints | CREATE TABLE `porthints` ( `port_id` varchar(36) NOT NULL, `hints` varchar(4095) NOT NULL, PRIMARY KEY (`port_id`), CONSTRAINT `porthints_ibfk_1` FOREIGN KEY (`port_id`) REFERENCES `ports` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
So, we have tried to create the porthints table in mysql, forcing the collation to utf8mb3_general_ci
mysql> CREATE TABLE porthints ( port_id VARCHAR(36) NOT NULL, hints VARCHAR(4095) NOT NULL, PRIMARY KEY (port_id), FOREIGN KEY(port_id) REFERENCES ports (id) ON DELETE CASCADE )ENGINE=InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE utf8mb3_general_ci; and commenting out the creation of the new table in the neutron code /usr/lib/python3.9/site-packages/neutron/db/migration/alembic_migrations/versions/2023.2/expand/6f1145bff34c_port_hints.py.
In this way, the upgrade works, but we encounter the same kind of problem during the upgrade to Caracal, specifically with the creation of the new table porthardwareoffloadtype.
Have you already encountered this issue during the Neutron upgrade?
Do you have any suggestions on how to upgrade the neutron DB, without manually creating new tables?
Thanks a lot,
cheers,
Federica
-- Federica Fanzago INFN Sezione di Padova Via Marzolo, 8 35131 Padova - Italy Tel: +39 049.967.7367 --
Hello Federica: Neutron does not set the table collation during the alembic migrations. According to [1], prior to MariaDB 11.6.0, the default character set is latin1 and the default collation is latin1_swedish_ci. I would recommend manually setting the default charset to utf8 and the table collation to utf8mb3_general_ci. From [2]: $ SET character_set_server = 'utf8'; $ SET collation_server = 'utf8mb3_general_ci'; Regards. [1]https://mariadb.com/kb/en/character-set-and-collation-overview/ [2]https://mariadb.com/kb/en/setting-character-sets-and-collations/ On Fri, Jan 24, 2025 at 5:56 PM federica fanzago < federica.fanzago@pd.infn.it> wrote:
Dear all, I find the same problem during the upgrade of neutron db, from Bobcat to Caracal, with the creation of the new "porthardwareoffloadtype" table. The error is " Referencing column 'port_id' and referenced column 'id' in foreign key constraint 'porthardwareoffloadtype_ibfk_1' are incompatible". I tried adding in the neutron.conf file the values
*[database]* *connection = mysql+pymysql://neutron:PWD@xxx/neutron?charset=utf8* *engine = "InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE utf8"* *connection_parameters = charset=utf8*
but they don't resolve the upgrade problem.
Is it compulsory to have the ports.id columns in latin1?
Thanks, cheers Federica
On 1/20/25 6:55 PM, federica fanzago wrote:
Dear all,
we are running an OpenStack deployment Yoga on AlmaLinux 9.5 and we are attempting the upgrade Openstack up to Caracal release, one release at time (Zed--Antelope--Bobcat--Caracal)
The procedure we are following is to shut down all openstack services, to remove the "old" openstack release and to install the new one, upgrading all service databases.
This procedure works well for Zed and Antelope, but during the installation of the Bobcat release, the Neutron database upgrade fails when creating the new "porthints" table.
The command we are using is
su -s /bin/sh -c "neutron-db-manage --config-file /etc/neutron/neutron.conf --config-file /etc/neutron/plugins/ml2/ml2_conf.ini upgrade head" neutron
and the error message we receive is
...
File "/usr/lib/python3.9/site-packages/pymysql/err.py", line 107, in raise_mysql_exception raise errorclass(errno, errval) sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (3780, "Referencing column 'port_id' and referenced column 'id' in foreign key constraint 'porthints_ibfk_1' are incompatible.") [SQL: CREATE TABLE porthints ( port_id VARCHAR(36) NOT NULL, hints VARCHAR(4095) NOT NULL, PRIMARY KEY (port_id), FOREIGN KEY(port_id) REFERENCES ports (id) ON DELETE CASCADE )ENGINE=InnoDB ...
Our "ports" table uses the collation utf8mb3_general_ci
mysql> SHOW FULL COLUMNS FROM ports; +------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| project_id | varchar(255) | utf8mb3_general_ci | YES | MUL | NULL | | select,insert,update,references | | | id | varchar(36) | utf8mb3_general_ci | NO | PRI | NULL | | select,insert,update,references | | | name | varchar(255) | utf8mb3_general_ci | YES | | NULL | | select,insert,update,references | | | network_id | varchar(36) | utf8mb3_general_ci | NO | MUL | NULL | | select,insert,update,references | | | mac_address | varchar(32) | utf8mb3_general_ci | NO | | NULL | | select,insert,update,references | | | admin_state_up | tinyint(1) | NULL | NO | | NULL | | select,insert,update,references | | | status | varchar(16) | utf8mb3_general_ci | NO | | NULL | | select,insert,update,references | | | device_id | varchar(255) | utf8mb3_general_ci | NO | MUL | NULL | | select,insert,update,references | | | device_owner | varchar(255) | utf8mb3_general_ci | NO | | NULL | | select,insert,update,references | | | standard_attr_id | bigint | NULL | NO | UNI | NULL | | select,insert,update,references | | | ip_allocation | varchar(16) | utf8mb3_general_ci | YES | | NULL | | select,insert,update,references | | +------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
in Bobcat the collation required for the new "porthints" table is latin1_swedish_ci
MariaDB [neutron]> show create table porthints; +-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table | +-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| porthints | CREATE TABLE `porthints` ( `port_id` varchar(36) NOT NULL, `hints` varchar(4095) NOT NULL, PRIMARY KEY (`port_id`), CONSTRAINT `porthints_ibfk_1` FOREIGN KEY (`port_id`) REFERENCES `ports` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
So, we have tried to create the porthints table in mysql, forcing the collation to utf8mb3_general_ci
mysql> CREATE TABLE porthints ( port_id VARCHAR(36) NOT NULL, hints VARCHAR(4095) NOT NULL, PRIMARY KEY (port_id), FOREIGN KEY(port_id) REFERENCES ports (id) ON DELETE CASCADE )ENGINE=InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE utf8mb3_general_ci; and commenting out the creation of the new table in the neutron code /usr/lib/python3.9/site-packages/neutron/db/migration/alembic_migrations/versions/2023.2/expand/6f1145bff34c_port_hints.py.
In this way, the upgrade works, but we encounter the same kind of problem during the upgrade to Caracal, specifically with the creation of the new table porthardwareoffloadtype.
Have you already encountered this issue during the Neutron upgrade?
Do you have any suggestions on how to upgrade the neutron DB, without manually creating new tables?
Thanks a lot,
cheers,
Federica
-- Federica Fanzago INFN Sezione di Padova Via Marzolo, 8 35131 Padova - Italy
Tel: +39 049.967.7367 --
Hi Rodolfo, thanks for you answer. Cheers, Federica On 1/27/25 7:22 AM, Rodolfo Alonso Hernandez wrote:
Hello Federica:
Neutron does not set the table collation during the alembic migrations. According to [1], prior to MariaDB 11.6.0, the default character set is latin1 and the default collation is latin1_swedish_ci. I would recommend manually setting the default charset to utf8 and the table collation to utf8mb3_general_ci. From [2]: $ SET character_set_server = 'utf8'; $ SET collation_server = 'utf8mb3_general_ci';
Regards.
[1]https://mariadb.com/kb/en/character-set-and-collation-overview/ [2]https://mariadb.com/kb/en/setting-character-sets-and-collations/
On Fri, Jan 24, 2025 at 5:56 PM federica fanzago <federica.fanzago@pd.infn.it <mailto:federica.fanzago@pd.infn.it>> wrote:
Dear all, I find the same problem during the upgrade of neutron db, from Bobcat to Caracal, with the creation of the new "porthardwareoffloadtype" table. The error is " Referencing column 'port_id' and referenced column 'id' in foreign key constraint 'porthardwareoffloadtype_ibfk_1' are incompatible". I tried adding in the neutron.conf file the values
/[database]/ /connection = mysql+pymysql://neutron:PWD@xxx/neutron?charset=utf8// //engine = "InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE utf8"// //connection_parameters = charset=utf8/
but they don't resolve the upgrade problem.
Is it compulsory to have the ports.id <http://ports.id> columns in latin1?
Thanks, cheers Federica
On 1/20/25 6:55 PM, federica fanzago wrote:
Dear all,
we are running an OpenStack deployment Yoga on AlmaLinux 9.5 and we are attempting the upgrade Openstack up to Caracal release, one release at time (Zed--Antelope--Bobcat--Caracal)
The procedure we are following is to shut down all openstack services, to remove the "old" openstack release and to install the new one, upgrading all service databases.
This procedure works well for Zed and Antelope, but during the installation of the Bobcat release, the Neutron database upgrade fails when creating the new "porthints" table.
The command we are using is
su -s /bin/sh -c "neutron-db-manage --config-file /etc/neutron/neutron.conf --config-file /etc/neutron/plugins/ml2/ml2_conf.ini upgrade head" neutron
and the error message we receive is
...
File "/usr/lib/python3.9/site-packages/pymysql/err.py", line 107, in raise_mysql_exception raise errorclass(errno, errval) sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (3780, "Referencing column 'port_id' and referenced column 'id' in foreign key constraint 'porthints_ibfk_1' are incompatible.") [SQL: CREATE TABLE porthints ( port_id VARCHAR(36) NOT NULL, hints VARCHAR(4095) NOT NULL, PRIMARY KEY (port_id), FOREIGN KEY(port_id) REFERENCES ports (id) ON DELETE CASCADE )ENGINE=InnoDB ...
Our "ports" table uses the collation utf8mb3_general_ci
mysql> SHOW FULL COLUMNS FROM ports; +------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| project_id | varchar(255) | utf8mb3_general_ci | YES | MUL | NULL | | select,insert,update,references | | | id | varchar(36) | utf8mb3_general_ci | NO | PRI | NULL | | select,insert,update,references | | | name | varchar(255) | utf8mb3_general_ci | YES | | NULL | | select,insert,update,references | | | network_id | varchar(36) | utf8mb3_general_ci | NO | MUL | NULL | | select,insert,update,references | | | mac_address | varchar(32) | utf8mb3_general_ci | NO | | NULL | | select,insert,update,references | | | admin_state_up | tinyint(1) | NULL | NO | | NULL | | select,insert,update,references | | | status | varchar(16) | utf8mb3_general_ci | NO | | NULL | | select,insert,update,references | | | device_id | varchar(255) | utf8mb3_general_ci | NO | MUL | NULL | | select,insert,update,references | | | device_owner | varchar(255) | utf8mb3_general_ci | NO | | NULL | | select,insert,update,references | | | standard_attr_id | bigint | NULL | NO | UNI | NULL | | select,insert,update,references | | | ip_allocation | varchar(16) | utf8mb3_general_ci | YES | | NULL | | select,insert,update,references | | +------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
in Bobcat the collation required for the new "porthints" table is latin1_swedish_ci
MariaDB [neutron]> show create table porthints; +-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table | +-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| porthints | CREATE TABLE `porthints` ( `port_id` varchar(36) NOT NULL, `hints` varchar(4095) NOT NULL, PRIMARY KEY (`port_id`), CONSTRAINT `porthints_ibfk_1` FOREIGN KEY (`port_id`) REFERENCES `ports` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
So, we have tried to create the porthints table in mysql, forcing the collation to utf8mb3_general_ci
mysql> CREATE TABLE porthints ( port_id VARCHAR(36) NOT NULL, hints VARCHAR(4095) NOT NULL, PRIMARY KEY (port_id), FOREIGN KEY(port_id) REFERENCES ports (id) ON DELETE CASCADE )ENGINE=InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE utf8mb3_general_ci; and commenting out the creation of the new table in the neutron code /usr/lib/python3.9/site-packages/neutron/db/migration/alembic_migrations/versions/2023.2/expand/6f1145bff34c_port_hints.py.
In this way, the upgrade works, but we encounter the same kind of problem during the upgrade to Caracal, specifically with the creation of the new table porthardwareoffloadtype.
Have you already encountered this issue during the Neutron upgrade?
Do you have any suggestions on how to upgrade the neutron DB, without manually creating new tables?
Thanks a lot,
cheers,
Federica
-- Federica Fanzago INFN Sezione di Padova Via Marzolo, 8 35131 Padova - Italy
Tel: +39 049.967.7367 --
-- Federica Fanzago INFN Sezione di Padova Via Marzolo, 8 35131 Padova - Italy Tel: +39 049.967.7367 --
Dear all, we are updating our production cloud from openstack Yoga to openstack Caracal, and now we find the problem with the update of neutron db. The update from Antilope to Bobcat: # su -s /bin/sh -c "neutron-db-manage --config-file /etc/neutron/neutron.conf --config-file /etc/neutron/plugins/ml2/ml2_conf.ini upgrade head" neutron gets the error File "/usr/lib/python3.9/site-packages/pymysql/connections.py", line 676, in _read_packet packet.raise_for_error() File "/usr/lib/python3.9/site-packages/pymysql/protocol.py", line 223, in raise_for_error err.raise_mysql_exception(self._data) File "/usr/lib/python3.9/site-packages/pymysql/err.py", line 107, in raise_mysql_exception raise errorclass(errno, errval) sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (3780, "Referencing column 'port_id' and referenced column 'id' in foreign key constraint 'porthints_ibfk_1' are incompatible.") [SQL: CREATE TABLE porthints ( port_id VARCHAR(36) NOT NULL, hints VARCHAR(4095) NOT NULL, PRIMARY KEY (port_id), FOREIGN KEY(port_id) REFERENCES ports (id) ON DELETE CASCADE )ENGINE=InnoDB where ports table has mysql> SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'neutron_prod' AND TABLE_NAME = 'ports'; +------------------+--------------------+--------------------+ | COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME | +------------------+--------------------+--------------------+ | project_id | utf8mb3 | utf8mb3_general_ci | | id | utf8mb3 | utf8mb3_general_ci | | name | utf8mb3 | utf8mb3_general_ci | | network_id | utf8mb3 | utf8mb3_general_ci | | mac_address | utf8mb3 | utf8mb3_general_ci | | admin_state_up | NULL | NULL | | status | utf8mb3 | utf8mb3_general_ci | | device_id | utf8mb3 | utf8mb3_general_ci | | device_owner | utf8mb3 | utf8mb3_general_ci | | standard_attr_id | NULL | NULL | | ip_allocation | utf8mb3 | utf8mb3_general_ci | +------------------+--------------------+--------------------+ 11 rows in set (0.00 sec) In neutron db we have: mysql> show create database neutron_prod; +--------------+----------------------------------------------------------------------------------------------------------------------------------------+ | Database | Create Database | +--------------+----------------------------------------------------------------------------------------------------------------------------------------+ | neutron_prod | CREATE DATABASE `neutron_prod` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ | +--------------+----------------------------------------------------------------------------------------------------------------------------------------+ In the /etc/my.cfn of mysql [mysqld] server-id=1 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid collation-server = utf8_general_ci character-set-server = utf8 Changing collation-server value with collation_server = 'utf8mb3_general_ci'; the error is the same. Do you have suggestions about to fix the problem? Thanks a lot, cheers Federica mysql> SHOW FULL COLUMNS FROM ports; +------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+ | project_id | varchar(255) | utf8mb3_general_ci | YES | MUL | NULL | | select,insert,update,references | | | id | varchar(36) | utf8mb3_general_ci | NO | PRI | NULL | | select,insert,update,references | | | name | varchar(255) | utf8mb3_general_ci | YES | | NULL | | select,insert,update,references | | | network_id | varchar(36) | utf8mb3_general_ci | NO | MUL | NULL | | select,insert,update,references | | | mac_address | varchar(32) | utf8mb3_general_ci | NO | | NULL | | select,insert,update,references | | | admin_state_up | tinyint(1) | NULL | NO | | NULL | | select,insert,update,references | | | status | varchar(16) | utf8mb3_general_ci | NO | | NULL | | select,insert,update,references | | | device_id | varchar(255) | utf8mb3_general_ci | NO | MUL | NULL | | select,insert,update,references | | | device_owner | varchar(255) | utf8mb3_general_ci | NO | | NULL | | select,insert,update,references | | | standard_attr_id | bigint | NULL | NO | UNI | NULL | | select,insert,update,references | | | ip_allocation | varchar(16) | utf8mb3_general_ci | YES | | NULL | | select,insert,update,references | | +------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+ 11 rows in set (0.01 sec) On 1/28/25 2:11 PM, federica fanzago wrote:
Hi Rodolfo, thanks for you answer. Cheers, Federica
On 1/27/25 7:22 AM, Rodolfo Alonso Hernandez wrote:
Hello Federica:
Neutron does not set the table collation during the alembic migrations. According to [1], prior to MariaDB 11.6.0, the default character set is latin1 and the default collation is latin1_swedish_ci. I would recommend manually setting the default charset to utf8 and the table collation to utf8mb3_general_ci. From [2]: $ SET character_set_server = 'utf8'; $ SET collation_server = 'utf8mb3_general_ci';
Regards.
[1]https://mariadb.com/kb/en/character-set-and-collation-overview/ [2]https://mariadb.com/kb/en/setting-character-sets-and-collations/
On Fri, Jan 24, 2025 at 5:56 PM federica fanzago <federica.fanzago@pd.infn.it <mailto:federica.fanzago@pd.infn.it>> wrote:
Dear all, I find the same problem during the upgrade of neutron db, from Bobcat to Caracal, with the creation of the new "porthardwareoffloadtype" table. The error is " Referencing column 'port_id' and referenced column 'id' in foreign key constraint 'porthardwareoffloadtype_ibfk_1' are incompatible". I tried adding in the neutron.conf file the values
/[database]/ /connection = mysql+pymysql://neutron:PWD@xxx/neutron?charset=utf8// //engine = "InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE utf8"// //connection_parameters = charset=utf8/
but they don't resolve the upgrade problem.
Is it compulsory to have the ports.id <http://ports.id> columns in latin1?
Thanks, cheers Federica
On 1/20/25 6:55 PM, federica fanzago wrote:
Dear all,
we are running an OpenStack deployment Yoga on AlmaLinux 9.5 and we are attempting the upgrade Openstack up to Caracal release, one release at time (Zed--Antelope--Bobcat--Caracal)
The procedure we are following is to shut down all openstack services, to remove the "old" openstack release and to install the new one, upgrading all service databases.
This procedure works well for Zed and Antelope, but during the installation of the Bobcat release, the Neutron database upgrade fails when creating the new "porthints" table.
The command we are using is
su -s /bin/sh -c "neutron-db-manage --config-file /etc/neutron/neutron.conf --config-file /etc/neutron/plugins/ml2/ml2_conf.ini upgrade head" neutron
and the error message we receive is
...
File "/usr/lib/python3.9/site-packages/pymysql/err.py", line 107, in raise_mysql_exception raise errorclass(errno, errval) sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (3780, "Referencing column 'port_id' and referenced column 'id' in foreign key constraint 'porthints_ibfk_1' are incompatible.") [SQL: CREATE TABLE porthints ( port_id VARCHAR(36) NOT NULL, hints VARCHAR(4095) NOT NULL, PRIMARY KEY (port_id), FOREIGN KEY(port_id) REFERENCES ports (id) ON DELETE CASCADE )ENGINE=InnoDB ...
Our "ports" table uses the collation utf8mb3_general_ci
mysql> SHOW FULL COLUMNS FROM ports; +------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| project_id | varchar(255) | utf8mb3_general_ci | YES | MUL | NULL | | select,insert,update,references | | | id | varchar(36) | utf8mb3_general_ci | NO | PRI | NULL | | select,insert,update,references | | | name | varchar(255) | utf8mb3_general_ci | YES | | NULL | | select,insert,update,references | | | network_id | varchar(36) | utf8mb3_general_ci | NO | MUL | NULL | | select,insert,update,references | | | mac_address | varchar(32) | utf8mb3_general_ci | NO | | NULL | | select,insert,update,references | | | admin_state_up | tinyint(1) | NULL | NO | | NULL | | select,insert,update,references | | | status | varchar(16) | utf8mb3_general_ci | NO | | NULL | | select,insert,update,references | | | device_id | varchar(255) | utf8mb3_general_ci | NO | MUL | NULL | | select,insert,update,references | | | device_owner | varchar(255) | utf8mb3_general_ci | NO | | NULL | | select,insert,update,references | | | standard_attr_id | bigint | NULL | NO | UNI | NULL | | select,insert,update,references | | | ip_allocation | varchar(16) | utf8mb3_general_ci | YES | | NULL | | select,insert,update,references | | +------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
in Bobcat the collation required for the new "porthints" table is latin1_swedish_ci
MariaDB [neutron]> show create table porthints; +-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table | +-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| porthints | CREATE TABLE `porthints` ( `port_id` varchar(36) NOT NULL, `hints` varchar(4095) NOT NULL, PRIMARY KEY (`port_id`), CONSTRAINT `porthints_ibfk_1` FOREIGN KEY (`port_id`) REFERENCES `ports` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
So, we have tried to create the porthints table in mysql, forcing the collation to utf8mb3_general_ci
mysql> CREATE TABLE porthints ( port_id VARCHAR(36) NOT NULL, hints VARCHAR(4095) NOT NULL, PRIMARY KEY (port_id), FOREIGN KEY(port_id) REFERENCES ports (id) ON DELETE CASCADE )ENGINE=InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE utf8mb3_general_ci; and commenting out the creation of the new table in the neutron code /usr/lib/python3.9/site-packages/neutron/db/migration/alembic_migrations/versions/2023.2/expand/6f1145bff34c_port_hints.py.
In this way, the upgrade works, but we encounter the same kind of problem during the upgrade to Caracal, specifically with the creation of the new table porthardwareoffloadtype.
Have you already encountered this issue during the Neutron upgrade?
Do you have any suggestions on how to upgrade the neutron DB, without manually creating new tables?
Thanks a lot,
cheers,
Federica
-- Federica Fanzago INFN Sezione di Padova Via Marzolo, 8 35131 Padova - Italy
Tel: +39 049.967.7367 --
-- Federica Fanzago INFN Sezione di Padova Via Marzolo, 8 35131 Padova - Italy
Tel: +39 049.967.7367 --
-- Federica Fanzago INFN Sezione di Padova Via Marzolo, 8 35131 Padova - Italy Tel: +39 049.967.7367 --
I'm not the biggest expert wrt database schema, but to me it looks like a mismatch: mysql> SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'neutron_prod' AND TABLE_NAME = 'ports'; shows "utf8mb3_general_ci" while: mysql> show create database neutron_prod; shows "COLLATE utf8mb4_0900_ai_ci" I would expect that those have to match. Here's an excerpt from my test cloud which was upgraded from V to C over the last months: MariaDB [neutron]> show create database neutron; +----------+------------------------------------------------------------------------------------------------+ | Database | Create Database | +----------+------------------------------------------------------------------------------------------------+ | neutron | CREATE DATABASE `neutron` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ | +----------+------------------------------------------------------------------------------------------------+ MariaDB [neutron]> SELECT COLUMN_NAME,CHARACTER_SET_NAME,COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ports'; +------------------+--------------------+--------------------+ | COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME | +------------------+--------------------+--------------------+ | project_id | utf8mb4 | utf8mb4_general_ci | | id | utf8mb4 | utf8mb4_general_ci | | name | utf8mb4 | utf8mb4_general_ci | | network_id | utf8mb4 | utf8mb4_general_ci | | mac_address | utf8mb4 | utf8mb4_general_ci | | admin_state_up | NULL | NULL | | status | utf8mb4 | utf8mb4_general_ci | | device_id | utf8mb4 | utf8mb4_general_ci | | device_owner | utf8mb4 | utf8mb4_general_ci | | standard_attr_id | NULL | NULL | | ip_allocation | utf8mb4 | utf8mb4_general_ci | +------------------+--------------------+--------------------+ I don't have anything customized in neutron.conf regarding database settings. The mariadb.conf settings are also untouched: grep -ri collation /etc/ /etc/mysql/mariadb.conf.d/50-server.cnf:collation-server = utf8mb4_general_ci Hope this helps! Zitat von federica fanzago <federica.fanzago@pd.infn.it>:
Dear all, we are updating our production cloud from openstack Yoga to openstack Caracal, and now we find the problem with the update of neutron db.
The update from Antilope to Bobcat:
# su -s /bin/sh -c "neutron-db-manage --config-file /etc/neutron/neutron.conf --config-file /etc/neutron/plugins/ml2/ml2_conf.ini upgrade head" neutron
gets the error
File "/usr/lib/python3.9/site-packages/pymysql/connections.py", line 676, in _read_packet packet.raise_for_error() File "/usr/lib/python3.9/site-packages/pymysql/protocol.py", line 223, in raise_for_error err.raise_mysql_exception(self._data) File "/usr/lib/python3.9/site-packages/pymysql/err.py", line 107, in raise_mysql_exception raise errorclass(errno, errval) sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (3780, "Referencing column 'port_id' and referenced column 'id' in foreign key constraint 'porthints_ibfk_1' are incompatible.") [SQL: CREATE TABLE porthints ( port_id VARCHAR(36) NOT NULL, hints VARCHAR(4095) NOT NULL, PRIMARY KEY (port_id), FOREIGN KEY(port_id) REFERENCES ports (id) ON DELETE CASCADE )ENGINE=InnoDB
where ports table has mysql> SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'neutron_prod' AND TABLE_NAME = 'ports'; +------------------+--------------------+--------------------+ | COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME | +------------------+--------------------+--------------------+ | project_id | utf8mb3 | utf8mb3_general_ci | | id | utf8mb3 | utf8mb3_general_ci | | name | utf8mb3 | utf8mb3_general_ci | | network_id | utf8mb3 | utf8mb3_general_ci | | mac_address | utf8mb3 | utf8mb3_general_ci | | admin_state_up | NULL | NULL | | status | utf8mb3 | utf8mb3_general_ci | | device_id | utf8mb3 | utf8mb3_general_ci | | device_owner | utf8mb3 | utf8mb3_general_ci | | standard_attr_id | NULL | NULL | | ip_allocation | utf8mb3 | utf8mb3_general_ci | +------------------+--------------------+--------------------+ 11 rows in set (0.00 sec)
In neutron db we have:
mysql> show create database neutron_prod; +--------------+----------------------------------------------------------------------------------------------------------------------------------------+ | Database | Create Database | +--------------+----------------------------------------------------------------------------------------------------------------------------------------+ | neutron_prod | CREATE DATABASE `neutron_prod` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ | +--------------+----------------------------------------------------------------------------------------------------------------------------------------+
In the /etc/my.cfn of mysql
[mysqld] server-id=1 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid collation-server = utf8_general_ci character-set-server = utf8
Changing collation-server value with collation_server = 'utf8mb3_general_ci'; the error is the same.
Do you have suggestions about to fix the problem?
Thanks a lot, cheers Federica
mysql> SHOW FULL COLUMNS FROM ports; +------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+ | project_id | varchar(255) | utf8mb3_general_ci | YES | MUL | NULL | | select,insert,update,references | | | id | varchar(36) | utf8mb3_general_ci | NO | PRI | NULL | | select,insert,update,references | | | name | varchar(255) | utf8mb3_general_ci | YES | | NULL | | select,insert,update,references | | | network_id | varchar(36) | utf8mb3_general_ci | NO | MUL | NULL | | select,insert,update,references | | | mac_address | varchar(32) | utf8mb3_general_ci | NO | | NULL | | select,insert,update,references | | | admin_state_up | tinyint(1) | NULL | NO | | NULL | | select,insert,update,references | | | status | varchar(16) | utf8mb3_general_ci | NO | | NULL | | select,insert,update,references | | | device_id | varchar(255) | utf8mb3_general_ci | NO | MUL | NULL | | select,insert,update,references | | | device_owner | varchar(255) | utf8mb3_general_ci | NO | | NULL | | select,insert,update,references | | | standard_attr_id | bigint | NULL | NO | UNI | NULL | | select,insert,update,references | | | ip_allocation | varchar(16) | utf8mb3_general_ci | YES | | NULL | | select,insert,update,references | | +------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+ 11 rows in set (0.01 sec)
On 1/28/25 2:11 PM, federica fanzago wrote:
Hi Rodolfo, thanks for you answer. Cheers, Federica
On 1/27/25 7:22 AM, Rodolfo Alonso Hernandez wrote:
Hello Federica:
Neutron does not set the table collation during the alembic migrations. According to [1], prior to MariaDB 11.6.0, the default character set is latin1 and the default collation is latin1_swedish_ci. I would recommend manually setting the default charset to utf8 and the table collation to utf8mb3_general_ci. From [2]: $ SET character_set_server = 'utf8'; $ SET collation_server = 'utf8mb3_general_ci';
Regards.
[1]https://mariadb.com/kb/en/character-set-and-collation-overview/ [2]https://mariadb.com/kb/en/setting-character-sets-and-collations/
On Fri, Jan 24, 2025 at 5:56 PM federica fanzago <federica.fanzago@pd.infn.it <mailto:federica.fanzago@pd.infn.it>> wrote:
Dear all, I find the same problem during the upgrade of neutron db, from Bobcat to Caracal, with the creation of the new "porthardwareoffloadtype" table. The error is " Referencing column 'port_id' and referenced column 'id' in foreign key constraint 'porthardwareoffloadtype_ibfk_1' are incompatible". I tried adding in the neutron.conf file the values
/[database]/ /connection = mysql+pymysql://neutron:PWD@xxx/neutron?charset=utf8// //engine = "InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE utf8"// //connection_parameters = charset=utf8/
but they don't resolve the upgrade problem.
Is it compulsory to have the ports.id <http://ports.id> columns in latin1?
Thanks, cheers Federica
On 1/20/25 6:55 PM, federica fanzago wrote:
Dear all,
we are running an OpenStack deployment Yoga on AlmaLinux 9.5 and we are attempting the upgrade Openstack up to Caracal release, one release at time (Zed--Antelope--Bobcat--Caracal)
The procedure we are following is to shut down all openstack services, to remove the "old" openstack release and to install the new one, upgrading all service databases.
This procedure works well for Zed and Antelope, but during the installation of the Bobcat release, the Neutron database upgrade fails when creating the new "porthints" table.
The command we are using is
su -s /bin/sh -c "neutron-db-manage --config-file /etc/neutron/neutron.conf --config-file /etc/neutron/plugins/ml2/ml2_conf.ini upgrade head" neutron
and the error message we receive is
...
File "/usr/lib/python3.9/site-packages/pymysql/err.py", line 107, in raise_mysql_exception raise errorclass(errno, errval) sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (3780, "Referencing column 'port_id' and referenced column 'id' in foreign key constraint 'porthints_ibfk_1' are incompatible.") [SQL: CREATE TABLE porthints ( port_id VARCHAR(36) NOT NULL, hints VARCHAR(4095) NOT NULL, PRIMARY KEY (port_id), FOREIGN KEY(port_id) REFERENCES ports (id) ON DELETE CASCADE )ENGINE=InnoDB ...
Our "ports" table uses the collation utf8mb3_general_ci
mysql> SHOW FULL COLUMNS FROM ports;
+------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| project_id | varchar(255) | utf8mb3_general_ci | YES | MUL | NULL | | select,insert,update,references | | | id | varchar(36) | utf8mb3_general_ci | NO | PRI | NULL | | select,insert,update,references | | | name | varchar(255) | utf8mb3_general_ci | YES | | NULL | | select,insert,update,references | | | network_id | varchar(36) | utf8mb3_general_ci | NO | MUL | NULL | | select,insert,update,references | | | mac_address | varchar(32) | utf8mb3_general_ci | NO | | NULL | | select,insert,update,references | | | admin_state_up | tinyint(1) | NULL | NO | | NULL | | select,insert,update,references | | | status | varchar(16) | utf8mb3_general_ci | NO | | NULL | | select,insert,update,references | | | device_id | varchar(255) | utf8mb3_general_ci | NO | MUL | NULL | | select,insert,update,references | | | device_owner | varchar(255) | utf8mb3_general_ci | NO | | NULL | | select,insert,update,references | | | standard_attr_id | bigint | NULL | NO | UNI | NULL | | select,insert,update,references | | | ip_allocation | varchar(16) | utf8mb3_general_ci | YES | | NULL | | select,insert,update,references | |
+------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
in Bobcat the collation required for the new "porthints" table is latin1_swedish_ci
MariaDB [neutron]> show create table porthints;
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| porthints | CREATE TABLE `porthints` ( `port_id` varchar(36) NOT NULL, `hints` varchar(4095) NOT NULL, PRIMARY KEY (`port_id`), CONSTRAINT `porthints_ibfk_1` FOREIGN KEY (`port_id`) REFERENCES `ports` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
So, we have tried to create the porthints table in mysql, forcing the collation to utf8mb3_general_ci
mysql> CREATE TABLE porthints ( port_id VARCHAR(36) NOT NULL, hints VARCHAR(4095) NOT NULL, PRIMARY KEY (port_id), FOREIGN KEY(port_id) REFERENCES ports (id) ON DELETE CASCADE )ENGINE=InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE utf8mb3_general_ci; and commenting out the creation of the new table in the neutron code
/usr/lib/python3.9/site-packages/neutron/db/migration/alembic_migrations/versions/2023.2/expand/6f1145bff34c_port_hints.py.
In this way, the upgrade works, but we encounter the same kind of problem during the upgrade to Caracal, specifically with the creation of the new table porthardwareoffloadtype.
Have you already encountered this issue during the Neutron upgrade?
Do you have any suggestions on how to upgrade the neutron DB, without manually creating new tables?
Thanks a lot,
cheers,
Federica
-- Federica Fanzago INFN Sezione di Padova Via Marzolo, 8 35131 Padova - Italy
Tel: +39 049.967.7367 --
-- Federica Fanzago INFN Sezione di Padova Via Marzolo, 8 35131 Padova - Italy
Tel: +39 049.967.7367 --
-- Federica Fanzago INFN Sezione di Padova Via Marzolo, 8 35131 Padova - Italy
Tel: +39 049.967.7367 --
Ironic dealt with a bug years ago that appears related: https://opendev.org/openstack/ironic/commit/1435a15ce3013da0a3138e1c5ab6ac52... Essentially, mysql (and/or mariadb) utf8 has changed meaning over time: it can either alias to utf8mb3 (any utf8 character in 3 bytes or less) or utf8mb4 (any utf8 character up to 4 bytes ... aka all of them). My hunch would be the mysql version upgraded, changing the meaning of utf8 on your machine. I am not a neutron expert, but one workaround for now might be manually ensuring the character sets match between the linked tables. A real fix would likely have to be done in code/migrations. Good luck, Jay Faulkner On 5/19/25 6:53 AM, Eugen Block wrote:
I'm not the biggest expert wrt database schema, but to me it looks like a mismatch:
mysql> SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'neutron_prod' AND TABLE_NAME = 'ports';
shows "utf8mb3_general_ci" while:
mysql> show create database neutron_prod;
shows "COLLATE utf8mb4_0900_ai_ci"
I would expect that those have to match. Here's an excerpt from my test cloud which was upgraded from V to C over the last months:
MariaDB [neutron]> show create database neutron; +----------+------------------------------------------------------------------------------------------------+
| Database | Create Database | +----------+------------------------------------------------------------------------------------------------+
| neutron | CREATE DATABASE `neutron` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ | +----------+------------------------------------------------------------------------------------------------+
MariaDB [neutron]> SELECT COLUMN_NAME,CHARACTER_SET_NAME,COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ports'; +------------------+--------------------+--------------------+ | COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME | +------------------+--------------------+--------------------+ | project_id | utf8mb4 | utf8mb4_general_ci | | id | utf8mb4 | utf8mb4_general_ci | | name | utf8mb4 | utf8mb4_general_ci | | network_id | utf8mb4 | utf8mb4_general_ci | | mac_address | utf8mb4 | utf8mb4_general_ci | | admin_state_up | NULL | NULL | | status | utf8mb4 | utf8mb4_general_ci | | device_id | utf8mb4 | utf8mb4_general_ci | | device_owner | utf8mb4 | utf8mb4_general_ci | | standard_attr_id | NULL | NULL | | ip_allocation | utf8mb4 | utf8mb4_general_ci | +------------------+--------------------+--------------------+
I don't have anything customized in neutron.conf regarding database settings. The mariadb.conf settings are also untouched:
grep -ri collation /etc/ /etc/mysql/mariadb.conf.d/50-server.cnf:collation-server = utf8mb4_general_ci
Hope this helps!
Zitat von federica fanzago <federica.fanzago@pd.infn.it>:
Dear all, we are updating our production cloud from openstack Yoga to openstack Caracal, and now we find the problem with the update of neutron db.
The update from Antilope to Bobcat:
# su -s /bin/sh -c "neutron-db-manage --config-file /etc/neutron/neutron.conf --config-file /etc/neutron/plugins/ml2/ml2_conf.ini upgrade head" neutron
gets the error
File "/usr/lib/python3.9/site-packages/pymysql/connections.py", line 676, in _read_packet packet.raise_for_error() File "/usr/lib/python3.9/site-packages/pymysql/protocol.py", line 223, in raise_for_error err.raise_mysql_exception(self._data) File "/usr/lib/python3.9/site-packages/pymysql/err.py", line 107, in raise_mysql_exception raise errorclass(errno, errval) sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (3780, "Referencing column 'port_id' and referenced column 'id' in foreign key constraint 'porthints_ibfk_1' are incompatible.") [SQL: CREATE TABLE porthints ( port_id VARCHAR(36) NOT NULL, hints VARCHAR(4095) NOT NULL, PRIMARY KEY (port_id), FOREIGN KEY(port_id) REFERENCES ports (id) ON DELETE CASCADE )ENGINE=InnoDB
where ports table has mysql> SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'neutron_prod' AND TABLE_NAME = 'ports'; +------------------+--------------------+--------------------+ | COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME | +------------------+--------------------+--------------------+ | project_id | utf8mb3 | utf8mb3_general_ci | | id | utf8mb3 | utf8mb3_general_ci | | name | utf8mb3 | utf8mb3_general_ci | | network_id | utf8mb3 | utf8mb3_general_ci | | mac_address | utf8mb3 | utf8mb3_general_ci | | admin_state_up | NULL | NULL | | status | utf8mb3 | utf8mb3_general_ci | | device_id | utf8mb3 | utf8mb3_general_ci | | device_owner | utf8mb3 | utf8mb3_general_ci | | standard_attr_id | NULL | NULL | | ip_allocation | utf8mb3 | utf8mb3_general_ci | +------------------+--------------------+--------------------+ 11 rows in set (0.00 sec)
In neutron db we have:
mysql> show create database neutron_prod; +--------------+----------------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database | +--------------+----------------------------------------------------------------------------------------------------------------------------------------+
| neutron_prod | CREATE DATABASE `neutron_prod` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ | +--------------+----------------------------------------------------------------------------------------------------------------------------------------+
In the /etc/my.cfn of mysql
[mysqld] server-id=1 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid collation-server = utf8_general_ci character-set-server = utf8
Changing collation-server value with collation_server = 'utf8mb3_general_ci'; the error is the same.
Do you have suggestions about to fix the problem?
Thanks a lot, cheers Federica
mysql> SHOW FULL COLUMNS FROM ports; +------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| project_id | varchar(255) | utf8mb3_general_ci | YES | MUL | NULL | | select,insert,update,references | | | id | varchar(36) | utf8mb3_general_ci | NO | PRI | NULL | | select,insert,update,references | | | name | varchar(255) | utf8mb3_general_ci | YES | | NULL | | select,insert,update,references | | | network_id | varchar(36) | utf8mb3_general_ci | NO | MUL | NULL | | select,insert,update,references | | | mac_address | varchar(32) | utf8mb3_general_ci | NO | | NULL | | select,insert,update,references | | | admin_state_up | tinyint(1) | NULL | NO | | NULL | | select,insert,update,references | | | status | varchar(16) | utf8mb3_general_ci | NO | | NULL | | select,insert,update,references | | | device_id | varchar(255) | utf8mb3_general_ci | NO | MUL | NULL | | select,insert,update,references | | | device_owner | varchar(255) | utf8mb3_general_ci | NO | | NULL | | select,insert,update,references | | | standard_attr_id | bigint | NULL | NO | UNI | NULL | | select,insert,update,references | | | ip_allocation | varchar(16) | utf8mb3_general_ci | YES | | NULL | | select,insert,update,references | | +------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
11 rows in set (0.01 sec)
On 1/28/25 2:11 PM, federica fanzago wrote:
Hi Rodolfo, thanks for you answer. Cheers, Federica
On 1/27/25 7:22 AM, Rodolfo Alonso Hernandez wrote:
Hello Federica:
Neutron does not set the table collation during the alembic migrations. According to [1], prior to MariaDB 11.6.0, the default character set is latin1 and the default collation is latin1_swedish_ci. I would recommend manually setting the default charset to utf8 and the table collation to utf8mb3_general_ci. From [2]: $ SET character_set_server = 'utf8'; $ SET collation_server = 'utf8mb3_general_ci';
Regards.
[1]https://mariadb.com/kb/en/character-set-and-collation-overview/ [2]https://mariadb.com/kb/en/setting-character-sets-and-collations/
On Fri, Jan 24, 2025 at 5:56 PM federica fanzago <federica.fanzago@pd.infn.it <mailto:federica.fanzago@pd.infn.it>> wrote:
Dear all, I find the same problem during the upgrade of neutron db, from Bobcat to Caracal, with the creation of the new "porthardwareoffloadtype" table. The error is " Referencing column 'port_id' and referenced column 'id' in foreign key constraint 'porthardwareoffloadtype_ibfk_1' are incompatible". I tried adding in the neutron.conf file the values
/[database]/ /connection = mysql+pymysql://neutron:PWD@xxx/neutron?charset=utf8// //engine = "InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE utf8"// //connection_parameters = charset=utf8/
but they don't resolve the upgrade problem.
Is it compulsory to have the ports.id <http://ports.id> columns in latin1?
Thanks, cheers Federica
On 1/20/25 6:55 PM, federica fanzago wrote:
Dear all,
we are running an OpenStack deployment Yoga on AlmaLinux 9.5 and we are attempting the upgrade Openstack up to Caracal release, one release at time (Zed--Antelope--Bobcat--Caracal)
The procedure we are following is to shut down all openstack services, to remove the "old" openstack release and to install the new one, upgrading all service databases.
This procedure works well for Zed and Antelope, but during the installation of the Bobcat release, the Neutron database upgrade fails when creating the new "porthints" table.
The command we are using is
su -s /bin/sh -c "neutron-db-manage --config-file /etc/neutron/neutron.conf --config-file /etc/neutron/plugins/ml2/ml2_conf.ini upgrade head" neutron
and the error message we receive is
...
File "/usr/lib/python3.9/site-packages/pymysql/err.py", line 107, in raise_mysql_exception raise errorclass(errno, errval) sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (3780, "Referencing column 'port_id' and referenced column 'id' in foreign key constraint 'porthints_ibfk_1' are incompatible.") [SQL: CREATE TABLE porthints ( port_id VARCHAR(36) NOT NULL, hints VARCHAR(4095) NOT NULL, PRIMARY KEY (port_id), FOREIGN KEY(port_id) REFERENCES ports (id) ON DELETE CASCADE )ENGINE=InnoDB ...
Our "ports" table uses the collation utf8mb3_general_ci
mysql> SHOW FULL COLUMNS FROM ports; +------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| project_id | varchar(255) | utf8mb3_general_ci | YES | MUL | NULL | | select,insert,update,references | | | id | varchar(36) | utf8mb3_general_ci | NO | PRI | NULL | | select,insert,update,references | | | name | varchar(255) | utf8mb3_general_ci | YES | | NULL | | select,insert,update,references | | | network_id | varchar(36) | utf8mb3_general_ci | NO | MUL | NULL | | select,insert,update,references | | | mac_address | varchar(32) | utf8mb3_general_ci | NO | | NULL | | select,insert,update,references | | | admin_state_up | tinyint(1) | NULL | NO | | NULL | | select,insert,update,references | | | status | varchar(16) | utf8mb3_general_ci | NO | | NULL | | select,insert,update,references | | | device_id | varchar(255) | utf8mb3_general_ci | NO | MUL | NULL | | select,insert,update,references | | | device_owner | varchar(255) | utf8mb3_general_ci | NO | | NULL | | select,insert,update,references | | | standard_attr_id | bigint | NULL | NO | UNI | NULL | | select,insert,update,references | | | ip_allocation | varchar(16) | utf8mb3_general_ci | YES | | NULL | | select,insert,update,references | | +------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
in Bobcat the collation required for the new "porthints" table is latin1_swedish_ci
MariaDB [neutron]> show create table porthints; +-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table | +-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| porthints | CREATE TABLE `porthints` ( `port_id` varchar(36) NOT NULL, `hints` varchar(4095) NOT NULL, PRIMARY KEY (`port_id`), CONSTRAINT `porthints_ibfk_1` FOREIGN KEY (`port_id`) REFERENCES `ports` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
So, we have tried to create the porthints table in mysql, forcing the collation to utf8mb3_general_ci
mysql> CREATE TABLE porthints ( port_id VARCHAR(36) NOT NULL, hints VARCHAR(4095) NOT NULL, PRIMARY KEY (port_id), FOREIGN KEY(port_id) REFERENCES ports (id) ON DELETE CASCADE )ENGINE=InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE utf8mb3_general_ci; and commenting out the creation of the new table in the neutron code /usr/lib/python3.9/site-packages/neutron/db/migration/alembic_migrations/versions/2023.2/expand/6f1145bff34c_port_hints.py.
In this way, the upgrade works, but we encounter the same kind of problem during the upgrade to Caracal, specifically with the creation of the new table porthardwareoffloadtype.
Have you already encountered this issue during the Neutron upgrade?
Do you have any suggestions on how to upgrade the neutron DB, without manually creating new tables?
Thanks a lot,
cheers,
Federica
-- Federica Fanzago INFN Sezione di Padova Via Marzolo, 8 35131 Padova - Italy
Tel: +39 049.967.7367 --
-- Federica Fanzago INFN Sezione di Padova Via Marzolo, 8 35131 Padova - Italy
Tel: +39 049.967.7367 --
-- Federica Fanzago INFN Sezione di Padova Via Marzolo, 8 35131 Padova - Italy
Tel: +39 049.967.7367 --
Hello: As Jay suggested, this could be an issue in a server upgrade. Neutron does not consider the character set in the table creation, nor the database ``collation_server`` parameter. If any upgrade changed the default char set, an option could be to update all Neutron tables to use this new char set or to set in the configuration the currently used one. Regards. On Mon, May 19, 2025 at 4:17 PM Jay Faulkner <jay@gr-oss.io> wrote:
Ironic dealt with a bug years ago that appears related:
https://opendev.org/openstack/ironic/commit/1435a15ce3013da0a3138e1c5ab6ac52...
Essentially, mysql (and/or mariadb) utf8 has changed meaning over time: it can either alias to utf8mb3 (any utf8 character in 3 bytes or less) or utf8mb4 (any utf8 character up to 4 bytes ... aka all of them). My hunch would be the mysql version upgraded, changing the meaning of utf8 on your machine.
I am not a neutron expert, but one workaround for now might be manually ensuring the character sets match between the linked tables. A real fix would likely have to be done in code/migrations.
Good luck, Jay Faulkner
On 5/19/25 6:53 AM, Eugen Block wrote:
I'm not the biggest expert wrt database schema, but to me it looks like a mismatch:
mysql> SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'neutron_prod' AND TABLE_NAME = 'ports';
shows "utf8mb3_general_ci" while:
mysql> show create database neutron_prod;
shows "COLLATE utf8mb4_0900_ai_ci"
I would expect that those have to match. Here's an excerpt from my test cloud which was upgraded from V to C over the last months:
MariaDB [neutron]> show create database neutron;
+----------+------------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------------------------------------+
| neutron | CREATE DATABASE `neutron` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ |
+----------+------------------------------------------------------------------------------------------------+
MariaDB [neutron]> SELECT COLUMN_NAME,CHARACTER_SET_NAME,COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ports'; +------------------+--------------------+--------------------+ | COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME | +------------------+--------------------+--------------------+ | project_id | utf8mb4 | utf8mb4_general_ci | | id | utf8mb4 | utf8mb4_general_ci | | name | utf8mb4 | utf8mb4_general_ci | | network_id | utf8mb4 | utf8mb4_general_ci | | mac_address | utf8mb4 | utf8mb4_general_ci | | admin_state_up | NULL | NULL | | status | utf8mb4 | utf8mb4_general_ci | | device_id | utf8mb4 | utf8mb4_general_ci | | device_owner | utf8mb4 | utf8mb4_general_ci | | standard_attr_id | NULL | NULL | | ip_allocation | utf8mb4 | utf8mb4_general_ci | +------------------+--------------------+--------------------+
I don't have anything customized in neutron.conf regarding database settings. The mariadb.conf settings are also untouched:
grep -ri collation /etc/ /etc/mysql/mariadb.conf.d/50-server.cnf:collation-server = utf8mb4_general_ci
Hope this helps!
Zitat von federica fanzago <federica.fanzago@pd.infn.it>:
Dear all, we are updating our production cloud from openstack Yoga to openstack Caracal, and now we find the problem with the update of neutron db.
The update from Antilope to Bobcat:
# su -s /bin/sh -c "neutron-db-manage --config-file /etc/neutron/neutron.conf --config-file /etc/neutron/plugins/ml2/ml2_conf.ini upgrade head" neutron
gets the error
File "/usr/lib/python3.9/site-packages/pymysql/connections.py", line 676, in _read_packet packet.raise_for_error() File "/usr/lib/python3.9/site-packages/pymysql/protocol.py", line 223, in raise_for_error err.raise_mysql_exception(self._data) File "/usr/lib/python3.9/site-packages/pymysql/err.py", line 107, in raise_mysql_exception raise errorclass(errno, errval) sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (3780, "Referencing column 'port_id' and referenced column 'id' in foreign key constraint 'porthints_ibfk_1' are incompatible.") [SQL: CREATE TABLE porthints ( port_id VARCHAR(36) NOT NULL, hints VARCHAR(4095) NOT NULL, PRIMARY KEY (port_id), FOREIGN KEY(port_id) REFERENCES ports (id) ON DELETE CASCADE )ENGINE=InnoDB
where ports table has mysql> SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'neutron_prod' AND TABLE_NAME = 'ports'; +------------------+--------------------+--------------------+ | COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME | +------------------+--------------------+--------------------+ | project_id | utf8mb3 | utf8mb3_general_ci | | id | utf8mb3 | utf8mb3_general_ci | | name | utf8mb3 | utf8mb3_general_ci | | network_id | utf8mb3 | utf8mb3_general_ci | | mac_address | utf8mb3 | utf8mb3_general_ci | | admin_state_up | NULL | NULL | | status | utf8mb3 | utf8mb3_general_ci | | device_id | utf8mb3 | utf8mb3_general_ci | | device_owner | utf8mb3 | utf8mb3_general_ci | | standard_attr_id | NULL | NULL | | ip_allocation | utf8mb3 | utf8mb3_general_ci | +------------------+--------------------+--------------------+ 11 rows in set (0.00 sec)
In neutron db we have:
mysql> show create database neutron_prod;
+--------------+----------------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database |
+--------------+----------------------------------------------------------------------------------------------------------------------------------------+
| neutron_prod | CREATE DATABASE `neutron_prod` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+--------------+----------------------------------------------------------------------------------------------------------------------------------------+
In the /etc/my.cfn of mysql
[mysqld] server-id=1 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid collation-server = utf8_general_ci character-set-server = utf8
Changing collation-server value with collation_server = 'utf8mb3_general_ci'; the error is the same.
Do you have suggestions about to fix the problem?
Thanks a lot, cheers Federica
mysql> SHOW FULL COLUMNS FROM ports;
+------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| project_id | varchar(255) | utf8mb3_general_ci | YES | MUL | NULL | | select,insert,update,references | | | id | varchar(36) | utf8mb3_general_ci | NO | PRI | NULL | | select,insert,update,references | | | name | varchar(255) | utf8mb3_general_ci | YES | | NULL | | select,insert,update,references | | | network_id | varchar(36) | utf8mb3_general_ci | NO | MUL | NULL | | select,insert,update,references | | | mac_address | varchar(32) | utf8mb3_general_ci | NO | | NULL | | select,insert,update,references | | | admin_state_up | tinyint(1) | NULL | NO | | NULL | | select,insert,update,references | | | status | varchar(16) | utf8mb3_general_ci | NO | | NULL | | select,insert,update,references | | | device_id | varchar(255) | utf8mb3_general_ci | NO | MUL | NULL | | select,insert,update,references | | | device_owner | varchar(255) | utf8mb3_general_ci | NO | | NULL | | select,insert,update,references | | | standard_attr_id | bigint | NULL | NO | UNI | NULL | | select,insert,update,references | | | ip_allocation | varchar(16) | utf8mb3_general_ci | YES | | NULL | | select,insert,update,references | |
+------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
11 rows in set (0.01 sec)
On 1/28/25 2:11 PM, federica fanzago wrote:
Hi Rodolfo, thanks for you answer. Cheers, Federica
On 1/27/25 7:22 AM, Rodolfo Alonso Hernandez wrote:
Hello Federica:
Neutron does not set the table collation during the alembic migrations. According to [1], prior to MariaDB 11.6.0, the default character set is latin1 and the default collation is latin1_swedish_ci. I would recommend manually setting the default charset to utf8 and the table collation to utf8mb3_general_ci. From [2]: $ SET character_set_server = 'utf8'; $ SET collation_server = 'utf8mb3_general_ci';
Regards.
[1]https://mariadb.com/kb/en/character-set-and-collation-overview/ [2]https://mariadb.com/kb/en/setting-character-sets-and-collations/
On Fri, Jan 24, 2025 at 5:56 PM federica fanzago <federica.fanzago@pd.infn.it <mailto:federica.fanzago@pd.infn.it>> wrote:
Dear all, I find the same problem during the upgrade of neutron db, from Bobcat to Caracal, with the creation of the new "porthardwareoffloadtype" table. The error is " Referencing column 'port_id' and referenced column 'id' in foreign key constraint 'porthardwareoffloadtype_ibfk_1' are incompatible". I tried adding in the neutron.conf file the values
/[database]/ /connection = mysql+pymysql://neutron:PWD@xxx/neutron?charset=utf8// //engine = "InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE utf8"// //connection_parameters = charset=utf8/
but they don't resolve the upgrade problem.
Is it compulsory to have the ports.id <http://ports.id> columns in latin1?
Thanks, cheers Federica
On 1/20/25 6:55 PM, federica fanzago wrote:
Dear all,
we are running an OpenStack deployment Yoga on AlmaLinux 9.5 and we are attempting the upgrade Openstack up to Caracal release, one release at time (Zed--Antelope--Bobcat--Caracal)
The procedure we are following is to shut down all openstack services, to remove the "old" openstack release and to install the new one, upgrading all service databases.
This procedure works well for Zed and Antelope, but during the installation of the Bobcat release, the Neutron database upgrade fails when creating the new "porthints" table.
The command we are using is
su -s /bin/sh -c "neutron-db-manage --config-file /etc/neutron/neutron.conf --config-file /etc/neutron/plugins/ml2/ml2_conf.ini upgrade head" neutron
and the error message we receive is
...
File "/usr/lib/python3.9/site-packages/pymysql/err.py", line 107, in raise_mysql_exception raise errorclass(errno, errval) sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (3780, "Referencing column 'port_id' and referenced column 'id' in foreign key constraint 'porthints_ibfk_1' are incompatible.") [SQL: CREATE TABLE porthints ( port_id VARCHAR(36) NOT NULL, hints VARCHAR(4095) NOT NULL, PRIMARY KEY (port_id), FOREIGN KEY(port_id) REFERENCES ports (id) ON DELETE CASCADE )ENGINE=InnoDB ...
Our "ports" table uses the collation utf8mb3_general_ci
mysql> SHOW FULL COLUMNS FROM ports;
+------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| project_id | varchar(255) | utf8mb3_general_ci | YES | MUL | NULL | | select,insert,update,references | | | id | varchar(36) | utf8mb3_general_ci | NO | PRI | NULL | | select,insert,update,references | | | name | varchar(255) | utf8mb3_general_ci | YES | | NULL | | select,insert,update,references | | | network_id | varchar(36) | utf8mb3_general_ci | NO | MUL | NULL | | select,insert,update,references | | | mac_address | varchar(32) | utf8mb3_general_ci | NO | | NULL | | select,insert,update,references | | | admin_state_up | tinyint(1) | NULL | NO | | NULL | | select,insert,update,references | | | status | varchar(16) | utf8mb3_general_ci | NO | | NULL | | select,insert,update,references | | | device_id | varchar(255) | utf8mb3_general_ci | NO | MUL | NULL | | select,insert,update,references | | | device_owner | varchar(255) | utf8mb3_general_ci | NO | | NULL | | select,insert,update,references | | | standard_attr_id | bigint | NULL | NO | UNI | NULL | | select,insert,update,references | | | ip_allocation | varchar(16) | utf8mb3_general_ci | YES | | NULL | | select,insert,update,references | |
+------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
in Bobcat the collation required for the new "porthints" table is latin1_swedish_ci
MariaDB [neutron]> show create table porthints;
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| porthints | CREATE TABLE `porthints` ( `port_id` varchar(36) NOT NULL, `hints` varchar(4095) NOT NULL, PRIMARY KEY (`port_id`), CONSTRAINT `porthints_ibfk_1` FOREIGN KEY (`port_id`) REFERENCES `ports` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
So, we have tried to create the porthints table in mysql, forcing the collation to utf8mb3_general_ci
mysql> CREATE TABLE porthints ( port_id VARCHAR(36) NOT NULL, hints VARCHAR(4095) NOT NULL, PRIMARY KEY (port_id), FOREIGN KEY(port_id) REFERENCES ports (id) ON DELETE CASCADE )ENGINE=InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE utf8mb3_general_ci; and commenting out the creation of the new table in the neutron code
/usr/lib/python3.9/site-packages/neutron/db/migration/alembic_migrations/versions/2023.2/expand/6f1145bff34c_port_hints.py.
In this way, the upgrade works, but we encounter the same kind of problem during the upgrade to Caracal, specifically with the creation of the new table porthardwareoffloadtype.
Have you already encountered this issue during the Neutron upgrade?
Do you have any suggestions on how to upgrade the neutron DB, without manually creating new tables?
Thanks a lot,
cheers,
Federica
-- Federica Fanzago INFN Sezione di Padova Via Marzolo, 8 35131 Padova - Italy
Tel: +39 049.967.7367 --
-- Federica Fanzago INFN Sezione di Padova Via Marzolo, 8 35131 Padova - Italy
Tel: +39 049.967.7367 --
-- Federica Fanzago INFN Sezione di Padova Via Marzolo, 8 35131 Padova - Italy
Tel: +39 049.967.7367 --
Hi all, thanks for your reply. We have resolved the problem by modifying the default character set and collation with ALTER DATABASE: ALTER DATABASE neutron DEFAULT CHARACTER SET utf8mb3 DEFAULT COLLATE utf8mb3_general_ci; Cheers, Federica On 5/19/25 5:27 PM, Rodolfo Alonso Hernandez wrote:
Hello:
As Jay suggested, this could be an issue in a server upgrade. Neutron does not consider the character set in the table creation, nor the database ``collation_server`` parameter. If any upgrade changed the default char set, an option could be to update all Neutron tables to use this new char set or to set in the configuration the currently used one.
Regards.
On Mon, May 19, 2025 at 4:17 PM Jay Faulkner <jay@gr-oss.io <mailto:jay@gr-oss.io>> wrote:
Ironic dealt with a bug years ago that appears related: https://opendev.org/openstack/ironic/commit/1435a15ce3013da0a3138e1c5ab6ac52...
Essentially, mysql (and/or mariadb) utf8 has changed meaning over time: it can either alias to utf8mb3 (any utf8 character in 3 bytes or less) or utf8mb4 (any utf8 character up to 4 bytes ... aka all of them). My hunch would be the mysql version upgraded, changing the meaning of utf8 on your machine.
I am not a neutron expert, but one workaround for now might be manually ensuring the character sets match between the linked tables. A real fix would likely have to be done in code/migrations.
Good luck, Jay Faulkner
On 5/19/25 6:53 AM, Eugen Block wrote: > I'm not the biggest expert wrt database schema, but to me it looks > like a mismatch: > > mysql> SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME FROM > INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'neutron_prod' AND > TABLE_NAME = 'ports'; > > shows "utf8mb3_general_ci" while: > > mysql> show create database neutron_prod; > > shows "COLLATE utf8mb4_0900_ai_ci" > > I would expect that those have to match. Here's an excerpt from my > test cloud which was upgraded from V to C over the last months: > > MariaDB [neutron]> show create database neutron; > +----------+------------------------------------------------------------------------------------------------+
> > | Database | Create Database | > +----------+------------------------------------------------------------------------------------------------+
> > | neutron | CREATE DATABASE `neutron` /*!40100 DEFAULT CHARACTER SET > utf8mb4 COLLATE utf8mb4_general_ci */ | > +----------+------------------------------------------------------------------------------------------------+
> > > MariaDB [neutron]> SELECT > COLUMN_NAME,CHARACTER_SET_NAME,COLLATION_NAME FROM > INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ports'; > +------------------+--------------------+--------------------+ > | COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME | > +------------------+--------------------+--------------------+ > | project_id | utf8mb4 | utf8mb4_general_ci | > | id | utf8mb4 | utf8mb4_general_ci | > | name | utf8mb4 | utf8mb4_general_ci | > | network_id | utf8mb4 | utf8mb4_general_ci | > | mac_address | utf8mb4 | utf8mb4_general_ci | > | admin_state_up | NULL | NULL | > | status | utf8mb4 | utf8mb4_general_ci | > | device_id | utf8mb4 | utf8mb4_general_ci | > | device_owner | utf8mb4 | utf8mb4_general_ci | > | standard_attr_id | NULL | NULL | > | ip_allocation | utf8mb4 | utf8mb4_general_ci | > +------------------+--------------------+--------------------+ > > I don't have anything customized in neutron.conf regarding database > settings. The mariadb.conf settings are also untouched: > > grep -ri collation /etc/ > /etc/mysql/mariadb.conf.d/50-server.cnf:collation-server = > utf8mb4_general_ci > > Hope this helps! > > Zitat von federica fanzago <federica.fanzago@pd.infn.it <mailto:federica.fanzago@pd.infn.it>>: > >> Dear all, >> we are updating our production cloud from openstack Yoga to openstack >> Caracal, and >> now we find the problem with the update of neutron db. >> >> The update from Antilope to Bobcat: >> >> # su -s /bin/sh -c "neutron-db-manage --config-file >> /etc/neutron/neutron.conf --config-file >> /etc/neutron/plugins/ml2/ml2_conf.ini upgrade head" neutron >> >> gets the error >> >> File "/usr/lib/python3.9/site-packages/pymysql/connections.py", line >> 676, in _read_packet >> packet.raise_for_error() >> File "/usr/lib/python3.9/site-packages/pymysql/protocol.py", line >> 223, in raise_for_error >> err.raise_mysql_exception(self._data) >> File "/usr/lib/python3.9/site-packages/pymysql/err.py", line 107, >> in raise_mysql_exception >> raise errorclass(errno, errval) >> sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) >> (3780, "Referencing column 'port_id' and referenced column 'id' in >> foreign key constraint 'porthints_ibfk_1' are incompatible.") >> [SQL: >> CREATE TABLE porthints ( >> port_id VARCHAR(36) NOT NULL, >> hints VARCHAR(4095) NOT NULL, >> PRIMARY KEY (port_id), >> FOREIGN KEY(port_id) REFERENCES ports (id) ON DELETE CASCADE >> )ENGINE=InnoDB >> >> >> where ports table has >> mysql> SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME FROM >> INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'neutron_prod' AND >> TABLE_NAME = 'ports'; >> +------------------+--------------------+--------------------+ >> | COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME | >> +------------------+--------------------+--------------------+ >> | project_id | utf8mb3 | utf8mb3_general_ci | >> | id | utf8mb3 | utf8mb3_general_ci | >> | name | utf8mb3 | utf8mb3_general_ci | >> | network_id | utf8mb3 | utf8mb3_general_ci | >> | mac_address | utf8mb3 | utf8mb3_general_ci | >> | admin_state_up | NULL | NULL | >> | status | utf8mb3 | utf8mb3_general_ci | >> | device_id | utf8mb3 | utf8mb3_general_ci | >> | device_owner | utf8mb3 | utf8mb3_general_ci | >> | standard_attr_id | NULL | NULL | >> | ip_allocation | utf8mb3 | utf8mb3_general_ci | >> +------------------+--------------------+--------------------+ >> 11 rows in set (0.00 sec) >> >> >> >> >> In neutron db we have: >> >> mysql> show create database neutron_prod; >> +--------------+----------------------------------------------------------------------------------------------------------------------------------------+
>> >> | Database | Create Database | >> +--------------+----------------------------------------------------------------------------------------------------------------------------------------+
>> >> | neutron_prod | CREATE DATABASE `neutron_prod` /*!40100 DEFAULT >> CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT >> ENCRYPTION='N' */ | >> +--------------+----------------------------------------------------------------------------------------------------------------------------------------+
>> >> >> >> In the /etc/my.cfn of mysql >> >> [mysqld] >> server-id=1 >> datadir=/var/lib/mysql >> socket=/var/lib/mysql/mysql.sock >> log-error=/var/log/mysqld.log >> pid-file=/var/run/mysqld/mysqld.pid >> collation-server = utf8_general_ci >> character-set-server = utf8 >> >> >> Changing collation-server value with collation_server = >> 'utf8mb3_general_ci'; the error is the same. >> >> >> Do you have suggestions about to fix the problem? >> >> >> Thanks a lot, >> cheers >> Federica >> >> >> >> >> >> mysql> SHOW FULL COLUMNS FROM ports; >> +------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
>> >> | Field | Type | Collation | Null | Key | >> Default | Extra | Privileges | Comment | >> +------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
>> >> | project_id | varchar(255) | utf8mb3_general_ci | YES | MUL | >> NULL | | select,insert,update,references | | >> | id | varchar(36) | utf8mb3_general_ci | NO | PRI | >> NULL | | select,insert,update,references | | >> | name | varchar(255) | utf8mb3_general_ci | YES | | >> NULL | | select,insert,update,references | | >> | network_id | varchar(36) | utf8mb3_general_ci | NO | MUL | >> NULL | | select,insert,update,references | | >> | mac_address | varchar(32) | utf8mb3_general_ci | NO | | >> NULL | | select,insert,update,references | | >> | admin_state_up | tinyint(1) | NULL | NO | | >> NULL | | select,insert,update,references | | >> | status | varchar(16) | utf8mb3_general_ci | NO | | >> NULL | | select,insert,update,references | | >> | device_id | varchar(255) | utf8mb3_general_ci | NO | MUL | >> NULL | | select,insert,update,references | | >> | device_owner | varchar(255) | utf8mb3_general_ci | NO | | >> NULL | | select,insert,update,references | | >> | standard_attr_id | bigint | NULL | NO | UNI | >> NULL | | select,insert,update,references | | >> | ip_allocation | varchar(16) | utf8mb3_general_ci | YES | | >> NULL | | select,insert,update,references | | >> +------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
>> >> 11 rows in set (0.01 sec) >> >> >> >> >> >> On 1/28/25 2:11 PM, federica fanzago wrote: >>> Hi Rodolfo, >>> thanks for you answer. >>> Cheers, >>> Federica >>> >>> >>> >>> On 1/27/25 7:22 AM, Rodolfo Alonso Hernandez wrote: >>>> Hello Federica: >>>> >>>> Neutron does not set the table collation during the alembic >>>> migrations. According to [1], prior to MariaDB 11.6.0, the default >>>> character set is latin1 and the default collation is >>>> latin1_swedish_ci. I would recommend manually setting the default >>>> charset to utf8 and the table collation to utf8mb3_general_ci. From >>>> [2]: >>>> $ SET character_set_server = 'utf8'; >>>> $ SET collation_server = 'utf8mb3_general_ci'; >>>> >>>> Regards. >>>> >>>> [1]https://mariadb.com/kb/en/character-set-and-collation-overview/ >>>> [2]https://mariadb.com/kb/en/setting-character-sets-and-collations/ >>>> >>>> On Fri, Jan 24, 2025 at 5:56 PM federica fanzago >>>> <federica.fanzago@pd.infn.it <mailto:federica.fanzago@pd.infn.it> <mailto:federica.fanzago@pd.infn.it <mailto:federica.fanzago@pd.infn.it>>> >>>> wrote: >>>> >>>> Dear all, >>>> I find the same problem during the upgrade of neutron db, from >>>> Bobcat to Caracal, with the creation of the new >>>> "porthardwareoffloadtype" table. >>>> The error is " Referencing column 'port_id' and referenced column >>>> 'id' in foreign key constraint 'porthardwareoffloadtype_ibfk_1' >>>> are incompatible". >>>> I tried adding in the neutron.conf file the values >>>> >>>> /[database]/ >>>> /connection = >>>> mysql+pymysql://neutron:PWD@xxx/neutron?charset=utf8// >>>> //engine = "InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE utf8"// >>>> //connection_parameters = charset=utf8/ >>>> >>>> but they don't resolve the upgrade problem. >>>> >>>> Is it compulsory to have the ports.id <http://ports.id> <http://ports.id> columns >>>> in latin1? >>>> >>>> Thanks, >>>> cheers >>>> Federica >>>> >>>> >>>> >>>> >>>> >>>> >>>> On 1/20/25 6:55 PM, federica fanzago wrote: >>>>> Dear all, >>>>> >>>>> we are running an OpenStack deployment Yoga on AlmaLinux 9.5 and >>>>> we are attempting the upgrade Openstack up to Caracal release, >>>>> one release at time (Zed--Antelope--Bobcat--Caracal) >>>>> >>>>> The procedure we are following is to shut down all openstack >>>>> services, to remove the "old" openstack release and to install >>>>> the new one, upgrading all service databases. >>>>> >>>>> This procedure works well for Zed and Antelope, but during the >>>>> installation of the Bobcat release, the Neutron database upgrade >>>>> fails when creating the new "porthints" table. >>>>> >>>>> The command we are using is >>>>> >>>>> su -s /bin/sh -c "neutron-db-manage --config-file >>>>> /etc/neutron/neutron.conf --config-file >>>>> /etc/neutron/plugins/ml2/ml2_conf.ini upgrade head" neutron >>>>> >>>>> and the error message we receive is >>>>> >>>>> ... >>>>> >>>>> File "/usr/lib/python3.9/site-packages/pymysql/err.py", line >>>>> 107, in raise_mysql_exception >>>>> raise errorclass(errno, errval) >>>>> sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) >>>>> (3780, "Referencing column 'port_id' and referenced column 'id' >>>>> in foreign key constraint 'porthints_ibfk_1' are incompatible.") >>>>> [SQL: >>>>> CREATE TABLE porthints ( >>>>> port_id VARCHAR(36) NOT NULL, >>>>> hints VARCHAR(4095) NOT NULL, >>>>> PRIMARY KEY (port_id), >>>>> FOREIGN KEY(port_id) REFERENCES ports (id) ON DELETE CASCADE >>>>> )ENGINE=InnoDB >>>>> ... >>>>> >>>>> Our "ports" table uses the collation utf8mb3_general_ci >>>>> >>>>> mysql> SHOW FULL COLUMNS FROM ports; >>>>> +------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+ >>>>> >>>>> | Field | Type | Collation | Null | >>>>> Key | Default | Extra | Privileges | Comment | >>>>> +------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+ >>>>> >>>>> | project_id | varchar(255) | utf8mb3_general_ci | YES | >>>>> MUL | NULL | | select,insert,update,references | | >>>>> | id | varchar(36) | utf8mb3_general_ci | NO | >>>>> PRI | NULL | | select,insert,update,references | | >>>>> | name | varchar(255) | utf8mb3_general_ci | YES >>>>> | | NULL | | select,insert,update,references >>>>> | | >>>>> | network_id | varchar(36) | utf8mb3_general_ci | NO | >>>>> MUL | NULL | | select,insert,update,references | | >>>>> | mac_address | varchar(32) | utf8mb3_general_ci | NO >>>>> | | NULL | | select,insert,update,references >>>>> | | >>>>> | admin_state_up | tinyint(1) | NULL | NO >>>>> | | NULL | | select,insert,update,references >>>>> | | >>>>> | status | varchar(16) | utf8mb3_general_ci | NO >>>>> | | NULL | | select,insert,update,references >>>>> | | >>>>> | device_id | varchar(255) | utf8mb3_general_ci | NO | >>>>> MUL | NULL | | select,insert,update,references | | >>>>> | device_owner | varchar(255) | utf8mb3_general_ci | NO >>>>> | | NULL | | select,insert,update,references >>>>> | | >>>>> | standard_attr_id | bigint | NULL | NO | >>>>> UNI | NULL | | select,insert,update,references | | >>>>> | ip_allocation | varchar(16) | utf8mb3_general_ci | YES >>>>> | | NULL | | select,insert,update,references >>>>> | | >>>>> +------------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+ >>>>> >>>>> >>>>> in Bobcat the collation required for the new "porthints" table >>>>> is latin1_swedish_ci >>>>> >>>>> MariaDB [neutron]> show create table porthints; >>>>> +-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ >>>>> >>>>> | Table | Create Table | >>>>> +-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ >>>>> >>>>> | porthints | CREATE TABLE `porthints` ( >>>>> `port_id` varchar(36) NOT NULL, >>>>> `hints` varchar(4095) NOT NULL, >>>>> PRIMARY KEY (`port_id`), >>>>> CONSTRAINT `porthints_ibfk_1` FOREIGN KEY (`port_id`) >>>>> REFERENCES `ports` (`id`) ON DELETE CASCADE >>>>> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci | >>>>> >>>>> So, we have tried to create the porthints table in mysql, >>>>> forcing the collation to utf8mb3_general_ci >>>>> >>>>> mysql> CREATE TABLE porthints ( port_id VARCHAR(36) NOT NULL, >>>>> hints VARCHAR(4095) NOT NULL, PRIMARY KEY (port_id), FOREIGN >>>>> KEY(port_id) REFERENCES ports (id) ON DELETE CASCADE >>>>> )ENGINE=InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE >>>>> utf8mb3_general_ci; >>>>> and commenting out the creation of the new table in the neutron >>>>> code >>>>> /usr/lib/python3.9/site-packages/neutron/db/migration/alembic_migrations/versions/2023.2/expand/6f1145bff34c_port_hints.py. >>>>> >>>>> >>>>> In this way, the upgrade works, but we encounter the same kind >>>>> of problem during the upgrade to Caracal, specifically with the >>>>> creation of the new table porthardwareoffloadtype. >>>>> >>>>> Have you already encountered this issue during the Neutron >>>>> upgrade? >>>>> >>>>> Do you have any suggestions on how to upgrade the neutron DB, >>>>> without manually creating new tables? >>>>> >>>>> Thanks a lot, >>>>> >>>>> cheers, >>>>> >>>>> Federica >>>>> >>>> >>>> -- Federica Fanzago >>>> INFN Sezione di Padova >>>> Via Marzolo, 8 >>>> 35131 Padova - Italy >>>> >>>> Tel: +39 049.967.7367 >>>> -- >>>> >>> >>> -- >>> Federica Fanzago >>> INFN Sezione di Padova >>> Via Marzolo, 8 >>> 35131 Padova - Italy >>> >>> Tel: +39 049.967.7367 >>> -- >> >> >> -- >> Federica Fanzago >> INFN Sezione di Padova >> Via Marzolo, 8 >> 35131 Padova - Italy >> >> Tel: +39 049.967.7367 >> -- > > >
-- Federica Fanzago INFN Sezione di Padova Via Marzolo, 8 35131 Padova - Italy Tel: +39 049.967.7367 --
participants (4)
-
Eugen Block
-
federica fanzago
-
Jay Faulkner
-
Rodolfo Alonso Hernandez