zabbix.proxy_dhistory doesn't exist upgrade from 4 to 5

[registration_is_lame]

The 4 to 5 upgrade according to the documentation should go smoothly however I get the following error:

tail -f /var/log/zabbix/zabbix_server.log
3298:20210617:223901.794 current database version (mandatory/optional): 04000000/04000003
3298:20210617:223901.795 required mandatory version: 05040000
3298:20210617:223901.795 starting automatic database upgrade
3298:20210617:223901.856 completed 0% of database upgrade
3298:20210617:223902.148 completed 1% of database upgrade
3298:20210617:223903.286 completed 2% of database upgrade
3298:20210617:223904.614 completed 3% of database upgrade
3298:20210617:223904.922 completed 4% of database upgrade
3298:20210617:223905.364 [Z3005] query failed: [1146] Table 'zabbix.proxy_dhistory' doesn't exist [create index proxy_dhistory_2 on proxy_dhistory (druleid)]
3298:20210617:223905.364 database upgrade failed

How to fix this?

[registration_is_lame]

this is absolutely terrible that the promised auto db upgrade process is not working.

I have downloaded the latest 5.4 Zabbix source code and created a clean DB and loaded the base:

data.sql 100% 16MB 1.4MB/s 00:11
images.sql 100% 1932KB 1.5MB/s 00:01
schema.sql 100% 154KB 177.9KB/s 00:00

With this of course it starts up as it should. Now I used mysql to import the old 4.X dump into this DB, this way the missing tables remained however now I get:

4226:20210618:180354.157 [Z3005] query failed: [1050] Table 'lld_macro_path' already exists [create table lld_macro_path (
`lld_macro_pathid` bigint unsigned not null,
`itemid` bigint unsigned not null,
`lld_macro` varchar(255) default '' not null,
`path` varchar(255) default '' not null,
primary key (lld_macro_pathid)
) engine=innodb]
4226:20210618:180354.157 database upgrade failed

The DB upgrade from 4 to 5 should go without problems why ((

[Markku]

According to https://zabbix.org/wiki/Docs/DB_sche...proxy_dhistory the proxy_dhistory table should already exist so I guess the problem is not in the 5.4 upgrade but somewhere else. What's the history of your installation?

Markku

[registration_is_lame]

It is a clean 4.0.1 install, it was never upgraded.
Zabbix 4.0.1. © 2001–2018, Zabbix SIA

Here are all the tables:

mysql> show tables;
+----------------------------+
| Tables_in_zabbix |
+----------------------------+
| acknowledges |
| actions |
| alerts |
| application_discovery |
| application_prototype |
| application_template |
| applications |
| auditlog |
| auditlog_details |
| autoreg_host |
| conditions |
| config |
| corr_condition |
| corr_condition_group |
| corr_condition_tag |
| corr_condition_tagpair |
| corr_condition_tagvalue |
| corr_operation |
| correlation |
| dashboard |
| dashboard_user |
| dashboard_usrgrp |
| dbversion |
| dchecks |
| dhosts |
| drules |
| dservices |
| escalations |
| event_recovery |
| event_suppress |
| event_tag |
| events |
| expressions |
| functions |
| globalmacro |
| globalvars |
| graph_discovery |
| graph_theme |
| graphs |
| graphs_items |
| group_discovery |
| group_prototype |
| history |
| history_log |
| history_str |
| history_text |
| history_uint |
| host_discovery |
| host_inventory |
| hostmacro |
| hosts |
| hosts_groups |
| hosts_templates |
| housekeeper |
| hstgrp |
| httpstep |
| httpstep_field |
| httpstepitem |
| httptest |
| httptest_field |
| httptestitem |
| icon_map |
| icon_mapping |
| ids |
| images |
| interface |
| interface_discovery |
| item_application_prototype |
| item_condition |
| item_discovery |
| item_preproc |
| items |
| items_applications |
| maintenance_tag |
| maintenances |
| maintenances_groups |
| maintenances_hosts |
| maintenances_windows |
| mappings |
| media |
| media_type |
| opcommand |
| opcommand_grp |
| opcommand_hst |
| opconditions |
| operations |
| opgroup |
| opinventory |
| opmessage |
| opmessage_grp |
| opmessage_usr |
| optemplate |
| problem |
| problem_tag |
| profiles |
| proxy_autoreg_host |
| regexps |
| rights |
| screen_user |
| screen_usrgrp |
| screens |
| screens_items |
| scripts |
| service_alarms |
| services |
| services_links |
| services_times |
| sessions |
| slides |
| slideshow_user |
| slideshow_usrgrp |
| slideshows |
| sysmap_element_trigger |
| sysmap_element_url |
| sysmap_shape |
| sysmap_url |
| sysmap_user |
| sysmap_usrgrp |
| sysmaps |
| sysmaps_elements |
| sysmaps_link_triggers |
| sysmaps_links |
| tag_filter |
| task |
| task_acknowledge |
| task_check_now |
| task_close_problem |
| task_remote_command |
| task_remote_command_result |
| timeperiods |
| trends |
| trends_uint |
| trigger_depends |
| trigger_discovery |
| trigger_tag |
| triggers |
| users |
| users_groups |
| usrgrp |
| valuemaps |
| widget |
| widget_field |
+----------------------------+
142 rows in set (0.00 sec)

[Markku]

Since you already showed skills with sql templates and source packages, you may want to check the 4.0.1 sources if there is anything strange while creating the tables. Or, if you are not using proxies, you may want to create one and see if it affects the missing table, or you could try upgrading to latest 4.0.x version and see how it goes. Or you could open a ticket at support.zabbix.com if you think everything has been done by the book and there still are problems.

Markku

[registration_is_lame]

CREATE TABLE `proxy_dhistory` (
        `id`                     bigint unsigned                           NOT NULL auto_increment,
        `clock`                  integer         DEFAULT '0'               NOT NULL,
        `druleid`                bigint unsigned                           NOT NULL,
        `ip`                     varchar(39)     DEFAULT ''                NOT NULL,
        `port`                   integer         DEFAULT '0'               NOT NULL,
        `value`                  varchar(255)    DEFAULT ''                NOT NULL,
        `status`                 integer         DEFAULT '0'               NOT NULL,
        `dcheckid`               bigint unsigned                           NULL,
        `dns`                    varchar(255)    DEFAULT ''                NOT NULL,
        PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE `proxy_history` (
        `id`                     bigint unsigned                           NOT NULL auto_increment,
        `itemid`                 bigint unsigned                           NOT NULL,
        `clock`                  integer         DEFAULT '0'               NOT NULL,
        `timestamp`              integer         DEFAULT '0'               NOT NULL,
        `source`                 varchar(64)     DEFAULT ''                NOT NULL,
        `severity`               integer         DEFAULT '0'               NOT NULL,
        `value`                  longtext                                  NOT NULL,
        `logeventid`             integer         DEFAULT '0'               NOT NULL,
        `ns`                     integer         DEFAULT '0'               NOT NULL,
        `state`                  integer         DEFAULT '0'               NOT NULL,
        `lastlogsize`            bigint unsigned DEFAULT '0'               NOT NULL,
        `mtime`                  integer         DEFAULT '0'               NOT NULL,
        `flags`                  integer         DEFAULT '0'               NOT NULL,
        `write_clock`            integer         DEFAULT '0'               NOT NULL,
        PRIMARY KEY (id)
) ENGINE=InnoDB;

ALTER TABLE proxy_history DROP COLUMN write_clock;

After this it finally started up and "converted" 100% of it ... or NOT?


During the frontend setup I got:

Unsupported charset or collation for tables: acknowledges, actions, alerts, auditlog, auditlog_details, autoreg_host, conditions, config, corr_condition_tag, corr_condition_tagpair, corr_condition_tagvalue, correlation, dashboard, dchecks, drules, dservices, event_tag, events, expressions, functions, globalmacro, graph_theme, graphs, graphs_items, group_discovery, group_prototype, history_log, history_str, history_text, host_discovery, hostmacro, hosts, housekeeper, hstgrp, httpstep, httpstep_field, httptest, httptest_field, icon_map, icon_mapping, ids, images, interface, item_condition, item_discovery, item_preproc, items, maintenance_tag, maintenances, media, media_type, opconditions, operations, opmessage, problem, problem_tag, profiles, proxy_autoreg_host, regexps, scripts, services, services_times, sessions, sysmap_element_url, sysmap_shape, sysmap_url, sysmaps, sysmaps_elements, sysmaps_link_triggers, sysmaps_links, tag_filter, task_remote_command, task_remote_command_result, trigger_tag, triggers, users, usrgrp, widget, widget_field.

For this I have followed this guide to fix the collation:

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

After this finally the frontend install finished then I got an empty page with error on the backend:

2021/06/19 18:55:33 [error] 5303#5303: *78 FastCGI sent in stderr: "PHP message: PHP Notice:  Undefined offset: 0 in /usr/share/webapps/zabbix/include/classes/user/CWebUser.php on line 211PHP message: PHP Notice:  Trying to access array offset on value of type null in /usr/share/webapps/zabbix/include/classes/user/CWebUser.php on line 211PHP message: PHP Notice:  Undefined offset: 0 in /usr/share/webapps/zabbix/include/classes/user/CWebUser.php on line 212PHP message: PHP Notice:  Trying to access array offset on value of type null in /usr/share/webapps/zabbix/include/classes/user/CWebUser.php on line 212PHP message: PHP Fatal error:  Uncaught TypeError: Argument 1 passed to getUserGuiAccess() must be of the type string, null given, called in /usr/share/webapps/zabbix/include/classes/user/CWebUser.php on line 212 and defined in /usr/share/webapps/zabbix/include/perm.inc.php:98
Stack trace:
#0 /usr/share/webapps/zabbix/include/classes/user/CWebUser.php(212): getUserGuiAccess()
#1 /usr/share/webapps/zabbix/index.php(100): CWebUser::isGuestAllowed()
#2 {main}

Also from the backend I keep getting error logs like:

  5831:20210619:200342.910 [Z3005] query failed: [1452] Cannot add or update a child row: a foreign key constraint fails (`zabbix`.`event_recovery`, CONSTRAINT `c_event_recovery_1` FOREIGN KEY (`eventid`) REFERENCES `events` (`eventid`) ON DELETE CASCADE) [insert into event_recovery (eventid,r_eventid,correlationid,c_eventid,userid) values (4160594,340,null,null,null),(4160593,339,null,null,null),(4160595,341,null,null,null);
]
  5831:20210619:200442.983 [Z3005] query failed: [1452] Cannot add or update a child row: a foreign key constraint fails (`zabbix`.`event_recovery`, CONSTRAINT `c_event_recovery_1` FOREIGN KEY (`eventid`) REFERENCES `events` (`eventid`) ON DELETE CASCADE) [insert into event_recovery (eventid,r_eventid,correlationid,c_eventid,userid) values (4160594,344,null,null,null),(4160593,343,null,null,null),(4160595,345,null,null,null);
]
  5831:20210619:200543.091 [Z3005] query failed: [1452] Cannot add or update a child row: a foreign key constraint fails (`zabbix`.`event_recovery`, CONSTRAINT `c_event_recovery_1` FOREIGN KEY (`eventid`) REFERENCES `events` (`eventid`) ON DELETE CASCADE) [insert into event_recovery (eventid,r_eventid,correlationid,c_eventid,userid) values (4160594,348,null,null,null),(4160593,347,null,null,null),(4160595,349,null,null,null);
]
  5831:20210619:200643.165 [Z3005] query failed: [1452] Cannot add or update a child row: a foreign key constraint fails (`zabbix`.`event_recovery`, CONSTRAINT `c_event_recovery_1` FOREIGN KEY (`eventid`) REFERENCES `events` (`eventid`) ON DELETE CASCADE) [insert into event_recovery (eventid,r_eventid,correlationid,c_eventid,userid) values (4160594,352,null,null,null),(4160593,351,null,null,null),(4160595,353,null,null,null);
]
  5831:20210619:200743.248 [Z3005] query failed: [1452] Cannot add or update a child row: a foreign key constraint fails (`zabbix`.`event_recovery`, CONSTRAINT `c_event_recovery_1` FOREIGN KEY (`eventid`) REFERENCES `events` (`eventid`) ON DELETE CASCADE) [insert into event_recovery (eventid,r_eventid,correlationid,c_eventid,userid) values (4160594,356,null,null,null),(4160593,355,null,null,null),(4160595,357,null,null,null);

This is seriously a dumpster fire. I might as well just going to give up with Zabbix 5 and wait until the 6 comes out...