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> 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 
--


-- 
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 
--