Jira looking at incorrect tables

[John Perakis]

Testing upgrade of 4.0.2 to 5.0

In order to test this upgrade I did the following

1. Copied the production installation directory i.e c:\Program Files\Altassian to another machine, same location

2. verified this instance code connect to the Production Database and was operating correctly

3. modified the server.xml file to point it at an empty Test DB and verified this worked

4. shutdown the server

5. dropped the test DB and imported a copy of the production DB to this DB\

6. started up my test JIRA

This is where things go south; it appears that starting jira again is creating empty tables in my test DB instead of accessing the populated tables

Any advice?

[Gavin Fowler]

Hi John

I have JIRA 5.x installed - and historically i ran into a similar situation as you when creating a non production instance. My installation is Linux (not windows), but i think you should look for a file called dbconfig.xml (jira home directory) within which you will see the db connection details.

Gavin.

[John Perakis]

Let me clarify

My Jira instances is 4.0.2

and until I can resolve this DB issue I can't test the upgrade to 5.X

Here's a snippet of my server.xml

<Context path="" docBase="${catalina.home}/atlassian-jira" reloadable="false">

<Resource name="jdbc/JiraDS" auth="Container" type="javax.sql.DataSource"

username="JiraUserDev"

password="Jira23desk"

driverClassName="net.sourceforge.jtds.jdbc.Driver"

url="jdbc:jtds:sqlserver://LoftSQL08:1433/Jira-Dev"

maxActive="80"

/>

[Gavin Fowler]

ah...that's right version 4.x used server.xml for the DB config. My expertease is with oracle, rather than MS SQL Server?? I'd be tempted to use Jobin's advice (above) and attempt to connect to the database using the username "JiraUserDev" and an appropriate database client and see what unfolds i.e. can you see the restored table structures with that login, etc. I do know that MS SQl Server 2008 (maybe even 2005), also uses a schema context, which may need specifiying in your server.xml connection string.

That's about all i can offer right now, i'll keep an eye on this thread - best of luck.

Gavin.

[John Perakis]

Some additional information and disclaimers

I'm a DB novice

My DBA tells me the user JiraUser is corrupt on my test Servers DB therefore he created a user JiraUserDev.

I can connect to the DB using Database Browser and can see all the same tables

i.e.

[Jira-Dev].[dbo].[userbase]

[Jira-Dev].[JiraUser].[userbase]

Starting Jira and logging in via the dashboard only works against the dbo tables. How do I get my dashboard to access the JiraUser tables

[Gavin Fowler]

Like i say, I'm no expert on MS SQL Server *but* i suspect you are falling foul of the different versions. I would ask your DBA to restore your production database [SQL 2000] into your dev db instance [SQL 2008] in (a) SQL 2000 compatibility mode or (b) restore the database with the owner "dbo" rather than "JiraUser". LIke i mentioned earlier - you may be falling foul of the new schema context that was added to MS SQL Server 2008 (and possibly 2005). I think "compatibility mode" may mitigate that....you DBA will know more....

[EDIT] You may also want to look at this article that indicates some requisite detail before connecting JIRA 4.x to MS SQL Server 2008. http://confluence.atlassian.com/display/JIRA040/Connecting+JIRA+to+SQL+Server+2008

[Gavin Fowler]

What database & version are you using??

[John Perakis]

My prod DB is running on sql 2000, My test DB which was imported from the prod server is running sql 2008

[John Perakis]

Problem solved we re-imported the db and changed all table names from JiraUser to dbo.

Thanks to all who help!

[Jobin Kuruvilla [Adaptavist]]

If the tables are already there, JIRA shouldn't create new ones. Couple of things to check:

- Verify the DB used and imported to are same

- Verify that the same db user is used

Btw, the new JIRA installed, is it 5.0? or 4.0.2?

[John Perakis]

Thanks Jobin, I'm moving from 4.0.2 to 5.0.

As for the user in name:

In Prod DB the user name of JiraUser; my TEST DB is JiraUserDev. Is that my problem?

[Jobin Kuruvilla [Adaptavist]]

No that shouldn't be the issue. Make sure JiraUserDev has access to the tables!