Trgiger status Problem - after restore database

[lukisec]

Hi,
Yesterday i had problem with ibdata1 file (150 GB), i resolve it via this tutorial: https://www.thegeekstuff.com/2016/02...ile-per-table/
Now i have another problem: everything work fine new problems are solved , but old problems after restore db still have status problem although it should resolved.
Thanks in advance!
BR

[aigars.kadikis]

Hello, lukisec!

Welcome to forum!

Please expand your "still have status problem" story.
Is it database related issue?

Regards,
Aigars

[lukisec]

Hello!

i think that is issue with database, for example: PROBLEM host is unavailable by ICMP, when icmp back zabbix doesn't resolve this problem.
After restore database i cant ack old problems, i have all read write privileges but: Access denied

• You are logged in as "Admin". You have no permissions to access this page.
• If you think this message is wrong, please consult your administrators about getting the necessary permissions.

[aigars.kadikis]

Hello!

i think that is issue with database, for example: PROBLEM host is unavailable by ICMP, when icmp back zabbix doesn't resolve this problem.
After restore database i cant ack old problems, i have all read write privileges but: Access denied

• You are logged in as "Admin". You have no permissions to access this page.
• If you think this message is wrong, please consult your administrators about getting the necessary permissions.

Hi, lukisec,

Please provide some output from zabbix_server.log.
Also please take a look how many unsupported items you got and what is the cause of unsupported state. This can be done by:
0) open any host
1) select items
2) delete host name
3) select state as "Not supported"
4) apply

Once you execute this filter then click on the red [X] to expand the information about cause.

Regards,

[kloczek]

If item is in unsupported/not supported state it means that it is some issue with sampling monitoring data.
Gernerally it has nothing to do with what was/is in database or upgrade of the zabbix.

[lukisec]

But i think it is problem with database, like i said before after delete ibdata1 file and restore database from dump, i have old problems in "system status" for example zabbix have still no memory even than i free up space etc.. i cant do anythynig with this and triggers doesnt work.

P.s sorry for my english.

[aigars.kadikis]

But i think it is problem with database, like i said before after delete ibdata1 file and restore database from dump, i have old problems in "system status" for example zabbix have still no memory even than i free up space etc.. i cant do anythynig with this and triggers doesnt work.

P.s sorry for my english.

In this situation I would suggest:
1) take a note of existing password for mysql user 'zabbix'
2) drop existing 'zabbix' database;
3) creat new 'zabbix' database;
4) restore again from dump.

grep "^DBPassword" /etc/zabbix/zabbix_server.conf
mysql -uzabbix -p
DROP DATABASE zabbix;
CREATE DATABASE zabbix CHARACTER SET UTF8 collate utf8_bin;
GRANT ALL PRIVILEGES on zabbix.* to "zabbix"@"localhost" IDENTIFIED BY "same password noted before"
bzcat dbdump.bz2 | sudo mysql -uzabbix -p zabbix

Regards,

[lukisec]

Hi,

I restored again but the same situation.

Old problem after restore:

These problem are resolved but zabbix doesnt check this..
When i try ACK Admin account with full privileges:

How can i clear old problems? In problem, in system status etc

Regards

[aigars.kadikis]

Hi, lukisec,

Another shot would be to backup zabbix database without history. Only configurations will be saved. You can do this by executing:

time=$(date +%Y%m%d%H%M)
sudo mysqldump -uroot -p --flush-logs --single-transaction --create-options --ignore-table=zabbix.acknowledges --ignore-table=zabbix.alerts --ignore-table=zabbix.auditlog --ignore-table=zabbix.auditlog_details --ignore-table=zabbix.escalations --ignore-table=zabbix.events --ignore-table=zabbix.history --ignore-table=zabbix.history_log --ignore-table=zabbix.history_str --ignore-table=zabbix.history_str_sync --ignore-table=zabbix.history_sync --ignore-table=zabbix.history_text --ignore-table=zabbix.history_uint --ignore-table=zabbix.history_uint_sync --ignore-table=zabbix.profiles --ignore-table=zabbix.service_alarms --ignore-table=zabbix.sessions --ignore-table=zabbix.trends --ignore-table=zabbix.trends_uint --ignore-table=zabbix.user_history --ignore-table=zabbix.node_cksum zabbix | bzip2 -9 > /root/$time.bz2

P.S. This execution of backup will take only 10 seconds or something.

Regards,

[lukisec]

After this i cant log in via GUI "Error in query"

[aigars.kadikis]

After this i cant log in via GUI "Error in query"

Is MySQL is running?

systemctl status mariadb

Please share the output of

cat /var/log/mariadb/mariadb.log

Did you performed backup restore on clean MySQL setup?

If you do not mind you can send your configuration.bz2 privately using dropbox or gdrive link (it should be 2mb or something). I will try to perform restore myself and see how it goes.

Regards,

[lukisec]

Hi thank you for your answer,
I cloned all hosts with old problems and delete them now its work correctly but i think i have some issue with mysql:

zabbix-server.log -
[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 (51393,4343,null,null,null),(47462,4343,null,null, null),(60303,4343,null,null,null),(57386,4343,null ,null,null),(12959,4343,null,null,null),(69140,434 3,null,null,null),(93898,4343,null,null,null),(145 06,4343,null,null,null),(115795,4343,null,null,nul l),(38593......................................... ................................

52021:20180508:133312.597 [Z3005] query failed: [1062] Duplicate entry '3883' for key 'PRIMARY' [insert into event_recovery (eventid,r_eventid,correlationid,c_eventid,userid) values (3675,4533,null,null,null),(4068,4533,null,null,nu ll),(3615,4533,null,null,null),(3983,4533,null,nul l,null),(3533,4533,null,null,null),(3256,4533,null ,null,null),(3386,4533,null,null,null),(4528,4533, null,null,null),(3883,4533,null,null,null),(3300,4 533,null,null,null),(3178,4533,null,null,null),(37 63,4533,null,null,null);
]
52021:20180508:133312.650 [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 (51393,4534,null,null,null),...................... .................................................. ................


mariadb log:


180508 13:01:56 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended
180508 13:01:57 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
180508 13:01:57 [Note] /usr/libexec/mysqld (mysqld 5.5.56-MariaDB) starting as process 56428 ...
180508 13:01:57 InnoDB: The InnoDB memory heap is disabled
180508 13:01:57 InnoDB: Mutexes and rw_locks use GCC atomic builtins
180508 13:01:57 InnoDB: Compressed tables use zlib 1.2.7
180508 13:01:57 InnoDB: Using Linux native AIO
180508 13:01:57 InnoDB: Initializing buffer pool, size = 128.0M
180508 13:01:57 InnoDB: Completed initialization of buffer pool
180508 13:01:57 InnoDB: highest supported file format is Barracuda.
180508 13:01:57 InnoDB: Waiting for the background threads to start
180508 13:01:58 Percona XtraDB (http://www.percona.com) 5.5.52-MariaDB-38.3 started; log sequence number 1048891244
180508 13:01:58 [Note] Plugin 'FEEDBACK' is disabled.
180508 13:01:58 [Note] Server socket created on IP: '0.0.0.0'.
180508 13:01:58 [Note] Event Scheduler: Loaded 0 events
180508 13:01:58 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.56-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server

[aigars.kadikis]

Hi thank you for your answer,
I cloned all hosts with old problems and delete them now its work correctly but i think i have some issue with mysql:

zabbix-server.log -
[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 (51393,4343,null,null,null),(47462,4343,null,null, null),(60303,4343,null,null,null),(57386,4343,null ,null,null),(12959,4343,null,null,null),(69140,434 3,null,null,null),(93898,4343,null,null,null),(145 06,4343,null,null,null),(115795,4343,null,null,nul l),(38593......................................... ................................

52021:20180508:133312.597 [Z3005] query failed: [1062] Duplicate entry '3883' for key 'PRIMARY' [insert into event_recovery (eventid,r_eventid,correlationid,c_eventid,userid) values (3675,4533,null,null,null),(4068,4533,null,null,nu ll),(3615,4533,null,null,null),(3983,4533,null,nul l,null),(3533,4533,null,null,null),(3256,4533,null ,null,null),(3386,4533,null,null,null),(4528,4533, null,null,null),(3883,4533,null,null,null),(3300,4 533,null,null,null),(3178,4533,null,null,null),(37 63,4533,null,null,null);
]
52021:20180508:133312.650 [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 (51393,4534,null,null,null),...................... .................................................. ................


mariadb log:


180508 13:01:56 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended
180508 13:01:57 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
180508 13:01:57 [Note] /usr/libexec/mysqld (mysqld 5.5.56-MariaDB) starting as process 56428 ...
180508 13:01:57 InnoDB: The InnoDB memory heap is disabled
180508 13:01:57 InnoDB: Mutexes and rw_locks use GCC atomic builtins
180508 13:01:57 InnoDB: Compressed tables use zlib 1.2.7
180508 13:01:57 InnoDB: Using Linux native AIO
180508 13:01:57 InnoDB: Initializing buffer pool, size = 128.0M
180508 13:01:57 InnoDB: Completed initialization of buffer pool
180508 13:01:57 InnoDB: highest supported file format is Barracuda.
180508 13:01:57 InnoDB: Waiting for the background threads to start
180508 13:01:58 Percona XtraDB (http://www.percona.com) 5.5.52-MariaDB-38.3 started; log sequence number 1048891244
180508 13:01:58 [Note] Plugin 'FEEDBACK' is disabled.
180508 13:01:58 [Note] Server socket created on IP: '0.0.0.0'.
180508 13:01:58 [Note] Event Scheduler: Loaded 0 events
180508 13:01:58 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.56-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server

Which zabbix servers version exactly do you have?

[lukisec]

Hi,

(Zabbix) 3.4.4
Revision 74338 7 November 2017

[aigars.kadikis]

Hi,

(Zabbix) 3.4.4
Revision 74338 7 November 2017

Please log in into MySQL and show output for:

select * from zabbix.event_recovery where eventid=3883;