[ZBX-20309] Stop processing data if failed to update local proxy configuration copy: database error happens Created: 2021 Dec 03  Updated: 2022 Jul 12

Status: Confirmed
Project: ZABBIX BUGS AND ISSUES
Component/s: None
Affects Version/s: 5.0.17
Fix Version/s: None

Type: Incident report Priority: Trivial
Reporter: Andrey Vanik Assignee: Zabbix Development Team
Resolution: Unresolved Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: PNG File master server db.PNG     PNG File zabbix_proxy.PNG     PNG File zabbix_proxy2.PNG    

 Description   

Hello!

I have a main Zabbix server (with a dedicated DB server) and a few proxies.

And I see an error in the Zabbix proxy (below).
I've tried adding the values I want to the regexps table. After that, the initial synchronization goes well, but then again an error.
As I understand it, old data comes from the main zabbix server to the proxy, which is no longer in the web interface.
Please help me.

1547:20211202:131102.148 received configuration data from server at "zbbxsrv", datalen 4980259
1547:20211202:131102.798 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR: insert or update on table "expressions" violates foreign key constraint "c_expressions_1"
DETAIL: Key (regexpid)=(20) is not present in table "regexps".
[insert into expressions (expressionid,regexpid,expression,expression_type, exp_delimiter,case_sensitive) values (41,20,'^Interface Microsoft 6to4 Adapter|Interface Microsoft IP-HTTPS Platform Adapter$',3,',',0),(43,21,'^Interface Microsoft 6to4 Adapter|Interface Microsoft IP-HTTPS Platform Adapter|ISATAP$',4,',',0);
]
1547:20211202:131102.798 failed to update local proxy configuration copy: database error

 Comments   
Comment by Alexey Pustovalov [ 2021 Dec 03 ]

Please execute the following queries on Zabbix server DB side:

select * from regexps where regexpid = 20;
select * from expressions where regexpid = 20;
Comment by Andrey Vanik [ 2021 Dec 03 ]

Добрый день!

Да,  я понимаю что такого значения в таблице нет. Но почему то с главного сервера (как мне кажется с него) есть попытка внести данные именно в эти таблицы.

Что необходимо чтобы БД нормально обновилась с главного сервера?

Скрины с прокси:

Скрин с мастер сервера (гл. бд):

 

Т.е. таблицы в БД по количеству идентичны. 

А на прокси снова отправляются данные из General -> RegExp которые были удалены.

Comment by Alexey Pustovalov [ 2021 Dec 03 ]

Please reply only in English here.
Zabbix proxy can not receive non-existing data from Zabbix server. It is impossible. Are you sure that you checked correct Zabbix server DB. Do you have any SQL errors in zabbix_server.log?

Comment by Andrey Vanik [ 2021 Dec 03 ]

Ok.

I'm pretty sure this is the correct database server.

I do not see any errors in the log file of the Zabbix server on the master host.
Also, I don't see any errors in the main database.

I only see errors on the proxy and in the Zabbix proxy log file and in the database log file. Errors started after removing regular expressions.

Comment by Alexey Pustovalov [ 2021 Dec 03 ]

Try to enable debug on proxy side for "configuration syncer" or "trapper" processes (active or passive). Then check what you actualy receive from Zabbix server.

Comment by Andrey Vanik [ 2021 Dec 03 ]

I Inscreased debug level.

In PostgreSQL log:

 

[root@dcm-zabbix01 ~]# tail -f /var/lib/pgsql/11/data/log/postgresql-Fri.log
2021-12-03 17:45:32.094 MSK [1548] STATEMENT:  insert into expressions (expressionid,regexpid,expression,expression_type,exp_delimiter,case_sensitive) values (41,20,'^Interface Microsoft 6to4 Adapter|Interface Microsoft IP-HTTPS Platform Adapter$',3,',',0),(43,21,'^Interface Microsoft 6to4 Adapter|Interface Microsoft IP-HTTPS Platform Adapter|ISATAP$',4,',',0),(55,22,'test',0,',',0),(56,23,'test2',0,',',0);
2021-12-03 17:45:51.458 MSK [1548] ERROR:  insert or update on table "expressions" violates foreign key constraint "c_expressions_1"
2021-12-03 17:45:51.458 MSK [1548] DETAIL:  Key (regexpid)=(20) is not present in table "regexps".
2021-12-03 17:45:51.458 MSK [1548] STATEMENT:  insert into expressions (expressionid,regexpid,expression,expression_type,exp_delimiter,case_sensitive) values (41,20,'^Interface Microsoft 6to4 Adapter|Interface Microsoft IP-HTTPS Platform Adapter$',3,',',0),(43,21,'^Interface Microsoft 6to4 Adapter|Interface Microsoft IP-HTTPS Platform Adapter|ISATAP$',4,',',0),(55,22,'test',0,',',0),(56,23,'test2',0,',',0);
2021-12-03 17:49:13.403 MSK [1548] ERROR:  insert or update on table "expressions" violates foreign key constraint "c_expressions_1"
2021-12-03 17:49:13.403 MSK [1548] DETAIL:  Key (regexpid)=(20) is not present in table "regexps".
2021-12-03 17:49:13.403 MSK [1548] STATEMENT:  insert into expressions (expressionid,regexpid,expression,expression_type,exp_delimiter,case_sensitive) values (41,20,'^Interface Microsoft 6to4 Adapter|Interface Microsoft IP-HTTPS Platform Adapter$',3,',',0),(43,21,'^Interface Microsoft 6to4 Adapter|Interface Microsoft IP-HTTPS Platform Adapter|ISATAP$',4,',',0),(55,22,'test',0,',',0),(56,23,'test2',0,',',0);
2021-12-03 17:50:29.815 MSK [1548] ERROR:  insert or update on table "expressions" violates foreign key constraint "c_expressions_1"
2021-12-03 17:50:29.815 MSK [1548] DETAIL:  Key (regexpid)=(20) is not present in table "regexps".
2021-12-03 17:50:29.815 MSK [1548] STATEMENT:  insert into expressions (expressionid,regexpid,expression,expression_type,exp_delimiter,case_sensitive) values (41,20,'^Interface Microsoft 6to4 Adapter|Interface Microsoft IP-HTTPS Platform Adapter$',3,',',0),(43,21,'^Interface Microsoft 6to4 Adapter|Interface Microsoft IP-HTTPS Platform Adapter|ISATAP$',4,',',0),(55,22,'test',0,',',0),(56,23,'test2',0,',',0);

And I don't see error 
PGRES_FATAL_ERROR:ERROR: insert or update on table "expressions" violates foreign key constraint "c_expressions_1"
in file /var/log/zabbix/zabbix_proxy.log (when debug level on).

If I disabled debug level, I again see error: 

 1547:20211203:175432.580 forced reloading of the configuration cache
  1547:20211203:175433.324 received configuration data from server at "zbbxsrv", datalen 4956510
  1627:20211203:175433.441 resuming Zabbix agent checks on host "msv-cabint-01.XXX.local": connection restored
  1631:20211203:175433.446 resuming Zabbix agent checks on host "msk-clpsdb-01": connection restored
  1625:20211203:175433.448 resuming Zabbix agent checks on host "msv-ulbdr-03": connection restored
  1636:20211203:175433.505 resuming Zabbix agent checks on host "msk-elbint02": connection restored
  1547:20211203:175433.960 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR:  insert or update on table "expressions" violates foreign key constraint "c_expressions_1"
DETAIL:  Key (regexpid)=(20) is not present in table "regexps".
 [insert into expressions (expressionid,regexpid,expression,expression_type,exp_delimiter,case_sensitive) values (41,20,'^Interface Microsoft 6to4 Adapter|Interface Microsoft IP-HTTPS Platform Adapter$',3,',',0),(43,21,'^Interface Microsoft 6to4 Adapter|Interface Microsoft IP-HTTPS Platform Adapter|ISATAP$',4,',',0),(55,22,'test',0,',',0),(56,23,'test2',0,',',0);
]
  1547:20211203:175433.961 failed to update local proxy configuration copy: database error
Comment by Andrey Vanik [ 2021 Dec 03 ] 
1547:20211203:190821.921 End of process_proxyconfig_table():SUCCEED
  1547:20211203:190821.921 In process_proxyconfig_table() table:'regexps'
  1547:20211203:190821.921 query [txnlev:1] [select regexpid,name from regexps]
  1547:20211203:190821.922 End of process_proxyconfig_table():SUCCEED
  1547:20211203:190821.922 In process_proxyconfig_table() table:'expressions'
  1547:20211203:190821.922 query [txnlev:1] [select expressionid,regexpid,expression,expression_type,exp_delimiter,case_sensitive from expressions]
  1547:20211203:190821.923 query [txnlev:1] [insert into expressions (expressionid,regexpid,expression,expression_type,exp_delimiter,case_sensitive) values (41,20,'^Interface Microsoft 6to4 Adapter|Interface Microsoft IP-HTTPS Platform Adapter$',3,',',0),(43,21,'^Interface Microsoft 6to4 Adapter|Interface Microsoft IP-HTTPS Platform Adapter|ISATAP$',4,',',0),(55,22,'test',0,',',0),(56,23,'test2',0,',',0);
]
  1547:20211203:190821.923 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR:  insert or update on table "expressions" violates foreign key constraint "c_expressions_1"
DETAIL:  Key (regexpid)=(20) is not present in table "regexps".
 [insert into expressions (expressionid,regexpid,expression,expression_type,exp_delimiter,case_sensitive) values (41,20,'^Interface Microsoft 6to4 Adapter|Interface Microsoft IP-HTTPS Platform Adapter$',3,',',0),(43,21,'^Interface Microsoft 6to4 Adapter|Interface Microsoft IP-HTTPS Platform Adapter|ISATAP$',4,',',0),(55,22,'test',0,',',0),(56,23,'test2',0,',',0);
]
  1547:20211203:190821.923 query [insert into expressions (expressionid,regexpid,expression,expression_type,exp_delimiter,case_sensitive) values (41,20,'^Interface Microsoft 6to4 Adapter|Interface Microsoft IP-HTTPS Platform Adapter$',3,',',0),(43,21,'^Interface Microsoft 6to4 Adapter|Interface Microsoft IP-HTTPS Platform Adapter|ISATAP$',4,',',0),(55,22,'test',0,',',0),(56,23,'test2',0,',',0);
] failed, setting transaction as failed
  1547:20211203:190821.923 End of process_proxyconfig_table():FAIL
  1547:20211203:190821.923 query [txnlev:1] [rollback;]
  1547:20211203:190821.924 failed to update local proxy configuration copy: database error
  1547:20211203:190821.924 End of process_proxyconfig()
  1547:20211203:190821.924 End of process_configuration_sync()
  1547:20211203:190821.924 zbx_setproctitle() title:'configuration syncer [synced config 4956510 bytes in 1.278128 sec, idle 3600 sec]'
Comment by Andrey Vanik [ 2021 Dec 03 ]

I'm sorry.

I found in the Expressions table in the main database that the values that were removed from the web are still there. Why are they not removed from the database?

Comment by Alexey Pustovalov [ 2021 Dec 03 ]

what values are incorrect? According error it must be regexpid = 20 in expressions table, but you attached output that no values with such filter.

Comment by Andrey Vanik [ 2021 Dec 04 ]

I created a global regex under General -> RegExp. Then deleted it. But this global expression remained in the database. Is that how it should be?

Comment by Aigars Kadikis [ 2021 Dec 06 ]

You have errors in posthresql log:

  1547:20211203:190821.923 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR:  insert or update on table "expressions" violates foreign key constraint "c_expressions_1"

Please run:

SELECT regexpid FROM expressions WHERE regexpid NOT IN (SELECT regexpid FROM regexps);

It should report nothing on normal occasion if consistency of a relational database is good. If output reports something, then one way how to fix is to remove entries which do not any linkage to Zabbix application:

DELETE FROM expressions WHERE regexpid NOT IN (SELECT regexpid FROM regexps);

Your statement:

I found in the Expressions table in the main database that the values that were removed from the web are still there. Why are they not removed from the database?

When removing setting via GUI, the line should instantly disappear from database.

Comment by Andrey Vanik [ 2021 Dec 06 ]

Hello!

Thanks for the reply.

zabbix=# SELECT regexpid FROM expressions WHERE regexpid NOT IN (SELECT regexpid FROM regexps);
 regexpid
----------
       20
       22
       23
       21
(4 rows)
zabbix=# select * from regexps;
 regexpid |                      name                       |   test_string
----------+-------------------------------------------------+------------------
        1 | File systems for discovery                      | ext3
        3 | Storage devices for SNMP discovery              | /boot
        4 | Linux disks for autodiscovery                   |
        5 | Linux processes for autodiscovery               |
        6 | Linux TCP services for fordiscovery by name     |
        7 | Linux TCP services for fordiscovery by port     |
        8 | Linux UDP services for fordiscovery by name     |
        9 | Linux UDP services for fordiscovery by port     |
       13 | Windows service startup states for discovery    |
       14 | Network interfaces for discovery                | eno2
       17 | Windows TS service names for discovery          | MySQL
       18 | Windows TS service startup states for discovery | automatic
       19 | RabbitMQ queues names for discovery             | 1amq.gen-
       12 | Windows service names for discovery             | MySQL
       10 | net.discovery.tempsensor.intake                 | asdf intake asdf
       11 | net.discovery.tempsensor                        | sensor
       15 | Windows FS names for autodiscovery              | D:
        2 | NetworkInterfaces                               | vmxnet3
(18 rows)
zabbix=# select * from expressions;
 expressionid | regexpid |                                                                                                                       expression
                                      | expression_type | exp_delimiter | case_sensitive
--------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------+-----------------+---------------+----------------
            1 |        1 | ^(btrfs|ext2|ext3|ext4|jfs|reiser|xfs|ffs|ufs|jfs|jfs2|vxfs|hfs|ntfs|fat32|zfs)$
                                      |               3 | ,             |              0
            3 |        3 | ^(Physical memory|Virtual memory|Memory buffers|Cached memory|Swap space)$
                                      |               4 | ,             |              1
            5 |        4 | ^(hd[a-z]+|sd[a-z]+|vd[a-z]+|dm-[0-9]+|drbd[0-9]+)$
                                      |               3 | ,             |              0
            6 |        4 | ^(vzsnap.*|.*-cow|.*-real|ram[0-9]+|loop[0-9]+|sr[0-9]*|fd[0-9]*)$
                                      |               4 | ,             |              0
            7 |        5 | ^(mimedefang|named|snmpd|snmptt|nfsd|syslogd|rsyslogd|syslog-ng|slapd|nmbd|inetd|cron|smbd|pvedaemon|ogSoapServ.pl|postgres|java)$
                                      |               3 | ,             |              0
            8 |        6 | ^$
                                      |               4 | ,             |              0
            9 |        6 | ^(rpc.*|proftpd.+)$
                                      |               4 | ,             |              0
           10 |        7 | ^(21|22|25|26|53|80|83|110|111|139|143|443|445|465|540|587|636|652|801|802|842|901|953|993|995|1100|1111|2049|3050|3142|3306|4190|5432|9101|9102|9103|10022|10023|10024|10025|22000|28081|28082|28083|28084|28085|
28086|28087|28088|28089|28090|50000)$ |               3 | ,             |              0
           11 |        8 | ^$
                                      |               4 | ,             |              0
           12 |        8 | ^(avahi-daemon:|named|snmpd|ntpdate|zabbix_server|smbd|rpc.*|rsyslogd|snmpwalk|proxpolicy\s.*)$
                                      |               4 | ,             |              0
           13 |        9 | ^(53|69|111|123|137|138|161|162|514|1812|1813|2049)$
                                      |               3 | ,             |              0
           27 |       13 | ^automatic
                                      |               3 | ,             |              0
           31 |       14 | ^(eno\d+|eth\d+|bond\d+)$
                                      |               3 | ,             |              0
           37 |       17 | ^(quik.*)
                                      |               3 | ,             |              0
           38 |       18 | ^automatic|^manual
                                      |               3 | ,             |              0
           40 |       19 | (amq.gen-.*)
                                      |               4 | ,             |              0
           41 |       20 | ^Interface Microsoft 6to4 Adapter|Interface Microsoft IP-HTTPS Platform Adapter$
                                      |               3 | ,             |              0
           26 |       12 | ^(MySQL|MSSQLSERVER|SQL Server|SQL Server Agent)$
                                      |               3 | ,             |              0
           23 |       10 | inlet|intake|Outlet|FRONT|BACK
                                      |               3 | ,             |              0
           25 |       11 | inlet|intake|Outlet|FRONT|BACK
                                      |               4 | ,             |              0
           55 |       22 | test
                                      |               0 | ,             |              0
           33 |       15 | ^(C:|D:)$
                                      |               3 | ,             |              0
           56 |       23 | test2
                                      |               0 | ,             |              0
           49 |        2 | ^HPE|vmxnet3
                                      |               3 | ,             |              0
           14 |        2 | ^Microsoft Teredo Tunneling Adapter
                                      |               4 | ,             |              1
           15 |        2 | ^Microsoft ISATAP
                                      |               4 | ,             |              1
           17 |        2 | ^Microsoft Loopback
                                      |               4 | ,             |              1
           16 |        2 | ^WAN Miniport
                                      |               4 | ,             |              1
           18 |        2 | ^RAS Async Adapter
                                      |               4 | ,             |              1
           43 |       21 | ^Interface Microsoft 6to4 Adapter|Interface Microsoft IP-HTTPS Platform Adapter|ISATAP$
                                      |               4 | ,             |              0
           47 |        2 | ^Microsoft IP-HTTPS
                                      |               4 | ,             |              0
           19 |        2 | ^MS TCP Loopback interface
                                      |               4 | ,             |              1
           45 |        2 | ^Team
                                      |               4 | ,             |              0
            4 |        2 | ^Software Loopback Interface
                                      |               4 | ,             |              1
           29 |        2 | ^Microsoft Kernel Debug Network Adapter
                                      |               4 | ,             |              1
           22 |        2 | ^LightWeight Filter
                                      |               4 | ,             |              1
           39 |        2 | QoS|WFP|Pseudo|Multiplexor|Balancing|Filter|Npcap|Loopback
                                      |               4 | ,             |              0
           48 |        2 | ^Microsoft 6to4 Adapter
                                      |               4 | ,             |              0
           21 |        2 | ^QoS Packet Scheduler
                                      |               4 | ,             |              1
           46 |        2 | ^WAN Miniport
                                      |               4 | ,             |              0
            2 |        2 | ^lo$
                                      |               4 | ,             |              1
           20 |        2 | ^Kernel Debug Network Adapter
                                      |               4 | ,             |              1
(42 rows)

When removing setting via GUI, the line should instantly disappear from database.

Yes, I understand what you are talking about, but this is not happening.

Version of Zabbix Server and Proxys is 5.0.12.

Comment by Andrey Vanik [ 2021 Dec 06 ]

I executed the command:

DELETE FROM expressions WHERE regexpid NOT IN (SELECT regexpid FROM regexps);

And configuration replication was successful and without errors to the proxy server.

Thank you very much.

1. Tell me why the records deleted in the web interface were not deleted from the database? Where to look?

2. Tell me for the future how to check the consistency of the Zabbix database and fix it? Maybe there is some command or function that checks this?

Comment by Francys Nivea (Inactive) [ 2021 Dec 08 ]

Hello Andrey,

You have had integrity issues with your database. A constraint was not being respected. In the future, when you have problems, check the database logs for errors. If something is happening to your database, the cause will almost always be there.

ZBX project is for bug reports. Since the problem was not a bug, but an inconsistency in your database, we will be closing the ticket..

Generated at Mon Apr 28 09:30:36 EEST 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.