Missing unique key for constraint 'director_generated_config_activity' in the referenced table 'director_activity_log'

[nilmerg]

Importing the MySQL schema on MySQL 8.4.0 shows this error:

ERROR 6125 (HY000) at line 108: Failed to add the foreign key constraint. Missing unique key for constraint 'director_generated_config_activity' in the referenced table 'director_activity_log'

This is caused by a deprecation in v8.4.0: https://dev.mysql.com/doc/refman/8.4/en/mysql-nutshell.html#mysql-nutshell-deprecations (Nonstandard foreign keys)

[mcsken]

Ran into this while trying to setup Icinga for the first time. Highly annoying as there is no way to find this info without analysing the SQL queries, and this is the only reference relating to Icinga and it won't show up on google searches.

I ended up fixing the SQL schema myself (with the help of AI since I am not highly experience in neither SQL or icinga):

Warning

Working AI generated solution.

Replaced the following:

CREATE TABLE director_activity_log (
  id BIGINT(20) UNSIGNED AUTO_INCREMENT NOT NULL,
  object_type VARCHAR(64) NOT NULL,
  object_name VARCHAR(255) NOT NULL,
  action_name ENUM('create', 'delete', 'modify') NOT NULL,
  old_properties TEXT DEFAULT NULL COMMENT 'Property hash, JSON',
  new_properties TEXT DEFAULT NULL COMMENT 'Property hash, JSON',
  author VARCHAR(64) NOT NULL,
  change_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  checksum VARBINARY(20) NOT NULL,
  parent_checksum VARBINARY(20) DEFAULT NULL,
  PRIMARY KEY (id),
  INDEX sort_idx (change_time),
  INDEX search_idx (object_name),
  INDEX search_idx2 (object_type(32), object_name(64), change_time),
  INDEX search_author (author),
  INDEX checksum (checksum),
  UNIQUE INDEX idx_checksum (checksum)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE director_generated_config (
  checksum VARBINARY(20) NOT NULL COMMENT 'SHA1(last_activity_checksum;file_path=checksum;file_path=checksum;...)',
  director_version VARCHAR(64) DEFAULT NULL,
  director_db_version INT(10) DEFAULT NULL,
  duration INT(10) UNSIGNED DEFAULT NULL COMMENT 'Config generation duration (ms)',
  first_activity_checksum VARBINARY(20) NOT NULL,
  last_activity_checksum VARBINARY(20) NOT NULL,
  PRIMARY KEY (checksum),
  INDEX idx_checksum (checksum),
  CONSTRAINT fk_director_generated_config_activity
    FOREIGN KEY (last_activity_checksum)
    REFERENCES director_activity_log(checksum)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;