Gemini Community Support Site

This Gemini community support site can be used to find solutions to product issues. You can log in using Open Id, Google Profile and even Facebook. Feel free to ask a question or browse FAQs and documentation. Product tour videos are also available along with how-to videos demonstrating key Gemini capabilities.




after upgrade from 2.2.6 to 3.0.4 all issues are lost in hyper space

web-app

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 'gemini
issuesissuetypeidfk'. 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
deniz
Replies (14)
helpful
0
not helpful

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


Mark Wing
· 9108
Mark Wing
helpful
0
not helpful

We rolled everything back.

We don't know how to proceed here, do you have something like a clean up query etc.?


deniz
· 1
deniz
helpful
0
not helpful

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


Mark Wing
· 9108
Mark Wing
helpful
0
not helpful

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.

 

 


deniz
· 1
deniz
helpful
0
not helpful

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)


Saar Cohen
· 5000
Saar Cohen
helpful
0
not helpful

Still struggeling with the update :(

I get the following err when I run Step1:

 

Server: Msg 547, Level 16, State 1, Line 318
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'geminiissuecomponentscomponentidfk'. The conflict occurred in database 'PQMITSUpdateTest', table 'geminicomponents', column 'componentid'.
The statement has been terminated.

 

Please tell me what I need to do to fix this issue.

Thank you very much in advance


deniz
· 1
deniz
helpful
0
not helpful

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     *
FROM         PQMIssueTrackingUser.issuecomponent
WHERE     (componentid NOT IN
                          (SELECT DISTINCT compid
                            FROM          [PQM
IssueTracking].[PQM_IssueTrackingUser].[components]))


deniz
· 1
deniz
helpful
0
not helpful

Yes, this will show all orphaned issue components.


Saar Cohen
· 5000
Saar Cohen
helpful
0
not helpful

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.
Warning: The table 'geminicustomfielddata' has been created but its maximum row size (8064) 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.
Warning: The table 'gemini_customfielddata' has been created but its maximum row size (8064) 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!


deniz
· 1
deniz
helpful
0
not helpful

Yes, you can safely ignore these.


Mark Wing
· 9108
Mark Wing
helpful
0
not helpful

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!


deniz
· 1
deniz
helpful
0
not helpful

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.


Mark Wing
· 9108
Mark Wing
helpful
0
not helpful

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]


deniz
· 1
deniz
helpful
0
not helpful

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.


deniz
· 1
deniz