[ZBX-14485] Proxy fails to execute web scenario, failed to update local proxy configuration copy: column "items.params" cannot be null Created: 2018 Jun 15 Updated: 2018 Jul 13 Resolved: 2018 Jul 13 |
|
Status: | Closed |
Project: | ZABBIX BUGS AND ISSUES |
Component/s: | Proxy (P) |
Affects Version/s: | 3.4.10 |
Fix Version/s: | None |
Type: | Problem report | Priority: | Blocker |
Reporter: | Cloud Ops | Assignee: | Unassigned |
Resolution: | Commercial support required | Votes: | 0 |
Labels: | proxy, webcheck, webmonitoring | ||
Remaining Estimate: | Not Specified | ||
Time Spent: | Not Specified | ||
Original Estimate: | Not Specified |
Attachments: |
![]() |
Description |
We've set up a Zabbix Proxy but can't get it to execute web monitoring scenarios. We get the error "failed to update local proxy configuration copy: column "items.params" cannot be null". We've tried using PostgreSQL, MySQL, and SQLite as our proxy DB. We've tried lifting the non-null constraint from the params column of the items table. We've configured it as both an active and passive proxy. We've tried versions 3.4.2, 3.4.8, and 3.4.10. (The server is running 3.4.8.) We still get the same error message. zabbix-get-3.4.10-1.el7.x86_64 Steps to reproduce:
Result: From /var/log/zabbix/zabbix_proxy.log: 12410:20180615:190847.385 query [txnlev:1] [select itemid,type,snmp_community,snmp_oid,hostid,key_,delay,status,value_type,trapper_hosts,snmpv3_securityname,snmpv3_securitylevel,snmpv3_authpassphrase,snmpv3_privpassphrase,lastlogsize,logtimefmt,params,ipmi_sensor,authtype,username,password,publickey,privatekey,mtime,flags,interfaceid,port,snmpv3_authprotocol,snmpv3_privprotocol,snmpv3_contextname,jmx_endpoint from items]
Versions: zabbix-get-3.4.10-1.el7.x86_64
|
Comments |
Comment by Cloud Ops [ 2018 Jun 15 ] |
[centos]# pg_dump -U zabbix zabbix -t items --schema-only SET statement_timeout = 0; SET client_encoding = 'UTF8'; SET standard_conforming_strings = on; SET check_function_bodies = false; SET client_min_messages = warning; SET search_path = public, pg_catalog; SET default_tablespace = ''; SET default_with_oids = false; -- -- Name: items; Type: TABLE; Schema: public; Owner: postgres; Tablespace: -- CREATE TABLE items ( itemid bigint NOT NULL, type integer DEFAULT 0 NOT NULL, snmp_community character varying(64) DEFAULT ''::character varying NOT NULL, snmp_oid character varying(512) DEFAULT ''::character varying NOT NULL, hostid bigint NOT NULL, name character varying(255) DEFAULT ''::character varying NOT NULL, key_ character varying(255) DEFAULT ''::character varying NOT NULL, delay character varying(1024) DEFAULT '0'::character varying NOT NULL, history character varying(255) DEFAULT '90d'::character varying NOT NULL, trends character varying(255) DEFAULT '365d'::character varying NOT NULL, status integer DEFAULT 0 NOT NULL, value_type integer DEFAULT 0 NOT NULL, trapper_hosts character varying(255) DEFAULT ''::character varying NOT NULL, units character varying(255) DEFAULT ''::character varying NOT NULL, snmpv3_securityname character varying(64) DEFAULT ''::character varying NOT NULL, snmpv3_securitylevel integer DEFAULT 0 NOT NULL, snmpv3_authpassphrase character varying(64) DEFAULT ''::character varying NOT NULL, snmpv3_privpassphrase character varying(64) DEFAULT ''::character varying NOT NULL, formula character varying(255) DEFAULT ''::character varying NOT NULL, error character varying(2048) DEFAULT ''::character varying NOT NULL, lastlogsize numeric(20,0) DEFAULT 0::numeric NOT NULL, logtimefmt character varying(64) DEFAULT ''::character varying NOT NULL, templateid bigint, valuemapid bigint, params text, ipmi_sensor character varying(128) DEFAULT ''::character varying NOT NULL, authtype integer DEFAULT 0 NOT NULL, username character varying(64) DEFAULT ''::character varying NOT NULL, password character varying(64) DEFAULT ''::character varying NOT NULL, publickey character varying(64) DEFAULT ''::character varying NOT NULL, privatekey character varying(64) DEFAULT ''::character varying NOT NULL, mtime integer DEFAULT 0 NOT NULL, flags integer DEFAULT 0 NOT NULL, interfaceid bigint, port character varying(64) DEFAULT ''::character varying NOT NULL, description text DEFAULT ''::text NOT NULL, inventory_link integer DEFAULT 0 NOT NULL, lifetime character varying(255) DEFAULT '30d'::character varying NOT NULL, snmpv3_authprotocol integer DEFAULT 0 NOT NULL, snmpv3_privprotocol integer DEFAULT 0 NOT NULL, state integer DEFAULT 0 NOT NULL, snmpv3_contextname character varying(255) DEFAULT ''::character varying NOT NULL, evaltype integer DEFAULT 0 NOT NULL, jmx_endpoint character varying(255) DEFAULT ''::character varying NOT NULL, master_itemid bigint ); ALTER TABLE public.items OWNER TO postgres; -- -- Name: items_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: -- ALTER TABLE ONLY items ADD CONSTRAINT items_pkey PRIMARY KEY (itemid); -- -- Name: items_1; Type: INDEX; Schema: public; Owner: postgres; Tablespace: -- CREATE UNIQUE INDEX items_1 ON items USING btree (hostid, key_); -- -- Name: items_3; Type: INDEX; Schema: public; Owner: postgres; Tablespace: -- CREATE INDEX items_3 ON items USING btree (status); -- -- Name: items_4; Type: INDEX; Schema: public; Owner: postgres; Tablespace: -- CREATE INDEX items_4 ON items USING btree (templateid); -- -- Name: items_5; Type: INDEX; Schema: public; Owner: postgres; Tablespace: -- CREATE INDEX items_5 ON items USING btree (valuemapid); -- -- Name: items_6; Type: INDEX; Schema: public; Owner: postgres; Tablespace: -- CREATE INDEX items_6 ON items USING btree (interfaceid); -- -- Name: items_7; Type: INDEX; Schema: public; Owner: postgres; Tablespace: -- CREATE INDEX items_7 ON items USING btree (master_itemid); -- -- Name: c_items_1; Type: FK CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY items ADD CONSTRAINT c_items_1 FOREIGN KEY (hostid) REFERENCES hosts(hostid) ON DELETE CASCADE; -- -- Name: c_items_2; Type: FK CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY items ADD CONSTRAINT c_items_2 FOREIGN KEY (templateid) REFERENCES items(itemid) ON DELETE CASCADE; -- -- Name: c_items_3; Type: FK CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY items ADD CONSTRAINT c_items_3 FOREIGN KEY (valuemapid) REFERENCES valuemaps(valuemapid); -- -- Name: c_items_4; Type: FK CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY items ADD CONSTRAINT c_items_4 FOREIGN KEY (interfaceid) REFERENCES interface(interfaceid); -- -- Name: c_items_5; Type: FK CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY items ADD CONSTRAINT c_items_5 FOREIGN KEY (master_itemid) REFERENCES items(itemid) ON DELETE CASCADE; -- -- PostgreSQL database dump complete -- |
Comment by Vladislavs Sokurenko [ 2018 Jun 16 ] |
Could you also please do the same on Zabbix proxy ? It should have been: params text DEFAULT ''::text NOT NULL, But looks like you have params text,
|
Comment by Cloud Ops [ 2018 Jun 18 ] |
This was done on the Zabbix proxy. We tried modifying the default and not null constraints to work around the problem but it didn't help.
|
Comment by Cloud Ops [ 2018 Jun 20 ] |
We reinstalled the Zabbix DB with the same results, here is the table creation:
– PostgreSQL database dump SET statement_timeout = 0; SET search_path = public, pg_catalog; SET default_tablespace = ''; SET default_with_oids = false; – CREATE TABLE items ( ALTER TABLE public.items OWNER TO postgres; – ALTER TABLE ONLY items – CREATE UNIQUE INDEX items_1 ON items USING btree (hostid, key_); – CREATE INDEX items_3 ON items USING btree (status); – CREATE INDEX items_4 ON items USING btree (templateid); – CREATE INDEX items_5 ON items USING btree (valuemapid); – CREATE INDEX items_6 ON items USING btree (interfaceid); – CREATE INDEX items_7 ON items USING btree (master_itemid); – ALTER TABLE ONLY items – ALTER TABLE ONLY items – ALTER TABLE ONLY items – ALTER TABLE ONLY items – ALTER TABLE ONLY items – |
Comment by Cloud Ops [ 2018 Jun 25 ] |
We're really stuck for weeks on this and would be grateful for any help to get this proxy working for us!
|
Comment by Vladislavs Sokurenko [ 2018 Jun 27 ] |
Please do show create table both on Zabbix proxy and Zabbix server, you can also try increasing log level of trapper on Zabbix proxy and attach log here. |
Comment by Cloud Ops [ 2018 Jun 27 ] |
show create table on Zabbix proxy is above, here is show create table on Zabbix server:
-- -- PostgreSQL database dump -- -- Dumped from database version 9.6.6 -- Dumped by pg_dump version 9.6.9 SET statement_timeout = 0; SET lock_timeout = 0; SET idle_in_transaction_session_timeout = 0; SET client_encoding = 'UTF8'; SET standard_conforming_strings = on; SELECT pg_catalog.set_config('search_path', '', false); SET check_function_bodies = false; SET client_min_messages = warning; SET row_security = off; SET default_tablespace = ''; SET default_with_oids = false; -- -- Name: items; Type: TABLE; Schema: public; Owner: zabbix -- CREATE TABLE public.items ( itemid bigint NOT NULL, type integer DEFAULT 0 NOT NULL, snmp_community character varying(64) DEFAULT ''::character varying NOT NULL, snmp_oid character varying(512) DEFAULT ''::character varying NOT NULL, hostid bigint NOT NULL, name character varying(255) DEFAULT ''::character varying NOT NULL, key_ character varying(255) DEFAULT ''::character varying NOT NULL, delay character varying(1024) DEFAULT '0'::character varying NOT NULL, history character varying(255) DEFAULT '90d'::character varying NOT NULL, trends character varying(255) DEFAULT '365d'::character varying NOT NULL, status integer DEFAULT 0 NOT NULL, value_type integer DEFAULT 0 NOT NULL, trapper_hosts character varying(255) DEFAULT ''::character varying NOT NULL, units character varying(255) DEFAULT ''::character varying NOT NULL, snmpv3_securityname character varying(64) DEFAULT ''::character varying NOT NULL, snmpv3_securitylevel integer DEFAULT 0 NOT NULL, snmpv3_authpassphrase character varying(64) DEFAULT ''::character varying NOT NULL, snmpv3_privpassphrase character varying(64) DEFAULT ''::character varying NOT NULL, formula character varying(255) DEFAULT ''::character varying NOT NULL, error character varying(2048) DEFAULT ''::character varying NOT NULL, lastlogsize bigint DEFAULT 0 NOT NULL, logtimefmt character varying(64) DEFAULT ''::character varying NOT NULL, templateid bigint, valuemapid bigint, params text, ipmi_sensor character varying(128) DEFAULT ''::character varying NOT NULL, authtype integer DEFAULT 0 NOT NULL, username character varying(64) DEFAULT ''::character varying NOT NULL, password character varying(64) DEFAULT ''::character varying NOT NULL, publickey character varying(64) DEFAULT ''::character varying NOT NULL, privatekey character varying(64) DEFAULT ''::character varying NOT NULL, mtime integer DEFAULT 0 NOT NULL, flags integer DEFAULT 0 NOT NULL, interfaceid bigint, port character varying(64) DEFAULT ''::character varying NOT NULL, description text, inventory_link integer DEFAULT 0 NOT NULL, lifetime character varying(255) DEFAULT '30d'::character varying NOT NULL, snmpv3_authprotocol integer DEFAULT 0 NOT NULL, snmpv3_privprotocol integer DEFAULT 0 NOT NULL, state integer DEFAULT 0 NOT NULL, snmpv3_contextname character varying(255) DEFAULT ''::character varying NOT NULL, evaltype integer DEFAULT 0 NOT NULL, jmx_endpoint character varying(255) DEFAULT ''::character varying NOT NULL, master_itemid bigint ); ALTER TABLE public.items OWNER TO zabbix; -- -- Name: items items_1_items; Type: CONSTRAINT; Schema: public; Owner: zabbix -- ALTER TABLE ONLY public.items ADD CONSTRAINT items_1_items UNIQUE (hostid, key_); -- -- Name: items pk_items; Type: CONSTRAINT; Schema: public; Owner: zabbix -- ALTER TABLE ONLY public.items ADD CONSTRAINT pk_items PRIMARY KEY (itemid); -- -- Name: items_3_items; Type: INDEX; Schema: public; Owner: zabbix -- CREATE INDEX items_3_items ON public.items USING btree (status); -- -- Name: items_4_items; Type: INDEX; Schema: public; Owner: zabbix -- CREATE INDEX items_4_items ON public.items USING btree (templateid); -- -- Name: items_5_items; Type: INDEX; Schema: public; Owner: zabbix -- CREATE INDEX items_5_items ON public.items USING btree (valuemapid); -- -- Name: items_6_items; Type: INDEX; Schema: public; Owner: zabbix -- CREATE INDEX items_6_items ON public.items USING btree (interfaceid); -- -- Name: items_7; Type: INDEX; Schema: public; Owner: zabbix -- CREATE INDEX items_7 ON public.items USING btree (master_itemid); -- -- Name: items c_items_1; Type: FK CONSTRAINT; Schema: public; Owner: zabbix -- ALTER TABLE ONLY public.items ADD CONSTRAINT c_items_1 FOREIGN KEY (hostid) REFERENCES public.hosts(hostid) ON UPDATE RESTRICT ON DELETE CASCADE; -- -- Name: items c_items_2; Type: FK CONSTRAINT; Schema: public; Owner: zabbix -- ALTER TABLE ONLY public.items ADD CONSTRAINT c_items_2 FOREIGN KEY (templateid) REFERENCES public.items(itemid) ON UPDATE RESTRICT ON DELETE CASCADE; -- -- Name: items c_items_3; Type: FK CONSTRAINT; Schema: public; Owner: zabbix -- ALTER TABLE ONLY public.items ADD CONSTRAINT c_items_3 FOREIGN KEY (valuemapid) REFERENCES public.valuemaps(valuemapid) ON UPDATE RESTRICT ON DELETE RESTRICT; -- -- Name: items c_items_4; Type: FK CONSTRAINT; Schema: public; Owner: zabbix -- ALTER TABLE ONLY public.items ADD CONSTRAINT c_items_4 FOREIGN KEY (interfaceid) REFERENCES public.interface(interfaceid) ON UPDATE RESTRICT ON DELETE RESTRICT; -- -- Name: items c_items_5; Type: FK CONSTRAINT; Schema: public; Owner: zabbix -- ALTER TABLE ONLY public.items ADD CONSTRAINT c_items_5 FOREIGN KEY (master_itemid) REFERENCES public.items(itemid) ON DELETE CASCADE; -- -- PostgreSQL database dump complete
|
Comment by Cloud Ops [ 2018 Jun 27 ] |
Log attached with Debug=5. Is there a specific option for trapper log level?
|
Comment by Cloud Ops [ 2018 Jul 09 ] |
Hi @Vladislavs Sokurenko, hope there is something else we can try?
|
Comment by Vladislavs Sokurenko [ 2018 Jul 09 ] |
I see that params |
Comment by Cloud Ops [ 2018 Jul 09 ] |
The proxy was installed directly from the included schema.. Did you mean the server's params table? Which part was non default?
Thanks for the help!
|
Comment by Vladislavs Sokurenko [ 2018 Jul 09 ] |
Sorry I meant row, It should have been: params text DEFAULT ''::text NOT NULL, But looks like you have params text, |
Comment by Cloud Ops [ 2018 Jul 09 ] |
OK, so this applies to both server and proxy?
|
Comment by Vladislavs Sokurenko [ 2018 Jul 09 ] |
Yes, schema must match your fresh installation of Zabbix this includes Zabbix server and Zabbix proxy. |
Comment by Cloud Ops [ 2018 Jul 11 ] |
Hi! Thanks very much for your response on this.
As you suggested, I want to change params text, to params text DEFAULT ''::text NOT NULL,
I tried to add the NOT NULL constraint to items.params but the column already has null values so it failed. Is there anything I can do without hurting the server DB? Should I try to replace the nulls with some other value?
|
Comment by Vladislavs Sokurenko [ 2018 Jul 11 ] |
Yes, please replace to empty value where param is null |
Comment by Cloud Ops [ 2018 Jul 12 ] |
Looks like this has gotten us further! We have other errors (maybe we had more than one problem.) Still not able to execute web scenarios on proxy though we can gather data for other items with it. We get errors about foreign key constraint for some of our regexes:
8443:20180712:190119.964 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR: insert or update on table "expressions" violates foreign key constraint "c_expressions_1"
|
Comment by Vladislavs Sokurenko [ 2018 Jul 13 ] |
Sorry, this tracker is for bug reports only. |