DB error migrating from 2.0.2 to 3.6.5
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 |
|
Tuesday, May 18, 2010, 11:17:48 AM |
0
|
Can you please post the scripts name (and order) that you ran? Which one gave the error? |
||||
|
0
|
I executed the following: For v2.0.x 1 Execute “UPGRADEv2-1.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. |
||||
|
0
|
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. |
||||
|
0
|
You need to stop at step 6 (don't run 7). This has upgraded your database to the latest.
|
||||
|
0
|
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) Msg 547, Level 16, State 0, Line 181 INSERT INTO geminiversions (versionid, projectid, versionnumber, versionname, versiondesc, versionreleased, versionorder, versionarchived, created) Msg 547, Level 16, State 0, Line 219 INSERT INTO geminicomponents (componentid, projectid, componentname, componentdesc, componentreadonly, created) Msg 547, Level 16, State 0, Line 225 INSERT INTO geminiprojectattributes (attributeid, projectid, attributename, attributevalue, attributeorder) Msg 547, Level 16, State 0, Line 249 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) Msg 547, Level 16, State 0, Line 271 INSERT INTO geminiprojectattributes (attributeid, projectid, attributename, attributevalue, attributeorder) Msg 547, Level 16, State 0, Line 249
|
||||
|
0
|
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. |
||||
|
0
|
When I run the Upgradev3-0-Step1.sql I got the error, (14 row(s) affected) 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 Msg 2627, Level 14, State 1, Line 48 Msg 547, Level 16, State 0, Line 65 Msg 2627, Level 14, State 1, Line 88 Msg 2627, Level 14, State 1, Line 89 Msg 2627, Level 14, State 1, Line 90 Msg 547, Level 16, State 0, Line 110 Msg 2627, Level 14, State 1, Line 125 Msg 547, Level 16, State 0, Line 135 Msg 2627, Level 14, State 1, Line 151 Msg 547, Level 16, State 0, Line 202 Msg 2627, Level 14, State 1, Line 204 Msg 547, Level 16, State 0, Line 219 Msg 547, Level 16, State 0, Line 225 Msg 2627, Level 14, State 1, Line 239 Msg 547, Level 16, State 0, Line 249 Msg 547, Level 16, State 0, Line 271 Msg 547, Level 16, State 0, Line 372 Msg 2627, Level 14, State 1, Line 374 Msg 547, Level 16, State 0, Line 426
|
||||
|
0
|
Please re-run hte create_tables.sql script and then the upgrade 3.0 step 1 |
||||
|
0
|
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. |
||||
|
0
|
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. |
||||
|
0
|
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? |
||||
|
0
|
Sorry it should read "After following the steps of create / update I have migrated to 3.0 without any errors" |
||||
|
0
|
yes, that is all fine. Make sure you have ran create views and procs.sql |
||||
|
0
|
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. |
||||
|
0
|
You don't need to run any other upgrade script after 3.0 step 1 |
||||
|
0
|
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? |
||||
|
0
|
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. |
||||
|
0
|
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? |
||||
|
0
|
Yes, these are only for 3.x to 3.6.5. |
||||
|
0
|
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? |
||||
|
0
|
version id of 0 can be left as it will converted to NULL in the target. Just remove those that are non 0. |
||||
|