* 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.
Created attachment 284681 [details] HTML page displaying current diff information from 2.18 to 3.0 schema
*** Bug 408761 has been marked as a duplicate of this bug. ***
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.
Created attachment 285731 [details] summary of fields exist in bugzilla2.18 and not in bugzilla3
Created attachment 285741 [details] summary of new fields in bugzilla3 that are set to NOT NULL
Tony, what is the time estimate to complete this bug?
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.
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.
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'?
> 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?
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
update "hours worked" information
Tony, why did you remove the 130 from the devel_whiteboard? Please leave it in for now.
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)' } ],
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>
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 '
Calculated the original time estimate as 40 hours based on 130 LOC.
Looks better Tony. I have updated the bugs3 database on bz-db1-test.devel.redhat.com with the updated schema definition.
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
(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
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
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.
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?
Created attachment 291816 [details] new Bugzilla/Install/DB.pm file
Created attachment 291817 [details] new Bugzilla/DB/Schema.pm file
Created attachment 291818 [details] db-scheam-diff between rh_bz_2.18 and bz3
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
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
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
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
(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
(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.
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.
Created attachment 292118 [details] file used by install-update_db hook
Created attachment 292119 [details] new version of Bugzilla/DB/Schema.pm (prefix redhat specific tables' name with "redhat_")
Tony, can you obsolete the patches that no longer apply please. To do that edit the attachment and click the "obsolete" box. thanks Kev
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
(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 >
Created attachment 292335 [details] Bugzilla/DB/Schema.pm
Created attachment 292336 [details] extension/redhat/code/install-update_db.pl
> 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?
> 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 >
Created attachment 292464 [details] Bugzilla/DB/Schema.pm
Created attachment 292465 [details] extension/redhat/code/db_schema-abstract_schema.pl
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
(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
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
(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.
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
Created attachment 293354 [details] SQL script making some changes needed for checksetup.pl to run properly (v1)
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
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
(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.
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.
> 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
Created attachment 293546 [details] SQL script moving attachment data from attachments table to attach_data table
(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
(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
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
Created attachment 293608 [details] SQL script making some changes needed for checksetup.pl to run properly (v2)
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! ;)
(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
(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
Have checked in all updates into cvs repo. Closing this bugs.