Problem creating web checks after upgrade from 4..x LTS to 5.0.22

[fredrikjansson]

Hello everyone,

I recently did an upgrade of a Zabbix server running the Zabbix 4.0 LTS release to the latest version of Zabbix 5 (5.0.22).
The upgrade was a bit of a tedious process as there was a few issues in the database preventing the upgrade from successfully being upgraded to the new version. These issues were resolved by the help of database queries and the server was started successfully on the new version.

Unfortunately one issue remains after the upgrade and that is that it is no longer possible to create "Web Checks". The creation of any web checks fails with an error message related to key constraints in the database:

Error in query [INSERT INTO httptest (hostid,name,authentication,applicationid,delay,retries,status,agent,http_proxy,http_user,http_password,verify_peer,verify_host,ssl_cert_file,ssl_key_file,ssl_key_password,templateid,httptestid) VALUES ('11001','HTTPD Check','0',NULL,'1m','5','0','Zabbix','','','','0','0','','','','1','2'),('11002','HTTPD Check','0',NULL,'1m','5','0','Zabbix','','','','0','0','','','','1','3'),('11003','HTTPD Check','0',NULL,'1m','5','0','Zabbix','','','','0','0','','','','1','4')] [Cannot add or update a child row: a foreign key constraint fails (`zabbix`.`httptest`, CONSTRAINT `c_httptest_2` FOREIGN KEY (`hostid`) REFERENCES `hosts` (`hostid`) ON DELETE CASCADE)] SQL statement execution has failed "INSERT INTO httptest (hostid,name,authentication,applicationid,delay,retries,status,agent,http_proxy,http_user,http_password,verify_peer,verify_host,ssl_cert_file,ssl_key_file,ssl_key_password,templateid,httptestid) VALUES ('11001','HTTPD Check','0',NULL,'1m','5','0','Zabbix','','','','0','0','','','','1','2'),('11002','HTTPD Check','0',NULL,'1m','5','0','Zabbix','','','','0','0','','','','1','3'),('11003','HTTPD Check','0',NULL,'1m','5','0','Zabbix','','','','0','0','','','','1','4')".

I have searched around for a bit and cannot find any relevant information in regards to this problem in the bug tracker or other sources.

Does anyone have any suggestions in regards how to resolve this problem?

Thanks in advance!

/Fredrik

[fredrikjansson]

Sorry for the bump, but does anyone have any idea in regards to this issue? I have been looking for a solution to this in vain.

Thanks!

[tim.mooney]

Hello everyone,

I recently did an upgrade of a Zabbix server running the Zabbix 4.0 LTS release to the latest version of Zabbix 5 (5.0.22).
The upgrade was a bit of a tedious process as there was a few issues in the database preventing the upgrade from successfully being upgraded to the new version. These issues were resolved by the help of database queries and the server was started successfully on the new version.

You can be pretty certain that the reason you're having the SQL error for web checks is that there's still something that didn't get fixed, so your database doesn't match the schema that is expected.

Alternately, it's possible that the (new) schema is correct, but there's something that was inserted previously that now violates the schema. That's less likely, I think, and I would expect that it would have been detected at upgrade time, but it's at least theoretically possible that it was not.

Unfortunately one issue remains after the upgrade and that is that it is no longer possible to create "Web Checks". The creation of any web checks fails with an error message related to key constraints in the database:

Error in query [INSERT INTO httptest (hostid,name,authentication,applicationid,delay,retries,status,agent,http_proxy,http_user,http_password,verify_peer,verify_host,ssl_cert_file,ssl_key_file,ssl_key_password,templateid,httptestid) VALUES ('11001','HTTPD Check','0',NULL,'1m','5','0','Zabbix','','','','0','0','','','','1','2'),('11002','HTTPD Check','0',NULL,'1m','5','0','Zabbix','','','','0','0','','','','1','3'),('11003','HTTPD Check','0',NULL,'1m','5','0','Zabbix','','','','0','0','','','','1','4')] [Cannot add or update a child row: a foreign key constraint fails (`zabbix`.`httptest`, CONSTRAINT `c_httptest_2` FOREIGN KEY (`hostid`) REFERENCES `hosts` (`hostid`) ON DELETE CASCADE)] SQL statement execution has failed "INSERT INTO httptest (hostid,name,authentication,applicationid,delay,retries,status,agent,http_proxy,http_user,http_password,verify_peer,verify_host,ssl_cert_file,ssl_key_file,ssl_key_password,templateid,httptestid) VALUES ('11001','HTTPD Check','0',NULL,'1m','5','0','Zabbix','','','','0','0','','','','1','2'),('11002','HTTPD Check','0',NULL,'1m','5','0','Zabbix','','','','0','0','','','','1','3'),('11003','HTTPD Check','0',NULL,'1m','5','0','Zabbix','','','','0','0','','','','1','4')".

I have searched around for a bit and cannot find any relevant information in regards to this problem in the bug tracker or other sources.

Does anyone have any suggestions in regards how to resolve this problem?

I've been using Zabbix a long time, and my production site has been upgraded through many, many versions. My experience is that certain upgrades introduced schema differences between how a fresh, pristine database would be created and how an upgraded database might be. Before I upgraded my site to 5.0, I spent a bunch of time comparing a freshly-created 4.4 database schema with our existing schema for 4.4.7. I then manually corrected all of the differences, until our existing database closely matched a brand new database.

What I recommend you do is to create a new database, something like 'zabbix_new', and then import the 5.0 database setup script. You don't need to import any of the default contents, you just need to make sure that the schema is present.

Then use a tool like 'mysqldiff' or manual comparisons of something like "SHOW CREATE TABLE `httptest` \G" for each database, to basically compare all the tables, constraints, etc.

I'm guessing that you'll find more things that differ, that need to be corrected.