Unable to restore Zabbix 6.2.4 from backup

[Leonkh1]

Unable to restore Zabbix 6.2.4 from backup. Zabbix version: 6.2.4 Mariadb: 10.5.17

When I am trying to restore MariaDB database from the backup - I receive the error sudo -u root mysql zabbix < ~/backup/zabbix_cfg.sql

*ERROR 1359 (HY000) at line 14309: Trigger 'zabbix.functions_insert' already exists*

Please help to solve the issue.

[infodimus]

I have the same problem with Zabbix 6.0 and Mariadb 10.5.17

[ironbishop]

Can you confirm if you did drop the database before the restore?

I tested the restore on an empty db, after dumping a Zabbix 6.0, and it works.

[Leonkh1]

Zabbix restore did not work on empty db in my case.

[ironbishop]

Can you provide more informations and logs about the issue?

[Leonkh1]

I don't have any particular log on hand. I faced the issue just when I tried to restore Zabbix from backup.

[infodimus]

I will submit logs when I run restore again.

[kerryland]

Same problem with me. The backup contains many duplicated "create trigger" commands:

> grep  "50003 CREATE.* trigger.*on" zabbix_cfg_blackbox_20231125-1740_db-mysql-6.2.16.sql | sort

/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger functions_delete before delete on functions
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger functions_delete before delete on functions
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger functions_insert after insert on functions
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger functions_insert after insert on functions
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger functions_update after update on functions
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger functions_update after update on functions
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger hosts_delete before delete on hosts
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger hosts_delete before delete on hosts
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger hosts_insert after insert on hosts
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger hosts_insert after insert on hosts
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger hosts_update after update on hosts
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger hosts_update after update on hosts
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger host_tag_delete before delete on host_tag
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger host_tag_delete before delete on host_tag
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger host_tag_insert after insert on host_tag
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger host_tag_insert after insert on host_tag
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger host_tag_update after update on host_tag
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger host_tag_update after update on host_tag
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger item_preproc_delete before delete on item_preproc
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger item_preproc_delete before delete on item_preproc
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger item_preproc_insert after insert on item_preproc
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger item_preproc_insert after insert on item_preproc
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger item_preproc_update after update on item_preproc
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger item_preproc_update after update on item_preproc
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger items_delete before delete on items
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger items_delete before delete on items
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger items_insert after insert on items
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger items_insert after insert on items
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger items_update after update on items
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger items_update after update on items
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger item_tag_delete before delete on item_tag
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger item_tag_delete before delete on item_tag
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger item_tag_insert after insert on item_tag
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger item_tag_insert after insert on item_tag
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger item_tag_update after update on item_tag
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger item_tag_update after update on item_tag
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger triggers_delete before delete on triggers
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger triggers_delete before delete on triggers
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger triggers_insert after insert on triggers
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger triggers_insert after insert on triggers
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger triggers_update after update on triggers
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger triggers_update after update on triggers
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger trigger_tag_delete before delete on trigger_tag
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger trigger_tag_delete before delete on trigger_tag
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger trigger_tag_insert after insert on trigger_tag
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger trigger_tag_insert after insert on trigger_tag
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger trigger_tag_update after update on trigger_tag
/*!50003 CREATE*/ /*!50017 DEFINER=`zabbix`@`localhost`*/ /*!50003 trigger trigger_tag_update after update on trigger_tag

If you go through the file and manually remove the duplicate triggers the process does run successfully, but Zabbix runs very slowly. Investigating further reveals that the backup contains NO INDEXES, which seems wrong :-(

 grep  -i "create.*index" zabbix_cfg_blackbox_20231125-1740_db-mysql-6.2.16.sql | grep -v "^INSERT"

EDIT: Perhaps the indexes arrive automagically. I didn't check prior to re-running a script I wrote that recreates all missing indexes, based on the Zabbix schema.sql file, so I may be speaking from a position of ignorance.

PS: Zabbix 6.4, and zabbix-backup 0.9.10

[ironbishop]

we added skip-triggers after 0.9.11 and didnt create a release yet, see https://github.com/npotorino/zabbix-backup/compare/v0.9.11...master

[kerryland]

"skip-triggers", I assume, means that triggers are no longer put in the backup script?

If so, how do the triggers get created?

Also, if I don't use "skip-triggers", does that mean the backup script will no longer have repeated trigger definitions?

Thank you!