Once on Ubuntu Server 18.04 I upgraded Zabbix 4.4 to 5.0 and found an error in the logs during the upgrade:
current database version (mandatory/optional): 04050003/04050003
required mandatory version: 05000000
optional patches were found
starting automatic database upgrade
[Z3005] query failed: [1071] Specified key was too long; max key length is 767 bytes [create index items_1 on items (hostid,key_(1021))]
database upgrade failed
In my case, the MariaDB 10.1.48 database server was used, and the minimum versions according to the Zabbix 5.0 requirements are as follows:
php 7.2
MySQL 5.5.62
MariaDB 10.0.37
PostgreSQL 9.2.24
Oracle 11.2
Fortunately, everything was fine with this, I checked with the commands:
php -v
mysql -v
To solve the error, I just executed the SQL query:
SET GLOBAL innodb_default_row_format='dynamic';
And enable innodb_large_prefix:
SELECT @@innodb_large_prefix;
set global innodb_large_prefix=on;
nano /etc/mysql/mariadb.conf.d/50-server.cnf
[mysqld]
innodb_large_prefix=1
And also, just in case, changed the encoding of the database and all tables to the one recommended in the official Zabbix documentation:
SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8mb4 | utf8mb4_general_ci |
+--------------------------+----------------------+
SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = "zabbix";
ALTER DATABASE zabbix CHARACTER SET utf8 COLLATE utf8_bin;
SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;') FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'zabbix';
After that, I restarted zabbix-server and the database was successfully updated:
service zabbix-server restart
See also my other articles:
About Zabbix
About MySQL