Specified key was too long; max key length is 3072 bytes

[lbm]

trying to change charset+collations, but I get the following error. Specified key was too long; max key length is 3072 bytes

SELECT @@VERSION;
+-----------+
| @@VERSION |
+-----------+
| 8.0.29 |
+-----------+
1 row in set (0.00 sec)

mysql> use zabbixdb;
Database changed
mysql> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8mb4 | utf8mb4_bin |
+--------------------------+----------------------+
1 row in set (0.00 sec)

mysql> CALL zbx_convert_utf8();
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
mysql>

refs:
https://www.zabbix.com/documentation...b_charset_coll

[tim.mooney]

I can't speak for MySQL 8.x since my site's Zabbix install is using MariaDB as the backend, but at least with MariaDB it was necessary to alter the ROW_FORMAT before making certain column changes.

See the section of the Known Issues for 5.0 , related to "Upgrade with MySQL below 5.7.0 or MariaDB below 10.2.2"

This generally only applies to databases that have been upgraded from older versions of MySQL/MariaDB. If your database was created fresh with MySQL 8.0.29, even if it was recreated by loading a mysql dump from an earlier version, it should have defaulted to the newer ROW_FORMAT.

Look at the issues referenced in the Known Issues and follow the instructions for updating the ROW_FORMAT for all tables (if necessary).

[lbm]

Thanks. I can see that the problem, could be similar to MySQL: https://support.zabbix.com/browse/ZBX-20165 , but unfortunately thats not the issue. All the tables are Dynamic.

[lbm]

I found this. https://support.zabbix.com/browse/ZBX-20905

Schema upgrades needs to done first, before doing anything else it seems, I was able to circumvent the issue when upgrading from 5.4 to 6.0.

1. Upgrade zabbix from 5.4 to 6.0, and restart the zabbix-server service, if it does not do this by itself (it should). This is an important step, since it will upgrade table schemas, etc. It you are doing the UTF8MB3 to UTF8MB4 conversion first, you will get yourself into trouble.
2. stop the zabbix-service service
3. import the utf8mb4_convert.sql script and run the stored procedure, according to the documentation link above:
4. start the zabbix-server again.

I had this issue on an cloned system, where I test the zabbix upgrade, before doing in production. If you are stuck here, on an "prod" env, im not 100% sure how to fix it, But it seems that the order is very important, and the risk of race-condition is pretty big.
I would GUESS, that it can be fixed by reverting to the UTF8MB4 to UTF8MB3 charset+collation, and restart restart the-zabbix server, so zabbix-server can execute the schema-changes first, and then the stored procedure will work.