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...
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.
Working on this bug.
(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.
Created attachment 325175 [details] sql file to fix the db schema
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.
Ran the sql file on live server. [1] close this ticket. [1] https://rt.corp.redhat.com:443/rt3/Ticket/Display.html?id=857471