civicrm_relationship table can't be updated when logging is enabled
Overview
After the created_date
and modified_date
columns were added to the civicrm_relationship
table in commit a0efcd4d, the table does not allow any update or insert actions.
There are triggers in that table that try to apply all updates and inserts also to the log_civicrm_relationship
table, where the created_date
and modified_date
columns don't exist. This leads to the misleading error "ERROR 1054 (42S22): Unknown column 'created_date' in 'field list'"
(at least it was misleading for me, because I had a hard time figuring out why the field should not exist, while I could see it in the civicrm_relationship
table, where the error occurs on updates and inserts).
civicrm.civicrm_relationship.civicrm_relationship_after_insert
create definer = civicrm@localhost trigger civicrm_relationship_after_insert
after insert
on civicrm_relationship
for each row
BEGIN IF ( @civicrm_disable_logging IS NULL OR @civicrm_disable_logging = 0 ) THEN INSERT INTO log_civicrm_relationship (`id`, `contact_id_a`, `contact_id_b`, `relationship_type_id`, `start_date`, `end_date`, `is_active`, `description`, `is_permission_a_b`, `is_permission_b_a`, `case_id`, `created_date`, `modified_date`, log_conn_id, log_user_id, log_action) VALUES ( NEW.`id`, NEW.`contact_id_a`, NEW.`contact_id_b`, NEW.`relationship_type_id`, NEW.`start_date`, NEW.`end_date`, NEW.`is_active`, NEW.`description`, NEW.`is_permission_a_b`, NEW.`is_permission_b_a`, NEW.`case_id`, NEW.`created_date`, NEW.`modified_date`, COALESCE(@uniqueID, LEFT(CONCAT('c_', unix_timestamp()/3600, CONNECTION_ID()), 17)), @civicrm_user_id, 'insert'); END IF;
civicrm.civicrm_relationship.civicrm_relationship_after_update
create definer = civicrm@localhost trigger civicrm_relationship_after_update
after update
on civicrm_relationship
for each row
BEGIN IF ( (IFNULL(OLD.`id`,'') <> IFNULL(NEW.`id`,'') OR IFNULL(OLD.`contact_id_a`,'') <> IFNULL(NEW.`contact_id_a`,'') OR IFNULL(OLD.`contact_id_b`,'') <> IFNULL(NEW.`contact_id_b`,'') OR IFNULL(OLD.`relationship_type_id`,'') <> IFNULL(NEW.`relationship_type_id`,'') OR IFNULL(OLD.`start_date`,'') <> IFNULL(NEW.`start_date`,'') OR IFNULL(OLD.`end_date`,'') <> IFNULL(NEW.`end_date`,'') OR IFNULL(OLD.`is_active`,'') <> IFNULL(NEW.`is_active`,'') OR IFNULL(OLD.`description`,'') <> IFNULL(NEW.`description`,'') OR IFNULL(OLD.`is_permission_a_b`,'') <> IFNULL(NEW.`is_permission_a_b`,'') OR IFNULL(OLD.`is_permission_b_a`,'') <> IFNULL(NEW.`is_permission_b_a`,'') OR IFNULL(OLD.`case_id`,'') <> IFNULL(NEW.`case_id`,'') OR IFNULL(OLD.`created_date`,'') <> IFNULL(NEW.`created_date`,'')) AND ( @civicrm_disable_logging IS NULL OR @civicrm_disable_logging = 0 ) ) THEN INSERT INTO log_civicrm_relationship (`id`, `contact_id_a`, `contact_id_b`, `relationship_type_id`, `start_date`, `end_date`, `is_active`, `description`, `is_permission_a_b`, `is_permission_b_a`, `case_id`, `created_date`, `modified_date`, log_conn_id, log_user_id, log_action) VALUES (NEW.`id`, NEW.`contact_id_a`, NEW.`contact_id_b`, NEW.`relationship_type_id`, NEW.`start_date`, NEW.`end_date`, NEW.`is_active`, NEW.`description`, NEW.`is_permission_a_b`, NEW.`is_permission_b_a`, NEW.`case_id`, NEW.`created_date`, NEW.`modified_date`, COALESCE(@uniqueID, LEFT(CONCAT('c_', unix_timestamp()/3600, CONNECTION_ID()), 17)), @civicrm_user_id, 'update'); END IF;
I solved that problem by manually adding both columns to the log_civicrm_relationship
table, but there should be a more sustainable way to do that.
Reproduction steps
- Activate logging
- Try to add or update a relationship via UI or MySQL
- Get an error
"ERROR 1054 (42S22): Unknown column 'created_date' in 'field list'"
.
- CiviCRM: 5.47.2
- PHP: 7.4.3_
- CMS: Drupal 9.3.8
- Database: _10.3.34-MariaDB-0ubuntu0.20.04.1 _
- Web Server: _Apache/2.4.41 (Ubuntu) _