Red Hat Bugzilla – Bug 600464
Execute a schema comparison (upgraded vs. new)
Last modified: 2015-02-01 18:26:19 EST
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
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.
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
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.
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
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.