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.




Upgrade from 2.2.x to 3.5

migration

Having an issue trying to upgrade from 2.2 to version 3.5. I downloaded 3.5 and ran the new gemini_ table, view, and proc scripts to create the new objects in teh DB. When I run the Step1..3 upgrade scripts contained in the upgrade folder to perform the migration, i get numerous error about pre-existing objects and constarint issues. Do I need to have the ORIGINAL 3.0 DB create scripts to perform the initial new object creates (tables, views, procs) and then can perform the upgrade. Where can I download the original scipts to create a 3.0 database?

MWolfel
· 1
MWolfel
Replies (5)
helpful
0
not helpful

Can you post the errors please?

You should just run the create tables and the 3.0 upgrade scripts and it should work.


Saar Cohen
· 5000
Saar Cohen
helpful
0
not helpful

Here is the process I did and the errors.

Created and restored copy of existing database for Gemini v2.2.6.

From the 3.5 directory of database scripts, first ran in order the following.

  1. Create_tables - ran fine, just a few warnings on table row limits

  2. Create_views - no errors

  3. Create_procs - no errors

I then started the 2.2 to 3.0 migration scripts as per the documentation.

  1. Ran Upgrade_v3-0-Step1.sql

-- generate this error:

Server: Msg 207, Level 16, State 1, Line 385 Invalid column name 'reportedbyuser'.

the problem SQL appears to be this...

--************************** -- Issue Filters --************************** DELETE FROM geminipersonalfilters SET IDENTITYINSERT geminipersonalfilters ON INSERT INTO geminipersonalfilters (filterid, filtername, userid, projectid, projectids, components, versions, affectedversions, resources, issuetypes, issuepriorities, issuestatuses, issueresolutions, risklevels, keywords, excludeclosed, systemfilter, issueids, sortfield, sortdirection, reportedbyuser, createdafter, createdbefore, revisedafter, revisedbefore, created) SELECT filterid, filtername, userid, projid, projids, components, versions, affectedversions, resources, issuetype, issuepriority, issuestatus, issueresolution, risklevel, keywords, excludeclosed, systemfilter, issueid, sortfield, sortdirection, reportedbyuser, createdafter, createdbefore, revisedafter, revisedbefore, created FROM personalfilters WHERE userid IN (SELECT userid FROM geminiusers) AND projid IN (SELECT projectid FROM geminiprojects) SET IDENTITYINSERT geminipersonalfilters OFF

If I ignore the errant migration statement, and run the balance of the Step1 SQL, then I also get these errors...

Server: Msg 547, Level 16, State 1, Line 25 INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'geminiusesettingsuseridfk'. The conflict occurred in database 'esidevtrack', table 'geminiusers', column 'userid'. The statement has been terminated.

(0 row(s) affected)

Server: Msg 547, Level 16, State 1, Line 30 INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'geminiuserissuesviewsuseridfk'. The conflict occurred in database 'esidevtrack', table 'geminiusers', column 'userid'. The statement has been terminated.

I ignored Step2 SQL as i wanted to resetup the securty model as per the documentation.

when running the 3.1 SQL, this is my result

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

Server: Msg 2705, Level 16, State 4, Line 5 Column names in each table must be unique. Column name 'parentcomponentid' in table 'geminicomponents' is specified more than once. Server: Msg 3726, Level 16, State 1, Line 5 Could not drop object 'geminiprojectlabels' because it is referenced by a FOREIGN KEY constraint. Server: Msg 2714, Level 16, State 6, Line 2 There is already an object named 'geminiprojectlabels' in the database. Server: Msg 2705, Level 16, State 4, Line 2 Column names in each table must be unique. Column name 'projectlabelid' in table 'geminiprojects' is specified more than once. Server: Msg 2705, Level 16, State 4, Line 5 Column names in each table must be unique. Column name 'apikey' in table 'gemini_users' is specified more than once.

then running the 3.5 upgrade, i get this

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

Server: Msg 2705, Level 16, State 4, Line 5 Column names in each table must be unique. Column name 'startdate' in table 'geminiversions' is specified more than once. Server: Msg 2705, Level 16, State 4, Line 1 Column names in each table must be unique. Column name 'releasedate' in table 'geminiversions' is specified more than once. Server: Msg 2705, Level 16, State 4, Line 5 Column names in each table must be unique. Column name 'reportedbyuserid' in table 'geminipersonalfilters' is specified more than once. Server: Msg 2705, Level 16, State 4, Line 1 Column names in each table must be unique. Column name 'initialstartdate' in table 'geminipersonalfilters' is specified more than once. Server: Msg 2705, Level 16, State 4, Line 1 Column names in each table must be unique. Column name 'finalstartdate' in table 'geminipersonalfilters' is specified more than once. Server: Msg 2705, Level 16, State 4, Line 1 Column names in each table must be unique. Column name 'initialduedate' in table 'geminipersonalfilters' is specified more than once. Server: Msg 2705, Level 16, State 4, Line 1 Column names in each table must be unique. Column name 'finalduedate' in table 'geminipersonalfilters' is specified more than once. Server: Msg 2705, Level 16, State 4, Line 1 Column names in each table must be unique. Column name 'savedfromreports' in table 'geminipersonalfilters' is specified more than once. Server: Msg 3728, Level 16, State 1, Line 1 'geminipersonalfiltersreportedbyuserdef' is not a constraint. Server: Msg 3727, Level 16, State 1, Line 1 Could not drop constraint. See previous errors. Server: Msg 3726, Level 16, State 1, Line 5 Could not drop object 'geminiissuetimetype' because it is referenced by a FOREIGN KEY constraint. Server: Msg 2714, Level 16, State 6, Line 2 There is already an object named 'geminiissuetimetype' in the database. Server: Msg 2705, Level 16, State 4, Line 5 Column names in each table must be unique. Column name 'timetypeid' in table 'geminitimetracking' is specified more than once.

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

Server: Msg 1913, Level 16, State 1, Line 3 There is already an index on table 'geminiissues' named 'indgeminiissuesparentissueid'.


MWolfel
· 1
MWolfel
helpful
0
not helpful

Ok, all we need to do is concentrate on the errors during the 3.0 upgrade script as this is the only one you need to run.

--**************************
-- Issue Filters
--
**************************
DELETE FROM geminipersonalfilters
SET IDENTITY
INSERT geminipersonalfilters ON
INSERT INTO gemini
personalfilters (filterid, filtername, userid, projectid, projectids, components, versions, affectedversions, resources, issuetypes, issuepriorities, issuestatuses, issueresolutions, risklevels, keywords, excludeclosed, systemfilter, issueids, sortfield, sortdirection, reportedbyuser, createdafter, createdbefore, revisedafter, revisedbefore, created)
SELECT filterid, filtername, userid, projid, projids, components, versions, affectedversions, resources, issuetype, issuepriority, issuestatus, issueresolution, risklevel, keywords, excludeclosed, systemfilter, issueid, sortfield, sortdirection, reportedbyuser, createdafter, createdbefore, revisedafter, revisedbefore, created FROM personalfilters
WHERE userid IN (SELECT userid FROM geminiusers)
AND projid IN (SELECT projectid FROM gemini
projects)
SET IDENTITYINSERT geminipersonalfilters OFF

Please remove the reportedbyuser from both the insert and select statement and that should solve this issue.

 

The 2 errors below are occuring because it seems like you have a userid in the usersettings and userissuesviews that does not exist in the users table. You can remove the records for the user in question from the 2 tables and it should work.


Server: Msg 547, Level 16, State 1, Line 25
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'geminiusesettingsuseridfk'. The conflict occurred in database 'esidevtrack', table 'geminiusers', column 'userid'.
The statement has been terminated.

(0 row(s) affected)

Server: Msg 547, Level 16, State 1, Line 30
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'geminiuserissuesviewsuseridfk'. The conflict occurred in database 'esidevtrack', table 'geminiusers', column 'userid'.
The statement has been terminated.


Saar Cohen
· 5000
Saar Cohen
helpful
0
not helpful

Ok. Looks like I got the database upgrade to run with a few hacks of the old database for integrity issues and the corrections you suggested.

Thanks


MWolfel
· 1
MWolfel
helpful
0
not helpful

Good news, thanks for the update.


Saar Cohen
· 5000
Saar Cohen