505165 - The flags.setter_id DB column cannot be NULL

Status: RESOLVED FIXED

(Bugzilla :: Database, defect)

Description

[Frédéric Buclin]

A flag always has a setter. The DB should enforce this (it already has a FK pointing to profiles.userid). NOTNULL => 1 is missing.

Comment 1

[Max Kanat-Alexander]

Isn't it NULL when there's only a requester and no responder?

Comment 2

[Frédéric Buclin]

No idea what a responder is. Do you mean requestee? A flag has two user fields, one setter/requester (flags.setter_id, which must always be set), and the requestee (flags.requestee_id).

Comment 3

[Max Kanat-Alexander]

By responder I mean the person who grants +/-. I thought that if you just requested a flag, then requester_id was set, but setter_id was not.

Comment 4

[Frédéric Buclin]

Max, I wrote:

 $dbh->bz_alter_column('flags', 'setter_id', {TYPE => 'INT3', NOTNULL => 1});

but Pg crashes with:

Adding foreign key: flags.setter_id -> profiles.userid...
DBD::Pg::db do failed: ERROR:  constraint « fk_flags_setter_id_profiles_userid » for table « flags » already exists at Bugzilla/DB.pm line 515
        Bugzilla::DB::bz_add_fk('Bugzilla::DB::Pg=HASH(0xa2e6130)', 'flags', 'setter_id', 'HASH(0xa342250)') called at Bugzilla/DB.pm line 450
        Bugzilla::DB::bz_setup_foreign_keys('Bugzilla::DB::Pg=HASH(0xa2e6130)') called at Bugzilla/Install/DB.pm line 593
        Bugzilla::Install::DB::update_table_definitions('HASH(0x8a847b0)') called at ./checksetup.pl line 192
 at Bugzilla/DB.pm line 515
        Bugzilla::DB::bz_add_fk('Bugzilla::DB::Pg=HASH(0xa2e6130)', 'flags', 'setter_id', 'HASH(0xa342250)') called at Bugzilla/DB.pm line 450
        Bugzilla::DB::bz_setup_foreign_keys('Bugzilla::DB::Pg=HASH(0xa2e6130)') called at Bugzilla/Install/DB.pm line 593
        Bugzilla::Install::DB::update_table_definitions('HASH(0x8a847b0)') called at ./checksetup.pl line 192


Why is checksetup.pl trying to add the FK again?

Comment 5

[Max Kanat-Alexander]

(In reply to comment #4)
> Why is checksetup.pl trying to add the FK again?

  Hmm, dunnow--could be a bug. Does it still happen if you create a fresh database and then apply your patch and run checksetup again?

Comment 6

[Frédéric Buclin]

Attachment: patch, v1

OK, I cannot reproduce the error mentioned above. I was probably playing with a broken DB (due to another patch I was testing).

Comment 7

[Frédéric Buclin]

Attachment: patch, v1

Oops, I missed one file.

Comment 8

[Frédéric Buclin]

Committing to: bzr+ssh://[e-mail]/bugzilla/trunk/
modified Bugzilla/DB/Schema.pm
modified Bugzilla/Install/DB.pm
Committed revision 7532.

Committing to: bzr+ssh://[e-mail]/bugzilla/4.0/
modified Bugzilla/DB/Schema.pm
modified Bugzilla/Install/DB.pm
Committed revision 7435.