Zabbix supports utf8mb4 starting from 6.0 (ZBXNEXT-3706). MySQL supports utf8mb4 starting from =>5.5.3 series (2010), so schema can be converted in-place before moving data or using the same server.

Old databases should be checked/prepared/converted accordingly by DBA:
https://www.zabbix.com/documentation/6.0/en/manual/appendix/install/db_charset_coll
https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-conversion.html

Regards,
Edgar

I tryed converting the database using the script provided. It executed successfully, converted all tables.

Starting zabbix_server 6.0.3 after that, still fails with the same error:

655424:20220418:142124.670 completed 13% of database upgrade
655424:20220418:142128.759 [Z3005] query failed: [1071] Specified key was too long; max key length is 3072 bytes [create index items_1 on items (hostid,key_(1021))]
655424:20220418:142128.760 database upgrade failed

The problem seems to be in src/libs/zbxdbupgrade/dbuprgade_4050.c:

static int    DBpatch_4050004(void)
{
#ifdef HAVE_MYSQL
    return DBcreate_index("items", "items_1", "hostid,key_(1021)", 0);
#else
    return DBcreate_index("items", "items_1", "hostid,key_", 0);
#endif
}

It should be:

static int    DBpatch_4050004(void)
{
#ifdef HAVE_MYSQL
    return DBcreate_index("items", "items_1", "hostid,key_(768)", 0);
#else
    return DBcreate_index("items", "items_1", "hostid,key_", 0);
#endif
}

It is also present in dbupgrade_5030.c:

static int    DBpatch_5030170(void)
{
#ifdef HAVE_MYSQL
    return DBcreate_index("items", "items_8", "key_(1024)", 0);
#else
    return DBcreate_index("items", "items_8", "key_", 0);
#endif
}

It should be:

static int    DBpatch_5030170(void)
{
#ifdef HAVE_MYSQL
    return DBcreate_index("items", "items_8", "key_(768)", 0);
#else
    return DBcreate_index("items", "items_8", "key_", 0);
#endif
}

It seems to work if:

• Initial database migration is done in utf8 (utf8mb3)
• start zabbix_server and let it upgrade the schema
• execute utf8mb4 conversion

As the db patch in dbupgrade_5050.c drops items_1 index and recreate it correctly with hostid,key_(764) and also drops index items_8 and recreate it key_(768)

Yes, correct. 5.0 knows only utf8mb3, it looks like we need provide guideline in our documentation.

Updated documentation for executing utfmb4 conversion mentions in a note the need to upgrade the server and database schema to 6.0.x before attempting utfmb4 conversion.

Additional improvement: "What's new" for 6.0 now points to the instructions for utf8mb4 conversion.

Hi,

We have same issue. After Zabbix upgrade from 3.4.9 to 6.0.18 database conversion failed with below error,

    [Z3005] query failed: [1071] Specified key was too long; max key length is 3072 bytes [alter table `items` modify `key_` varchar(2048) default '' not null].

We have default database schema is latin1_swedish_ci. We have directly upgraded Zabbix from 3.4.9 to 6.0.18 without any issue.  After Zabbix upgrade tried to execute utf8mb4 conversion but table conversion failed with above error,   

   [Z3005] query failed: [1071] Specified key was too long; max key length is 3072 bytes [alter table `items` modify `key_` varchar(2048) default '' not null]. 

https://www.zabbix.com/documentation/6.0/en/manual/appendix/install/db_charset_coll

Executed below command and then ran the database migration again. Database migration completed without any issue.  

ALTER TABLE zabbix.items MODIFY COLUMN key_ VARCHAR(764) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin not null default ' ';

Query is modifying table `items` modify `key_` varchar(2048) to key_ VARCHAR(764) will cause any issue to Zabbix database? Any help appreciated. 

 Mysql version  8.0.33
zabbix version 6.0.18