MySQL Database Missing Triggers | New Hosts Wont Begin Checking Until Service Restart

[ncaple]

Hello,

Have a strange recurring issue that I believe I've narrowed down to missing database triggers.

Essentially, any new hosts that are created via an API script will not run any checks applied from a template, and checking their ping has an error saying "Cannot execute script. Unknown host identifier". As soon as I restart the zabbix-server.service daemon, everything starts working as expected.

This also will randomly occur to existing hosts. We will notice some hosts will appear as up/down when being in the opposite state and not be running any service checks. As soon as the zabbix-server.service is restarted, it refreshes and the items start checking again.

During these events, there is nothing in journalctl or zabbix-server.log indicating an error.

From my research, it sounds like I may be missing database triggers, but I have no idea what triggers I may be missing or what to look for in my database (I am not a database guy).

Would appreciate any guidance on what tables/databases I can nuke or replace. Or where how I could restore a functioning backup from /usr/share/zabbix-sql-scripts/mysql/server.sql.gz

[ncaple]

I did find this guide and similar articles, but it wasnt clear how they actually fixed it.

https://support.zabbix.com/browse/ZBX-22948

trigger_schema,
trigger_name,
event_manipulation,
action_statement

None of these tables exist

select * from changelog order by changelogid desc limit 10;

This returns an empty result.

[tim.mooney]

You don't say anything about what version of Zabbix you're running. The database schema changes as the product evolves, so knowing what version you're using is important.

Regarding trigger_schema, trigger_name, event_manipulation, and action_statement, those aren't tables. They're columns from the triggers "table" in the INFORMATION_SCHEMA. The query in the issue you linked is selecting those four columns from INFORMATION_SCHEMA.triggers.

Assuming you're correct and the problem is that your environment is missing triggers, my question is how did you get into this situation? Have you done a database migration recently? Triggers don't just "disappear", so I'm puzzled why yours are missing.

If they are missing, the good news is that it's pretty easy to fix. There's an initial "schema.sql.gz" file, which differs for each type of database (MySQL, PostgreSQL, etc.) that has the statements you need to re-create the triggers, but you have to use the schema.sql file that matches your version of Zabbix. You also need to hunt through the schema.sql and delete everything else except the section that creates the triggers. You don't want to recreate tables or anything else, you just want to recreate the triggers.

[ncaple]

Thanks for the reply. I'm on version 7.0.8. I'm not sure what would have caused the database to lose those columns, but it worked perfectly fine up until pretty recently.

I will try to find a schema.sql file for my version of Zabbix.

Our Zabbix instance was installed via puppet, so its possible the database was installed for an earlier release, and then upgrading broke the database somewhere. Hard to say as I don't know how Zabbix was originally deployed in our environment.

[ncaple]

I reduced the schema.sql file to just the "create trigger" statements imported it into our DB. This seems to have fixed the issue. Thanks for the help