Missing 'protocol' Column Error After Upgrade Due to Incorrect Entity Engines Configuration

Summary

Symptoms

Mail Servers in JIRA Administration is missing an option to edit/add SMTP server.

Diagnosis

1. During startup, JIRA logs similar entries in the atlassian-jira.log:

   1 2 3 4 5 2011-03-24 10:24:51,053 main WARN [core.entity.jdbc.DatabaseUtil] Entity "MailServer" has no table in the database 2011-03-24 10:24:51,053 main ERROR [core.entity.jdbc.DatabaseUtil] Could not create table "mailserver" 2011-03-24 10:24:51,053 main ERROR [core.entity.jdbc.DatabaseUtil] SQL Exception while executing the following: CREATE TABLE mailserver (ID NUMERIC(18,0) NOT NULL, NAME VARCHAR(255), DESCRIPTION TEXT, mailfrom VARCHAR(255), PREFIX VARCHAR(60), smtp_port VARCHAR(60), protocol VARCHAR(60), server_type VARCHAR(60), SERVERNAME VARCHAR(255), JNDILOCATION VARCHAR(255), mailusername VARCHAR(255), mailpassword VARCHAR(255), ISTLSREQUIRED VARCHAR(60), TIMEOUT NUMERIC(18,0), CONSTRAINT PK_mailserver PRIMARY KEY (ID)) Error was: org.postgresql.util.PSQLException: ERROR: relation "mailserver" already exists

2. The mail service reports the following error in the atlassian-jira.log:

   1 2 3 4 5 6 7 8 2011-03-24 10:27:22,392 QuartzWorker-0 ERROR ServiceRunner Backup Service [jira.action.admin.DataExport] Error exporting: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT ID, NAME, DESCRIPTION, mailfrom, PREFIX, smtp_port, protocol, server_type, SERVERNAME, JNDILOCATION, mailusername, mailpassword, ISTLSREQUIRED, TIMEOUT FROM mailserver (ERROR: column "protocol" does not exist Position: 60) org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT ID, NAME, DESCRIPTION, mailfrom, PREFIX, smtp_port, protocol, server_type, SERVERNAME, JNDILOCATION, mailusername, mailpassword, ISTLSREQUIRED, TIMEOUT FROM mailserver (ERROR: column "protocol" does not exist Position: 60) at org.ofbiz.core.entity.jdbc.SQLProcessor.executeQuery(SQLProcessor.java:597) at org.ofbiz.core.entity.GenericDAO.selectListIteratorByCondition(GenericDAO.java:1083) at org.ofbiz.core.entity.GenericHelperDAO.findListIteratorByCondition(GenericHelperDAO.java:178) at org.ofbiz.core.entity.GenericDelegator.findListIteratorByCondition(GenericDelegator.java:1014)

1. There's a missing entry for Entity model schema name in the Database Configuration section printed on startup in atlassian-jira.log:

   1 2 3 4 5 6 7 8 9 ___ Database Configuration _________________ Loading entityengine.xml from : file:/home/bdziedzic/Desktop/atlassian-jira-4.3-standalone/atlassian-jira/WEB-INF/classes/entityengine.xml Entity model field type name : postgres72 Entity model schema name : Database Version : PostgreSQL - 9.0.3 Database Driver : PostgreSQL Native Driver - PostgreSQL 8.4 JDBC3 (build 701) Database URL : jdbc:postgresql://localhost:5432/jira43 Database JNDI address : java:comp/env/jdbc/JiraDS

Cause

On startup, JIRA is unable to create correctly its tables due to the misconfiguration of the database connection in entitiengine.xml.

Solution

Resolution

Follow instruction from our Connecting JIRA to PostgreSQL and ensure that the scheme attribute in entitiengine.xml is specified correctly.

Modification of the file is performed with help of config.sh/bat or manually:

Follow instruction from our Postgres integration documentation page and specify

On the consecutive restart JIRA should report in the log file: