[closed] Error while updating CCM application to 4.0.5
I'm back again with some curious error.
we had the situation (during a training) that some work items filed with a visibility restricted (and access restricted) category where visible to users who where no member of the restricted team.
This is the category tree - all categories are restricted. But we take our eyes on "Reihe 4 Category":
Within this team (Core Team/Reihe 4 Team) you see there is no user "eickel_s":
But when I create a query as user "eickel_s" I'm able to see a subset of work items filed against "Reihe 4 Cateroy".
Same for one work item with "Reihe 3 Category". Those work items shouldn't be visible to me as I'm no team member of this team.
Other work items of this category are not visible to me - there are some more - but those 3 work items are visible ... why?
After this we noticed that the full text search for work items didn't work any more.
So I rebuilt all the indexes of ccm and jts but only effect was that the full text search was working again. The visibility problem still existed.
As I wanted to update the server to 4.0.5 this weekend I did not think about this error anymore and started the update process.
The upgrade failed while adding tables to the ccm database With this error message:
My assumption on this is this:
All the work items which are still visible do not have this entry within the context tables. Why? I think those work items where created while the index was corrupt. After rebuilding all the indexes searching was possible again but the entry within the context tables was still missing for those work items.
I switched the category for one of those work items to another category and than back to the restricted one and yes - I lost view within the query.
This means not - as the index is workin' again - setting the restricted category to the work item creates the entry within the context tables and the visibility protection is set correctly.
I'm glad that the update process recognizes such problems and fails in updating until they are repaired.
My problem is now
- How can we monitor if the index is corrupted and how can we repair such a missing entry in the context tables?
- For three work items I can reset the category manually. But if there are more "corrupted work items"? Do I have
to set this manually all the way?
- Why did the index got corrupted?
- How can we ensure that this won't happen again?
At the moment we are using 4.0.4 and SQL server database.
For answers and ideas on this I would be very thankful.
Greetings,
Simon
The question has been closed for the following reason: "The question is answered, right answer was accepted" by eickel Dec 27 '13, 5:01 a.m.
Accepted answer
If a server contains a project area that is set to private (an access control setting other than "Everyone") and the list of members with access is empty (such as the project area hierarchy, or an access list), along with the administrator list, then that project area can sometimes be seen as an invalid context. This causes errors during upgrading.
I restored DB and started old CCM. Then I checked all my project areas, also the archived ones. I found one archived PA with NO projectarea members and NO administrator assigned. I also found three archived project areas without projectarea members but with administrators defined.
I added a member to all four archived PA's and restarted the upgrade.
This time it run though without an error.
here the answer from IBM support I got:
It seems that you are running into known defect 294764 [1].
[1] https://jazz.net/jazz/resource/itemName/com.ibm.team.workitem.WorkItem/294764
If a server contains a project area that is set to private (an access control setting other than "Everyone") and the list of members with access is empty (such as the project area hierarchy, or an access list), along with the administrator list, then that project area can sometimes be seen as an invalid context. This causes errors during upgrading.
This is fixed when upgrading from 4.0.4 and future versions.
You need to add at least one member to such Project Areas to complete the upgrading process.
See [2] for a workaround: https://jazz.net/library/article/1311#00001
If you have too many project areas to check them manually, then you will need to run SQL commands against the DB to get the list of Project Areas in the defective state.
------------------------------------------------------
For RTC:
"select NAME_COL from PROCESS.PROCESS_AREA where JZ_DISCRIMINATOR=1 and CONTEXT_ID not in (select X.CONTEXT_UUID from REPOSITORY.CONTEXT_MEMBERS X UNION select Y.CONTEXT_UUID from REPOSITORY.CONTEXT_SUBCONTEXTS Y)
If you need to determine what the correct value for JZ_DISCRIMINATOR is, run this command to determine the possible values:
select ITEM_ID, JZ_DISCRIMINATOR from PROCESS.PROCESS_AREA
Then, run this command to get the name of one of the process areas:
select NAME_COL from PROCESS.PROCESS_AREA where ITEM_ID='<enter item id from previous SQL here'"
--------------------------------------------------------
For RQM:
select NAME_COL from PROCESS.PROCESS_AREA where JZ_DISCRIMINATOR=7 and CONTEXT_ID not in (select X.CONTEXT_UUID from REPOSITORY.CONTEXT_MEMBERS X UNION select Y.CONTEXT_UUID from REPOSITORY.CONTEXT_SUBCONTEXTS Y)
--------------------------------------------------------
If you find that there are no Project Areas matching the aforementioned criteria, then please send us complete logs of your CLM server and repotools.
Comments
Hi Guido,
thanks for this information.
In my case the problem was the same. By company decision we remove all members of archived Project Areas. So all our archived PAs are without members.
But - in my case I tried to upgrade from 4.0.4 to 4.0.5 ... your answer from IBM sais that this issue should be fixed within 4.0.4 ... this seems to be a wrong information by IBM.
Nevertheless - my update works now - thanks for this :)
Greetings,
Simon
Guido,
I ran into this issue when I was upgrading from 4.0.4 to 4.0.6.
I have restored the DB and as you mentioned started the old CCM i had. Now i don't see any projects.....nothing at all. Could you please tell me if you did any extra steps before you started the old CCM.
One other answer
I believe that more users than you have already such issue. See e.g.
https://jazz.net/jazz/web/projects/Rational%20Team%20Concert#action=com.ibm.team.workitem.viewWorkItem&id=294764
- How can we monitor if the index is corrupted and how can we repair such a missing entry in the context tables?
You can check it in server diagnostic page under JFS Indexs section: https://your-server:9443/ccm/admin#action=com.ibm.team.repository.admin.serverDiagnostics
https://jazz.net/jazz/web/projects/Jazz%20Foundation#action=com.ibm.team.workitem.viewWorkItem&id=155003
- For three work items I can reset the category manually. But if there are more "corrupted work items"? Do I have
to set this manually all the way?
I would create new dummy category, run the query for searching all work items from restricted category and change the category to the dummy one (see answer for Guido Schneider how to bulk modify workitems: https://jazz.net/forum/questions/87399/i-am-using-rtc-3012-is-there-any-way-of-deleting-multiple-workitems-at-one-go) and then rollback to old category.
- Why did the index got corrupted?
Good question :) It might have been any outage, someone has been modified your index files or simply it might be any (unknown) defect in the product. As you have already opened PMR for that, please deliver your jts and ccm log files and let us know when you have observed first the issue with searching.
- How can we ensure that this won't happen again?
We need to know why the index got corrupted to know how we can avoid it in the future. What you can do is checking regularly server diagnostic page.
How to check if the index or some other things are corrupted within database is possible with the "repotool -verify" command.
Once we determine what and why exactly is corrupted, we can consider creating the diagnostic tool to check that.
Even after setting the restriction correctly the problem still exists.
We already unrestricted all categories and at least deleted all work items but the upgrade progress still fails...
Any idea how to fix this error within the database tables?
See defect 294764 with troubleshooting steps:
Please run following query on RQM to find the projects in this state:
SELECT ER.ITEM_ID, 54 from EXECUTION.EXECUTION_RECORD ER LEFT JOIN REPOSITORY.ITEM_TYPES IT ON ER.ITEM_ID = IT.ITEM_UUID WHERE IT.ITEM_UUID IS NULL
and on RTC:
select NAME_COL from PROCESS.PROCESS_AREA where JZ_DISCRIMINATOR=1 and CONTEXT_ID not in (select X.CONTEXT_UUID from REPOSITORY.CONTEXT_MEMBERS X UNION select Y.CONTEXT_UUID from REPOSITORY.CONTEXT_SUBCONTEXTS Y)
if there are no results for the query above, please run following query to find possible JZ_DISCRIMINATOR values:
select ITEM_ID, JZ_DISCRIMINATOR from PROCESS.PROCESS_AREA
The query select NAME_COL from PROCESS.PROCESS_AREA ... will return you all private project areas with no members. There is known defect and the work around is to add any member to these project areas.
Please let me know if my tips helped you.
Comments
Hi Christoph,
thanks for your answer. I tried using those select statements but the second one needs to select NAME from PROCESS ... I think this difference is for SQL Server.
But both statements returned no results and the third entry returned many results with either 74 or 73 as value ... I don't know what this should mean.
Your last sentence was the issue in my case
"There is known defect and the work around is to add any member to these project areas"
After adding members to my archived Project Areas the upgrade process finished successful.
Greetings,
Simon
How to fix:
on database
db2 connect to CCM
db2 "select JZ_DISCRIMINATOR from PROCESS.PROCESS_AREA FETCH FIRST 1 ROWS ONLY"
db2 "select NAME_COL from PROCESS.PROCESS_AREA where JZ_DISCRIMINATOR=<last sql query result> and CONTEXT_ID not in (select X.CONTEXT_UUID from REPOSITORY.CONTEXT_MEMBERS X UNION select Y.CONTEXT_UUID from REPOSITORY.CONTEXT_SUBCONTEXTS Y)"
These are the broken projects
Boot up the app (with new WARs) and go through all of the broken projects:
https://<servername>:<port>/ccm/admin#action=com.ibm.team.process.manageProjectAreas
Search for the project area returned by the SQL query
Go to "Access Control"
Remember the current setting
Switch the project Access Control to "Everyone"
Click "Save"
Switch the project area back to the original setting
Click "Save"
Re-run the sql queries until you have 0 rows returned. Once you do, shut down the app and you may safely re-run the upgrade and it will successfully pass. Your application is now successfully upgraded. Have a beer, you deserve it.
Comments
Simon Eickel
Dec 18 '13, 8:56 a.m.How to check if the index or some other things are corrupted within database is possible with the "repotool -verify" command.
But still we're facing the problem - how to fix this?
Simon Eickel
Dec 18 '13, 8:58 a.m.just one more information:
Even after setting the restriction correctly the problem still exists.
We already unrestricted all categories and at least deleted all work items but the upgrade progress still fails...
Any idea how to fix this error within the database tables?
Guido Schneider
Dec 23 '13, 8:56 a.m.During my 405 upgrade preparation I run into same problem on my staging machine.
For me it would also be interesting, how serious the issue is. The DB seems to be migrated but unstable. Does this mean, just this problem is still in the DB, but the it's not worse than before the upgrade?
@@Simon: have you solved the problem on your site? Will you proceed with 405 upgrade?
Simon Eickel
Dec 27 '13, 4:56 a.m.Hi Guido,
sorry for my late reply but I was on holiday :)
Please see my answer as comment to your answer.