Ironic dealt with a bug years ago that appears related:
https://opendev.org/openstack/ironic/commit/1435a15ce3013da0a3138e1c5ab6ac523c382bb8
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
>> --
>
>
>