The mysql commands you need to run are stored in

Question

Have got this after the most recent CiviCRM 5.58.1. update on Drupal 7 ( same udate went fine on WP sites ).

ConfigAndLog/CiviCRM.trigger63f7a62151e54WPFX4db1365ff9d7f93fb47a7eb13184cce6.sql contains, well, a lot of "delete/insert trigger" instructions; however any attempt executing even a single command, like thisone

CREATE TRIGGER civicrm_campaign_group_after_insert after insert ON civicrm_campaign_group FOR EACH ROW BEGIN IF ( @civicrm_disable_logging IS NULL OR @civicrm_disable_logging = 0 ) THEN INSERT INTO log_civicrm_campaign_group (id, campaign_id, group_type, entity_table, entity_id, log_conn_id, log_user_id, log_action) VALUES ( NEW.id, NEW.campaign_id, NEW.group_type, NEW.entity_table, NEW.entity_id, COALESCE(@uniqueID, LEFT(CONCAT('c_', unix_timestamp()/3600, CONNECTION_ID()), 17)), @civicrm_user_id, 'insert')

in phpmyadmin leads to "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1"

Looks like this breaks normal system operations and leads to errors like the one below:

[db_error: message="DB Error: constraint violation" code=-3 mode=callback callback=CRM_Core_Error::exceptionHandler prefix="" info="INSERT INTO civicrm_mailing_event_queue (email_id , contact_id , hash ) VALUES ( 6984 , 7051 , '0828cd3309c1d93d' ) [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (comedyev_crm_civicrm.civicrm_mailing_event_queue, CONSTRAINT FK_civicrm_mailing_event_queue_job_id FOREIGN KEY (job_id) REFERENCES civicrm_mailing_job (id) ON DELETE CASCADE)]"] )

Not quite sure why the triggers weren't created while update; how do I get out from here please?

Answer 1

For some reason you have the civi setting logging_no_trigger_permission set. Possibly at one time it temporarily solved a different problem.

You can remove it with DELETE FROM civicrm_setting WHERE name='logging_no_trigger_permission';.

Then clearing cache or cv flush or visit civicrm/menu/rebuild?reset=1&triggerRebuild=1 should rebuild the triggers.