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.




Migration issue

install

Hi,

We are moving from 1.9.1 to 4 and having followed the instructions for migration have come across the following errors when running the final part of the migration script, c:\Program Files\Gemini\Gemini Database\Upgrades\UPGRADE_v3-0-Step1.sql

Msg 207, Level 16, State 1, Line 49 Invalid column name 'comment'.

Msg 207, Level 16, State 1, Line 49 Invalid column name 'active'.

Msg 207, Level 16, State 1, Line 205 Invalid column name 'resourcemode'.

Msg 207, Level 16, State 1, Line 205 Invalid column name 'componentmode'.

Msg 207, Level 16, State 1, Line 397 Invalid column name 'affectedversions'.

Can any one shed some light on what we are doing wrong and/or how we can rectify these issues. Additionally can you confirm that we should be using the 'create.sql' scripts for v4 as it seems odd that these should be run prior to the 'upgrade v3' script.

Many thanks

Chris

chrisb
· 1 		chrisb
Friday, June 3, 2011, 1:24:20 PM
Replies (8)
helpful
1
not helpful

Yes, you have to run create_tables etc... as we have changed all table names. Can you post all scripts you have run and the order?
Mark Wing
· 9108 		Mark Wing
Friday, June 3, 2011, 1:33:27 PM
helpful
0
not helpful

Hi Mark,

Thanks for you prompt response, this is the detail our DBA set me.

  1. Full back up the Gemini database on wswd01.
  2. Restored the backup from 1. as database "GeminiFrom2006" onto aswd02. This included migrating the database to SQL Server 2008 R2.

On database "GeminiFrom2006"

  1. Removed the internal SQL Server logins of XXXX03, XXXXX04 and XXXX07 as I think these were used for testing, years ago.
  2. Removed the login ASPNET as this is not used by IIS 7.
  3. Added login HQ\sqlserveradmin and gave it db_owner privilege.
  4. Had a read of the migration process to take the database from version 1.9.1 to version 4
  5. Ran the following SQL scripts:

Run scripts in c:\Program Files\Gemini\Gemini Database\Upgrades

c:\Program Files\Gemini\Gemini Database\Upgrades\UPGRADE_v2-0.sql

c:\Program Files\Gemini\Gemini Database\Upgrades\UPGRADE_v2-1.sql

c:\Program Files\Gemini\Gemini Database\Upgrades\UPGRADE_v2-2.sql

c:\Program Files\Gemini\Gemini Database\CREATE_TABLES.sql

c:\Program Files\Gemini\Gemini Database\CREATE_VIEWS.sql

c:\Program Files\Gemini\Gemini Database\CREATE_PROCS.sql

c:\Program Files\Gemini\Gemini Database\Upgrades\UPGRADE_v3-0-Step1.sql


chrisb
· 1 		chrisb
Friday, June 3, 2011, 1:42:26 PM
helpful
0
not helpful

Looks like you might have a few schemas in the database and that might be causing the issue. For example if you look at the upgrade 2.2 script (lines 62 and 64) it is adding the active and comment columns to the users table. Can you check that they have been added?


Mark Wing
· 9108 		Mark Wing
Friday, June 3, 2011, 1:59:25 PM
helpful
0
not helpful

To answer the immediate question, table dbo.users has the active nor the comment columns. Regarding multiple schemata, we only appear to have one schemata in the database. The query, "select distinct tableschema from INFORMATIONSCHEMA.TABLES" returns one value: dbo
chrisb
· 1 		chrisb
Friday, June 3, 2011, 2:14:52 PM
helpful
0
not helpful

So the columns are not there? If so, did you get any errors when running the upgrade 2.2 script?
Mark Wing
· 9108 		Mark Wing
Friday, June 3, 2011, 2:18:41 PM
helpful
0
not helpful

Hi Mark,

Our DBA is going to try the whole thing again to see if it works better a second time around. He said he will capture any output and if it fails again we can then send this through to you guys for analysis. Thanks again for you very quick responses.
chrisb
· 1 		chrisb
Friday, June 3, 2011, 2:32:01 PM
helpful
0
not helpful

After trying again, we no longer get the error messages outlined above, the old 1.9.1 projects are now visible in v4, however we cannot see any issues.

The only error message we get is:

Msg 547, Level 16, State 1, Server ASWD02\SQLDEV2, Line 271 The INSERT statement conflicted with the FOREIGN KEY constraint "geminiissuesissuetypeidfk". The conflict occurred in database "geminiFrom20062", table "dbo.gemini_issuetypes", column 'typeid'. The statement has been terminated. issueid

Issue not imported because Project ID is invalid 962

Issue not imported because Project ID is invalid 4446

Issue not imported because Project ID is invalid 4447


chrisb
· 1 		chrisb
Monday, June 6, 2011, 8:39:39 AM
helpful
0
not helpful
ANSWER

This means old issue data did not have a valid project Id was not migrated over.

You can always look at the raw data via the following query:

select * from issues where issueid in (962., 4446, 4447)

However, I don't think that the data is now relevant since you deleted the project that it used to belong to.
Harvey Kandola
· 212 		Harvey Kandola
Monday, June 6, 2011, 9:07:56 AM