Bug 600464 - Execute a schema comparison (upgraded vs. new)
Summary: Execute a schema comparison (upgraded vs. new)
Alias: None
Product: RHQ Project
Classification: Other
Component: Database
Version: unspecified
Hardware: All
OS: Linux
Target Milestone: ---
: ---
Assignee: Jay Shaughnessy
QA Contact: Corey Welton
Depends On:
Blocks: jon-sprint11-bugs
TreeView+ depends on / blocked
Reported: 2010-06-04 18:56 UTC by Charles Crouch
Modified: 2015-02-01 23:26 UTC (History)
3 users (show)

Fixed In Version: 3.0.0.GA 4.0.0
Doc Type: Bug Fix
Doc Text:
Clone Of:
Last Closed: 2010-07-14 12:39:14 UTC

Attachments (Terms of Use)

Description Charles Crouch 2010-06-04 18:56:31 UTC
In order to try to validate that our dbupgrade steps match those in dbsetup we should test that the actual db schemas (and relevant data) that result are identical.

Options to test (preferably on both postgres and oracle)
1) New 2.3.1 upgraded to 2.4, matches new 2.4
2) New 2.2 upgraded to 2.3.1 upgraded to 2.4, matches new 2.4
3) New 2.2 upgraded to 2.4, matches new 2.4

1) is the key on to test, 2) and 3) are secondary

Comment 1 John Sanda 2010-06-17 19:10:52 UTC
The dbupgrade jon on hudson provides some coverage as it has already uncovered some discrepancies. That job though is only running against postgresql. We should create another dbupgrade hudson job that runs against oracle.

Comment 2 Charles Crouch 2010-06-17 19:38:58 UTC
I think for right now I'd like to see a more brute force approach focusing on test 1) above.

a) For oracle something like this maybe: http://juliusdavies.ca/oracle/oracle-dump.html
b) For postgres, maybe pg_dump -n rhq

An oracle version of the dbupgrade job is a good idea, we should have another BZ to track that and lets get it running (on release and master) as soon as we're through with 2.4

Comment 3 Jay Shaughnessy 2010-07-13 15:37:58 UTC
Change commit: 1d91fa3e29f338c0f9a30092d48052020f993bd1

After some failures I came across a nice free tool for performing Oracle db compare.  See TOYS, here: http://www.impacttoys.com/download.htm.

Using this tool I discovered several discrepancies between latest release-3.0.0 branch databases created from scratch (dbsetup) and those upgraded from JON 2.3.1 (dbsetup-upgrade). Unfortunately, the RHQ 3.0.0.final release had already gone out and depending on the install option dbs using this version could have schema version 2.88 with different schemas.

I've made fixes to both the dbsetup (*-schema.xml) and dbsetup-upgrade to get things in synch, and to handle upgrades from either of the schema version 2.88 dbs resulting from RHQ 3.0.0.final installs.

To do this I added a new attribute "ignoreError=true|false" usable on any of the schema spec tasks. In this way you can perform upgrade steps that may or may not be successfully applied. The default is, of course, "false" and this should be used sparingly, only when you're not sure of the db state (and this should not really ever be the case, but unfortunately it is at the moment).

On a positive note, I checked upgrades going back to JON 2.2.0 and they did not show any discrepancies past those in the JON2.3.1 upgrade.

Note, one difference I noticed for oracle is that dbsetup tables generate primary key indexes with "nologging" and dbsetup-upgrade generates them with "logging".  I'm not planning on changing this. The only real difference is that index rebuild perf will be slower (but safer) for the tables with "logging". Index rebuild is not part of any standard workflow for rhq.

The changes were all applicable to oracle and postgres.  I have not tested postgres upgrades so I would suggest that be done by QA.

Comment 4 Charles Crouch 2010-07-13 17:33:40 UTC
11:23:18 AM) ccrouch: so going back to testing this change...
(11:24:44 AM) ccrouch: for new installs, i guess we need to test each of the subsystems impacted by changes in dbsetup
(11:24:44 AM) ccrouch: for upgrades, we need to test all of the subsystems impacted by changes in dbupgrade
(11:24:44 AM) ccrouch: and for both, test the subsystems impacted by the entity changes
(11:24:48 AM) ccrouch: does that sounds right?
(11:44:04 AM) jshaughn: ccrouch: the changes probably don't impact things too horribly.  there are a couple of removals which are basically cleanup.  There is one foreign key added in the saved search stuff.  Other than that there is a lot of tweaking around nullable settings.  Saved search and alert notification would be the areas of impact

Comment 5 Jay Shaughnessy 2010-07-13 18:24:05 UTC
OK, to help ccrouch sleep at night I've also done a comparison for postgres.  I created two schema dumps (via pg_dump --schema-only), one for an upgraded 2.3.1 db and one for a 3.0.0.GA (i.e. 2.4) db.

I then used the compare tool found here: http://apgdiff.sourceforge.net/ to compare the two dumps.  The tool is far more primitive than the TOYS tool for ora, but it did seem to work and did not detect any differences.  I tested it by editing the dumps to see if it would catch some fake differences and it did, so it seems to be a valid tool.

Comment 6 Corey Welton 2010-07-14 12:39:14 UTC
QA Closing.

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