support upgrading from 5.0 schemas

[dylanwh]

Details

We need to tweak the schema migration code to support coming from 5.0.

Additional info

Test Plan

Start with a 5.0 database dump. Run checksetup.pl the result should be a working bugzilla install

[dylanwh]

Current problem: chicken/egg with the new columns in the profiles table

DBD::mysql::db selectrow_hashref failed: Unknown column 'profiles.nickname' in 'field list' [for Statement "SELECT profiles.userid,profiles.login_name,profiles.realname,profiles.mybugslink AS showmybugslink,profiles.disabledtext,profiles.disable_mail,profiles.extern_id,profiles.is_enabled,DATE_FORMAT(last_seen_date, '%Y-%m-%d') AS last_seen_date,profiles.password_change_required,profiles.password_change_reason,profiles.mfa,profiles.mfa_required_date,profiles.nickname,profiles.bounce_count,first_patch_reviewed_id,review_request_count,feedback_request_count,needinfo_request_count,comment_count,creation_ts,first_patch_bug_id,last_activity_ts,last_statistics_ts FROM profiles WHERE login_name = ?"] at /Users/dylan/Git/bugzilla/harmony/Bugzilla/DB.pm line 71.

[dylanwh]

Fixed the previous errors. now we have

DBD::mysql::db do failed: Cannot change column 'attach_id': used in a foreign key constraint 'fk_bugs_activity_attach_id_attachments_attach_id' [for Statement "ALTER TABLE bugs_activity CHANGE COLUMN
                       attach_id attach_id bigint(20)"] at /Users/dylan/Git/bugzilla/harmony/Bugzilla/DB.pm line 77.
 at /Users/dylan/Git/bugzilla/harmony/Bugzilla/DB.pm line 78.
        Bugzilla::DB::__ANON__(Bugzilla::DB::Mysql=HASH(0x7fd49d6882f0), "ALTER TABLE bugs_activity CHANGE COLUMN\x{a}                     "...) called at /Users/dylan/Git/bugzilla/harmony/Bugzilla/DB.pm line 795
        Bugzilla::DB::bz_alter_column_raw(Bugzilla::DB::Mysql=HASH(0x7fd49d6882f0), "bugs_activity", "attach_id", HASH(0x7fd47d85c8a0), HASH(0x7fd48e48fe70), undef) called at /Users/dylan/Git/bugzilla/harmony/Bugzilla/DB.pm line 754
        Bugzilla::DB::bz_alter_column(Bugzilla::DB::Mysql=HASH(0x7fd49d6882f0), "bugs_activity", "attach_id", HASH(0x7fd47d85c8a0)) called at /Users/dylan/Git/bugzilla/harmony/Bugzilla/Install/DB.pm line 812
        Bugzilla::Install::DB::update_table_definitions(HASH(0x7fd4cd5724c8)) called at checksetup.pl line 272

[dylanwh]

ok, worked past those. now we're at this error

Can't call method "id" on an undefined value at /Users/dylan/Git/bugzilla/harmony/extensions/BMO/Extension.pm line 1542.

[dylanwh]

the previous error has a very reasonable fix. But now we're left with this.

DBD::mysql::db do failed: Cannot add foreign key constraint [for Statement "ALTER TABLE bugmail_filters ADD 
     CONSTRAINT fk_bugmail_filters_product_id_products_id FOREIGN KEY (product_id)
     REFERENCES products(id)
      ON UPDATE CASCADE ON DELETE CASCADE, ADD 
     CONSTRAINT fk_bugmail_filters_changer_id_profiles_userid FOREIGN KEY (changer_id)
     REFERENCES profiles(userid)
      ON UPDATE CASCADE ON DELETE CASCADE, ADD 
     CONSTRAINT fk_bugmail_filters_user_id_profiles_userid FOREIGN KEY (user_id)
     REFERENCES profiles(userid)
      ON UPDATE CASCADE ON DELETE CASCADE, ADD 
     CONSTRAINT fk_bugmail_filters_component_id_components_id FOREIGN KEY (component_id)
     REFERENCES components(id)
      ON UPDATE CASCADE ON DELETE CASCADE"] at /Users/dylan/Git/bugzilla/harmony/Bugzilla/DB.pm line 77.
 at /Users/dylan/Git/bugzilla/harmony/Bugzilla/DB.pm line 78.
        Bugzilla::DB::__ANON__(Bugzilla::DB::Mysql=HASH(0x7fc0b8e5e688), "ALTER TABLE bugmail_filters ADD \x{a}     CONSTRAINT fk_bugmail_f"...) called at /Users/dylan/Git/bugzilla/harmony/Bugzilla/DB.pm line 717
        Bugzilla::DB::bz_add_fks(Bugzilla::DB::Mysql=HASH(0x7fc0b8e5e688), "bugmail_filters", HASH(0x7fc0af79da98), HASH(0x7fc04f2347b0)) called at /Users/dylan/Git/bugzilla/harmony/Bugzilla/DB.pm line 622
        Bugzilla::DB::bz_setup_foreign_keys(Bugzilla::DB::Mysql=HASH(0x7fc0b8e5e688)) called at /Users/dylan/Git/bugzilla/harmony/Bugzilla/Install/DB.pm line 840
        Bugzilla::Install::DB::update_table_definitions(HASH(0x7fc0b8b2d0c8)) called at checksetup.pl line 272

[dylanwh]

trying each constraint in isolation is fun.

ALTER TABLE bugmail_filters 
     ADD 
     CONSTRAINT fk_bugmail_filters_product_id_products_id FOREIGN KEY (product_id)
     REFERENCES products(id)
      ON UPDATE CASCADE ON DELETE CASCADE;
ERROR 1022 (23000) at line 1: Can't write; duplicate key in table '#sql-f228_c6'
ALTER TABLE bugmail_filters 
     ADD 
     CONSTRAINT fk_bugmail_filters_changer_id_profiles_userid FOREIGN KEY (changer_id)
     REFERENCES profiles(userid)
      ON UPDATE CASCADE ON DELETE CASCADE;
ERROR 1022 (23000) at line 1: Can't write; duplicate key in table '#sql-f228_c7'
ALTER TABLE bugmail_filters 
     ADD 
     CONSTRAINT fk_bugmail_filters_user_id_profiles_userid FOREIGN KEY (user_id)
     REFERENCES profiles(userid)
      ON UPDATE CASCADE ON DELETE CASCADE;
ERROR 1022 (23000) at line 1: Can't write; duplicate key in table '#sql-f228_c8'

ALTER TABLE bugmail_filters 
     ADD 
     CONSTRAINT fk_bugmail_filters_component_id_components_id FOREIGN KEY (component_id)
     REFERENCES components(id)
      ON UPDATE CASCADE ON DELETE CASCADE;
ERROR 1215 (HY000) at line 2: Cannot add foreign key constraint

[dylanwh]

Holy crap, checksetup completes without error.

[justdave]

Does that mean this is ready to go?

[CyberShadow]

Thanks! The first commit seemed incomplete, I needed 0c67afe7f660d6ea679aa0632b99a0f523da64fe to get the process to complete.

[justdave]

Setting this back to draft, as a) what's here won't merge cleanly anyway, and b) the demo @dylanwh just showed me still breaks on the keyworks table, so I know it's WIP still anyway.

[CyberShadow]

b) the demo @dylanwh just showed me still breaks on the keyworks table

How would one reproduce this problem?

[justdave]

@dylanwh if you're around Sunday afternoon, let's play with this.

[CyberShadow]

How would one reproduce this problem?

Please :)