after upgrade from 2.2.6 to 3.0.4 all issues are lost in hyper space
New issue creation works fine in 3.0.4, but all issues created in 2.2.6 are lost.
While running the scripts we got the following warnings.
Server: Msg 547, Level 16, State 1, Line 251
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'geminiprojectversionattributevaluesattributeidfk'. The conflict occurred in database 'PQMIssueTracking', table 'geminiprojectversionattributes', column 'attributeid'.
The statement has been terminated.
(0 row(s) affected)
Server: Msg 547, Level 16, State 1, Line 260
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'geminiissuesissuetypeidfk'. The conflict occurred in database 'PQMIssueTracking', table 'geminiissuetypes', column 'typeid'.
The statement has been terminated.
(21074 row(s) affected)
Please help, thanks in advance.
deniz
· 1 |
|
Wednesday, September 10, 2008, 4:13:46 PM |
0
|
You have a few project attribute values with project attribute id that does not exist You also have issues with type id that does not exist in the types table. You will have to sort this before finishing the upgrade |
||||
|
0
|
We rolled everything back. We don't know how to proceed here, do you have something like a clean up query etc.? |
||||
|
0
|
Replace the insert into the geminiprojectversionattributevalues: DELETE FROM geminiprojectversionattributevalues SET IDENTITYINSERT geminiprojectversionattributevalues ON INSERT INTO geminiprojectversionattributevalues (attributevalueid, attributeid, projectid, versionid, attributevalue) SELECT attributevalueid, attributeid, projid, verid, attributevalue FROM projectversionattributevalues where attributeid in (select attributeid from projectversionattributes) SET IDENTITYINSERT geminiprojectversionattributevalues OFF And into issues: DELETE FROM geminiissues SET IDENTITYINSERT geminiissues ON INSERT INTO geminiissues (issueid, projectid, fixedinversionid, reportedby, summary, longdesc, issuetypeid, issuepriorityid, issueseverityid, issuestatusid, issueresolutionid, issuerisklevelid, userdata1, userdata2, userdata3, percentcomplete, estimatedays, estimatehours, estimateminutes, startdate, duedate, isprivate, created, revised, resolveddate, closeddate) SELECT issueid, projid, (CASE fixedinverid WHEN 0 THEN NULL ELSE fixedinverid end) as fixedinversionid, ISNULL(userid,-1) as reportedby, summary, longdesc, isstype, isspriority, isspriority as issueseverityid, issstatus, issresolution, risklevel, userdata1, userdata2, userdata3, percentcomplete, estimatedays, estimatehours, estimateminutes, startdate, duedate, isprivate, issues.created, revised, resolveddate, closeddate FROM issues LEFT JOIN geminiusers ON reportedby = userid WHERE projid IN (SELECT projects.projid FROM projects) AND risklevel IN (SELECT risklevelid FROM geminiissuerisklevels) AND issresolution IN (SELECT resolutionid FROM geminiissueresolutions) AND isstype IN (SELECT typeid FROM geminiissuetypes) SELECT 'Issue not imported because Project ID is invalid',issueid FROM issues WHERE projid NOT IN (SELECT projects.projid FROM projects) UNION ALL SELECT 'Issue not imported because Risk Level ID is invalid',issueid FROM issues WHERE risklevel NOT IN (SELECT risklevelid FROM geminiissuerisklevels) UNION ALL SELECT 'Issue not imported because Resolution ID is invalid',issueid FROM issues WHERE issresolution NOT IN (SELECT resolutionid FROM geminiissueresolutions) SELECT 'Issue not imported because Type ID is invalid',issueid FROM issues WHERE isstype NOT IN (SELECT typeid FROM geminiissuetypes) SET IDENTITYINSERT geminiissues OFF |
||||
|
0
|
Hello, thank you very much for your quick respond. We did run the sql statement and run next into this issue: Server: Msg 547, Level 16, State 1, Line 262 INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'geminiissuesversionidfk'. The conflict occurred in database 'PQMIssueTracking', table 'gemini_versions', column 'versionid'. The statement has been terminated.
|
||||
|
0
|
Add this line to the end of the insert statment (at the end of the select) AND fixedinverid = 0 OR fixedinverid IN (SELECT versionid FROM gemini_versions) |
||||
|
0
|
Still struggeling with the update :( I get the following err when I run Step1:
Server: Msg 547, Level 16, State 1, Line 318
Please tell me what I need to do to fix this issue. Thank you very much in advance |
||||
|
0
|
According to this thread: http://community.countersoft.com/forums/thread/8562.aspx the error comes up, because of: According to Saar: You have some issues that have a component that does not exist.
Do I list these kind of issues with the following query?
SELECT * |
||||
|
0
|
Yes, this will show all orphaned issue components. |
||||
|
0
|
Ok, we do not get any errors anymore when executing the 3 + 2 SQL queries. Except this warning when executing: CreateTables.sql. Can they be ignored?
Warning: The table 'geminicustomfielddefinitions' has been created but its maximum row size (8460) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes. (1 row(s) affected) Warning: The table 'geminicustomfielddefinitions' has been created but its maximum row size (8460) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes. Thanks in advance! |
||||
|
0
|
Yes, you can safely ignore these. |
||||
|
0
|
We did the upgrade and did not received any errors. But all issues which had a resources and components assigned, do not have the assigned resource and component anymore. These fields are empty: geminiissueresources and geminiissuecomponents are empty. The original tables: isseuresources and issuecomponents are still there and have the correct values. [EDIT: the following tables are also empty :( affectedversion, customfielddata, customfielddefinitions, customfieldusage, geminifieldvisibilityschemeitemgroups, geminiissuealerts, gemini_issueattachments... and many more.)
Please help asap. Thank you! |
||||
|
0
|
And you have run Upgrade-v3-0-Step1 without errors or warnings? That script moves all the tables to the new schema, try executing the lines that transfer the components and see if it works. |
||||
|
0
|
Our DB was full [li], thats why it failed. After clean up we run it again, everything looks beautyfull now. Thanks for your immediate support!
Cheers [D] |
||||
|
0
|
Maybe helpfull info for next, which should be considered for this kind of major upgrades. This update (2.2.7 to 3.0.4) was a real struggle, cause with this update the DB needs data integrity, and our previous DB was not fullfilling this requirements. It would helped a lot of you could provide a prepartion SQL statment which checks the integrity and shows the user a helpfull msg, like: 1.) Table "issues", has issues with invalid risk id, make sure they refer to valid risk ids. Check in table risklevellut. Same for issue priority, issue tpye, user IDs, component IDs, project IDs, fixed in Version IDs. |
||||
|