Проблема при апгрейде с 4.0.6 до 4.2

[inkostin]

It would be nice to see create table after failed upgrade, if not too much trouble:

show create table lld_macro_path;

Also please do:

SELECT @@character_set_database, @@collation_database;

Can also try test table manually and seeing if it succeeds:

CREATE TABLE `lld_macro_path` (`lld_macro` varchar(255) DEFAULT '' NOT NULL) ENGINE=InnoDB;

CREATE UNIQUE INDEX `lld_macro_path_1` ON `lld_macro_path` (`lld_macro`);

drop table lld_macro_path;

Upgrade to 4.2:

Database changed
MariaDB [zabbix]> show create table lld_macro_path;
+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| lld_macro_path | CREATE TABLE `lld_macro_path` (
`lld_macro_pathid` bigint(20) unsigned NOT NULL,
`itemid` bigint(20) unsigned NOT NULL,
`lld_macro` varchar(255) NOT NULL DEFAULT '',
`path` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`lld_macro_pathid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [zabbix]> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8mb4 | utf8mb4_general_ci |
+--------------------------+----------------------+
1 row in set (0.00 sec)

MariaDB [zabbix]> CREATE TABLE `lld_macro_path` (`lld_macro` varchar(255) DEFAULT '' NOT NULL) ENGINE=InnoDB;
ERROR 1050 (42S01): Table 'lld_macro_path' already exists

MariaDB [zabbix]> CREATE UNIQUE INDEX `lld_macro_path_1` ON `lld_macro_path` (`lld_macro`);
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

MariaDB [zabbix]> drop table lld_macro_path;
Query OK, 0 rows affected (0.05 sec)

MariaDB [zabbix]> CREATE TABLE `lld_macro_path` (`lld_macro` varchar(255) DEFAULT '' NOT NULL) ENGINE=InnoDB;
Query OK, 0 rows affected (0.08 sec)

MariaDB [zabbix]> CREATE UNIQUE INDEX `lld_macro_path_1` ON `lld_macro_path` (`lld_macro`);
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
MariaDB [zabbix]> exit
Bye

767 bytes is the stated prefix limitation for InnoDB tables in MySQL version 5.6 (and prior versions). It's 1,000 bytes long for MyISAM tables. In MySQL version 5.7 and upwards this limit has been increased to 3072 bytes.

You also have to be aware that if you set an index on a big char or varchar field which is utf8mb4 encoded, you have to divide the max index prefix length of 767 bytes (or 3072 bytes) by 4 resulting in 191. This is because the maximum length of a utf8mb4 character is four bytes. For a utf8 character it would be three bytes resulting in max index prefix length of 254.

One option you have is to just place lower limit on your VARCHAR fields.

[vso]

I am sorry but character set utf8 and utf8_bin collation is required for Zabbix server to work properly with MySQL database.:
https://www.zabbix.com/documentation...all/db_scripts
https://www.zabbix.com/documentation..._from_packages
https://www.zabbix.com/documentation...rade_notes_220

Пожалуйста сконвертируйте базу данных в UTF8 так как UTF8mb4 пока не поддерживается.

[inkostin]

I am sorry but character set utf8 and utf8_bin collation is required for Zabbix server to work properly with MySQL database.:
https://www.zabbix.com/documentation...all/db_scripts
https://www.zabbix.com/documentation..._from_packages
https://www.zabbix.com/documentation...rade_notes_220

Пожалуйста сконвертируйте базу данных в UTF8 так как UTF8mb4 пока не поддерживается.

Подскажите, как правильно это сделать!

[vso]

Подскажите, как правильно это сделать!

I am sure that there are a lot specialists here who could advice, I would try:
1. Backup
2. Check current settings

SELECT @@character_set_database, @@collation_database;

3. Convert

alter database zabbix character set utf8 collate utf8_bin;

4. Upgrade Zabbix server

[inkostin]

I am sure that there are a lot specialists here who could advice, I would try:
1. Backup
2. Check current settings

SELECT @@character_set_database, @@collation_database;

3. Convert

alter database zabbix character set utf8 collate utf8_bin;

4. Upgrade Zabbix server

Ok.Everything is good.

[vso]

Супер! Дайте знать если будут ещё какие-то проблемы, вы не знаете как могло так получится что база стала UTF8mb4 ?

[inkostin]

Супер! Дайте знать если будут ещё какие-то проблемы, вы не знаете как могло так получится что база стала UTF8mb4 ?

Не знаю...