Delete plan Portfolio causes connection pool drain

[Yvo]

I'm currently evaluating Portfolio for Jira, but when i try to delete a plan based on boards of a existing project in Jira, several NumActive connections of the connection pool are assigned, but never unassigned. The delete plan action also seems to be stuck somehow, causing Portfolio to be non-responding.

What can i do to fix this, except a restart of Jira?

What logs/info do you require to see what's happening?

Jira software 7.9.2 (server based)

Jira core 7.9.2

Portfolio 2.13.3

[Yvo]

All data is now migrated to our newer SQL database. Jira wouldn't startup with an empty database, so all data was backed up and restored via SQL management studio. Don;t know if it hurts anywhere? The other steps are taken accordingly the link you provided.

Jira is running fine and all health checks add up. Only the log file analyzer won't analyze the log file. I don't really understand why, the log data location etc. aren't changed.

Portfolio keeps showing the exact same behavior. Several database connections are activated, but never removed. Every click on the portfolio tab gives another database connection until all are used.

Here is the latest logfile:

Log file download

[Andy Heinzer]

Hi Zethof,

I am afraid I can't see any clear problems in this log that would account for the behavior you are describing.  As such, I went ahead and created a support request for this issue with our support team.  Please see https://getsupport.atlassian.com/servicedesk/customer/portal/28/JPO-3026 for details, we have requested some addition information in there to help troubleshoot this issue.

Thanks,

Andy

[Yvo]

 would like to say that it's great to find the solution but... I'll update the database tomorrow morning and let you know if it worked. 

Do you have any idea why the same problem already existed in 7.3.2? I thought it still supported SQL 2008 back then.

Would you suggest to run the updater again afterwards? Maybe even 7.9.10?

Thanks for the support!

[Andy Heinzer]

Actually Jira has not supported MS SQL 2008 since 7.1.x versions, see the Jira 7.3 supported platforms documentation.  However we didn't see very many user reported problems in Atlassian support with admins utilizing this database version for Jira 7.2 -> 7.3.x versions.    That's just in regards to Jira itself.

But Portfolio is another story.  This is an Atlassian plugin, but this plugin supports a wide range of base Jira versions.  Right now, I think what happened is you might have had a more recent version of portfolio installed to that older version. In turn you could still see problems specific to Portfolio here, because that plugin is more likely to be using only these updated SQL syntax that don't work with SQL 2008.

I don't think you are going to need to run through the process of upgrading Jira to resolve this.  Simply switching to a supported database and then starting Jira with your data, or importing your previous data via the method I mentioned earlier, should trigger Jira to run through any upgradetasks that might have previously failed.

[Andy Heinzer]

Ah, well yeah, using this specific database version can cause a lot more problems for more recent versions of Jira.   I found this error in your logs:

Caused by: com.querydsl.core.QueryException: Caught SQLServerException for select ACTION.id, ACTION.issueid, ACTION.author, ACTION.actiontype, ACTION.actionlevel, ACTION.rolelevel, ACTION.actionbody, ACTION.created, ACTION.updateauthor, ACTION.updated, ACTION.actionnum
from jiraschema.jiraaction ACTION
where ACTION.issueid = ? and ACTION.actiontype = ?
order by ACTION.created asc, ACTION.id asc
offset ? rows fetch next ? rows only
    at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50)
    at com.querydsl.sql.AbstractSQLQuery.fetch(AbstractSQLQuery.java:502)
...[shortened]...
    at org.codehaus.jackson.map.ser.std.MapSerializer.serializeFields(MapSerializer.java:262)
    ... 261 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'offset'.

The key indicator here is that last part

Incorrect syntax near 'offset'

This happens because Jira 7.9 is using newer SQL syntax that does not work in MS SQL 2008.  Since that database is not supported by Jira anymore, those versions are not expecting to have to try to run legacy SQL commands.   We have seen this problem before too with upgrades to 7.4.x and higher versions too.  I recall specifically that the feature for Jira Software released in that version (kanplan: backlog for kanban boards), that feature doesn't appear for users on MS SQL 2008 because the upgrade task is specifically unable to complete due to this same kind of SQL syntax error.   So I know your concern here was specific to Portfolio, but I think the underlying problem here is much bigger than just Portfolio not working correctly.  I think it is possible (and likely) that your upgrade to Jira didn't really complete entirely because you're using an unsupported database.

The supported platforms for Jira are not just suggestions, these need to be closely adhered to for each version of Jira can have different supported version of these other components.  Otherwise, Jira does not work correctly. I would recommend following these steps to get past this:

1. Backup your data using the XML backup
2. Copy/move that backup file from your $JIRAHOME/export/ folder into the $JIRAHOME/import folder
3. Choose a database type/version from that page, then follow the specific steps in Connecting Jira to a database to create that database and connect it to Jira.
4. Stop Jira
5. Update the $JIRAHOME/dbconfig.xml to point to this new database (instructions on how to do this are in the corresponding Connecting Jira to database documentation)
6. Start Jira - When you start Jira connected to an empty database, it will launch the setup wizard when you visit the page
7. In the setup wizard, select 'import existing data' link and then import that XML backup file you created in the beginning

This way you can migrate your data into a supported platform.   These steps are also explained in Switching Databases.  I suspect this will also correct the other problems here.  But if this does not, let me know.

[Yvo]

<removed link>

It's available for 7 days from today.

[Yvo]

We're using SQL server 2008 R2. Planning to do the update on short term since it isn't supported anymore in 7.9.2 (it was still at 7.3.2 where we came from). 7.3.2 had the same problem though.

I'll upload the logfile when our it guys are in. 

[Andy Heinzer]

I can't seem to recreate this problem.  I'm using the same versions in my test instance, but I also am using a postgresql database on my back end.   I'm not sure what database type you are using with Jira (postgres, mysql, ms sql, or Oracle all have supported versions for Jira).   Please let me know the database type/version you are using here.

I would take a look at the $JIRAHOME/log/atlassian-jira.log file to see what is happening there at the time of this attempt to delete a plan in Portfolio.  You might not see anything in the log at that time, but if that is the case, I'd be interested to look at the entire log file for other clues about your environment that might be a factor here.