Bug 461220 - Database fixes to bring current schema in sync with 3.2 specifications.
Database fixes to bring current schema in sync with 3.2 specifications.
Status: CLOSED CURRENTRELEASE
Product: Bugzilla
Classification: Community
Component: Database (Show other bugs)
3.2
All Linux
medium Severity medium (vote)
: ---
: ---
Assigned To: Tony Fu
:
Depends On:
Blocks:
  Show dependency treegraph
 
Reported: 2008-09-05 00:12 EDT by David Lawrence
Modified: 2013-06-24 00:08 EDT (History)
1 user (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2008-12-02 18:50:04 EST
Type: ---
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)
sql file to fix the db schema (1.76 KB, text/plain)
2008-12-01 01:03 EST, Tony Fu
dkl: review+
Details

  None (edit)
Description David Lawrence 2008-09-05 00:12:03 EDT
During the 2.18 to 3.2 migration, a script (db_fixup.sql) was used to manipulate
the 2.18 database to ease the transition to the 3.2 schema. Upstrean noted some 
inconsistencies with the changes that need to be addresses post-upgrade. Here are the changes needed.

> ALTER TABLE resolution ADD COLUMN isactive BOOLEAN;

        That should be TINYINT NOT NULL DEFAULT 1;

> ALTER TABLE resolution ADD COLUMN sortkey INTEGER;

        That should be SMALLINT NOT NULL DEFAULT 0;

(Comments above apply to other isactive and sortkey fields.)

> UPDATE bug_severity SET sortkey = 1 WHERE value = 'urgent';
> UPDATE bug_severity SET sortkey = 2 WHERE value = 'high';

        You might want to make those 10 and 20 or 100 and
200--otherwise it's very difficult to add new things in the middle.
(Same goes for the other enum fields, particularly the ones that have
lots of values.)

> ---Some bugs set to 0 for qa_contact or had an empty string ''.
> Change to NULL. Fix others to proper value.

        qa_contact should be an int field (check Bugzilla::DB::Schema
or ravenbrook's schema docs), even in 2.18...
Comment 1 David Lawrence 2008-11-24 15:13:32 EST
We should do this at some point for consistency. Maybe Tony can work up a SQL script to make the needed changes. Changing to ASSIGNED.
Comment 2 Tony Fu 2008-11-25 01:05:23 EST
Working on this bug.
Comment 3 Tony Fu 2008-12-01 00:42:31 EST
(In reply to comment #0)
> During the 2.18 to 3.2 migration, a script (db_fixup.sql) was used to
> manipulate
> the 2.18 database to ease the transition to the 3.2 schema. Upstrean noted some 
> inconsistencies with the changes that need to be addresses post-upgrade. Here
> are the changes needed.
> 
> > ALTER TABLE resolution ADD COLUMN isactive BOOLEAN;
> 
>         That should be TINYINT NOT NULL DEFAULT 1;
> 
> > ALTER TABLE resolution ADD COLUMN sortkey INTEGER;
> 
>         That should be SMALLINT NOT NULL DEFAULT 0;
> 
> (Comments above apply to other isactive and sortkey fields.)

Looked through all tables and found following tables have isactive and sortkey columns
1. resolution
2. bug_severity
3. bug_status
4. op_sys
5. priority
6. rep_platform
7. groups (this table doesn't include sortkey column)

The attached sql file will change these columns aligning to upstream schema.


> 
> > UPDATE bug_severity SET sortkey = 1 WHERE value = 'urgent';
> > UPDATE bug_severity SET sortkey = 2 WHERE value = 'high';
> 
>         You might want to make those 10 and 20 or 100 and
> 200--otherwise it's very difficult to add new things in the middle.
> (Same goes for the other enum fields, particularly the ones that have
> lots of values.)
> 
I think we only need to change the sortkey value in bug_severity and priority tables, the order of sortkey value in other tables is not very important and we can add new sortkey into these tables without change existing sortkey values.

> > ---Some bugs set to 0 for qa_contact or had an empty string ''.
> > Change to NULL. Fix others to proper value.
> 
>         qa_contact should be an int field (check Bugzilla::DB::Schema
> or ravenbrook's schema docs), even in 2.18...
This issue has been fixed in our current db.
mysql> select count(*) from bugs where qa_contact=0;
+----------+
| count(*) |
+----------+
|        0 | 
+----------+
1 row in set (0.34 sec)

mysql> select count(*) from bugs where qa_contact='';
+----------+
| count(*) |
+----------+
|        0 | 
+----------+
1 row in set (0.33 sec)

Please see attached sql file for details.
Comment 4 Tony Fu 2008-12-01 01:03:14 EST
Created attachment 325175 [details]
sql file to fix the db schema
Comment 5 David Lawrence 2008-12-01 16:26:09 EST
Comment on attachment 325175 [details]
sql file to fix the db schema

Looks good Tony. Send email to mschick/bhowmick to execute the SQL and close this ticket when done.
Comment 6 Tony Fu 2008-12-02 18:50:04 EST
Ran the sql file on live server. [1]

close this ticket.

[1] https://rt.corp.redhat.com:443/rt3/Ticket/Display.html?id=857471

Note You need to log in before you can comment on or make changes to this bug.