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.




DB error migrating from 2.0.2 to 3.6.5

web-app

I have been trying to migrate from 2.0.2 to 3.6.5. by following the steps as below.

1. Restored the 2.0.2 database

2. Followed the steps in section 8.11 v2.0.x steps in installation guide. The scripts executed fine.

3. Followed the steps in sections 8.10.v2.1.x and I get the error copied below.

(Once all the steps are executed from 8.11 till 8.1, I assume the migration scripts have been executed to move to ver 3.6.5.)

----------------------------

alter table personalfilters add affectedversions nvarchar(100) not null default('')

Msg 2705, Level 16, State 4, Line 3
Column names in each table must be unique. Column name 'affectedversions' in table 'personalfilters' is specified more than once.
Msg 2705, Level 16, State 4, Line 3
Column names in each table must be unique. Column name 'comment' in table 'users' is specified more than once.
Msg 2705, Level 16, State 4, Line 1
Column names in each table must be unique. Column name 'active' in table 'users' is specified more than once.


alter table projects add resourcemode numeric(10,0) not null default(0)

Msg 2705, Level 16, State 4, Line 2
Column names in each table must be unique. Column name 'resourcemode' in table 'projects' is specified more than once.
Msg 2705, Level 16, State 4, Line 1
Column names in each table must be unique. Column name 'componentmode' in table 'projects' is specified more than once.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'compid'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'compid'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'compid'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'assignedto'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'assignedto'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'assignedto'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'constraint'.
Msg 3701, Level 11, State 7, Line 2
Cannot drop the index 'issues.ind_compid', because it does not exist or you do not have permission.
Msg 4924, Level 16, State 1, Line 2
ALTER TABLE DROP COLUMN failed because column 'assignedto' does not exist in table 'issues'.

Enn
· 1
Enn
Replies (21)
helpful
0
not helpful

Can you please post the scripts name (and order) that you ran? Which one gave the error?


Saar Cohen
· 5000
Saar Cohen
helpful
0
not helpful

I executed the following:

For v2.0.x

1 Execute “UPGRADEv2-1.sql” in Gemini Issues Database
2 Execute “UPGRADE
v2-2.sql” in Gemini Issues Database
3 Execute “CREATETABLES.sql” in Gemini Issues Database
4 Execute “CREATE
VIEWS.sql” in Gemini Issues Database
5 Execute “CREATEPROCS.sql” in Gemini Issues Database
6 Execute “UPGRADE
v3-0-Step1.sql” in Gemini Issues Database

The above executed without error.

Next I executed (for v2.1.x)

7. Execute “UPGRADE_v2-2.sql” in Gemini Issues Database

which gave error.


Enn
· 1
Enn
helpful
0
not helpful

I think I am wrong when I execute scripts for v2.1.x.

I suppose I should next start from v3.0.0.

Correct me if I am wrong.


Enn
· 1
Enn
helpful
0
not helpful

You need to stop at step 6 (don't run 7). This has upgraded your database to the latest.

 


Saar Cohen
· 5000
Saar Cohen
helpful
0
not helpful

I restored the database and executed the scripts again. I get the following error for

the 6th script : Upgradev3-0-Step1.sql script

-----------------------------

INSERT INTO geminiissueworkflowitemtransition(issueworkflowitemid ,issueworkflowid, targetstatusid)
     SELECT DISTINCT statusid, 1, otherstate FROM issuestatetransitionlut WHERE otherstate!=statusid

Msg 547, Level 16, State 0, Line 181
The INSERT statement conflicted with the FOREIGN KEY constraint "geminiissueworkflowitemtransitionissueworkflowitemidfk". The conflict occurred in database "CounterSoftGemini", table "dbo.geminiissueworkflowitem", column 'issueworkflowitemid'.

INSERT INTO geminiversions (versionid, projectid, versionnumber, versionname, versiondesc, versionreleased, versionorder, versionarchived, created)
 SELECT verid, projid, vernumber, vername, verdesc, verreleased, verorder, verarchived, created FROM versions

Msg 547, Level 16, State 0, Line 219
The INSERT statement conflicted with the FOREIGN KEY constraint "geminiversionsprojectidfk". The conflict occurred in database "CounterSoftGemini", table "dbo.geminiprojects", column 'projectid'.

INSERT INTO geminicomponents (componentid, projectid, componentname, componentdesc, componentreadonly, created)
 SELECT compid, projid, compname, compdesc, compreadonly, created FROM components

Msg 547, Level 16, State 0, Line 225
The INSERT statement conflicted with the FOREIGN KEY constraint "geminicomponentsprojectidfk". The conflict occurred in database "CounterSoftGemini", table "dbo.geminiprojects", column 'projectid'.

INSERT INTO geminiprojectattributes (attributeid, projectid, attributename, attributevalue, attributeorder)
 SELECT attributeid, projid, attributename, attributevalue, attributeorder FROM projectattributes

Msg 547, Level 16, State 0, Line 249
The INSERT statement conflicted with the FOREIGN KEY constraint "geminiprojectattributesprojectidfk". The conflict occurred in database "CounterSoftGemini", table "dbo.geminiprojects", column 'projectid'.

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 gemini
issuerisklevels)
  AND issresolution IN (SELECT resolutionid FROM geminiissueresolutions)

Msg 547, Level 16, State 0, Line 271
The INSERT statement conflicted with the FOREIGN KEY constraint "gemini
issuesversionidfk". The conflict occurred in database "CounterSoftGemini", table "dbo.geminiversions", column 'versionid'.

INSERT INTO geminiprojectattributes (attributeid, projectid, attributename, attributevalue, attributeorder)
 SELECT attributeid, projid, attributename, attributevalue, attributeorder FROM projectattributes

Msg 547, Level 16, State 0, Line 249
The INSERT statement conflicted with the FOREIGN KEY constraint "gemini
projectattributesprojectidfk". The conflict occurred in database "CounterSoftGemini", table "dbo.geminiprojects", column 'projectid'.

 


Enn
· 1
Enn
helpful
0
not helpful

did you run 1 to 5 with no issues?

Very strange as before you didn't get this error, correct?

Try and re-run create_tables and run the script again.


Saar Cohen
· 5000
Saar Cohen
helpful
0
not helpful

When I run the Upgradev3-0-Step1.sql I got the error,

(14 row(s) affected)
Msg 547, Level 16, State 0, Line 181
The INSERT statement conflicted with the FOREIGN KEY constraint "gemini
issueworkflowitemtransitionissueworkflowitemidfk". The conflict occurred in database "Gemini", table "dbo.geminiissueworkflowitem", column 'issueworkflowitemid'.
The statement has been terminated.

I found that there was this error reported in

http://support.countersoft.com/forums/thread/11485.aspx

After removing entries from issuestatetransitionlut which were not available in issuestatuslut I get the following errors:

Msg 547, Level 16, State 0, Line 44
The DELETE statement conflicted with the REFERENCE constraint "gemini
projectsuseridfk". The conflict occurred in database "Gemini", table "dbo.geminiprojects", column 'userid'.
The statement has been terminated.

Msg 2627, Level 14, State 1, Line 48
Violation of PRIMARY KEY constraint 'gemini
useriduseridpk'. Cannot insert duplicate key in object 'dbo.geminiusers'.
The statement has been terminated.

Msg 547, Level 16, State 0, Line 65
The DELETE statement conflicted with the REFERENCE constraint "gemini
projectsissuetypeschemeidfk". The conflict occurred in database "Gemini", table "dbo.geminiprojects", column 'issuetypeschemeid'.
The statement has been terminated.

Msg 2627, Level 14, State 1, Line 88
Violation of PRIMARY KEY constraint 'gemini
issuetypeschemesissuetypeschemeidpk'. Cannot insert duplicate key in object 'dbo.geminiissuetypeschemes'.
The statement has been terminated.

Msg 2627, Level 14, State 1, Line 89
Violation of PRIMARY KEY constraint 'gemini
issuetypeschemesissuetypeschemeidpk'. Cannot insert duplicate key in object 'dbo.geminiissuetypeschemes'.
The statement has been terminated.

Msg 2627, Level 14, State 1, Line 90
Violation of PRIMARY KEY constraint 'gemini
issuetypeschemesissuetypeschemeidpk'. Cannot insert duplicate key in object 'dbo.geminiissuetypeschemes'.
The statement has been terminated.

Msg 547, Level 16, State 0, Line 110
The DELETE statement conflicted with the REFERENCE constraint "gemini
projectsissuepriorityschemeidfk". The conflict occurred in database "Gemini", table "dbo.geminiprojects", column 'issuepriorityschemeid'.
The statement has been terminated.

Msg 2627, Level 14, State 1, Line 125
Violation of PRIMARY KEY constraint 'gemini
issuepriorityschemesissuepriorityschemeidpk'. Cannot insert duplicate key in object 'dbo.geminiissuepriorityschemes'.
The statement has been terminated.

Msg 547, Level 16, State 0, Line 135
The DELETE statement conflicted with the REFERENCE constraint "gemini
projectsissueseverityschemeidfk". The conflict occurred in database "Gemini", table "dbo.geminiprojects", column 'issueseverityschemeid'.
The statement has been terminated.

Msg 2627, Level 14, State 1, Line 151
Violation of PRIMARY KEY constraint 'gemini
issueseverityschemesissueseverityschemeidpk'. Cannot insert duplicate key in object 'dbo.geminiissueseverityschemes'.
The statement has been terminated.

Msg 547, Level 16, State 0, Line 202
The DELETE statement conflicted with the REFERENCE constraint "gemini
projectdocumentsprojectidfk". The conflict occurred in database "Gemini", table "dbo.geminiprojectdocuments", column 'projectid'.
The statement has been terminated.

Msg 2627, Level 14, State 1, Line 204
Violation of PRIMARY KEY constraint 'gemini
projectsprojectidpk'. Cannot insert duplicate key in object 'dbo.geminiprojects'.
The statement has been terminated.

Msg 547, Level 16, State 0, Line 219
The INSERT statement conflicted with the FOREIGN KEY constraint "gemini
versionsprojectidfk". The conflict occurred in database "Gemini", table "dbo.geminiprojects", column 'projectid'.
The statement has been terminated.

Msg 547, Level 16, State 0, Line 225
The INSERT statement conflicted with the FOREIGN KEY constraint "gemini
componentsprojectidfk". The conflict occurred in database "Gemini", table "dbo.geminiprojects", column 'projectid'.
The statement has been terminated.

Msg 2627, Level 14, State 1, Line 239
Violation of PRIMARY KEY constraint 'gemini
projectdocumentsdocumentidpk'. Cannot insert duplicate key in object 'dbo.geminiprojectdocuments'.
The statement has been terminated.

Msg 547, Level 16, State 0, Line 249
The INSERT statement conflicted with the FOREIGN KEY constraint "gemini
projectattributesprojectidfk". The conflict occurred in database "Gemini", table "dbo.geminiprojects", column 'projectid'.
The statement has been terminated.

Msg 547, Level 16, State 0, Line 271
The INSERT statement conflicted with the FOREIGN KEY constraint "gemini
issuesversionidfk". The conflict occurred in database "Gemini", table "dbo.geminiversions", column 'versionid'.
The statement has been terminated.

Msg 547, Level 16, State 0, Line 372
The DELETE statement conflicted with the REFERENCE constraint "gemini
userissuesviewscustomfieldidfk". The conflict occurred in database "Gemini", table "dbo.geminiuserissuesviews", column 'customfieldid'.
The statement has been terminated.

Msg 2627, Level 14, State 1, Line 374
Violation of PRIMARY KEY constraint 'gemini
customfielddefinitionscustomfieldidpk'. Cannot insert duplicate key in object 'dbo.geminicustomfielddefinitions'.
The statement has been terminated.

Msg 547, Level 16, State 0, Line 426
The INSERT statement conflicted with the FOREIGN KEY constraint "gemini
usesettingsuseridfk". The conflict occurred in database "Gemini", table "dbo.gemini_users", column 'userid'.
The statement has been terminated.

 


Enn
· 1
Enn
helpful
0
not helpful

Please re-run hte create_tables.sql script and then the upgrade 3.0 step 1


Saar Cohen
· 5000
Saar Cohen
helpful
0
not helpful

After doing the steps mentioned I get the following errors (reduced to 5 errors)

------------------------

The INSERT statement conflicted with the FOREIGN KEY constraint "geminiversionsprojectidfk". The conflict occurred in database "Gemini", table "dbo.geminiprojects", column 'projectid'.

The statement has been terminated.

(0 row(s) affected)

Msg 547, Level 16, State 0, Line 225

The INSERT statement conflicted with the FOREIGN KEY constraint "geminicomponentsprojectidfk". The conflict occurred in database "Gemini", table "dbo.geminiprojects", column 'projectid'.

The statement has been terminated.

Msg 547, Level 16, State 0, Line 249

The INSERT statement conflicted with the FOREIGN KEY constraint "geminiprojectattributesprojectidfk". The conflict occurred in database "Gemini", table "dbo.geminiprojects", column 'projectid'.

The statement has been terminated.

Msg 547, Level 16, State 0, Line 271

The INSERT statement conflicted with the FOREIGN KEY constraint "geminiissuesversionidfk". The conflict occurred in database "Gemini", table "dbo.geminiversions", column 'versionid'.

The statement has been terminated.

(Msg 547, Level 16, State 0, Line 426

The INSERT statement conflicted with the FOREIGN KEY constraint "geminiusesettingsuseridfk". The conflict occurred in database "Gemini", table "dbo.geminiusers", column 'userid'.

The statement has been terminated.


Enn
· 1
Enn
helpful
0
not helpful

Your old Gemini database has some versions (versions table) that belong to a non-existing project, the same applies to components and project attributes. Please check those records.

You also have some issue that have the fixed in version to an invalid version id. and usersettings that belong to a non existing user.


Saar Cohen
· 5000
Saar Cohen
helpful
0
not helpful

There were some redundant entries in the tables mentioned by you. I removed and executed the CREATETABLES script followed by the Upgradev3-0-Step1 script each time I made changes to the tables.

After following the steps of create / update I have migrated to 3.1 without any errors.

Was it right when I did create_tables followed by upgrade each time for the table changes?

Is it ok to follow the 2 steps for whatsoever tables I modify to rectify the redundant / wrong data?


Enn
· 1
Enn
helpful
0
not helpful

Sorry it should read "After following the steps of create / update I have migrated to 3.0 without any errors"


Enn
· 1
Enn
helpful
0
not helpful

yes, that is all fine.

Make sure you have ran create views and procs.sql


Saar Cohen
· 5000
Saar Cohen
helpful
0
not helpful

Now I try to execute Upgradev3-0-1.sql and I get

Column names in each table must be unique. Column name 'viewtype' in table 'geminiuserissuesviews' is specified more than once.


Enn
· 1
Enn
helpful
0
not helpful

You don't need to run any other upgrade script after 3.0 step 1


Saar Cohen
· 5000
Saar Cohen
helpful
0
not helpful

I need to migrate to 3.6.5 from 2.0.2. My understanding is that once I run scripts for 2.0.2, I need to run scripts for v3.0.0, v3.1.x, v3.5.x  and v3.6. Am I right?

I do not have scripts to migrate from v3.6 to v3.6.5. Where can I get them?


Enn
· 1
Enn
helpful
0
not helpful

No, to migrate from 2.x you need to migrate to 2.2, run create_tables, views and procs and upgrage 3.0 stpe 1 only.


Mark Wing
· 9108
Mark Wing
helpful
0
not helpful

I am confused here. In the installtion guide we have sections for v3.0.0, v3.1.x, v3.5.x  and v3.6. Because I now migrate to 2.x to 3.0, I was under the assumption that scripts under v3.0.0, v3.1.x, v3.5.x  and v3.6 also need to be executed to migrate to v3.6.5.

Is it that these (v3.0.0, v3.1.x, v3.5.x  and v3.6) scripts need to be executed only for the installations which have v3.0.0, v3.1.x, v3.5.x  and v3.6 versions on the system and need to migrate to higher versions?


Enn
· 1
Enn
helpful
0
not helpful

Yes, these are only for 3.x to 3.6.5.
You are migrating from 2.x to 3.6.5 and need to run create_tables which creates a 3.6.5 database.


Mark Wing
· 9108
Mark Wing
helpful
0
not helpful

We are in the process of migrating from 2.0.2 to 3.6.5 and it is in progress now.

I had got the error

"The INSERT statement conflicted with the FOREIGN KEY constraint "geminiissuesversionidfk". The conflict occurred in database "Gemini", table "dbo.geminiversions", column 'versionid'."

In the previous message you had mentioned "You also have some issue that have the fixed in version to an invalid version id"

My issues table had 21956 issues which had fixedinverid as 0 but no verid of value 0 in versions table. So I deleted all these values from issues table. But the Gemini admin wants all those issues intact. How do I proceeed with this?


Enn
· 1
Enn
helpful
0
not helpful

version id of 0 can be left as it will converted to NULL in the target. Just remove those that are non 0.


Mark Wing
· 9108
Mark Wing