Bug 1095970

Summary: RHQ 4.11 upgrade causes failure on agent registration due to bad sequence
Product: [Other] RHQ Project Reporter: John Mazzitelli <mazz>
Component: DatabaseAssignee: Michael Burman <miburman>
Status: CLOSED CURRENTRELEASE QA Contact: Mike Foley <mfoley>
Severity: urgent Docs Contact:
Priority: urgent    
Version: 4.11CC: hrupp, miburman, stianlund+bugzilla
Target Milestone: GA   
Target Release: RHQ 4.12   
Hardware: Unspecified   
OS: Unspecified   
Whiteboard:
Fixed In Version: Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of: Environment:
Last Closed: 2014-12-15 11:36:32 UTC Type: Bug
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: --- Target Upstream Version:
Embargoed:
Bug Depends On:    
Bug Blocks: 1128780, 1129705    

Description John Mazzitelli 2014-05-09 01:17:02 UTC
This is in upgrade step 2.144:

<schema-createSequence name="RHQ_AGENT_INSTALL_SEQ" initial="10001" />

this is the wrong name. It should have _ID_ in the name:

<schema-createSequence name="RHQ_AGENT_INSTALL_ID_SEQ" initial="10001" />

I am going to fix this directly in 2.144 rather than in a new db upgrade step. Here's why.

the dbsetup (for those people installing to a clean, empty DB) get the good sequence name. The problem ONLY happens if you upgrade from an earlier RHQ schema to 2.144. Therefore, those folks that install clean will have a GOOD sequence name and don't need any fix.

If I create a new db upgrade step to rename or delete the bad sequence, those users where nothing is wrong will get errors when they attempt to later upgrade to RHQ 4.12.

For those users where there is a problem (where they upgraded from an older schema to this one with the bad sequence name) they are broken bad - agents won't be able to register. So they need a quick fix - they will have to rename the sequence manually through direct SQL. At this point, they will have a good sequence name, and no further db upgrade steps will be needed for them (just like those folks that installed to a clean DB).

So fixing schema 2.144 will fix people upgrading from 4.10 or earlier to 4.12. 4.11 db upgrade is just bad and requires a manual SQL to fix.

For postgres, the SQL is:

   alter sequence rhq_agent_install_seq rename to rhq_agent_install_id_seq

For oracle, the SQL is:

   rename rhq_agent_install_seq rename to rhq_agent_install_id_seq

Comment 1 John Mazzitelli 2014-05-09 01:18:48 UTC
> If I create a new db upgrade step to rename or delete the bad sequence, those
> users where nothing is wrong will get errors when they attempt to later upgrade > to RHQ 4.12.

Jay reminded me we can do ignoreErrors=true to avoid errors. But I still think its easier and cleaner to fix schema 2.144 inline rather than make another db upgrade step, because that isn't going to help anyone anyway. Those that already upgraded to 4.11 will need to manually apply the SQL regardless. Those that installed 4.11 clean it doesn't matter - they get to schema 2.144 with the good name and any future db upgrade will be fine.

Comment 2 John Mazzitelli 2014-05-09 01:19:54 UTC
git commit to master: a77dc95

Comment 3 John Mazzitelli 2014-05-09 01:53:51 UTC
In the description, I gave a bad Oracle SQL statement. Here's how to manually fix:

For postgres, the SQL is:

   alter sequence rhq_agent_install_seq rename to rhq_agent_install_id_seq

For oracle, the SQL is:

   rename rhq_agent_install_seq to rhq_agent_install_id_seq

Comment 4 Stian Lund 2014-05-11 11:24:21 UTC
Hey thanks for the heads-up.

Would it be possible for me to change it directly in rhq-core-dbutils-4.11.0.jar by modifying db-upgrade.xml before doing the upgrade? This way I could avoid doing changes directly to the DB after the fact?

The file is located two places so not sure if any difference:
org\rhq\rhq-installer-util\main\rhq-core-dbutils-4.11.0.jar
org\rhq\server-startup\main\deployments\rhq.ear\lib\rhq-core-dbutils-4.11.0.jar

Comment 5 John Mazzitelli 2014-05-11 12:31:13 UTC
(In reply to Stian Lund from comment #4)
> Hey thanks for the heads-up.
> 
> Would it be possible for me to change it directly in
> rhq-core-dbutils-4.11.0.jar by modifying db-upgrade.xml before doing the
> upgrade? This way I could avoid doing changes directly to the DB after the
> fact?
> 
> The file is located two places so not sure if any difference:
> org\rhq\rhq-installer-util\main\rhq-core-dbutils-4.11.0.jar
> org\rhq\server-startup\main\deployments\rhq.ear\lib\rhq-core-dbutils-4.11.0.
> jar

Yes, if you go into the db-upgrade.xml and change the schema 2.144 upgrade step, rename the sequence to the proper name and your upgrade should work.

I think the important one to change is the one in the rhq-installer-util's jar. You can change that one, test it to make sure it works on a test machine, and then you know for your real upgrades. Otherwise, just change both. But since I believe its the installer that is the thing that upgrades the schema, I would think its the install-util's jar that has to change.

Comment 6 Michael Burman 2014-08-14 14:52:35 UTC
This issue still persists in 4.12. Release notes have been updated to reflect this. I will reissue the patch to 4.13 (the previous patch was overridden with a bad merge).

Comment 7 Michael Burman 2014-08-14 16:02:46 UTC
Fixed in master:

commit c7d22c367a373dfd51485b13c814565bb0818287
Author: Michael Burman <miburman>
Date:   Thu Aug 14 19:01:34 2014 +0300

    [BZ 1095970] Reapplying patch to fix incorrect sequence name

Comment 8 Heiko W. Rupp 2014-12-15 11:36:32 UTC
Bulk close of items fixed in RHQ 4.12

If you think this is not solved, then please open a *new* BZ and link to this one.