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.




Gemini 2.0.2 to 3.1.1 Data Migration Problems

web-app

I am upgrading our current Gemini instance to 3.1.1 and am having a little bit of trouble on the last part of the upgrade UPGRADEv3-0-Step1.sql

 

It is successful for the majority of the tables but fails on some with the following errors; 

 

(0 row(s) affected)

Msg 547, Level 16, State 0, Line 251

INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'geminiprojectversionattributevaluesattributeidfk'. The conflict occurred in database 'Gemini', table 'geminiprojectversionattributes', column 'attributeid'.

The statement has been terminated.

 

(0 row(s) affected)

Msg 547, Level 16, State 0, Line 260

INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'geminiissuesissuestatusidfk'. The conflict occurred in database 'Gemini', table 'geminiissuestatus', column 'statusid'.

The statement has been terminated.

 

(0 row(s) affected)

Msg 547, Level 16, State 0, Line 415

INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'geminiusesettingsuseridfk'. The conflict occurred in database 'Gemini', table 'gemini_users', column 'userid'.

The statement has been terminated.

 

 

Can you shed any light on this - mostly everything is ported over except for the issues and a couple of other tables as above.

 
BenYoung
· 1 		BenYoung
Wednesday, January 28, 2009, 3:05:13 AM
Replies (2)
helpful
0
not helpful

Seems like you have project version attribute values with not attributes, the same for status and users.

I suggest excluding these from the upgrdae by adding an SQL in statment (step 1.sql):

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 geminiprojectversionattributes)
SET IDENTITYINSERT geminiprojectversionattributevalues OFF

--*****************************************************************************
-- Issues (add associated issue level data)
--*****************************************************************************
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 issuestatus IN (SELECT statusid FROM geminiissuestatus)


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)
SET IDENTITYINSERT geminiissues OFF

DELETE FROM geminiusersettings
INSERT INTO geminiusersettings (userid, sname, svalue, created)
 SELECT userid, sname, svalue, created FROM usersettings
WHERE userid IN (SELECT userid from geminiusers)
Mark Wing
· 9108 		Mark Wing
Wednesday, January 28, 2009, 11:15:56 AM
helpful
0
not helpful

Hi Mark,

That worked great - with a few modifications to column names for some reason they didnt match up with the above in bold.

Thanks for the quick reply.

Ben
BenYoung
· 1 		BenYoung
Thursday, January 29, 2009, 2:57:47 AM