Bite my bytes

What I learn by day I blog at night - A blog from Microsoft Consultant working from Ljubljana, Slovenia

  Home :: Contact :: Syndication  
  999 Posts :: 7691 Comments :: 235 Trackbacks

Search

Most popular posts
in last 360 days

Categories

My Projects

Archives

Stuff


Copyright © by David Vidmar
 
Contact me!
 
LinkedIn Profile
 
 
 

I did an upgrade of Jira and the plugins in last two days (which is no fun at all!) and I decided to solve mysterious error that Jira reported at startup since my company ran Jira.

There was an error message for each table in the database like this:

[core.entity.jdbc.DatabaseUtil] Entity "Action" has no table in the database
2003-11-06 09:33:45,265 ERROR [core.entity.jdbc.DatabaseUtil] Could not create table "jiraaction"
2003-11-06 09:33:45,265 ERROR [core.entity.jdbc.DatabaseUtil] SQL Exception while executing the following:
CREATE TABLE jiraaction (ID NUMERIC NOT NULL, issueid NUMERIC, AUTHOR VARCHAR(255), actiontype VARCHAR(255), actionlevel VARCHAR(255), actionbody TEXT, CREATED DATETIME, actionnum NUMERIC, CONSTRAINT PK_jiraaction PRIMARY KEY (ID))
Error was: java.sql.SQLException: There is already an object named 'jiraaction' in the database.

The problem with this error is, that everything works just fine. It's just a cosmetic problem that doesn't even happen that often. It occurs only at startup.

The Jira documentation has a page on how to solve this, but it's clearly WRONG. Some time ago I also tried famous Atlassian Support, but I got same advice and it was wrong again. The diagnose is correct, but the solution isn't. So today I took some time and investigated...

Jira checks for table existence using sp_tables stored procedure and passes current SQL Server user as @table_owner a parameter. The current user is by default not the same as table owner. Table is of course owned by dbo schema.

Official recommendation is to give "jirauser" a db_owner role but not database owner and recreated the tables, but that doesn't solve anything.

The right solution is to this problem is:

  • create database
  • create new login and user
  • create new database schema with same name as the user (same name is important!)
  • make the schema default schema of user on Jira database
  • recreate the tables

Now the tables will be owned by newly created schema and the table existence check at startup will pass. Finally!

If you look hard enough, there is a right solution buried in support pages, but the official docs and support recommendations are just wrong.

 

Technorati tags: , , , , ,
Posted on Wednesday, September 26, 2007 8:30 PM | Filed under: Software Developement |

Feedback

# re: Jira Error - Could not create table ... there is already an object named "tablename" in the database 9/27/2007 7:59 AM Jed Wesley-Smith
Thanks for the feedback. We'll see if we can get the documentation clearer.

One thing we need to know, are you using SQLServer 2000 or 2005? The linked documentation (marked above as clearly wrong) has actually worked for our customers and us in the past for 2000, whereas the one you mark as correct is for 2005. If its the latter that may help explain things.

Unfortunately, while we always try and supply legendary service - due to the large number of target platforms we support we do not always have legendary knowledge of the particular quirks of all of them.

That being said, we appreciate the effort to help get our stuff in the best shape. We rely on people like you who let us know about any issues with their configuration.

# re: Jira Error - Could not create table ... there is already an object named "tablename" in the database 9/27/2007 11:33 AM David
I'm using SQL Server 2005. I'm not 100% sure, but the "old" solution might work for SQL Server 2000. That might explain things, yes.

I was planning to let you know about my findings through issue I have opened on Jira Support, but you were faster. :)

Thanks for a great product!



# [JRA-13799] investigate/improve MS SQL Server setup instructions 1/3/2008 1:16 AM JIRA: JIRA
The only way that I can reproduce this issue to to leave the schema-name out of entityengine.xml. This is is what happens to cause this error:<br /># JIRA is starts for the first time and looks for its tables. Because it does not have a schema, it will l...

Comments have been closed on this topic.