joe, still doesnt work....
Command (statement 1 of 1) Had an error:
java.sql.SQLException: ORA-01427: single-row subquery returns more than one row (error code=1427)
update rhq_alert_definition recovery set recovery_id = (select toBeRecovered.id from rhq_alert_definition toBeRecovered where toBeRecovered.resource_id = recovery.resource_id and toBeRecovered.parent_id = (select template.id from rhq_alert_definition template where template.id = recovery.recovery_id)) where recovery_id != 0 and parent_id != 0 and recovery_id = (select parent.recovery_id from rhq_alert_definition parent where parent.id = recovery.parent_id)
This is what I would focus on ....
ORA-01427: single-row subquery returns more than one row (error code=1427)
I don't immediately see why that second query would fail. Please execute the following:
select * from rhq_alert_definition
Then put that output here:
And copy the generated URL into this thread.
All i got were a bunch of alerts that wwere fired
is there any specific information you need from the results?
I want all of the results. This table does not represent fired alerts. These are your alert definitions. I'm trying to figure out what might be different in your environment from what I've tested in thus far.
does that link work joe?
Yup, I pulled down your data. However, customer support tickets get priority. ; )
I hope to respond to this by this afternoon (EST).
I've updated RHQ-2150 with the necessary changes. Assuming that the 1,646 rows you uploaded to this thread comprise ALL of your data, then this new query will work for you because I tested it by wiping my local data and using yours.
he changed the add to an and for the end statement you added and it executed correctly, should it now work automatically, or do i need to do anything else? and can i add/delete/modify templates without any problems, or do I need to keep running the script every time i change something
its not working, i ran the following test.
I shutdown an apps server, i got the alert saying it was down, then i turned it back on, and it never re-enabled and original
If you change your recovery alert rules through templates, you need to re-run the script. If you change your recovery alert rules on a resource-by-resource basis, you don't need to run the script.
I've confirmed that recovery alerts work properly at the definition-level, so let's investigate this...if you look at any resource that had recovery alert definitions created on it as the result of apply a template, does one correctly refer to the other at the definition-level now?
i dont think it does because i ran that test i mentioned above and the recovery alert has yet to fire.
i created two templated, one for a jboss app going down, and the other the recovery alert, then I had the person run the query. then i tested it, and the recovery alert still doesnt fire.
"i dont think it does" -- please confirm this.
Go to some resource in your inventory with recovery alert definitions created from your template. The url should look something like "/rhq/resource/alert/listAlertDefinitions.xhtml"
Hover the mouse over your two alerts, you'll see that they link to something along the lines of "/alerts/Config.do?mode=viewRoles&id=500053&ad=100010"
Note the "ad" values for the two alerts. Then go to /admin/test/sql.jsp (or /admin/sql.jsp in older versions of jopr) and execute:
select * from rhq_alert_definition where id IN (ad1, ad2)
Replace ad1 and ad2 with the values you captured from hovering over the links. Confirm that the recovery_id of one of the alert definitions points to the ID of the other alert definition.
ill get hose results soon, but im sure its not working because the recovery alert did not re enable the other one, its till false.