2052103 - Migration 5.0 -> Harmony: Referencing column 'attach_id' and referenced column 'attach_id' in foreign key constraint 'fk_bugs_activity_attach_id_attachments_attach_id' are incompatible.

Status: NEW

(Bugzilla :: Installation & Upgrading, defect)

Description

[Dave Miller [:justdave]]

Steps to reproduce:

1. Load database with a snapshot from version 5.0.4
2. Run checksetup.pl

This appears to be non-fatal, but almost certainly leaves the database semi-broken:

DBD::mysql::db do failed: Referencing column 'attach_id' and referenced column 'attach_id' in foreign key constraint 'fk_bugs_activity_attach_id_attachments_attach_id' are incompatible. [for Statement "ALTER TABLE `bugs_activity` CHANGE COLUMN attach_id attach_id bigint(20)"] at /app/Bugzilla/DB.pm line 71.

The code in question is at https://github.com/bugzilla/harmony/blob/28a325e73270397ffa19ff29af830e5eedc04287/Bugzilla/Install/DB.pm#L810-L818 and this is a regression from Bug 1588221

  # Bug 1588221 - [e-mail]
  $dbh->bz_alter_column('bugs_activity', 'attach_id', {TYPE => 'INT5'});
  $dbh->bz_alter_column('attachments', 'attach_id',
    {TYPE => 'BIGSERIAL', NOTNULL => 1, PRIMARYKEY => 1});
  $dbh->bz_alter_column('attach_data', 'id',
    {TYPE => 'INT5', NOTNULL => 1, PRIMARYKEY => 1});
  $dbh->bz_alter_column('flags',            'attach_id', {TYPE => 'INT5'});
  $dbh->bz_alter_column('user_request_log', 'attach_id', {TYPE => 'INT5', NOTNULL => 0});
  _populate_attachment_storage_class();

The fix is pretty easy... each of those bz_alter_column calls needs to be preceded by a call to bz_drop_related_fks on the same table/column pair. There's a block near the end of the database setup process where it will automatically restore all missing foreign key references, so we don't have to worry about putting them back afterwards.

Other places that do this wrap it in a loop which also checks if it needs to be done before dropping anything. See _update_flagtypes_id at https://github.com/bugzilla/harmony/blob/28a325e73270397ffa19ff29af830e5eedc04287/Bugzilla/Install/DB.pm#L908-L930

This would probably be a good candidate for changing it to work that same way.

Comment 1

[BMO Github Automation]

Attachment: [bugzilla/harmony] [harmony] Bug 2052103: Drop FK references around altering attachment id type (#157)

Comment 2

[Dave Miller [:justdave]]

Verified the patch that caused this came from BMO and 5.2 never had it, so is not affected.