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.