Ad Widget

**ace.cominder** · 26-07-2023, 23:38

After investigating the database content I have found quite strange facts
(at least 2 tables affected: zabbix.scripts and zabbix.interface. may be more).
1) when do query against indexed field - database return correct records like:
-- select scriptid, name from zabbix.scripts;
+----------+-------------------------+
| scriptid | name |
+----------+-------------------------+
| 3 | Detect operating system |
| 1 | Ping |
| 2 | Traceroute |
+----------+-------------------------+
-- select hostid, type from zabbix.interface LIMIT 7;
+--------+------+
| hostid | type |
+--------+------+
| 10084 | 1 |
| 10110 | 1 |
| 10124 | 1 |
| 10125 | 1 |
| 10126 | 1 |
| 10126 | 2 |
| 10132 | 1 |
+--------+------+

2) when SQL query have not indexed fields or all (*) - the database returns wrong data like:
-- select * from zabbix.scripts;
+----------+------+-------------------------+-------------+---------------------+---------+----------------------------------------+--------------+------+------------+---------+
| scriptid | name | command | host_access | usrgrpid | groupid | description | confirmation | type | execute_on | timeout |
+----------+------+-------------------------+-------------+---------------------+---------+----------------------------------------+--------------+------+------------+---------+
| 1 | 30s | Ping | -1352504978 | NULL | NULL | /ping -c 3 {HOST.CONN} 2>&1? | | 0 | 1 | 30s |
| 2 | 30s | Traceroute | -1351257230 | NULL | NULL | /bin/traceroute {HOST.CONN} 2>&1? | | 0 | 1 | 30s |
| 3 | 30s | Detect operating system | -210410385 | 2319201471816491625 | NULL | n/nmap -O {HOST.CONN} 2>&1? | | 0 | 1 | 30s |
+----------+------+-------------------------+-------------+---------------------+---------+----------------------------------------+--------------+------+------------+---------+
-- select hostid, interfaceid, useip, dns, ip from zabbix.interface LIMIT 7;
+---------------------+-------------+-------+---------------+---------------+
| hostid | interfaceid | useip | dns | ip |
+---------------------+-------------+-------+---------------+---------------+
| 2147483648 | 1 | 1 | 27.0.0.1159.1 | ? ? 1 |
| 140533477408768 | 11 | 1 | 68.0.129192.1 | ? ? 192.1 |
| 2147483648 | 36 | 1 | 16.134.2159.1 | ? ? 172. |
| 2147483648 | 39 | 1 | 68.0.131192.1 | ? ? 192.1 |
| 8055761505724923904 | 42 | 1 | 68.0.12691.19 | ? ? 192.1 |
| 2147483648 | 43 | 1 | | ? ? 1 |
| 864855653619859456 | 54 | 1 | 16.134.3159.1 | ? ? 172. |
+---------------------+-------------+-------+---------------+---------------+

As you can see in scripts table name field have value 30s for all 3 rows.
This is cause for [1062] Duplicate entry '30s' for key 'scripts.scripts_3' message in zabbix_server.log
I am not sure how it was possible to update data in table with index unaffected. It looks like it was done is some wrong way by upgrade routines.

You can also see that 'name' values are now in 'command' field, but part of the 'command' values now in 'description'
The root cause is data in table is corrupted by upgrade routines (You can see garbage numbers instead of IP address for Ip and DNS).
I have checked the tables content just after MySQL db upgrade (from 5.7.32 to 8.0.33), before starting zabbix_server service - data was correct in all queries.

3) I was able to fix scripts table manually by UPDATE statements with correct values.
This can help to finish upgrade, but at the end we can see plenty of errors like:
item "REALHOSTNAME:system.swap.size[,free]" became not supported: Invalid port number []

Even this can allow upgrade completed (tested) - in GUI.WUI we see NO interfaces in any host, just because interface table is corrupted and not fixed.

I have tested this 3 times (2 times with 6.4.4 version and 1 with 6.0.19 - using MySQL database restore from cold backup).
More details about the system and upgrade scope will be provided in next comment.

**ace.cominder** · 26-07-2023, 23:58

Upgrade scope:

--- Old system ---
OS: Ubuntu 20.04.1.LTS
Kernel: Linux 5.4.0-66-generic x86_64
MySQL: 5.7.32
Zabbix Server: 5.2.3

--- Upgraded system ---
OS: Ubuntu 22.04.2 LTS
Kernel: Linux 5.15.0-76-generic x86_64
MySQL: 8.0.33 (8.0.33-0ubuntu0.22.04.2)
Zabbix Server: 6.4.4 or 6.0.19

Note:
* Linux, MySQL and other components upgrade executed without issues.
* MySQL DB upgrade was done in-place (as result we have found automatic changes of charactersets: from CHARSET=utf8 COLLATE=utf8_bin to CHARSET=utf8mb3 COLLATE=utf8mb3_bin
* Zabbix upgrade to 6.4.4 was done with in-place upgrade.
* After problem has been observed I have restored DB from backup, deinstalled all zabbix packages of 6.4.4 and installed 6.0.19. Then started service, which made the same issue.

This is in TEST env. (VM cloned from prod.) So I can compare tables. Can do export/import of 2 corrupted tables from old system.
This could be some kind of workaround.
But I am not sure other tables have no similar issues.
So it is wanted to find out the roout cause and fix (this is required for me and other Zabbix users, which may experience the same trouble).

Any assitance is welcome!

**ace.cominder** · 27-07-2023, 12:28

I have compared the definitions of the tables before and after upgrades:
# --

ld: (in source system: MySQL 5.7.32, Zabbix 5.2.3)
| interface | CREATE TABLE `interface` (
`interfaceid` bigint(20) unsigned NOT NULL,
`hostid` bigint(20) unsigned NOT NULL,
`main` int(11) NOT NULL DEFAULT '0',
`type` int(11) NOT NULL DEFAULT '1',
`useip` int(11) NOT NULL DEFAULT '1',
`ip` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '127.0.0.1',
`dns` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`port` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '10050',
PRIMARY KEY (`interfaceid`),
KEY `interface_1` (`hostid`,`type`),
KEY `interface_2` (`ip`,`dns`),
CONSTRAINT `c_interface_1` FOREIGN KEY (`hostid`) REFERENCES `hosts` (`hostid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |

#--:new-mysql: (after mysql DB upgrade from 5.7.32 to 8.0.33)
| interface | CREATE TABLE `interface` (
`interfaceid` bigint unsigned NOT NULL,
`hostid` bigint unsigned NOT NULL,
`main` int NOT NULL DEFAULT '0',
`type` int NOT NULL DEFAULT '1',
`useip` int NOT NULL DEFAULT '1',
`ip` varchar(64) COLLATE utf8mb3_bin NOT NULL DEFAULT '127.0.0.1',
`dns` varchar(255) COLLATE utf8mb3_bin NOT NULL DEFAULT '',
`port` varchar(64) COLLATE utf8mb3_bin NOT NULL DEFAULT '10050',
PRIMARY KEY (`interfaceid`),
KEY `interface_1` (`hostid`,`type`),
KEY `interface_2` (`ip`,`dns`),
CONSTRAINT `c_interface_1` FOREIGN KEY (`hostid`) REFERENCES `hosts` (`hostid`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin |

#--:new-zabbox: (after zabbix upgrade started to Zabbix 6.4.4)
| interface | CREATE TABLE `interface` (
`interfaceid` bigint unsigned NOT NULL,
`hostid` bigint unsigned NOT NULL,
`main` int NOT NULL DEFAULT '0',
`type` int NOT NULL DEFAULT '1',
`useip` int NOT NULL DEFAULT '1',
`ip` varchar(64) COLLATE utf8mb3_bin NOT NULL DEFAULT '127.0.0.1',
`dns` varchar(255) COLLATE utf8mb3_bin NOT NULL DEFAULT '',
`port` varchar(64) COLLATE utf8mb3_bin NOT NULL DEFAULT '10050',
`available` int NOT NULL DEFAULT '0',
`error` varchar(2048) COLLATE utf8mb3_bin NOT NULL DEFAULT '',
`errors_from` int NOT NULL DEFAULT '0',
`disable_until` int NOT NULL DEFAULT '0',
PRIMARY KEY (`interfaceid`),
KEY `interface_1` (`hostid`,`type`),
KEY `interface_2` (`ip`,`dns`),
KEY `interface_3` (`available`),
CONSTRAINT `c_interface_1` FOREIGN KEY (`hostid`) REFERENCES `hosts` (`hostid`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin |

Note: after all upgraded the (default) characterset and collation of the database is utf8mb4 (not the utf8mb3! or latin1 as it was before upgrades)
SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8mb4 | utf8mb4_0900_ai_ci |
+--------------------------+----------------------+
Note: it was not UTF8 before DB upgrade (in old system):
+--------------------------+----------------------+
| latin1 | latin1_swedish_ci |
+--------------------------+----------------------+

**ace.cominder** · 27-07-2023, 12:34

I have also compared the records. For example for interface table - just some rows:
* in source db:
select interfaceid, ip, dns from zabbix.interface order by interfaceid LIMIT 3;
+-------------+---------------+---------------+
| interfaceid | ip | dns |
+-------------+---------------+---------------+
| 1 | 127.0.0.1 | 777.888.60.20 |
| 11 | 192.168.0.129 | 192.168.0.133 |
| 36 | 172.16.134.2 | 777.888.60.20 |

* in upgraded DB:
+-------------+---------------+---------------+
| interfaceid | ip | dns |
+-------------+---------------+---------------+
| 1 | ? ? 1 | 27.0.0.1777.8 |
| 11 | ? ? 192.1 | 68.0.129192.1 |
| 36 | ? ? 172. | 16.134.2777.8 |

We see that upgraded DB values are wrongly read from raw record (or has been wrongly updated during zabbix upgrade)
* OLD: | 36 | 172.16.134.2 | 777.888.60.20 |
* NEW:| 36 | ? ? 172. | 16.134.2777.8 |
part of DNS filed is constructed from part of IP field.

INFO: 777.888 is replacement of real IP part (just for security reason)
I suppose this is result of wrong length of field (or offset of field start in RAW data) calculated / used during data read from RAW record.
Make note: we have NOT done any manual characterset/collation updates/conversions before, during or after upgrade steps instructed in documentation.

**ace.cominder** · 27-07-2023, 12:55

default_character_set_name for the zabbix database is:
-- in old (before upgrade): utf8
-- in new (after upgrade): utf8mb3

**ace.cominder** · 27-07-2023, 14:08

It looks like the problem is with MySQL:
* stopped all
* restored DB (zabbix and system databases of MySQL from 5.7)
* started MySQL (8.0.33)
* NOT started zabbix-server
* checked queries are executed and returning CORRECT data:
--- select * from zabbix.scripts;
+----------+-------------------------+----------------------------------------+-------------+----------+---------+-------------+--------------+------+------------+
| scriptid | name | command | host_access | usrgrpid | groupid | description | confirmation | type | execute_on |
+----------+-------------------------+----------------------------------------+-------------+----------+---------+-------------+--------------+------+------------+
| 1 | Ping | /bin/ping -c 3 {HOST.CONN} 2>&1 | 2 | NULL | NULL | | | 0 | 1 |
| 2 | Traceroute | /usr/bin/traceroute {HOST.CONN} 2>&1 | 2 | NULL | NULL | | | 0 | 1 |
| 3 | Detect operating system | sudo /usr/bin/nmap -O {HOST.CONN} 2>&1 | 2 | 7 | NULL | | | 0 | 1 |
+----------+-------------------------+----------------------------------------+-------------+----------+---------+-------------+--------------+------+------------+
3 rows in set (0.00 sec)

* executed the DDL: alter table zabbix.scripts add column `timeout` varchar(32) COLLATE utf8mb3_bin NOT NULL DEFAULT '30s';
* checked queries are executed and returning WRONG data:

--- select * from zabbix.scripts;
+----------+------+-------------------------+-------------+---------------------+---------+----------------------------------------+--------------+------+------------+---------+
| scriptid | name | command | host_access | usrgrpid | groupid | description | confirmation | type | execute_on | timeout |
+----------+------+-------------------------+-------------+---------------------+---------+----------------------------------------+--------------+------+------------+---------+
| 1 | 30s | Ping | -1352504978 | NULL | NULL | /ping -c 3 {HOST.CONN} 2>&1? | | 0 | 1 | 30s |
| 2 | 30s | Traceroute | -1351257230 | NULL | NULL | /bin/traceroute {HOST.CONN} 2>&1? | | 0 | 1 | 30s |
| 3 | 30s | Detect operating system | -210410385 | 2319201471816491625 | NULL | n/nmap -O {HOST.CONN} 2>&1? | | 0 | 1 | 30s |
+----------+------+-------------------------+-------------+---------------------+---------+----------------------------------------+--------------+------+------------+---------+
3 rows in set (0.00 sec)

So, it looks like after MySQL changed the characterset namings utf8 (alias for utf8mb3) to utf8mb3
altering tables may cause wrong data returned.

From MySQL docs:
https://dev.mysql.com/doc/refman/8.0...code-utf8.html
"The utf8mb3 character set is deprecated and you should expect it to be removed in a future MySQL release.
Please use utf8mb4 instead. utf8 is currently an alias for utf8mb3,
but it is now deprecated as such, and utf8 is expected subsequently to become a reference to utf8mb4.
Beginning with MySQL 8.0.28, utf8mb3 is also displayed in place of utf8 in columns of Information Schema tables, and in the output of SQL SHOW statements."

Advise is wanted.

Taking into account Zabbix new version is required MySQL 8.0.30+ this can be an issue for Zabbix typical upgrade (when both database and Zabbix are upgraded at the same time).

**ace.cominder** · 22-08-2023, 14:16

Status update: problem with MySQL is fixed in mysql-server 8.0.34 on Ubuntu 22.04.3 LTS
Checked by making manual changes to the tables. Not tested yet by Zabbix upgrade procedure.

It looks like it can be Bug #35410528:

An in-place upgrade from MySQL 5.7 to MySQL 8.0, without a server restart, could result in unexpected errors when executing queries on tables. This fix eliminates the need to restart the server between the upgrade and queries.

src: https://dev.mysql.com/doc/relnotes/m...ws-8-0-34.html

Or some other problem related to row record fiels start/end positioning.

Anyway - this is exact MySQL version 8.0.33 and/or platform (Ubuntu 22.04.2 LTS).

I would recommend the Zabbxi team to update MySQL requirements.

Ad Widget

Interface, scripts tables content corrupted during upgrade to 6.4.4 or 6.0.19 (LTS).

Interface, scripts tables content corrupted during upgrade to 6.4.4 or 6.0.19 (LTS).

Comment

Comment

Comment

Comment

Comment

Comment

Comment