Bug 420611 - 3.41 Update database code in Bugzilla 3.0 to work with data from Red Hat 2.18 Bugzilla
3.41 Update database code in Bugzilla 3.0 to work with data from Red Hat 2.18...
Status: CLOSED NEXTRELEASE
Product: Bugzilla
Classification: Community
Component: Database (Show other bugs)
3.2
All Linux
high Severity medium (vote)
: ---
: ---
Assigned To: Tony Fu
:
: 408761 (view as bug list)
Depends On:
Blocks: RHBZ30UpgradeTracker 406351 427052
  Show dependency treegraph
 
Reported: 2007-12-11 16:45 EST by David Lawrence
Modified: 2013-06-24 00:15 EDT (History)
0 users

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2008-02-14 21:22:32 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)
HTML page displaying current diff information from 2.18 to 3.0 schema (150.34 KB, text/html)
2007-12-11 16:45 EST, David Lawrence
no flags Details
summary of fields exist in bugzilla2.18 and not in bugzilla3 (7.43 KB, text/plain)
2007-12-12 09:19 EST, Noura El hawary
no flags Details
summary of new fields in bugzilla3 that are set to NOT NULL (3.89 KB, text/plain)
2007-12-12 09:20 EST, Noura El hawary
no flags Details
new Bugzilla/Install/DB.pm file (132.39 KB, text/plain)
2008-01-16 00:35 EST, Tony Fu
no flags Details
new Bugzilla/DB/Schema.pm file (105.06 KB, text/plain)
2008-01-16 00:37 EST, Tony Fu
no flags Details
db-scheam-diff between rh_bz_2.18 and bz3 (155.43 KB, text/html)
2008-01-16 00:38 EST, Tony Fu
no flags Details
file used by install-update_db hook (1.69 KB, text/plain)
2008-01-18 04:26 EST, Tony Fu
no flags Details
new version of Bugzilla/DB/Schema.pm (prefix redhat specific tables' name with "redhat_") (103.82 KB, text/plain)
2008-01-18 04:29 EST, Tony Fu
no flags Details
Bugzilla/DB/Schema.pm (98.05 KB, text/plain)
2008-01-21 01:58 EST, Tony Fu
no flags Details
extension/redhat/code/install-update_db.pl (4.45 KB, text/plain)
2008-01-21 01:58 EST, Tony Fu
no flags Details
Bugzilla/DB/Schema.pm (96.63 KB, text/plain)
2008-01-22 00:49 EST, Tony Fu
no flags Details
extension/redhat/code/db_schema-abstract_schema.pl (1.75 KB, text/plain)
2008-01-22 00:50 EST, Tony Fu
no flags Details
SQL script making some changes needed for checksetup.pl to run properly (v1) (15.91 KB, text/plain)
2008-01-29 18:05 EST, David Lawrence
no flags Details
SQL script moving attachment data from attachments table to attach_data table (690 bytes, text/plain)
2008-01-31 00:47 EST, Tony Fu
no flags Details
SQL script making some changes needed for checksetup.pl to run properly (v2) (15.91 KB, text/plain)
2008-01-31 12:47 EST, David Lawrence
no flags Details

  None (edit)
Description David Lawrence 2007-12-11 16:45:09 EST
* Update Bugzilla/DB/Schema.pm to reflect Red Hat related tables and columns.
Also make changes to code to create required foreign key constraints and
indexes. Test checksetup.pl that it creates a database that has the necessary
Red Hat related schema so that no data is lost.

* Use 3.0 checksetup.pl to create new empty Bugzilla database that contains
additional Red Hat related schema without generating errors.
    
* Use latest Red Hat MySQL Bugzilla database dump file and import into new 3.0
created database. Errors should not be generated for import to be succesful.
    
* Create new database schema dump based on 3.0 stock schema with Red Hat changes
and dump to redhat/bug.mysql. This can be used for future imports to decrease
the amount time to needed to boot strap new test databases. This is similar to
process used for PostgreSQL to MySQL migration.

More Information from RT 18295:

For the looks of it the "green" rows where the datatype have changed are
fairly harmless as they are mostly
going from mediumint(9) to SMALLINT, or varchar(4000) to TINYTEXT which
are pretty much similar
datatypes. The "green" rows where the NULL constraints have changed are
still concerns.

attach_data: Not a problem depending on how we migrate the data
(explained below)
bug_severity: we will need to change to allow null, put some sortkey
values, isactive = 1 for all and then readd NOT NULL. Same with
op_sys, priority, rep_platform, and resolution
classifications: This should be fine since we do not have any
classification data in our 2.18 database anyway.
email_setting: New table which checksetup.pl should handle converting
data from profiles into.
fielddefs: Hopefully checksetup will handle the conversion for us here
as well since it is adding in custom field support
group_control_map: We will need to set this to allow nulls before import
as well. Then allow checksetup to clean up.
group_group_map: same
logincookies: Some work may be needed here as we no longer use the
ipaddr column which bz3 still does. We can remove the
not null constraint, import the data, and then populate with bogus ip
values before adding the not null back.
versions: Has a new ID column.
setting, setting_value, and status_workflow: new features that will get
populated with sane defaults by checksetup.pl. Will need to
go back later to editparams.cgi to change the values in setting and
setting_value to match our environment. Also will need to
add our own data/params keys into the UI as well.
series: is_public column will need null set before import.


Another problem scenario I have noticed is that some columns that were
originally in one table for 2.18
have been moved out to their own tables. One example at first glance is
'attachments'.

attachments.thedata was moved to attach_data.thedata.

1) we could simply add attachments.thedata column back to the Schema.pm
definition and
then let ./checksetup.pl handle the migration (which happens in
Bugzilla::Install::DB::_copy_attachments_thedata_to_attach_data)
or
2) We go ahead and convert the attachment data into the the schema
format using a data migration script.

I think personally we should go with 1) and let the checksetup.pl do
it's thing in regards to the attachments table.
If we can get a database configured that we can simply do a mysql import
directly into using one of our nightly snapshots
and then have checksetup.pl do the hard work, it will be less headache
for us. There may be other tables similar
to attachments where we would need to do the same.
Comment 1 David Lawrence 2007-12-11 16:45:09 EST
Created attachment 284681 [details]
HTML page displaying current diff information from 2.18 to 3.0 schema
Comment 2 David Lawrence 2007-12-12 00:00:57 EST
*** Bug 408761 has been marked as a duplicate of this bug. ***
Comment 3 Noura El hawary 2007-12-12 09:18:28 EST
Hi Tony ,,

Attached for you 2 summaries that you will need to create the new schema:

1- old_fields_to_be_added => and this is a summary containing all the fields
that we have in rh_bugzilla_2.18 but it doesn't exist in 3, This summary is list
of all the purple rows in the schema diff html attachment. Please use it to add
those fields to Bugzilla/Schema.pm

2- New_Not_Null_fields => and this is a summary of all the new fields that exist
 in bugzilla_3 and not in rh_bugzilla_2.18 and they are set to NOT NULL. This
summary is a list of the rows with red cells in the schema diff html attachment.
Please turn those fields to null at the time of the migration.

3- also please note that you will need to create the tables that exist in 2.18
and not 3.0 and they are all purple at the bottom of the html diff schema.
Please note that you will need to create proper indexes for these new tables.
  
Comment 4 Noura El hawary 2007-12-12 09:19:37 EST
Created attachment 285731 [details]
summary of fields exist in bugzilla2.18 and not in bugzilla3
Comment 5 Noura El hawary 2007-12-12 09:20:19 EST
Created attachment 285741 [details]
summary of new fields in bugzilla3 that are set to NOT NULL
Comment 6 Kevin Baker 2007-12-29 02:16:37 EST
Tony,

what is the time estimate to complete this bug?
Comment 7 Tony Fu 2008-01-06 23:42:37 EST
LOC estimate:

Two files need to be modified - Bugzilla/DB/Schema.pm and checksetup.pl.  I
expects to modified 100 lines for Schema.pm file and 30 lines for checksetup.pl.

The total LOC is 130.
Comment 8 Tony Fu 2008-01-08 01:40:43 EST
Modified Bugzilla/DB/Schema.pm, according the current diff information page from
rh_bz_2.18 to 3.0 schema.  All difference in diff page are commented in
Schema.pm file, starting with "#RH".

This new Schema.pm has been tested yet.  I am installing a test bz3.0 system on
my laptop, but this is a problem on installing DBD::MySQL v4 module.  Once I fix
it and install bz3.0 successfully, I will generate all new tables based on new
Scheam.pm and test it.

I have spent 6 hours on update Schema.pm file and 3 hours on installing a test
bz3.0 system so far.
Comment 10 Kevin Baker 2008-01-08 10:20:30 EST
Tony,

1) can you follow the coding standard please. 

    #RH EXTENSION <bugid>

should be

    # REDHAT EXTENSION <bugid>

I prefer the longhand REDHAT over RH since it will be easier to grep for. Less 
chance of false positives.

2) You haven't terminated some sections. 

lines     description
-------   -------------------------------------------------------------
268-277 - should the END be on line 277? seems like it should be on 273
321     - START but no END
377     - START but no END
897     - START but no END

3) Is it necessary to add '#RH comment: new field in bz3'?  
Comment 11 Kevin Baker 2008-01-08 10:44:33 EST
> 3) Is it necessary to add '#RH comment: new field in bz3'?  

I mean is it necessary to comment that these fields are new? What is the 
advantage?
Comment 12 Tony Fu 2008-01-10 01:08:05 EST
Modified Bugzilla/DB/Schema.pm to
1. follow coding standard (use #REDHAT EXTENSION instead of #RH EXTENSION; every
#REDHAT ENTENSION START has a corresponding #REDHAT ENTENSION END)
2. correct a few errors
3. add FKs

I have run checksetup.pl to use new Schema.pm to generate empty bugs database
which includes redhat specific tables without any error.

To do:
1. import latest rh_bz data into bz3 database in a testing system
2. generate a new db schema dump, based on new bz3 database
Comment 14 Tony Fu 2008-01-10 01:13:29 EST
update "hours worked" information
Comment 15 Kevin Baker 2008-01-10 14:11:19 EST
Tony,

why did you remove the 130 from the devel_whiteboard? Please leave it in for 
now. 
Comment 16 David Lawrence 2008-01-10 16:05:33 EST
Tony I have noticed that there are some columns missing in the 'bugs' table
definition such as the whiteboards and others. Did you mean this? Here is a list
from the summary of differences between 2.18 and 3 attached to this report.

         'bugs' => [
                      {
                        'KEY' => '',
                        'FIELD' => 'cust_facing',
                        'NULL' => 'YES',
                        'DEFAULT' => '',
                        'TYPE' => 'varchar(255)'
                      },
                      {
                        'KEY' => '',
                        'FIELD' => 'devel_whiteboard',
                        'NULL' => 'YES',
                        'DEFAULT' => '',
                        'TYPE' => 'varchar(4000)'
                      },
                      {
                        'KEY' => '',
                        'FIELD' => 'fixed_in',
                        'NULL' => 'YES',
                        'DEFAULT' => '',
                        'TYPE' => 'varchar(2000)'
                      },
                      {
                        'KEY' => '',
                        'FIELD' => 'internal_whiteboard',
                        'NULL' => 'YES',
                        'DEFAULT' => '',
                        'TYPE' => 'varchar(4000)'
                      },
                      {
                        'KEY' => '',
                        'FIELD' => 'qa_whiteboard',
                        'NULL' => 'YES',
                        'DEFAULT' => '',
                        'TYPE' => 'varchar(4000)'
                      }
                    ],
Comment 17 David Lawrence 2008-01-10 16:08:02 EST
The Schema.pm update that Tony posted has been used to create a test database
on bz-db1-test.devel.redhat.com. I installed the updated Schema.pm file and then
created a new database using ./checksetup.pl without error. It works well from
my test installation on my workstation as far as creating new bugs, querying,
etc. So the extra tables so far do not break anything normal but of course they
cannot yet be accessed.

mysql -h bz-db1-test.devel.redhat.com -u bug bugs3 -p<normalbugspassword>
Comment 19 Kevin Baker 2008-01-11 09:25:44 EST
Hi Tony,

1) I found an occurrence of #RH in your file! :) . To fix

   %s/#RH /# REDHAT /gc


2) Please prefix all comments added by us as

  # REDHAT this is a comment.

Otherwise it's hard to identify what is yours and what is the upstream. 

Nitpik -- put a space between the comment and the #.
i.e do
   # REDHAT
not
   #REDHAT

To fix these easily in vim do these substitutions. Order is important.

   %s/^\(\s\+#\)\(REDHAT\)/\1 \2/g
   %s/^\(\s\+#\)\(\w\)/\1 REDHAT \2/g

The first converts '#REDHAT' to '# REDHAT'
The second converts '#\w' to '# REDHAT '


Comment 20 Kevin Baker 2008-01-11 09:30:22 EST
Calculated the original time estimate as 40 hours based on 130 LOC.
Comment 21 David Lawrence 2008-01-11 17:38:51 EST
Looks better Tony. I have updated the bugs3 database on
bz-db1-test.devel.redhat.com with the updated schema definition.
Comment 22 David Lawrence 2008-01-11 17:41:44 EST
Can we get a run of the diffing tool that Noura has to see if the databases are
starting to match up in regard to Red Hat added fields?

We need to fix/diff, fix/diff, etc. until all missing tables/columns are present.

The end result should be that there are not tables/columns missing in 3.0 that
were present in 2.18, that any new columns in 3.0 that were not present in 2.18
set to allow NULLS, and that the datatypes align properly.

Dave
Comment 23 Tony Fu 2008-01-13 19:05:51 EST
(In reply to comment #22)
> Can we get a run of the diffing tool that Noura has to see if the databases are
> starting to match up in regard to Red Hat added fields?
> 
> We need to fix/diff, fix/diff, etc. until all missing tables/columns are present.
> 
> The end result should be that there are not tables/columns missing in 3.0 that
> were present in 2.18, that any new columns in 3.0 that were not present in 2.18
> set to allow NULLS, and that the datatypes align properly.
> 
> Dave


Dave,

I will do it and post the new Schema.pm file as well as the diff result once I
finish.

Tony
Comment 24 David Lawrence 2008-01-14 17:49:03 EST
I was thinking along with Kevin this morning that it may be best to create the
whiteboards as custom fields in the Bugzilla database instead of adding them to
the bugs table as we do in 2.18.

But after thinking some more this may not be possible as we need to have
security wrapped around them such as qa and devel whiteboards. Not sure if this
would be possible with custom fields as they seem always public at first glance.
If we do go the custom field route we would need to do the following:

1. Have Tony create one time import scripts to move the current whiteboard data
from 2.18 database to 3.0 database and put into the proper custom fields.
2. Add to the code that displays custom fields to look for private fields and
make sure the user is in the proper groups.

We would need to assess how much extra time it would take to do this and if is
too much effort we could just go on with do the fields directly in the bugs
table. The plus of the latter is that the data import should just go in more
smoothly without need of extra custom import scripts.

Thoughts?
Dave
Comment 25 Kevin Baker 2008-01-15 17:44:50 EST
E(In reply to comment #24)
>
> Thoughts?


Dave

experiment with one field and see what is required. Then we can extrapolate 
how much effort is required.
Comment 26 Kevin Baker 2008-01-15 18:14:51 EST
I'd like to recommend that we standardise on an prefix for our table names 
within the bugs database. 

is redhat_ to verbose for everyone? I like it because it's damn obvious. And 
with sql you can alias the name to a variable anyways


  SELECT r.x, r.y FROM redhat_xyz r

Any objections?
Comment 27 Tony Fu 2008-01-16 00:35:20 EST
Created attachment 291816 [details]
new Bugzilla/Install/DB.pm file
Comment 28 Tony Fu 2008-01-16 00:37:02 EST
Created attachment 291817 [details]
new Bugzilla/DB/Schema.pm file
Comment 29 Tony Fu 2008-01-16 00:38:11 EST
Created attachment 291818 [details]
db-scheam-diff between rh_bz_2.18 and bz3
Comment 30 Tony Fu 2008-01-16 00:54:05 EST
Besides Bugzilla/DB/Schema.pm file, we also need to modify
Bugzilla/Install/DB.pm file.  Otherwise, some definitions in Schema.pm file will
be alterred by Bugzilla/Install/DB.pm file when running checksetup.pl.

I attached the latest Schema.pm and DB.pm files(comment #27, #28), along with
db-schema-diff.html file(#29) which presented the differences between schema in
rh_bz_2.18 and newly generated bz3 schema by using latest Schema.pm and DB.pm
files.  The new schema meets the following requirements:

1. there are not tables/columns missing in 3.0 that were present in 2.18, except
the colums are obsolete in bz3 (e.g. attachments.thedata,
namedqueries.linkinfooter) or the colums are renamed in bz3 (e.g.
series.public->series.is_public, fielddefs.fieldid->fielddefs.id)

2. any new columns in 3.0 that were not present in 2.18
set to allow NULLS, except the columns are primary key in bz3 (e.g fielddefs.id)
or the columns are in new tables (e.g email_setting.user_id, email_setting.event)

3. the datatypes align properly

Please note, the current Schema.pm and DB.pm files have not tabke account into
comment #24, #26
Comment 31 Kevin Baker 2008-01-16 14:36:32 EST
Tony,

you don't need to say 

   # REDHAT this is a comment.  Update definitions to rh_bz_2.18 

this is sufficient

   # REDHAT Update definitions to rh_bz_2.18 
Comment 32 Kevin Baker 2008-01-16 15:13:20 EST
Tony,

regarding Bugzilla::Install::DB. I just found out about the "Hook" 
functionality in Bugzilla. 

  http://www.bugzilla.org/docs/tip/html/api/Bugzilla/Hook.html

If I understand the section "How Hooks Work" then we should be able to add the 
code you put in Bugzilla::Install::DB in

 extensions/redhat/code/install-update_db.pl

and it should work the same. Except that we didn't have to edit the original 
sources (a very very good thing). So can you move that code out and into the 
extensions file please.

Kev

more on hooks:   

    http://www.bugzilla.org/docs/tip/html/cust-hooks.html
Comment 33 David Lawrence 2008-01-16 16:37:06 EST
This is inline with upstream suggestions:

http://www.bugzilla.org/docs/tip/html/api/Bugzilla/Hook.html#db_schema-abstract_schema

Just curious if just simple rh_* would be sufficient? Otherwise I have no
problem with redhat_*

Some examples:

rh_external_bugzilla
rh_bz_it_map
rh_ext_bz_bug_map
Comment 34 Tony Fu 2008-01-16 20:38:12 EST
(In reply to comment #32)
> Tony,
> 
> regarding Bugzilla::Install::DB. I just found out about the "Hook" 
> functionality in Bugzilla. 
> 
>   http://www.bugzilla.org/docs/tip/html/api/Bugzilla/Hook.html
> 
> If I understand the section "How Hooks Work" then we should be able to add the 
> code you put in Bugzilla::Install::DB in
> 
>  extensions/redhat/code/install-update_db.pl
> 
> and it should work the same. Except that we didn't have to edit the original 
> sources (a very very good thing). So can you move that code out and into the 
> extensions file please.
> 
> Kev
> 
> more on hooks:   
> 
>     http://www.bugzilla.org/docs/tip/html/cust-hooks.html

Kevin,

Do you think we can put all our customized table definitions into
extensions/redhat/code/install-update_db.pl file?  So we don't need to touch
Bugzilla/DB/Schema.pm and Bugzilla/Install/DB.pm at all.


Tony

Comment 35 David Lawrence 2008-01-16 20:56:25 EST
(In reply to comment #34)
> 
> Kevin,
> 
> Do you think we can put all our customized table definitions into
> extensions/redhat/code/install-update_db.pl file?  So we don't need to touch
> Bugzilla/DB/Schema.pm and Bugzilla/Install/DB.pm at all.
> 
> 
> Tony
> 

Not a bad idea actually. Little more work but like Kevin said maybe better to
not edit the main code as much as possible. We will also need to truncate the
tables that get set up with default data such as TestProduct, etc. This way
we have completely empty tables to import the data dump into. I was looking
at the checksetup code and modules and did not see an option for creating an
empty database so we will need to do that as well.
 

Comment 36 Tony Fu 2008-01-18 04:22:46 EST
Created extension/redhat/code/install-update_db.pl and moved out red hat
extension code from Bugzilla/Install/DB.pm to it.  install-update_db hook will
use this file to change some table definitions after all tables have been created.  

Also, add prefix (redhat_) to redhat specific tables in Bugzilla/DB/Schema.pm file.

Comment 37 Tony Fu 2008-01-18 04:26:53 EST
Created attachment 292118 [details]
file used by install-update_db hook
Comment 38 Tony Fu 2008-01-18 04:29:50 EST
Created attachment 292119 [details]
new version of Bugzilla/DB/Schema.pm (prefix redhat specific tables' name with "redhat_")
Comment 39 Kevin Baker 2008-01-18 12:46:48 EST
Tony,

can you obsolete the patches that no longer apply please. To do that edit the 
attachment and click the "obsolete" box.

thanks

Kev
Comment 40 David Lawrence 2008-01-18 15:23:45 EST
Tony, 
   If we use the bz_alter_column and bz_add_column calls in install-update_db.pl
we no longer need the REDHAT EXTENSION parts in Schema.pm. Otherwise the calls
in install-update_db.pl are redundant. You can just use the Schema.pm file as is
from upstream source. 

   Also the redhat whiteboard fields and others redhat added columns can be
removed from Schema.pm and put into install-update_db.pl. For example:

    # REDHAT EXTENSION START 420611
    # REDHAT   the following fields are in rh_bz2.18, but not in bz_3.0
    cust_facing         => {TYPE => 'varchar(255)'},
    devel_whiteboard    => {TYPE => 'varchar(4000)'},
    fixed_in            => {TYPE => 'varchar(2000)'},
    internal_whiteboard => {TYPE => 'varchar(4000)'},
    qa_whiteboard       => {TYPE => 'varchar(4000)'},
    # REDHAT EXTENSION END 420611

Also for the tables with the redhat_ prefix. Did you try to see how much more
effort would be involved to move these to the separate db_schema-abstract_schema
file?

Should be just moving the redhat_* table definitions to the right file and
creating a hash in the file.

Dave
Comment 41 Tony Fu 2008-01-21 01:50:01 EST
(In reply to comment #40)
> Tony, 
>    If we use the bz_alter_column and bz_add_column calls in install-update_db.pl
> we no longer need the REDHAT EXTENSION parts in Schema.pm. Otherwise the calls
> in install-update_db.pl are redundant. You can just use the Schema.pm file as is
> from upstream source. 
I have moved these parts into install-update_db.pl, except the parts which are
used to add foreign key constraints.  I found it was hard to move foreign key
constraints from Bugzilla/DB/Schema.pm to install-update_db.pl, because
install-update_db.pl runs after checksetup.pl has inserted some initial data
into some tables.  The foreign key check will cause some problems when
install-update_db.pl tries to add foreign keys.  If we need to move these redhat
extension parts out of bugzilla/DB/Schema.pm file, one workaround I can think
about is to add a piece of code at the begining of install-update_db.pl file to
delete all checksetup.pl generated data in db.
> 
>    Also the redhat whiteboard fields and others redhat added columns can be
> removed from Schema.pm and put into install-update_db.pl. For example:
> 
>     # REDHAT EXTENSION START 420611
>     # REDHAT   the following fields are in rh_bz2.18, but not in bz_3.0
>     cust_facing         => {TYPE => 'varchar(255)'},
>     devel_whiteboard    => {TYPE => 'varchar(4000)'},
>     fixed_in            => {TYPE => 'varchar(2000)'},
>     internal_whiteboard => {TYPE => 'varchar(4000)'},
>     qa_whiteboard       => {TYPE => 'varchar(4000)'},
>     # REDHAT EXTENSION END 420611
> 
I have moved them into install-update_db.pl

> Also for the tables with the redhat_ prefix. Did you try to see how much more
> effort would be involved to move these to the separate db_schema-abstract_schema
> file?
> 
> Should be just moving the redhat_* table definitions to the right file and
> creating a hash in the file.
> 
I am tring to figure out how the db_schema-abstract hook works.  It shouldn't be
too much more effort.

Tony
> Dave
> 



Comment 42 Tony Fu 2008-01-21 01:58:03 EST
Created attachment 292335 [details]
Bugzilla/DB/Schema.pm
Comment 43 Tony Fu 2008-01-21 01:58:54 EST
Created attachment 292336 [details]
extension/redhat/code/install-update_db.pl
Comment 44 Kevin Baker 2008-01-21 16:39:15 EST
> I am tring to figure out how the db_schema-abstract hook works.  It 



I suspect you can do the foreign key changes in db_schema-abstract.

Have you spoken to the upstream about getting these FK's in the upstream code? 

Comment 45 Tony Fu 2008-01-22 00:47:19 EST
> Also for the tables with the redhat_ prefix. Did you try to see how much more
> effort would be involved to move these to the separate db_schema-abstract_schema
> file?
> 
> Should be just moving the redhat_* table definitions to the right file and
> creating a hash in the file.
> 
> Dave


I have moved redhat_* tables definition to
extension/redhat/code/db_schema-abstract_schema.pl.  The only REDHAT EXTENSION
code left in Bugzilla/DB/Schema.pm is the code to add some missing foreign keys.
 These code may be mergered into upstream code.

Tony
> 

Comment 46 Tony Fu 2008-01-22 00:49:18 EST
Created attachment 292464 [details]
Bugzilla/DB/Schema.pm
Comment 47 Tony Fu 2008-01-22 00:50:22 EST
Created attachment 292465 [details]
extension/redhat/code/db_schema-abstract_schema.pl
Comment 48 David Lawrence 2008-01-22 13:20:06 EST
I have reviewed your changes and also updated the bugs3 database on
bz-web1-test.devel.redhat.com.

Couple notes:

1. I was able to make the db_schema-abstract_schema.pl and install-update_db.pl
scripts work by creating the following file structure.

extensions/db_schema-abstract_schema/code/db_schema-abstract_schema.pl
extensions/install-update_db/code/install-update_db.pl

If you do extensions/redhat and not the above mentioned paths, you will need to
change the Bugzilla::Hook->process() calls to match the extension name in the
*.pm files as well. I recommend just naming them the way I did it so as not to
have to change more code than necessary in the *.pm files. Doing so will not
clash with anyone elses similar extensions as they are all created from scratch
by the particular admins.

2. I had to add the following to the bottom of install-update_db.pl in order for
my series cookie login support to work. Please add to your file as well. It will
not negatively impact an installation that is not using the series cookie
logins. Note that the datatype is changed to varchar(40) instead of 16.

$dbh->bz_alter_column( 'logincookies', 'cookie',
    { TYPE => 'varchar(40)', PRIMARYKEY => 1, NOTNULL => 1 } );

3. Tony, did you get a chance to look into putting all redhat related changes
into install-db_update.pl instead of Bugzilla/DB/Schema.pm and how much work it
would take to do that? I still prefer to put as much as we can in the separate
scripts instead of directly in Schema.pm unless it is much more work than is
necessary. It would seem to me anything in the # REDHAT tags could be
bz_add_column() or bz_alter_column() calls in install-update_db.pl. Please
comment on how much work it would take to do that.

Thanks Tony, looks real good. 
Dave
Comment 49 Tony Fu 2008-01-22 20:02:12 EST
(In reply to comment #48)

> 3. Tony, did you get a chance to look into putting all redhat related changes
> into install-db_update.pl instead of Bugzilla/DB/Schema.pm and how much work it
> would take to do that? I still prefer to put as much as we can in the separate
> scripts instead of directly in Schema.pm unless it is much more work than is
> necessary. It would seem to me anything in the # REDHAT tags could be
> bz_add_column() or bz_alter_column() calls in install-update_db.pl. Please
> comment on how much work it would take to do that.
> 
Actually, I have moved all changes which only apply to redhat bz into
install-db_update.pl.  The code inside "REDHAT EXTENSION START ... REDHAT
EXTENSION END" in Bugzilla/DB/Schema.pm is the code to add missing foreign keys
which actually is not redhat specific code and should apply to upstream bz as well.

I have talked to upstream about adding all foreign keys into
Bugzilla/DB/Schema.pm and basically they haven't implemented all foreign keys in
Schema.pm yet.  So it is a good chance we can add all foreign key constraints
into Schema.pm file and push it to upstream.  If upstream accepts our verion of
Bugzilla/DB/Schema.pm, we don't need to touch Bugzilla/DB/Schema.pm and
Bugzilla/Install/DB.pm files and all redhat related schema changes will
implemented by using install-update_db hook and db_schema-abstract_schema hook.


Tony

Comment 50 Kevin Baker 2008-01-22 20:32:44 EST
Tony & Dkl,

is it possible to push redhat_external_bugzilla & redhat_ext_bz_bug_map 
upstream? I don't think we need to keep that inhouse...and isn't there already 
something like this upstream?

Kev
Comment 51 David Lawrence 2008-01-22 22:07:58 EST
(In reply to comment #50)
> Tony & Dkl,
> 
> is it possible to push redhat_external_bugzilla & redhat_ext_bz_bug_map 
> upstream? I don't think we need to keep that inhouse...and isn't there already 
> something like this upstream?
> 
> Kev

I would like to see this upstream as well since most 3.x Bugzillas will finally
have a consistent web services API which is needed for this to be useful. As we
discussed though we may need to work on this in parallel with upstream and do
the final push for it once we are done with the upgrade.
Comment 52 David Lawrence 2008-01-29 18:04:03 EST
So I have for the most part pretty good luck with the in place upgrade of the
2.18 database to 3.0. I had to compose a SQL script that fixed some things so
that the checksetup.pl script would not die. I am attaching the SQL that I used
to get checksetup.pl to work to this bug. Tony, please take a look at it.
Basically it just add some columns values that are missing in our schema that
expects to be there such as isactive and sortkey for some of the enum tables.
Also it cleans up some values in the data that otherwise caused errors when the
foreign keys are added such as missing bug_id's, etc. Also some of the foreign
key constraints needed to be dropped so that columns could be updated with new
configuration.

Overall it seems to needs about 8 hours to do the full upgrade. 2 hours of that
is just to run the fix.sql script I am attaching. The longest portion of time is
spent on the attachments table renaming and data migration. Also migrating the
email settings from the profiles table to it's own table takes quite a bit of
time. We will need to run sanity checks on the data once the migration is done
to make sure no RH columns that should be there are missing and that row counts
are the same (minus the cleanup done by fix.sql).

Tony, can you look into coverting the scripts we used from Pg -> MySQL to do the
same except for MySQL -> MySQL? They should still be in the redhat/ directory
for 2.18.

Thanks
Dave
Comment 53 David Lawrence 2008-01-29 18:05:03 EST
Created attachment 293354 [details]
SQL script making some changes needed for checksetup.pl to run properly (v1)
Comment 54 David Lawrence 2008-01-29 23:29:29 EST
Ok last run took approximately 9 hours and 28 mins. One bright note is that we
can run the fix up SQL script on the live database while Bugzilla is still
running. Which should cut down the amount of actual down time while
checksetup.pl is running.

I have a test install setup now on bugdev.devel that uses this migrated data.

http://bugdev.devel.redhat.com/bugzilla

Please try it out. The Red Hat specific data is not yet visible/editable
but everything else should look correct.

Dave
Comment 55 Tony Fu 2008-01-30 02:22:15 EST
Dave,

I have modified and ran two data verifiy scripts which we used to verify data
when we did pg to mysql migration.

Here is the results:

[tfu@bz-db1-test ~]$ ./bz_upgrade_verify_table_counts.pl 
Compare table counts of bz_2.18 & bz_3 after upgrade

Table                                bz_2.18 op     bz_3
----------------------------------- -------- -- --------
attachments                           109083 ==   109083
bug_group_map                         132341 !=   132107
bug_severity                               4 ==        4
bug_status                                12 ==       12
bugs                                  256366 !=   256367
bugs_activity                        2551487 !=  2551428
bz_it_map                              20801 ==    20801
category_group_map                         0 ==        0
cc                                    296663 !=   296513
component_cc                            3633 ==     3633
components                             24124 ==    24124
dependencies                           70850 ==    70850
duplicates                             26784 ==    26784
ext_bz_bug_map                          3946 ==     3946
external_bugzilla                         28 ==       28
fielddefs                                 57 !=       65
flagexclusions                             0 ==        0
flaginclusions                           219 !=      210
flags                                 124582 ==   124582
flagtypes                                 72 ==       72
group_control_map                       2266 !=     2258
group_group_map                          384 !=      394
groups                                   148 !=      151
keyworddefs                               27 ==       27
keywords                               42248 !=    42246
logincookies                          419302 !=        3
longdescs                            1578687 !=  1578447
milestones                               165 !=      164
namedqueries                           11822 !=    11777
op_sys                                     5 ==        5
priority                                   4 ==        4
products                                  88 ==       88
profiles                              140051 ==   140051
profiles_activity                       4131 ==     4131
quips                                      1 ==        1
rep_platform                              37 ==       37
resolution                                12 ==       12
series                                  1549 ==     1549
series_categories                        637 ==      637
series_data                           198714 !=   191043
tokens                                     0 !=       34
user_group_map                        140447 !=   100714
versions                                 566 !=      560
votes                                      0 ==        0
watch                                   1093 !=     1092
whine_events                              79 ==       79
whine_queries                            113 ==      113
whine_schedules                           32 ==       32

expect logincookies & user_group_map to be different

[tfu@bz-db1-test ~]$ ./bz_upgrade_verify_max_ids.pl 
Compare max ids of tables bz_2.18 & bz3 after upgrade

Table                                bz_2.18 op     bz_3
----------------------------------- -------- -- --------
attachments                           292668 ==   292668
bug_severity                               4 ==        4
bug_status                                26 ==       26
bugs                                  429888 !=   429889
components                             87388 ==    87388
external_bugzilla                         28 ==       28
flags                                 349883 ==   349883
flagtypes                                153 ==      153
groups                                   181 !=      184
keyworddefs                               27 ==       27
op_sys                                    39 ==       39
priority                                  13 ==       13
products                                 152 ==      152
profiles                              261933 ==   261933
quips                                      1 ==        1
rep_platform                              46 ==       46
resolution                                12 ==       12
series                                  3107 ==     3107
series_categories                       1250 ==     1250
whine_events                             233 ==      233

This is another script to verify attachment data and I will run it later to
check attachment data.


Tony
Comment 56 Kevin Baker 2008-01-30 10:22:33 EST
(In reply to comment #55)
> This is another script to verify attachment data and I will run it later to
> check attachment data.

IIRC I never got that script to work. There was a problem getting mysql & 
postgres to order the attachments within a bug. That was because some bugs 
have attachments with identical dates so I couldn't predict the order between 
the RDBMS. You may be able to get it to work now since we are using mysql but 
it will need to change since in 3.2 the attachments table is indexed.

Besides just doing string compares on the comments I was trying to flush out 
utf8 issues in the conversion from Pg to Mysql. This may not be so important 
now.
Comment 57 David Lawrence 2008-01-30 10:51:52 EST
Hours worked on creating the fix.sql fix up script and commenting out lines in
install-update_db.pl: 8 hours

Of course alot more than that was taken up just watching it run but I was able
to work on other things.
Comment 58 Tony Fu 2008-01-31 00:45:33 EST
> Overall it seems to needs about 8 hours to do the full upgrade. 2 hours of that
> is just to run the fix.sql script I am attaching. The longest portion of time is
> spent on the attachments table renaming and data migration. Also migrating the
> email settings from the profiles table to it's own table takes quite a bit of
> time. 

Dave,

I found the checksetup.pl called a function which used INSERT command to move
attach data from attachments table to attach_data table.  Since the attachments
table is pretty larger, it must take very long to import data using INSERT command.

I have tried to use LOAD DATA command to move data from attachments table to
attach_data table and it spent 70 minutes to import all attachment data.  I
attached the SQL script here and they can be appended into your sql script to run.

If you think it is worth, we can use this way to speed up moving email settings
from prifles table to its own table as well.


Tony 
Comment 59 Tony Fu 2008-01-31 00:47:26 EST
Created attachment 293546 [details]
SQL script moving attachment data from attachments table to attach_data table
Comment 60 David Lawrence 2008-01-31 12:31:14 EST
(In reply to comment #55)
> Dave,
> 
> I have modified and ran two data verifiy scripts which we used to verify data
> when we did pg to mysql migration.
> 
> Here is the results:
> 
> [tfu@bz-db1-test ~]$ ./bz_upgrade_verify_table_counts.pl 
> Compare table counts of bz_2.18 & bz_3 after upgrade
> 
> Table                                bz_2.18 op     bz_3
> ----------------------------------- -------- -- --------
> attachments                           109083 ==   109083
> bug_group_map                         132341 !=   132107
> bug_severity                               4 ==        4
> bug_status                                12 ==       12
> bugs                                  256366 !=   256367
> bugs_activity                        2551487 !=  2551428
> bz_it_map                              20801 ==    20801
> category_group_map                         0 ==        0
> cc                                    296663 !=   296513
> component_cc                            3633 ==     3633
> components                             24124 ==    24124
> dependencies                           70850 ==    70850
> duplicates                             26784 ==    26784
> ext_bz_bug_map                          3946 ==     3946
> external_bugzilla                         28 ==       28
> fielddefs                                 57 !=       65
> flagexclusions                             0 ==        0
> flaginclusions                           219 !=      210
> flags                                 124582 ==   124582
> flagtypes                                 72 ==       72
> group_control_map                       2266 !=     2258
> group_group_map                          384 !=      394
> groups                                   148 !=      151
> keyworddefs                               27 ==       27
> keywords                               42248 !=    42246
> logincookies                          419302 !=        3
> longdescs                            1578687 !=  1578447
> milestones                               165 !=      164
> namedqueries                           11822 !=    11777
> op_sys                                     5 ==        5
> priority                                   4 ==        4
> products                                  88 ==       88
> profiles                              140051 ==   140051
> profiles_activity                       4131 ==     4131
> quips                                      1 ==        1
> rep_platform                              37 ==       37
> resolution                                12 ==       12
> series                                  1549 ==     1549
> series_categories                        637 ==      637
> series_data                           198714 !=   191043
> tokens                                     0 !=       34
> user_group_map                        140447 !=   100714
> versions                                 566 !=      560
> votes                                      0 ==        0
> watch                                   1093 !=     1092
> whine_events                              79 ==       79
> whine_queries                            113 ==      113
> whine_schedules                           32 ==       32
> 
> expect logincookies & user_group_map to be different
> 
> [tfu@bz-db1-test ~]$ ./bz_upgrade_verify_max_ids.pl 
> Compare max ids of tables bz_2.18 & bz3 after upgrade
> 
> Table                                bz_2.18 op     bz_3
> ----------------------------------- -------- -- --------
> attachments                           292668 ==   292668
> bug_severity                               4 ==        4
> bug_status                                26 ==       26
> bugs                                  429888 !=   429889
> components                             87388 ==    87388
> external_bugzilla                         28 ==       28
> flags                                 349883 ==   349883
> flagtypes                                153 ==      153
> groups                                   181 !=      184
> keyworddefs                               27 ==       27
> op_sys                                    39 ==       39
> priority                                  13 ==       13
> products                                 152 ==      152
> profiles                              261933 ==   261933
> quips                                      1 ==        1
> rep_platform                              46 ==       46
> resolution                                12 ==       12
> series                                  3107 ==     3107
> series_categories                       1250 ==     1250
> whine_events                             233 ==      233
> 
> This is another script to verify attachment data and I will run it later to
> check attachment data.
> 
> 
> Tony
> 

A lot of the differences can be attributed to the cleanup script that is being
ran before the upgrade process is started. Such as the invalid bug_id, profiles,
etc.

Also I created a test bug report which would account for the extra row in the
bugs table.

Dave
Comment 61 David Lawrence 2008-01-31 12:34:20 EST
(In reply to comment #59)
> Created an attachment (id=293546) [edit]
> SQL script moving attachment data from attachments table to attach_data table
> 

Thanks Tony. We can add this to the fix.sql script and do it before the
checksetup.pl process. Hopefully will speed things up considerably.

For the email preferences, we will need to leave that alone I think as it is
doing a lot of extra logic other than a straight move from one table to another.

See Bugzilla::Install::DB::_migrate_email_prefs_to_new_table()

Dave
Comment 62 David Lawrence 2008-01-31 12:38:33 EST
Also at some point we will need to address the other data corruption issues that
pop up with the new sanitycheck.cgi that comes with 3.2.

To see the fun check out
http://bugdev.devel.redhat.com/bugzilla/sanitycheck.cgi?rebuildkeywordcache=1&remove_invalid_flags=1

I will file a new bug to address this issue as it is not a stopper for this bug
report.

Dave
Comment 63 David Lawrence 2008-01-31 12:47:02 EST
Created attachment 293608 [details]
SQL script making some changes needed for checksetup.pl to run properly (v2)
Comment 64 Kevin Baker 2008-02-01 10:07:08 EST
Dave & Tony,

as Dave hinted, are there some SQL fixes we could run during the next outage 
window on the 8th of Feb? 

If we fix them now so we reduce future testing time and ultimately reduce the 
upgrade time.

For instance, do we affect 2.18 if we add sortkey to those tables? Or too 
dangerous?

Think of it as data cleanup. I'm sure we have an RT for that somewhere...don't 
we Tony! ;)
Comment 65 Tony Fu 2008-02-03 22:28:05 EST
(In reply to comment #64)
> Dave & Tony,
> 
> as Dave hinted, are there some SQL fixes we could run during the next outage 
> window on the 8th of Feb? 
> 
> If we fix them now so we reduce future testing time and ultimately reduce the 
> upgrade time.
> 
> For instance, do we affect 2.18 if we add sortkey to those tables? Or too 
> dangerous?
> 
> Think of it as data cleanup. I'm sure we have an RT for that somewhere...don't 
> we Tony! ;)


We did have a rt ticket #15270 about inconsistent data between bugs table and
components table and I think Dave has run script to fix it already.  There isn't
a master ticket to record all data cleanup related jobs and myabe we can create it.


Tony
Comment 66 David Lawrence 2008-02-04 00:09:31 EST
(In reply to comment #65)
> (In reply to comment #64)
> > Dave & Tony,
> > 
> > as Dave hinted, are there some SQL fixes we could run during the next outage 
> > window on the 8th of Feb? 
> > 
> > If we fix them now so we reduce future testing time and ultimately reduce the 
> > upgrade time.

Agreed. Sooner is better than later and will smooth the upgrade process.

> > For instance, do we affect 2.18 if we add sortkey to those tables? Or too 
> > dangerous?

This should not hurt anything as long as we are reference the columns by name
and also if we do not edit/add any any values to the enum tables without 
properly populating the new columns with valid values. But these tables change
very infrequently anyway.

Tony, can you verify that we load the data from those tables by specifying the
column names in the 2.18 code?
 
> We did have a rt ticket #15270 about inconsistent data between bugs table and
> components table and I think Dave has run script to fix it already.  There isn't
> a master ticket to record all data cleanup related jobs and myabe we can
create it.

Tony, do you mind creating a tracker bug in Bugzilla for the data cleanup
effort? The also create individual bugs for each of the cleanup tasks and attach
them to the tracker. These should not be attached to the 3.0 dependency tree as
we should hopefully be able to fix these up prior to the 3.0 roll out.
 
A good place to start for adding bugs to the data cleanup dependency is to take
each section of the sanitycheck error output and make a bug for it. Then they
can each be solved separately. Alot of what we get sent daily in the sanity
check emails are the same as what is showing up in 3.0's sanitycheck.cgi as
well. But there are some additional ones in 3.0 that we should address as well
as it seems they are doing more checks than what was done in 2.18.

The end result should be a clean sanitycheck output in both 2.18 and 3.0.

We can schedule the cleanup incrementally as part of our normal Thursday updates.

Thanks
Dave


Comment 67 Tony Fu 2008-02-14 21:22:32 EST
Have checked in all updates into cvs repo.

Closing this bugs.

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