776982 - Increase longdescs primary key size to INTSERIAL for future growth

Status: RESOLVED FIXED

(Bugzilla :: Database, defect)

Description

[David Lawrence [:dkl]]

+++ This bug was initially created as a clone of Bug #776972 +++

Currently longdescs primary key is MEDIUMSERIAL which is limited to ~8.3 million rows. Several large Bugzilla installations will hit that limit sooner rather than later so the size needs to be increases to INTSERIAL.

Patch coming

Comment 1

[David Lawrence [:dkl]]

Attachment: Patch to increase primary key size of longdescs (v1)

Comment 2

[Frédéric Buclin]

Comment on attachment 645400 [details] [diff] [review]
Patch to increase primary key size of longdescs (v1)

>=== modified file 'Bugzilla/Install/DB.pm'

>     # 2005-12-07 [e-mail] -- Bug 225221
>-    $dbh->bz_add_column('longdescs', 'comment_id',
>-        {TYPE => 'MEDIUMSERIAL', NOTNULL => 1, PRIMARYKEY => 1});
>+    # 2012-07-24 [e-mail] - Bug 776982
>+    _fix_longdescs_primary_key();

Here, simply replace MEDIUMSERIAL by INTSERIAL, and call _fix_longdescs_primary_key() only later (i.e. chronologically).


>+sub _fix_longdescs_primary_key {
>+    if (!$dbh->bz_column_info('longdescs', 'comment_id')) {
>+        $dbh->bz_add_column('longdescs', 'comment_id', $column_def);
>+    }

This IF block is useless with the change I suggested above.


>+    elsif ($dbh->bz_column_info('longdescs', 'comment_id')->{TYPE} ne 'INTSERIAL') {
>+        $dbh->bz_drop_fk('bugs_activity', 'comment_id');

You should rather call $dbh->bz_drop_related_fks('longdescs', 'comment_id') and let it find which FKs point to this column and remove them. This way, if another FK points to longdescs.comment_id (e.g. in a customized installation), it will be found and deleted.


>+        $dbh->bz_add_fk('bugs_activity', 'comment_id', 
>+                        {TABLE => 'longdescs', COLUMN => 'comment_id', DELETE => 'CASCADE'});

You don't need to call bz_add_fk() yourself. This is done automatically when checksetup.pl is called.

Comment 3

[David Lawrence [:dkl]]

Attachment: Patch to increase primary key size of longdescs (v2)

Thanks for the review and good suggestions. New patch.

Comment 4

[Frédéric Buclin]

Comment on attachment 645454 [details] [diff] [review]
Patch to increase primary key size of longdescs (v2)

>=== modified file 'Bugzilla/Install/DB.pm'

>     $dbh->bz_add_column('longdescs', 'comment_id',
>-        {TYPE => 'MEDIUMSERIAL', NOTNULL => 1, PRIMARYKEY => 1});
>+        {TYPE => 'INTSERIAL', NOTNULL => 1, PRIMARYKEY => 1});

Your patch looks mostly good, but you forgot to fix one place, see line 595:

  $dbh->bz_add_column('bugs_activity', 'comment_id', {TYPE => 'INT3'});

INT3 must be replaced by INT4, for consistency with your change above.


Also, your patch is going to conflict with bug 776972 so you should commit that one first, and then update this patch. :)

Comment 5

[David Lawrence [:dkl]]

Attachment: Patch to increase primary key size of longdescs (v3)

Comment 6

[Frédéric Buclin]

Comment on attachment 645719 [details] [diff] [review]
Patch to increase primary key size of longdescs (v3)

r=LpSolit

Comment 7

[David Lawrence [:dkl]]

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